npm package: @lintangoao/soulcode-api-database
Description: Database clients for MySQL and PostgreSQL with connection pooling
Author: Soulcode API Team
Latest version: 1.0.0

# @lintangoao/soulcode-api-database

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

## Installation

```bash
pnpm add @lintangoao/soulcode-api-database
```

## Usage

### MySQL Client

```typescript
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", "john@example.com"]
);

// 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

```typescript
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", "john@example.com"]
);

// 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

```typescript
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

```typescript
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

```typescript
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