Postgres Connection Pooling Pattern

May contain stale references

This document was audited on 2026-06-22 and contains references that need updating: uses Drizzle ORM, not raw pg; exports differ from description. Cross-check critical details against the current codebase before relying on them.

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

apps/webapp establishes a pool using the pg library (or the Neon serverless driver) encapsulated in packages/db/src/client.ts:

import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Neon serverless pools automatically reuse connections.
  // For CloudSQL we set: max: 10, idleTimeoutMillis: 30000
});

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

The pool is a singleton imported wherever a query is needed. The configuration is driven by environment variables, allowing the same code to run on Neon (serverless) or Cloud SQL (PgBouncer).

Recommended Pattern for apps/ops-mcp

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 apps/ops-mcp

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( in apps/ops-mcp. 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.