---
title: "Database. Postgres pool, idempotent migrations, the connection-leak trap | Recipe — StudioMeyer Academy"
description: "Pool sizing for serverless vs long-lived servers, ssl rejectUnauthorized for Supabase, idempotent DDL via DO blocks, and the connection-leak pattern that takes down a SaaS silently."
canonical: "https://studiomeyer.academy/en/recipes/7.4-database-pattern"
language: en
markdown_versions: ["https://studiomeyer.academy/en/recipes/7.4-database-pattern.md"]
publisher: "StudioMeyer Academy, https://studiomeyer.academy (llms.txt: https://studiomeyer.academy/llms.txt)"
---

[← Alle Recipes](https://studiomeyer.academy/recipes)

Phase 7 · MCP Server Patterns·7 steps

# Database. Postgres pool, idempotent migrations, the connection-leak trap

Pool sizing for serverless vs long-lived servers, ssl rejectUnauthorized for Supabase, idempotent DDL via DO blocks, and the connection-leak pattern that takes down a SaaS silently.

7 steps0%

Du liest ohne Account. Mit [Login](https://studiomeyer.academy/login?next=/recipes/7.4-database-pattern) speichern wir Step-Fortschritt + Notes.

# Database. Postgres pool, idempotent migrations, the connection-leak trap

The Postgres patterns for an MCP server are 90% the same as for any other Node service, but the 10% differences cause the most painful production incidents. This recipe covers the pool, the migration script, the SSL trap, and the connection-leak that took us down for 25 hours.

### Step 1: One pool per process, sized for your runtime

```typescript
// 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`.

### Step 2: SSL, the Supabase gotcha

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

```typescript
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:

```typescript
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.

### Step 3: Idempotent migrations

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

```sql
-- 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:

```javascript
// 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.

### Step 4: NEVER \`prisma db push\` against a shared DB

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.

### Step 5: The connection-leak trap

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

```typescript
// 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:

```typescript
// 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:

```typescript
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.

### Step 6: Monitor pool exhaustion

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

```javascript
// 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.

### Step 7: Verify

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

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

Then check connection limits:

```bash
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.

Client check · run on your machine

cat package.json 2>/dev/null | python3 -c "import json,sys; p=json.load(sys.stdin); deps=list((p.get(\\"dependencies\\") or {}).keys()); print(\\"sdk:\\", \\"@modelcontextprotocol/sdk\\" in deps); print(\\"bin:\\", bool(p.get(\\"bin\\"))); print(\\"main:\\", bool(p.get(\\"main\\")))" 2>/dev/null || echo "no package.json in cwd"

**Expect:** sdk: True, plus either bin or main is True.

**If stuck:** Run \`npm init -y && npm install @modelcontextprotocol/sdk zod\`, then add \`"bin": { "your-server": "./dist/server.js" }\` to package.json.

[← Logging pattern, stderr-only,](https://studiomeyer.academy/recipes/6.5-logging-pattern) [Error handling, what to surfac →](https://studiomeyer.academy/recipes/7.5-error-handling)

Phase 7

Database pool, error handling, testing patterns

[Alle Phasen →](https://studiomeyer.academy/recipes)

Concepts

[Multi-tenant SaaS](https://studiomeyer.academy/concepts/multi_tenant)

## Sitemap

Public pages of this site as Markdown: [sitemap](https://studiomeyer.academy/sitemap.md). Curated entry point: [llms.txt](https://studiomeyer.academy/llms.txt), full text: [llms-full.txt](https://studiomeyer.academy/llms-full.txt).