Connection Pooling

/**
 * Creates a new connection pool
 * @param config - Pool and connection configuration
 */
class Pool extends EventEmitter {
  constructor(config?: PoolConfig);
}

interface PoolConfig extends ClientConfig {
  /** Maximum number of clients in the pool (default: 10) */
  max?: number;
  /** Minimum number of clients in the pool (default: 0) */
  min?: number;
  /** Idle timeout in milliseconds (default: 10000) */
  idleTimeoutMillis?: number;
  /** Connection timeout in milliseconds (default: 0) */
  connectionTimeoutMillis?: number;
  /** Maximum number of uses per connection before recreation */
  maxUses?: number;
  /** Maximum lifetime of connections in seconds */
  maxLifetimeSeconds?: number;
  /** Allow process to exit when pool is idle */
  allowExitOnIdle?: boolean;
  /** Log function for pool events */
  log?: (message: string, level: string) => void;
  /** Client verification function */
  verify?: (client: Client) => Promise<void>;
}

const { Pool } = require('pg');

// Basic pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'myapp',
  password: 'secret',
  port: 5432,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Pool with connection string
const pool2 = new Pool({
  connectionString: 'postgresql://user:password@host:5432/database',
  max: 5
});

// Pool with SSL
const pool3 = new Pool({
  host: 'secure-host.com',
  ssl: true,
  max: 10,
  allowExitOnIdle: true
});

/**
 * Acquire a client from the pool
 * @returns Promise resolving to a PoolClient instance
 */
connect(): Promise<PoolClient>;

interface PoolClient extends Client {
  /** Release the client back to the pool */
  release(err?: Error | boolean): void;
}

// Manual client management
const client = await pool.connect();
try {
  const res = await client.query('SELECT * FROM users');
  console.log(res.rows);
} finally {
  client.release(); // Return client to pool
}

// Release with error (removes client from pool)
const client2 = await pool.connect();
try {
  await client2.query('INVALID SQL');
} catch (err) {
  client2.release(err); // Client will be destroyed
  throw err;
}

/**
 * Execute a SQL query using pool resources
 * @param text - SQL query string
 * @param values - Optional parameter values
 * @param callback - Optional callback for query result
 * @returns Promise resolving to query result
 */
query(text: string, values?: any[], callback?: QueryCallback): Promise<QueryResult>;

/**
 * Execute a SQL query with configuration object
 * @param config - Query configuration
 * @param callback - Optional callback for query result
 * @returns Promise resolving to query result
 */
query(config: QueryConfig, callback?: QueryCallback): Promise<QueryResult>;

// Simple query (client acquired and released automatically)
const res = await pool.query('SELECT NOW()');
console.log(res.rows[0]);

// Parameterized query
const users = await pool.query('SELECT * FROM users WHERE age > $1', [21]);

// Named prepared statement
const result = await pool.query({
  name: 'fetch-user-by-email',
  text: 'SELECT * FROM users WHERE email = $1',
  values: ['user@example.com']
});

// Callback style
pool.query('SELECT COUNT(*) FROM users', (err, result) => {
  if (err) throw err;
  console.log('User count:', result.rows[0].count);
});

// Transaction with manual client management
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
  await client.query('INSERT INTO profiles (user_id) VALUES ($1)', [123]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

// Helper for cleaner transaction syntax
async function withTransaction(pool, callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Usage
const result = await withTransaction(pool, async (client) => {
  const user = await client.query('INSERT INTO users (name) VALUES ($1) RETURNING *', ['Bob']);
  await client.query('INSERT INTO profiles (user_id) VALUES ($1)', [user.rows[0].id]);
  return user.rows[0];
});

/**
 * End all connections in the pool
 * @returns Promise that resolves when all connections are closed
 */
end(): Promise<void>;

// Graceful shutdown
await pool.end();

// With error handling
try {
  await pool.end();
  console.log('Pool has ended');
} catch (err) {
  console.error('Error ending pool', err);
}

interface Pool {
  /** Total number of clients in the pool */
  readonly totalCount: number;
  /** Number of idle clients available for use */
  readonly idleCount: number;
  /** Number of clients currently in use */
  readonly waitingCount: number;
  /** Number of expired clients that will be removed */
  readonly expiredCount: number;
}

// Monitor pool status
console.log(`Pool stats: ${pool.totalCount} total, ${pool.idleCount} idle, ${pool.waitingCount} waiting`);

// Pool status endpoint for health checks
app.get('/health/db', (req, res) => {
  res.json({
    totalConnections: pool.totalCount,
    idleConnections: pool.idleCount,
    waitingRequests: pool.waitingCount
  });
});

// Client lifecycle events
pool.on('connect', (client) => {
  console.log('New client connected');
});

pool.on('acquire', (client) => {
  console.log('Client acquired from pool');
});

pool.on('remove', (client) => {
  console.log('Client removed from pool');
});

pool.on('release', (err, client) => {
  console.log('Client released back to pool');
});

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

// Pool status events
pool.on('drain', () => {
  console.log('Pool has drained');
});

const pool = new Pool({
  // Connection settings
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: parseInt(process.env.DB_PORT) || 5432,
  
  // Pool settings
  max: 10,                      // Maximum connections
  idleTimeoutMillis: 30000,     // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Timeout when acquiring connection
  allowExitOnIdle: true         // Allow process to exit when idle
});

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  
  // SSL in production
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
  
  // Pool sizing based on expected load
  max: parseInt(process.env.DB_POOL_SIZE) || 20,
  min: 2,                       // Minimum connections to maintain
  
  // Timeouts
  idleTimeoutMillis: 10000,     // Close idle connections quickly
  connectionTimeoutMillis: 5000, // Fail fast on connection issues
  
  // Connection health
  maxUses: 7500,                // Recreate connections periodically
  
  // Monitoring
  log: (message, level) => {
    if (level === 'error') {
      console.error('Pool error:', message);
    }
  }
});

type PoolConfig = ClientConfig & {
  max?: number;
  min?: number;
  idleTimeoutMillis?: number;
  connectionTimeoutMillis?: number;
  maxUses?: number;
  maxLifetimeSeconds?: number;
  allowExitOnIdle?: boolean;
  log?: (message: string, level: string) => void;
  verify?: (client: Client) => Promise<void>;
};

interface PoolClient extends Client {
  release(err?: Error | boolean): void;
  processID: number;
  secretKey: number;
}