// src/lib/db.ts
import { Pool } from 'pg';

export const db = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false },  // see Step 2
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
});

Pool sizing depends on the runtime:

• Long-lived Node servers (Docker on a VPS): max: 10 per process is plenty. Postgres handles ~100 concurrent connections; if you have 4 containers, that's 40, well under the limit.
• Serverless / Lambda / Cloud Run cold-start: max: 1 per instance. Each cold-start opens a connection; with 100 concurrent invocations you'd exhaust the pool. Use a connection pooler (Supabase Pooler, RDS Proxy, PgBouncer) instead of direct connections.
• Local development: max: 5, idleTimeoutMillis: 5_000, release connections fast so reload-restarts don't pile up zombies.

Default to max: 10 for hosted SaaS. Tune down if you see too many clients already.

Supabase requires SSL but the Node pg driver sometimes can't find the system CA bundle:

ssl: { rejectUnauthorized: false }

This is safe. The connection is still TLS-encrypted (you can confirm with psql ... -c "SHOW ssl"). What you give up is verifying that the server's cert chain back to a trusted root, which would protect against MitM. Inside a VPC or to a trusted Postgres-as-a-service, that's an acceptable trade, you'd notice a MitM through other channels (cert mismatches, DNS hijacking, etc.).

If you need full cert validation (regulated environments), pass the CA bundle explicitly:

import { readFileSync } from 'node:fs';

ssl: {
  rejectUnauthorized: true,
  ca: readFileSync('/etc/ssl/certs/supabase-ca.pem').toString(),
}

Most SaaS skip the CA validation. We do.

Migrations should be re-runnable on every deploy. The DO $$ ... EXCEPTION pattern makes that work:

-- scripts/create-tables.sql
CREATE TABLE IF NOT EXISTS tenants (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       TEXT UNIQUE NOT NULL,
  plan        TEXT NOT NULL DEFAULT 'free',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

DO $$
BEGIN
  ALTER TABLE tenants ADD COLUMN stripe_customer_id TEXT;
EXCEPTION WHEN duplicate_column THEN NULL;
END $$;

DO $$
BEGIN
  ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
EXCEPTION WHEN OTHERS THEN NULL;
END $$;

DO $$
BEGIN
  CREATE POLICY "service_role full access on notes"
    ON notes FOR ALL TO service_role USING (true) WITH CHECK (true);
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE INDEX IF NOT EXISTS notes_tenant_id_idx ON notes (tenant_id);

Three patterns at work:

• CREATE TABLE IF NOT EXISTS, the easy case.
• ALTER TABLE ... ADD COLUMN inside DO $$ ... EXCEPTION WHEN duplicate_column, for additive column changes. Re-runnable.
• ENABLE ROW LEVEL SECURITY isn't natively idempotent, wrap in DO $$ ... EXCEPTION WHEN OTHERS (Postgres doesn't have a specific exception type for it).
• CREATE POLICY uses EXCEPTION WHEN duplicate_object.

Apply via a one-page script:

// scripts/apply-schema.mjs
import { Client } from 'pg';
import { readFileSync } from 'node:fs';

const sql = readFileSync(new URL('./create-tables.sql', import.meta.url), 'utf-8');
const client = new Client({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } });
await client.connect();
await client.query(sql);
await client.end();
console.log('Schema applied.');

Run on every deploy. Idempotent.

If you're using Prisma alongside raw SQL (some MCP servers do), there's a footgun: prisma db push syncs the DB schema to your schema.prisma file. If your Prisma schema only knows about 4 tables but the DB has 100 tables, it deletes the other 96. We've watched this happen, over a hundred tables wiped from a shared DB by an unrelated project's schema push, restored only because backups existed. The lesson is permanent.

Two rules:

• Never run prisma db push --force-reset on a shared DB. Period.
• Run prisma db push only from the project that owns the schema. If your MCP server uses raw SQL on a shared DB, stick to the SQL migration script, don't introduce a Prisma schema for it.

A real production SaaS we maintain went down for 25 hours from this exact pattern. The shape:

// BAD: connection never released on error
async function buggyHandler(req, res) {
  const client = await db.connect();
  const result = await client.query(/* ... */);
  if (result.rows.length === 0) return res.status(404).send('Not found');  // ← leak!
  client.release();
  res.json(result.rows[0]);
}

The early return skips client.release(). After enough requests, the pool is exhausted and the next call hangs. Other services on the same DB start failing too.

Right pattern: try/finally or just use the pool directly:

// GOOD: pool.query auto-releases
async function goodHandler(req, res) {
  const result = await db.query(/* ... */);
  if (result.rows.length === 0) return res.status(404).send('Not found');
  res.json(result.rows[0]);
}

Use db.query() for single-statement work (99% of cases). Only use db.connect() when you need a transaction:

async function withTransaction<T>(fn: (client) => Promise<T>): Promise<T> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();  // always
  }
}

The finally is non-negotiable.

A simple cron (every 10 min) that pages you when connections climb:

// scripts/pg-connections-monitor.mjs
import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } });

const r = await pool.query(`SELECT count(*) AS active FROM pg_stat_activity WHERE state IS NOT NULL`);
const active = Number(r.rows[0].active);
const max = 100;

if (active > 0.85 * max) {
  console.error(`ALERT: ${active}/${max} connections (${Math.round(100 * active / max)}%)`);
  // Send to your alerting channel (Telegram, Slack, PagerDuty)
  process.exit(1);
}

console.log(`OK: ${active}/${max}`);
await pool.end();

Alert at 70%, page at 85%, panic at 95%. By the time you hit too many clients, you're already breaking other services.

Run academy_validate_step. The validator confirms package.json is wired. For DB-specific verification, run the schema apply script:

DATABASE_URL='...' node scripts/apply-schema.mjs
# → Schema applied.

Then check connection limits:

psql "$DATABASE_URL" -c "SELECT count(*) FROM pg_stat_activity WHERE state IS NOT NULL;"

Should be a small number (< 20) when your app is idle. If it's higher, you have a leak.

Common traps

• max: 100 in pool, unintentionally exhausts the DB's connection limit on first burst.
• Forgetting ssl: { rejectUnauthorized: false } for Supabase, error: self-signed certificate. Set it.
• db.connect() without try/finally, guaranteed connection leak. Use db.query() directly when you can.
• prisma db push on shared DB, 111-tables-deleted territory.
• No connection-leak monitor, silent failure mode that takes down dependent services too.
• max_connections mismatch between hosted Postgres and your pool config, defaults vary (Supabase Free is 60, Pro is higher; RDS depends on instance size).

What good looks like

One Pool per process, max: 10, SSL with rejectUnauthorized: false. Migrations as one idempotent SQL file applied on every deploy. db.query() everywhere except real transactions, which use try/finally with client.release(). A cron that watches pool utilization. No connection-leak incident in the last 90 days.

If you ever hit too many clients already, drop everything and find the leak, it doesn't fix itself, and it slowly takes down every service that shares the DB.