Connection Pooling

/**
 * Create a new connection pool (Promise-based API)
 * @param config - Pool configuration object or connection string
 * @returns Pool instance
 */
function createPool(config: string | PoolConfig): Pool;

import mariadb from "mariadb";

const pool = mariadb.createPool({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test",
  connectionLimit: 10,
  acquireTimeout: 10000,
  idleTimeout: 1800
});

// Execute query directly on pool
const rows = await pool.query("SELECT * FROM users");

// Get dedicated connection from pool
const connection = await pool.getConnection();
try {
  await connection.query("START TRANSACTION");
  await connection.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  await connection.commit();
} finally {
  await connection.release(); // Return connection to pool
}

interface Pool extends EventEmitter {
  /** Whether the pool is closed */
  closed: boolean;
  
  /** Get connection from pool */
  getConnection(): Promise<PoolConnection>;
  
  /** Execute query on pool connection */
  query<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
  
  /** Execute prepared statement on pool connection */
  execute<T = any>(sql: string | QueryOptions, values?: any): Promise<T>;
  
  /** Execute batch operations on pool connection */
  batch<T = UpsertResult | UpsertResult[]>(sql: string | QueryOptions, values?: any): Promise<T>;
  
  /** Import SQL file using pool connection */
  importFile(config: SqlImportOptions): Promise<void>;
  
  /** Close all connections in pool */
  end(): Promise<void>;
  
  /** Get number of active connections */
  activeConnections(): number;
  
  /** Get total number of connections */
  totalConnections(): number;
  
  /** Get number of idle connections */
  idleConnections(): number;
  
  /** Get number of queued connection requests */
  taskQueueSize(): number;
  
  /** Escape SQL parameter */
  escape(value: any): string;
  
  /** Escape SQL identifier */
  escapeId(identifier: string): string;
  
  /** Pool event listeners */
  on(event: 'acquire', listener: (conn: Connection) => void): Pool;
  on(event: 'connection', listener: (conn: Connection) => void): Pool;
  on(event: 'enqueue', listener: () => void): Pool;
  on(event: 'release', listener: (conn: Connection) => void): Pool;
  on(event: 'error', listener: (err: SqlError) => void): Pool;
}

interface PoolConnection extends Connection {
  /** Release connection back to pool */
  release(): Promise<void>;
}

interface Pool extends EventEmitter {
  closed: boolean;
  
  /** All methods use Node.js callback pattern */
  getConnection(callback: (err: SqlError | null, conn?: PoolConnection) => void): void;
  query<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
  query<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
  execute<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
  execute<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T, meta?: FieldInfo[]) => void): void;
  batch<T>(sql: string | QueryOptions, values: any, callback: (err: SqlError | null, result?: T) => void): void;
  batch<T>(sql: string | QueryOptions, callback: (err: SqlError | null, result?: T) => void): void;
  importFile(config: SqlImportOptions, callback: (err: SqlError | null) => void): void;
  end(callback: (err: SqlError | null) => void): void;
  
  /** Pool statistics remain synchronous */
  activeConnections(): number;
  totalConnections(): number;
  idleConnections(): number;
  taskQueueSize(): number;
  escape(value: any): string;
  escapeId(identifier: string): string;
}

interface PoolConfig extends ConnectionConfig {
  /** Maximum time to wait for connection acquisition (default: 10000ms) */
  acquireTimeout?: number;
  
  /** Maximum number of connections in pool (default: 10) */
  connectionLimit?: number;
  
  /** Idle timeout before connection is released (default: 1800s) */
  idleTimeout?: number;
  
  /** Connection leak detection timeout (default: 0 - disabled) */
  leakDetectionTimeout?: number;
  
  /** Timeout for pool initialization */
  initializationTimeout?: number;
  
  /** Minimum delay between connection validations (default: 500ms) */
  minDelayValidation?: number;
  
  /** Minimum number of idle connections to maintain */
  minimumIdle?: number;
  
  /** Skip connection reset/rollback when returning to pool (default: false) */
  noControlAfterUse?: boolean;
  
  /** Use COM_STMT_RESET when returning connection to pool (default: true) */
  resetAfterUse?: boolean;
}

const pool = mariadb.createPool({
  // Connection settings
  host: "localhost",
  user: "dbuser",
  password: "dbpass",
  database: "myapp",
  
  // Pool settings
  connectionLimit: 20,
  acquireTimeout: 15000,
  idleTimeout: 900, // 15 minutes
  leakDetectionTimeout: 30000, // Log potential leaks after 30s
  
  // Connection validation
  minDelayValidation: 200,
  resetAfterUse: true,
  
  // Performance settings
  compress: true,
  pipelining: true,
  bulk: true
});

// Connection acquired from pool
pool.on('acquire', (connection: Connection) => {
  console.log('Connection acquired:', connection.threadId);
});

// New connection created
pool.on('connection', (connection: Connection) => {
  console.log('New connection created:', connection.threadId);
});

// Connection request enqueued (waiting for available connection)
pool.on('enqueue', () => {
  console.log('Connection request enqueued');
});

// Connection released back to pool
pool.on('release', (connection: Connection) => {
  console.log('Connection released:', connection.threadId);
});

// Pool error
pool.on('error', (err: SqlError) => {
  console.error('Pool error:', err);
});

// Check pool statistics
console.log('Total connections:', pool.totalConnections());
console.log('Active connections:', pool.activeConnections());
console.log('Idle connections:', pool.idleConnections());
console.log('Queued requests:', pool.taskQueueSize());

// Pool health check
if (pool.taskQueueSize() > 10) {
  console.warn('High queue size - consider increasing connectionLimit');
}

if (pool.idleConnections() === 0 && pool.activeConnections() === pool.totalConnections()) {
  console.warn('Pool at maximum capacity');
}

const pool = mariadb.createPool({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test",
  connectionLimit: 10,
  leakDetectionTimeout: 60000 // Log if connection not released after 60s
});

// This will trigger leak detection if connection.release() is forgotten
const connection = await pool.getConnection();
// ... perform operations
// await connection.release(); // Don't forget this!

// Good: Always release connections
const pool = mariadb.createPool(config);

async function processUser(userId: number) {
  const connection = await pool.getConnection();
  try {
    const user = await connection.query("SELECT * FROM users WHERE id = ?", [userId]);
    return user;
  } finally {
    await connection.release(); // Always release in finally block
  }
}

// Better: Use pool methods directly for simple queries
async function getUsers() {
  return await pool.query("SELECT * FROM users");
}

async function transferFunds(fromId: number, toId: number, amount: number) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    
    await connection.query(
      "UPDATE accounts SET balance = balance - ? WHERE id = ?",
      [amount, fromId]
    );
    
    await connection.query(
      "UPDATE accounts SET balance = balance + ? WHERE id = ?", 
      [amount, toId]
    );
    
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    await connection.release();
  }
}