# Database connection pool with recovery strategy

- **Pattern:** `ab-001291` (`error-resilience.graceful-degradation-shutdown.database-connection-pool`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-001291
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-001291)

## Why it matters

Without a connection pool, applications create a new database connection for every request — a pattern that fails catastrophically under load. At moderate traffic, the database server exhausts its connection limit and begins rejecting connections, producing 500 errors for all users simultaneously. CWE-400 (uncontrolled resource consumption) applies directly. ISO 25010 reliability.fault-tolerance requires resource management that prevents single-request failures from cascading into service-wide outages; unmanaged connection creation is one of the most common causes of that cascade in vibe-coded applications.

## Severity rationale

Low in development where request concurrency is low, but a systemic failure mode in production — connection exhaustion under modest load produces complete service outages.

## Remediation

Configure a connection pool with explicit `max`, `min`, and `idleTimeoutMillis` settings. For Prisma with PlanetScale or Supabase, set `connection_limit` in the connection string.

```ts
// lib/db.ts — pg connection pool
import { Pool } from 'pg'

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  min: 2,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
})

pool.on('error', (err) => {
  logger.error({ err }, 'Idle database client error')
})
```

For Prisma, append `?connection_limit=10&pool_timeout=10` to `DATABASE_URL`. Serverless environments (Vercel, AWS Lambda) should use PgBouncer or Supabase's connection pooler — each function invocation cannot hold a persistent pool.

## Detection

- **ID:** `database-connection-pool`
- **Severity:** `low`
- **What to look for:** Count all database client initializations. Enumerate which use connection pooling vs. which create new connections per request. For applications with a database, look for connection pool configuration. Check for max/min pool size and reconnection logic after transient failures.
- **Pass criteria:** Database connection pool is configured with max size, min size, and recovery strategy for transient failures. At least 1 connection pool must be configured with a maximum pool size of no more than 20 connections.
- **Fail criteria:** No connection pool configured, or pool lacks recovery strategy.
- **Skip (N/A) when:** The application has no database or uses a serverless database with no pooling configuration.

- **Cross-reference:** For graceful shutdown, see `graceful-shutdown`.
- **Detail on fail:** `"No connection pool configured. Database connection failures may cause cascading failures"` or `"Pool exists but reconnection strategy not documented"`
- **Remediation:** Configure a connection pool:

  ```ts
  // lib/db.ts — connection pool
  import { Pool } from 'pg'
  const pool = new Pool({ max: 10, connectionTimeoutMillis: 5000 })
  ```

  ```ts
  // For Postgres with node-postgres:
  import pg from 'pg'

  const pool = new pg.Pool({
    max: 20, // max connections
    min: 5, // min connections
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
  })

  pool.on('error', (err) => {
    console.error('Unexpected error on idle client', err)
  })

  // For Prisma:
  // connection_limit in DATABASE_URL
  // datasource db {
  //   provider = "postgresql"
  //   url      = env("DATABASE_URL") // Include "?connection_limit=10"
  // }
  ```

## External references

- cwe CWE-400
- iso-25010 reliability.fault-tolerance

Taxons: error-resilience

HTML version: https://auditbuffet.com/patterns/ab-001291