Postgres Connection Pooling Pattern

Overview

All services that talk to PostgreSQL must use a shared connection pool instead of opening a new raw pg client per request or per Cloud Run instance. This prevents exhausting the database’s max connections and aligns with the pattern used by apps/webapp.

Current Implementation in @repo/db (packages/db/src/client.ts)

packages/db/src/client.ts provides a singleton Drizzle ORM instance (db) backed by a connection pool. By default it uses @neondatabase/serverless (NeonPool + drizzle-orm/neon-serverless), or standard pg (PgPool + drizzle-orm/node-postgres) when USE_PG_DRIVER=1 is set (for direct TCP connections to GCE/Docker PostgreSQL):

packages/db/src/config.ts

export function getDatabaseConfig(): DatabaseConfig {
  return {
    connectionString: dbEnv.DATABASE_URL,
    max: dbEnv.DB_POOL_MAX || (isProduction() ? 20 : 5),
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
  };
}

packages/db/src/client.ts

import { Pool as NeonPool } from '@neondatabase/serverless';
import { drizzle as drizzleNeon } from 'drizzle-orm/neon-serverless';
import { drizzle as drizzlePg } from 'drizzle-orm/node-postgres';
import pg from 'pg';
import * as schema from './schema/index.js';

const usePgDriver = process.env.USE_PG_DRIVER === '1' || process.env.USE_PG_DRIVER === 'true';

const pool = usePgDriver
  ? new pg.Pool({
      connectionString: config.connectionString,
      max: config.max,
      idleTimeoutMillis: config.idleTimeoutMillis,
      connectionTimeoutMillis: config.connectionTimeoutMillis,
    })
  : new NeonPool({
      connectionString: config.connectionString,
      max: config.max,
      idleTimeoutMillis: config.idleTimeoutMillis,
      connectionTimeoutMillis: config.connectionTimeoutMillis,
    });

export const db = usePgDriver ? drizzlePg(pool, { schema }) : drizzleNeon(pool, { schema });

The Drizzle db instance and underlying pool are singletons imported from @repo/db wherever queries are needed.

Recommended Pattern for Other Services

1. Create a shared pool module similar to packages/db/src/client.ts.
2. Do not instantiate a pool inside request handlers – import the singleton.
3. Configure via DATABASE_URL. When running on Neon, the URL includes the serverless endpoint; for Cloud SQL use the PgBouncer host.
4. Use pool.connect() only for transactions and release the client promptly with finally { client.release(); }.
5. Set sensible limits in production Cloud SQL deployments (e.g., max: 5 per instance) to stay well below the instance’s total connection quota.

Example Usage in Another Service

src/db.ts

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Adjust for CloudSQL if needed
  max: Number(process.env.PG_POOL_MAX) || 5,
  idleTimeoutMillis: 30000,
});

export const query = (sql: string, params?: any[]) => pool.query(sql, params);

src/some-service.ts

import { query } from './db';

export const getUser = async (userId: string) => {
  const { rows } = await query('SELECT * FROM users WHERE id = $1', [userId]);
  return rows[0];
};

Enforcement

• Static analysis: search for new Pool( or new Client( outside packages/db. Ensure only the shared module is used.
• CodeRabbit rule: SQL identifier interpolation – verify that dynamic identifiers are validated.

References

• apps/webapp/src/lib/api/base-handler.ts – shows how the shared pool is used for request‑level DB access.
• packages/db/src/client.ts – the canonical pool implementation.