Database Setup

Set up Postgres with migrations, seeding, and connection pooling for local + prod.

Why

The database is the foundation that's hardest to change later — every other layer can be rewritten, but migrating live data is a multi-day operation with real risk. Setting up migrations from day one means every schema change is reviewed, versioned, and reversible. Seeding gives every dev the same starting data, so 'works on my machine' includes the data state. Connection pooling prevents the classic 'too many connections' crash under load, which is the #1 production DB outage for early-stage apps. Getting these three right at setup costs 2 hours; fixing them after launch costs 2 days of downtime.

When

Before the first feature that persists data. Takes 2-3 h. Skip for stateless apps (pure compute, no DB).

How

1. Use a managed Postgres for prod (Neon, Supabase, RDS) and docker-compose for local
   - Never run prod Postgres on a VM you manage — backups and patching are solved problems
   - Local: docker-compose with postgres:16 + a healthcheck

2. Set up Prisma or Drizzle as the ORM + migration tool
   - prisma migrate dev --name init creates the first migration
   - Commit migrations/ to git — they're the source of truth for schema

3. Create a seed script (prisma/seed.ts) that inserts deterministic dev data
   - Use stable IDs (crypto.randomUUID with a fixed seed) so tests are reproducible
   - Run on every fresh DB: prisma db seed

4. Configure connection pooling with PgBouncer or the managed provider's pooler
   - Set pool size to 2-10x the number of concurrent requests, not 100
   - Use a transactional pooler mode for serverless (Neon, Supabase)

5. Add a /api/health endpoint that checks DB connectivity
   - SELECT 1 — if it fails, the health check returns 503, and the load balancer drains

Example

// docker-compose.yml (local Postgres)
services:
  db:
    image: postgres:16
    environment: { POSTGRES_PASSWORD: dev, POSTGRES_DB: app }
    ports: ["5432:5432"]
    healthcheck: { test: ["CMD-SHELL", "pg_isready"], interval: "5s" }

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
await prisma.user.upsert({
  where: { email: "[email protected]" },
  create: { email: "[email protected]", role: "ADMIN" },
  update: {},
});

// Health check
export async function GET() {
  try { await prisma.$queryRaw`SELECT 1`; return new Response("ok"); }
  catch { return new Response("db down", { status: 503 }); }
}

Common Problems

⚠Running migrations in the deploy step instead of as a separate gated step, so a failed migration blocks the deploy.
⚠Not seeding the DB, so every dev has different data and bugs are unreproducible.
⚠Opening a new Prisma client per request in serverless, exhausting the connection pool in seconds.
⚠Using prisma db push in prod instead of prisma migrate deploy, losing migration history and rollback ability.

Best Practices

✓Run migrations in a pre-deploy step with a maintenance page, not during the rolling deploy — zero-downtime migrations only work with additive changes.
✓Use a global Prisma client singleton in serverless (globalThis.prisma) to reuse the connection across invocations.
✓Write migrations as additive (add column, add table) and deploy in two phases — backward-compatible first, then the breaking change.
✓Back up the DB before every prod migration (pg_dump or the provider's snapshot) — a 30s backup saves a 3h recovery.