35 — Running Databases in Practice — Docker, Seeds, Backups and Pools

Level: Intermediate · Time: ~35 min · Prerequisites: Lesson 34 — Repositories, Transactions and Data Modelling

---

Why this matters

Lesson 34 gave you repositories and transactions, and every example assumed a PostgreSQL that is simply there. In practice the database is a service you start, configure, seed, back up and monitor, and one day restore at 22:00 while somebody waits. None of that checks types, and all of it decides whether your data still exists next month. Running the server in Docker makes it reproducible, and the habits below make it fit to hold real production figures once the server is reproducible.

A real PostgreSQL, in one file

# docker-compose.yml
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: solar
      POSTGRES_DB: solar_dev
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?POSTGRES_PASSWORD is not set — copy .env.example to .env}
    ports:
      - "5432:5432" # host:container — the host port is what DATABASE_URL uses
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U solar -d solar_dev"]
volumes:
  pgdata:

The .env holds POSTGRES_PASSWORD and DATABASE_URL="postgresql://solar:...@localhost:5432/solar_dev?connection_limit=5", while the committed .env.example holds a placeholder; refusing to start without the password is deliberate.

Command	What it does
docker compose up -d	creates and starts the service in the background
docker compose ps	container state, including (healthy) from the healthcheck
docker compose logs -f db	follows the server log — the first place to look when a connection fails
docker compose exec db psql -U solar -d solar_dev	an interactive SQL shell inside the container
docker compose down	removes the containers; the named volume, and your data, survive
docker compose down -v	removes the volume too — every row is gone, the reset for a schema beyond repair
docker compose exec -T db pg_dump ...	-T disables the pseudo-terminal, which is what makes redirection work

Connecting, seeding, and never seeding production

localhost:5432 works from your machine because of the port mapping. A service inside the same Compose network reaches the database at db:5432 instead, which will not resolve from your terminal — that mismatch is the most common "connection refused" in development. Load the URL once, fail loudly if it is missing, and export a single shared PrismaClient; Node 20 reads .env itself with node --env-file=.env dist/main.js.

Seeds write the data the application cannot work without. Two rules make them safe: they are idempotent (upsert on a natural key, never create), and they refuse production unless a human says otherwise.

// prisma/seed.ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const BANDS = [{ code: "band-a", maxKwhPerMonth: 150, pricePerKwh: 0.09 }, { code: "band-b", maxKwhPerMonth: 400, pricePerKwh: 0.13 }] as const;

async function seed(): Promise<void> {
  if (process.env.NODE_ENV === "production" && process.env.ALLOW_PRODUCTION_SEED !== "yes") {
    throw new Error("refusing to seed production without ALLOW_PRODUCTION_SEED=yes");
  }
  for (const band of BANDS) {
    await prisma.tariffBand.upsert({ where: { code: band.code }, create: band, update: band });
  }
  console.log(`seed complete: ${BANDS.length} tariff bands upserted`);
}

seed()
  .catch((error: unknown) => { console.error(error); process.exitCode = 1; })
  .finally(() => prisma.$disconnect());

Register it with "prisma": { "seed": "tsx prisma/seed.ts" } in package.json, then run npx prisma db seed. Reference data belongs everywhere; forty fake sites belong in prisma/seed.dev.ts, which no deployment mentions.

[!IMPORTANT] Give each environment its own database, credentials and URL, and make the difference visible in the URL itself: ..._dev, ..._staging, ..._prod. Most "I seeded the wrong environment" incidents come from three terminal windows that look identical.

Backups you have actually restored

An untested backup is a hypothesis. pg_dump in the custom format is compressed and restores selectively; write a UTC-stamped dump, keep a rolling window, and restore one into a scratch database every month.

#!/usr/bin/env bash
# scripts/backup.sh
set -euo pipefail

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
mkdir -p backups
docker compose exec -T db pg_dump -U solar -d solar_dev -Fc > "backups/solar_dev_${STAMP}.dump"
find backups -name 'solar_dev_*.dump' -mtime +14 -delete   # retention: keep a fortnight

To prove the dump restores — the step people skip and later regret:

docker compose exec -T db createdb -U solar solar_restore_test
docker compose exec -T db pg_restore -U solar -d solar_restore_test --clean --if-exists < backups/solar_dev_*.dump
docker compose exec db psql -U solar -d solar_restore_test -c 'SELECT count(*) FROM "ProductionDay";'

Expected output: the count matches the live table. For SQLite the equivalent is sqlite3 data/solar.db ".backup 'data/solar-backup.db'" or VACUUM INTO; cp on a live file is not a backup.

Pools, graceful shutdown and health checks

A pool keeps a handful of connections open and reuses them, because each new one costs a round trip. Prisma holds one pool per client, so processes × connection_limit ≤ max_connections is the arithmetic that matters: PostgreSQL defaults to 100, so ten replicas at the default of 10 sit on the limit and one restart stampedes. For a small application connection_limit=5 is plenty, and pool_timeout=10 fails fast when saturated.

// src/db/shutdown.ts
import { prisma } from "./client.js";

async function shutdown(signal: string): Promise<void> {
  console.log(`${signal} received, closing the pool`);
  await prisma.$disconnect(); // hands the connections back instead of dropping them
  process.exit(0);
}
process.on("SIGINT", () => void shutdown("SIGINT"));
process.on("SIGTERM", () => void shutdown("SIGTERM"));

// src/db/health.ts — one cheap query, on a timer rather than in every request
export async function isDatabaseHealthy(): Promise<boolean> {
  try { await prisma.$queryRaw`SELECT 1`; return true; }
  catch (error: unknown) { console.error("database health check failed", error); return false; }
}

Three numbers are worth a weekly look. Slow queries: ALTER DATABASE solar_dev SET log_min_duration_statement = '500ms'; logs the ones you did not expect. Connections: a rising error count usually means a pool that is too small or connections that are leaking.

Environments, migrations and read-only credentials

Only one environment holds data you cannot recreate, so keep three databases, three URLs and three credential sets, and run migrations as an explicit deployment step before the new code starts, never from application startup where ten replicas would race. Reporting tools have no business writing:

CREATE ROLE solar_reporting LOGIN;      -- set the password interactively with \password solar_reporting
GRANT CONNECT ON DATABASE solar_dev TO solar_reporting;
GRANT USAGE ON SCHEMA public TO solar_reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO solar_reporting;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO solar_reporting;

---

Common mistakes

Mistake	What happens	The fix
docker compose down -v while debugging	The volume is deleted and the local database is empty	Use down; save -v for a deliberate reset
Committing .env	Real credentials in git history forever	Commit .env.example, ignore .env
Running the seed twice	Duplicate tariff bands, totals that double	upsert on a natural key
Seeding production by accident	Invented figures sit beside real ones and nothing looks wrong	Separate URLs plus a guard in the script
Copying a live SQLite file with cp	A torn copy that opens and silently misses rows	sqlite3 ... ".backup" or VACUUM INTO
A backup nobody ever restored	The dump turns out to be unusable during the incident	Restore into a scratch database monthly
Killing the process without $disconnect()	Connections linger until the server times them out	Handle SIGTERM/SIGINT and close the pool

---

Try it

1. Create docker-compose.yml and .env, then start the server: docker compose up -d && docker compose ps.

Expected output: a row containing Up ... (healthy). If it says unhealthy, read the log: the password variable is the usual suspect.

1. Apply migrations and seed twice: npx prisma migrate deploy && npx prisma db seed && npx prisma db seed.

Expected output: seed complete: 2 tariff bands upserted twice, with no duplicate rows.

1. Take a dump, destroy a table, restore it: bash scripts/backup.sh, then docker compose exec db psql -U solar -d solar_dev -c 'DROP TABLE "ProductionDay";', then

docker compose exec -T db pg_restore -U solar -d solar_dev --clean --if-exists < backups/solar_dev_*.dump
docker compose exec db psql -U solar -d solar_dev -c 'SELECT count(*) FROM "ProductionDay";'

Expected output: the count after the restore matches the count from before the DROP. Then run docker compose down -v on purpose and start again, so the day it matters is not the first time.

---

Key takeaways

• The container is disposable and the named volume is the database: know what down -v deletes.
• A seed script is idempotent and refuses production unless a human sets a variable.
• A backup counts only once you have restored it into a scratch database and compared row counts.
• Size pools as processes × connection_limit ≤ max_connections, and close them on SIGTERM.
• Three environments, three URLs, three credential sets — and reporting gets SELECT and nothing else.

---

Check yourself

1. You run docker compose down -v to fix a connection problem: what have you just lost?
2. Your seed script uses create and someone runs it twice in staging. What does the table look like?
3. Why is pg_dump -Fc preferred over a plain SQL dump, and what does -T on docker compose exec change?

---

Next

Lesson 36 — How the Browser Runs Your Code