Production-Ready Database Connection Pooling

Database connection pooling is critical for application performance and reliability. Poor connection management leads to connection exhaustion, memory leaks, and degraded performance under load.

Why Connection Pooling Matters

Without pooling:

• Each request creates a new DB connection (~50-100ms overhead)
• Connections aren't reused
• Database hits connection limits quickly
• Memory usage grows unbounded

With pooling:

• ✅ Connections reused across requests
• ✅ Connection creation amortized
• ✅ Bounded resource usage
• ✅ 10-100x performance improvement

Complete Implementation

1. Database Pool Manager

// lib/db-pool.ts
import { Pool, PoolClient, PoolConfig, QueryResult } from 'pg';

/**
 * Configuration for database pool with production-ready defaults
 */
interface DatabasePoolConfig {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  // Pool-specific settings
  min?: number;        // Minimum connections (default: 2)
  max?: number;        // Maximum connections (default: 10)
  idleTimeoutMillis?: number;  // Idle connection timeout (default: 30s)
  connectionTimeoutMillis?: number;  // Wait for connection timeout (default: 0 = no timeout)
  allowExitOnIdle?: boolean;  // Allow process to exit if all idle (default: false)
}

/**
 * Query execution options
 */
interface QueryOptions {
  name?: string;       // Prepared statement name
  timeout?: number;    // Query timeout in milliseconds
  logQuery?: boolean;  // Whether to log the query
}

/**
 * Database pool manager with automatic retries, health checks, and metrics
 */
export class DatabasePool {
  private pool: Pool;
  private config: DatabasePoolConfig;
  private metrics = {
    totalQueries: 0,
    failedQueries: 0,
    totalConnections: 0,
    activeConnections: 0,
    idleConnections: 0,
  };

  constructor(config: DatabasePoolConfig) {
    this.config = config;

    const poolConfig: PoolConfig = {
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      min: config.min ?? 2,
      max: config.max ?? 10,
      idleTimeoutMillis: config.idleTimeoutMillis ?? 30000,
      connectionTimeoutMillis: config.connectionTimeoutMillis ?? 0,
      allowExitOnIdle: config.allowExitOnIdle ?? false,
      // Important: Use application_name for connection tracking
      application_name: process.env.APP_NAME || 'nodejs-app',
    };

    this.pool = new Pool(poolConfig);

    // Set up event handlers
    this.setupEventHandlers();
  }

  /**
   * Set up pool event handlers for monitoring and debugging
   */
  private setupEventHandlers(): void {
    // Track when connections are acquired
    this.pool.on('connect', (client: PoolClient) => {
      this.metrics.totalConnections++;
      console.log('[DB Pool] New client connected', {
        total: this.pool.totalCount,
        idle: this.pool.idleCount,
        waiting: this.pool.waitingCount,
      });
    });

    // Track when connections are removed
    this.pool.on('remove', (client: PoolClient) => {
      console.log('[DB Pool] Client removed', {
        total: this.pool.totalCount,
        idle: this.pool.idleCount,
        waiting: this.pool.waitingCount,
      });
    });

    // Track errors on idle clients
    this.pool.on('error', (err: Error, client: PoolClient) => {
      console.error('[DB Pool] Unexpected error on idle client', err);
      // Don't exit on pool errors - let the pool handle it
    });

    // Track acquisition events
    this.pool.on('acquire', (client: PoolClient) => {
      this.metrics.activeConnections++;
      this.metrics.idleConnections = this.pool.idleCount;
    });

    // Track release events
    this.pool.on('release', (err: Error | undefined, client: PoolClient) => {
      this.metrics.activeConnections--;
      this.metrics.idleConnections = this.pool.idleCount;

      if (err) {
        console.error('[DB Pool] Client released with error', err);
      }
    });
  }

  /**
   * Execute a query with automatic connection management
   */
  async query<T = any>(
    text: string,
    params?: any[],
    options?: QueryOptions
  ): Promise<QueryResult<T>> {
    const startTime = Date.now();
    this.metrics.totalQueries++;

    if (options?.logQuery) {
      console.log('[DB Pool] Executing query:', {
        text: text.substring(0, 100),
        params,
        name: options.name,
      });
    }

    try {
      const queryConfig = {
        text,
        values: params,
        name: options?.name,
        ...(options?.timeout && { statement_timeout: options.timeout }),
      };

      const result = await this.pool.query<T>(queryConfig);

      const duration = Date.now() - startTime;
      if (duration > 1000) {
        console.warn('[DB Pool] Slow query detected', {
          duration,
          text: text.substring(0, 100),
        });
      }

      return result;
    } catch (error) {
      this.metrics.failedQueries++;
      console.error('[DB Pool] Query failed', {
        error: error instanceof Error ? error.message : 'Unknown error',
        text: text.substring(0, 100),
        params,
        duration: Date.now() - startTime,
      });
      throw error;
    }
  }

  /**
   * Execute a transaction with automatic rollback on error
   *
   * @example
   * await db.transaction(async (client) => {
   *   await client.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
   *   await client.query('INSERT INTO profiles (user_id) VALUES ($1)', [userId]);
   * });
   */
  async transaction<T>(
    callback: (client: PoolClient) => Promise<T>
  ): Promise<T> {
    const client = await this.pool.connect();

    try {
      await client.query('BEGIN');
      const result = await callback(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      console.error('[DB Pool] Transaction rolled back', {
        error: error instanceof Error ? error.message : 'Unknown error',
      });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * Get a client from the pool for manual management
   * IMPORTANT: You MUST call client.release() when done!
   *
   * @example
   * const client = await db.getClient();
   * try {
   *   await client.query('SELECT ...');
   * } finally {
   *   client.release(); // Always release!
   * }
   */
  async getClient(): Promise<PoolClient> {
    return this.pool.connect();
  }

  /**
   * Health check for monitoring
   */
  async healthCheck(): Promise<{
    healthy: boolean;
    latency: number;
    pool: {
      total: number;
      idle: number;
      waiting: number;
    };
  }> {
    const startTime = Date.now();

    try {
      await this.pool.query('SELECT 1');
      const latency = Date.now() - startTime;

      return {
        healthy: true,
        latency,
        pool: {
          total: this.pool.totalCount,
          idle: this.pool.idleCount,
          waiting: this.pool.waitingCount,
        },
      };
    } catch (error) {
      return {
        healthy: false,
        latency: Date.now() - startTime,
        pool: {
          total: this.pool.totalCount,
          idle: this.pool.idleCount,
          waiting: this.pool.waitingCount,
        },
      };
    }
  }

  /**
   * Get current pool metrics
   */
  getMetrics() {
    return {
      ...this.metrics,
      pool: {
        total: this.pool.totalCount,
        idle: this.pool.idleCount,
        waiting: this.pool.waitingCount,
      },
    };
  }

  /**
   * Gracefully shut down the pool
   */
  async close(): Promise<void> {
    console.log('[DB Pool] Shutting down connection pool...');
    await this.pool.end();
    console.log('[DB Pool] Connection pool closed');
  }
}

/**
 * Singleton pool instance
 */
let dbPool: DatabasePool | null = null;

/**
 * Get or create the database pool singleton
 */
export function getDbPool(): DatabasePool {
  if (!dbPool) {
    dbPool = new DatabasePool({
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '5432'),
      database: process.env.DB_NAME || 'myapp',
      user: process.env.DB_USER || 'postgres',
      password: process.env.DB_PASSWORD || '',
      min: parseInt(process.env.DB_POOL_MIN || '2'),
      max: parseInt(process.env.DB_POOL_MAX || '10'),
      idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT || '30000'),
    });
  }
  return dbPool;
}

/**
 * Close the database pool (call on app shutdown)
 */
export async function closeDbPool(): Promise<void> {
  if (dbPool) {
    await dbPool.close();
    dbPool = null;
  }
}

2. Repository Pattern with Pool

// repositories/user-repository.ts
import { DatabasePool } from '@/lib/db-pool';

export interface User {
  id: number;
  email: string;
  name: string;
  created_at: Date;
  updated_at: Date;
}

export class UserRepository {
  constructor(private db: DatabasePool) {}

  /**
   * Find user by ID
   */
  async findById(id: number): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE id = $1',
      [id],
      { name: 'find-user-by-id' } // Named prepared statement
    );

    return result.rows[0] || null;
  }

  /**
   * Find user by email
   */
  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db.query<User>(
      'SELECT * FROM users WHERE email = $1',
      [email],
      { name: 'find-user-by-email' }
    );

    return result.rows[0] || null;
  }

  /**
   * Create a new user
   */
  async create(email: string, name: string): Promise<User> {
    const result = await this.db.query<User>(
      `INSERT INTO users (email, name, created_at, updated_at)
       VALUES ($1, $2, NOW(), NOW())
       RETURNING *`,
      [email, name],
      { name: 'create-user' }
    );

    return result.rows[0];
  }

  /**
   * Update user
   */
  async update(id: number, updates: Partial<Pick<User, 'email' | 'name'>>): Promise<User | null> {
    const fields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (updates.email !== undefined) {
      fields.push(`email = $${paramIndex++}`);
      values.push(updates.email);
    }
    if (updates.name !== undefined) {
      fields.push(`name = $${paramIndex++}`);
      values.push(updates.name);
    }

    if (fields.length === 0) {
      return this.findById(id);
    }

    fields.push(`updated_at = NOW()`);
    values.push(id);

    const result = await this.db.query<User>(
      `UPDATE users SET ${fields.join(', ')} WHERE id = $${paramIndex} RETURNING *`,
      values
    );

    return result.rows[0] || null;
  }

  /**
   * Delete user
   */
  async delete(id: number): Promise<boolean> {
    const result = await this.db.query(
      'DELETE FROM users WHERE id = $1',
      [id]
    );

    return (result.rowCount ?? 0) > 0;
  }

  /**
   * Create user with profile in transaction
   */
  async createWithProfile(
    email: string,
    name: string,
    bio: string
  ): Promise<{ user: User; profileId: number }> {
    return this.db.transaction(async (client) => {
      // Insert user
      const userResult = await client.query<User>(
        `INSERT INTO users (email, name, created_at, updated_at)
         VALUES ($1, $2, NOW(), NOW())
         RETURNING *`,
        [email, name]
      );
      const user = userResult.rows[0];

      // Insert profile
      const profileResult = await client.query<{ id: number }>(
        `INSERT INTO profiles (user_id, bio, created_at)
         VALUES ($1, $2, NOW())
         RETURNING id`,
        [user.id, bio]
      );
      const profileId = profileResult.rows[0].id;

      return { user, profileId };
    });
  }
}

3. Usage in API Routes

// app/api/users/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { getDbPool } from '@/lib/db-pool';
import { UserRepository } from '@/repositories/user-repository';

const db = getDbPool();
const userRepo = new UserRepository(db);

/**
 * GET /api/users/:id
 */
export async function GET(request: NextRequest) {
  try {
    const id = parseInt(request.nextUrl.searchParams.get('id') || '');

    if (!id) {
      return NextResponse.json(
        { error: 'User ID required' },
        { status: 400 }
      );
    }

    const user = await userRepo.findById(id);

    if (!user) {
      return NextResponse.json(
        { error: 'User not found' },
        { status: 404 }
      );
    }

    return NextResponse.json({ user });
  } catch (error) {
    console.error('[API] Error fetching user:', error);
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    );
  }
}

/**
 * POST /api/users
 */
export async function POST(request: NextRequest) {
  try {
    const body = await request.json();
    const { email, name, bio } = body;

    // Validation
    if (!email || !name) {
      return NextResponse.json(
        { error: 'Email and name are required' },
        { status: 400 }
      );
    }

    // Create user with profile in transaction
    const result = await userRepo.createWithProfile(email, name, bio || '');

    return NextResponse.json({
      user: result.user,
      profileId: result.profileId,
    }, { status: 201 });
  } catch (error) {
    console.error('[API] Error creating user:', error);

    // Handle unique constraint violation
    if (error instanceof Error && error.message.includes('duplicate key')) {
      return NextResponse.json(
        { error: 'Email already exists' },
        { status: 409 }
      );
    }

    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    );
  }
}

4. Graceful Shutdown

// lib/shutdown.ts
import { closeDbPool } from '@/lib/db-pool';

/**
 * Graceful shutdown handler
 */
export async function gracefulShutdown(signal: string): Promise<void> {
  console.log(`\n[Shutdown] Received ${signal}, starting graceful shutdown...`);

  // Stop accepting new connections
  console.log('[Shutdown] Stopping HTTP server...');
  // server.close() if using custom server

  // Close database pool
  console.log('[Shutdown] Closing database connections...');
  await closeDbPool();

  console.log('[Shutdown] Graceful shutdown complete');
  process.exit(0);
}

// Register shutdown handlers
process.on('SIGTERM', () => gracefulShutdown('SIGTERM'));
process.on('SIGINT', () => gracefulShutdown('SIGINT'));

// Handle uncaught errors
process.on('uncaughtException', (error) => {
  console.error('[Shutdown] Uncaught exception:', error);
  gracefulShutdown('uncaughtException');
});

process.on('unhandledRejection', (reason, promise) => {
  console.error('[Shutdown] Unhandled rejection at:', promise, 'reason:', reason);
  gracefulShutdown('unhandledRejection');
});

Production Configuration

Environment Variables

# Database connection
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_USER=postgres
DB_PASSWORD=secret

# Pool configuration
DB_POOL_MIN=2          # Minimum idle connections
DB_POOL_MAX=10         # Maximum total connections
DB_IDLE_TIMEOUT=30000  # Close idle connections after 30s

# Application
APP_NAME=myapp-api

Recommended Pool Sizes

Development:

• Min: 2
• Max: 5

Production (per instance):

• Min: 5
• Max: 20

Database Limit:

-- Check max connections
SHOW max_connections;  -- Default: 100

-- Calculate per-instance max
max_per_instance = (max_connections - 10) / number_of_instances

Example:

• Database max: 100 connections
• Reserve: 10 (for admin, monitoring)
• App instances: 4
• Per-instance max: (100 - 10) / 4 = 22

Monitoring

Health Check Endpoint

// app/api/health/route.ts
import { getDbPool } from '@/lib/db-pool';

export async function GET() {
  const db = getDbPool();
  const health = await db.healthCheck();
  const metrics = db.getMetrics();

  const status = health.healthy ? 200 : 503;

  return Response.json({
    status: health.healthy ? 'healthy' : 'unhealthy',
    database: {
      latency: health.latency,
      pool: health.pool,
    },
    metrics,
  }, { status });
}

Pool Metrics to Track

// Monitor these metrics
{
  totalQueries: 1523,
  failedQueries: 3,
  totalConnections: 8,
  activeConnections: 2,
  idleConnections: 6,
  pool: {
    total: 8,      // Current total connections
    idle: 6,       // Available for use
    waiting: 0     // Requests waiting for connection
  }
}

Red flags:

• waiting > 0 consistently → Increase max pool size
• failedQueries growing → Check query errors
• latency > 100ms → Database or network issues
• idle = 0 consistently → Pool too small

Common Pitfalls

❌ Connection Leaks

// BAD: Connection never released
async function badQuery() {
  const client = await db.getClient();
  const result = await client.query('SELECT ...');
  // Forgot to release!
  return result.rows;
}

// GOOD: Always release in finally
async function goodQuery() {
  const client = await db.getClient();
  try {
    const result = await client.query('SELECT ...');
    return result.rows;
  } finally {
    client.release(); // Always release!
  }
}

❌ Nested Transactions

// BAD: Can't nest transactions
await db.transaction(async (client) => {
  await db.transaction(async (client2) => {
    // This will fail!
  });
});

// GOOD: Use savepoints for nesting
await db.transaction(async (client) => {
  await client.query('SAVEPOINT sp1');
  try {
    await client.query('INSERT ...');
    await client.query('RELEASE SAVEPOINT sp1');
  } catch (error) {
    await client.query('ROLLBACK TO SAVEPOINT sp1');
    throw error;
  }
});

❌ Long-Lived Connections

// BAD: Holding connection during async work
const client = await db.getClient();
const user = await client.query('SELECT ...');
await sendEmail(user); // Slow operation!
await client.query('UPDATE ...');
client.release();

// GOOD: Release between operations
const user = await db.query('SELECT ...');
await sendEmail(user); // No connection held
await db.query('UPDATE ...');

Testing

Mock Pool for Tests

// __tests__/repositories/user-repository.test.ts
import { UserRepository } from '@/repositories/user-repository';
import { DatabasePool } from '@/lib/db-pool';

// Mock pool
const mockQuery = jest.fn();
const mockTransaction = jest.fn();

const mockPool = {
  query: mockQuery,
  transaction: mockTransaction,
} as unknown as DatabasePool;

describe('UserRepository', () => {
  let repo: UserRepository;

  beforeEach(() => {
    repo = new UserRepository(mockPool);
    jest.clearAllMocks();
  });

  it('should find user by id', async () => {
    const mockUser = { id: 1, email: 'test@example.com', name: 'Test' };
    mockQuery.mockResolvedValue({ rows: [mockUser] });

    const user = await repo.findById(1);

    expect(user).toEqual(mockUser);
    expect(mockQuery).toHaveBeenCalledWith(
      expect.stringContaining('SELECT * FROM users WHERE id = $1'),
      [1],
      expect.any(Object)
    );
  });
});

Summary

✅ Connection pooling is critical for performance and reliability ✅ Always use transactions for multi-step operations ✅ Monitor pool metrics to detect issues early ✅ Release connections in finally blocks ✅ Set appropriate pool sizes based on database limits ✅ Implement graceful shutdown to avoid connection leaks

Production checklist:

• [ ] Pool min/max configured
• [ ] Health check endpoint added
• [ ] Metrics monitoring set up
• [ ] Graceful shutdown handlers registered
• [ ] Connection leak detection enabled
• [ ] Query timeout configured
• [ ] Error handling in place

Further reading:

• PostgreSQL Connection Pooling
• node-postgres Pool Documentation
• Database Reliability Engineering