Database Connection Pooling with Claude Code: Prisma Singleton and PgBouncer

Here's the math that kills production databases: 100 Node.js processes, each with its own Prisma client, each configured for a pool of 10 connections. That's 1,000 connections to PostgreSQL. PostgreSQL's default max_connections is 100. You've overloaded it by 10x, and every request is now competing for a slot that doesn't exist.

Claude Code generates proper pool management when you write the constraints in CLAUDE.md. Here's how.

---

The CLAUDE.md Rules That Drive Correct Pooling

## Database Connection Rules

- **Prisma connection_limit**: Set to `(CPU_COUNT * 2) + 1` per process
- **PostgreSQL max_connections**: Size based on server RAM (default 100; set to 200-400 for production)
- **Multi-process environments**: Use PgBouncer (transaction mode) or Prisma Accelerate — never let each process have its own full pool
- **PrismaClient**: Singleton only — one instance per process, never instantiate per request
- **Hot-reload (dev)**: Use `globalThis` to persist the singleton across module reloads
- **Pool exhaustion handling**: Queue with 30-second timeout, then throw (never silently drop)
- **Monitoring**: Alert when active connections exceed 80% of pool capacity
- **Slack alerts on errors**: Include pool metrics in alert payload

---

Connection Math: How to Calculate Your Limit

Before writing code, get the numbers right.

PostgreSQL max_connections = 100
Reserved for admin/management = 10
Available for application = 90

PM2 workers = 4
Per-process connection_limit = 90 / 4 = 22 → use 20 (buffer for spikes)
pool_timeout = 30 seconds (reject if no connection available within this window)

With these numbers, 4 workers × 20 connections = 80 connections. PostgreSQL has 20 left for admin tools, migrations, and monitoring queries.

If you skip this math and let Prisma default to 10 connections per process, you're at 40 connections — fine. But if you forget the singleton pattern and instantiate a new client per request, you're creating hundreds of pools with dozens of connections each, and PostgreSQL falls over.

---

src/lib/prisma.ts: The Singleton Pattern

import { PrismaClient } from '@prisma/client';

const createPrismaClient = () => {
  const cpuCount = require('os').cpus().length;
  const connectionLimit = cpuCount * 2 + 1;

  const url = new URL(process.env.DATABASE_URL!);
  url.searchParams.set('connection_limit', String(connectionLimit));
  url.searchParams.set('pool_timeout', '30');

  return new PrismaClient({
    datasources: {
      db: { url: url.toString() },
    },
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });
};

// globalThis singleton: survives hot-reload in development
const globalForPrisma = globalThis as unknown as {
  __prismaClient: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.__prismaClient ?? createPrismaClient();

// Only cache on globalThis in development
// In production, module cache is stable — no need for globalThis
if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.__prismaClient = prisma;
}

Why globalThis in development but not production?

In development, Next.js and similar frameworks reload modules on file change. Without globalThis, every reload creates a new PrismaClient with a new connection pool. After 20 hot-reloads, you have 20 pools with 20 connections each. PostgreSQL starts refusing connections.

In production, the module cache is stable. Once the process starts, prisma.ts is evaluated once and the singleton is module-cached forever. globalThis is unnecessary overhead.

---

getDatabaseMetrics(): Monitor Before It's Too Late

export async function getDatabaseMetrics() {
  const metrics = await prisma.$metrics.json();

  const active = metrics.gauges.find(
    (g) => g.key === 'prisma_pool_connections_open'
  )?.value ?? 0;

  const idle = metrics.gauges.find(
    (g) => g.key === 'prisma_pool_connections_idle'
  )?.value ?? 0;

  const busy = metrics.gauges.find(
    (g) => g.key === 'prisma_pool_connections_busy'
  )?.value ?? 0;

  return { active, idle, busy };
}

// Monitor every minute
setInterval(async () => {
  const metrics = await getDatabaseMetrics();
  const cpuCount = require('os').cpus().length;
  const poolLimit = cpuCount * 2 + 1;
  const utilizationPct = (metrics.busy / poolLimit) * 100;

  if (utilizationPct > 80) {
    console.warn(`[DB] Pool at ${utilizationPct.toFixed(1)}% capacity`, metrics);

    // Slack alert
    await fetch(process.env.SLACK_WEBHOOK_URL!, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        text: `DB pool warning: ${utilizationPct.toFixed(1)}% utilized`,
        attachments: [{ text: JSON.stringify(metrics) }],
      }),
    });
  }
}, 60_000);

The 80% threshold gives you time to react. At 100%, new requests start timing out after pool_timeout seconds (30s in our config). At 80%, you have time to scale up or investigate the slow query that's holding connections open.

---

PgBouncer: For Multi-Process Environments

When you have more than a handful of PM2 workers, or when you're running serverless functions, PgBouncer handles the math for you.

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

Key settings:

• pool_mode = transaction: PgBouncer holds a server connection only for the duration of a transaction. Between transactions, the connection returns to the pool. This is the correct mode for Prisma — it means 1,000 client connections can share 20 server connections if they're not all in a transaction simultaneously.
• max_client_conn = 1000: How many client-side connections PgBouncer accepts. Set high — clients connect to PgBouncer, which manages the smaller server pool.
• default_pool_size = 20: Server-side connections to PostgreSQL. This is what PostgreSQL actually sees.

With PgBouncer in front:

• Your app connects to PgBouncer (port 6432)
• PgBouncer maintains 20 real connections to PostgreSQL
• 100 Node.js workers can share those 20 connections in transaction mode
• PostgreSQL sees 20 connections instead of 1,000

---

The Complete Picture

PM2 Workers (4x)
  └─ PrismaClient singleton (per worker)
       └─ connection_limit = CPU*2+1 = 9
            └─ PgBouncer (port 6432)
                 └─ PostgreSQL (20 server connections)
                      └─ max_connections = 100 (80 available to app)

Each worker has a pool of 9. All 4 workers share PgBouncer's 20 server connections in transaction mode. PostgreSQL is never overwhelmed.

---

Summary

The pattern: CLAUDE.md defines the pooling contract → Claude Code generates code that follows it → no more connection storms in production.

The four pieces that matter:

1. Connection math first — calculate connection_limit before writing a line of code
2. globalThis singleton — one PrismaClient per process, dev hot-reload safe
3. 80% monitoring threshold — alert before exhaustion, not after
4. PgBouncer in transaction mode — for multi-process and serverless environments

If you want a pre-built prompt that generates this pattern (plus query optimization, index analysis, and N+1 detection) in one Claude Code run, it's in the Code Review Pack on prompt-works.jp — search /code-review. The pack covers connection pooling, query patterns, and production readiness checks.

---

What's caused your worst database overload in production — connection pools, N+1 queries, or missing indexes?