@lintangoao/soulcode-api-database

Database clients untuk MySQL dan PostgreSQL dengan connection pooling dan transaction support.

Installation

pnpm add @lintangoao/soulcode-api-database

Usage

MySQL Client

import { MySQLClient } from "@lintangoao/soulcode-api-database";

const mysql = new MySQLClient({
  host: "localhost",
  port: 3306,
  user: "root",
  password: "password",
  database: "mydb",
  connectionLimit: 10, // optional, default: 10
});

// Query
const users = await mysql.query("SELECT * FROM users WHERE id = ?", [1]);

// Execute
const result = await mysql.execute(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["John", "[email protected]"]
);

// Transaction
await mysql.transaction(async (connection) => {
  await connection.execute("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  await connection.execute("INSERT INTO logs (action) VALUES (?)", ["user_created"]);
});

// Disconnect
await mysql.disconnect();

PostgreSQL Client

import { PostgreSQLClient } from "@lintangoao/soulcode-api-database";

const postgres = new PostgreSQLClient({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "password",
  database: "mydb",
  max: 10, // optional, default: 10
});

// Query
const users = await postgres.query("SELECT * FROM users WHERE id = $1", [1]);

// Execute
const result = await postgres.execute(
  "INSERT INTO users (name, email) VALUES ($1, $2)",
  ["John", "[email protected]"]
);

// Transaction
await postgres.transaction(async (client) => {
  await client.query("INSERT INTO users (name) VALUES ($1)", ["Alice"]);
  await client.query("INSERT INTO logs (action) VALUES ($1)", ["user_created"]);
});

// Disconnect
await postgres.disconnect();

Configuration

MySQL Config

interface MySQLConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  connectionLimit?: number;       // default: 10
  waitForConnections?: boolean;   // default: true
  queueLimit?: number;           // default: 0
}

PostgreSQL Config

interface PostgreSQLConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  max?: number;                    // default: 10
  idleTimeoutMillis?: number;      // default: 30000
  connectionTimeoutMillis?: number; // default: 2000
}

Methods

Common Methods (Both Clients)

query<T>(sql: string, params?: any[]): Promise<T[]>

Execute SELECT query dan return rows

execute(sql: string, params?: any[]): Promise<any>

Execute INSERT, UPDATE, DELETE dan return result

disconnect(): Promise<void>

Close connection pool

MySQL Specific

getConnection(): Promise<PoolConnection>

Get dedicated connection dari pool

transaction<T>(callback): Promise<T>

Execute multiple queries dalam transaction

PostgreSQL Specific

getClient(): Promise<PoolClient>

Get dedicated client dari pool

transaction<T>(callback): Promise<T>

Execute multiple queries dalam transaction

Features

• ✅ Connection pooling untuk performa optimal
• ✅ Type-safe queries dengan TypeScript generics
• ✅ Transaction support
• ✅ Auto error logging
• ✅ Clean API yang konsisten
• ✅ Logger integration dengan @lintangoao/soulcode-api-core

Example dengan Type Safety

interface User {
  id: number;
  name: string;
  email: string;
}

// Type-safe query
const users = await mysql.query<User>(
  "SELECT * FROM users WHERE status = ?",
  ["active"]
);

users.forEach(user => {
  console.log(user.name); // TypeScript knows this is string
});

License

MIT