Tests and migrations
How we test an API (real Postgres, fakes only at the edge) and how schema changes are written and run. Read it when you add a table, a column or a test.
How it works
Tests
| Kind | What it covers | Uses |
|---|---|---|
| Unit | Pure rules: price maths, state transitions, parsers | Nothing external |
| Use-case | One use-case with its repository | Real Postgres in Docker, fakes for ports |
| Flow | A request end to end through the server | app.inject, real Postgres and Redis, the app's own database role |
- Tests sit next to the file they test:
cancel-order.test.ts. - Each test file gets a clean schema or runs in a transaction that rolls back.
- Fakes exist only for ports: payments, storage, email, AI.
- Run flow tests as the app's database role, not as the migrator. Row-level security and permissions only apply to the app role.
Migrations
- Plain SQL files with meaningful names and a number:
0127_billing_reconcile_subject.sql. Never random generated names. - One migration runner, with an advisory lock so two tasks cannot run it at once.
- Migrations run as a separate task before the deploy, never at app start-up. A migration waiting for locks at start-up looks like a crash loop.
- Every migration is safe to run against the old code still serving traffic: add first, backfill, switch, remove later.
- Seed data (test users, demo content) is never in schema migrations. It lives in a seed script that runs only in dev.
Rules
Rule
A test that passes against a fake database proves nothing about SQL. Our billing callback threw on every real request while every test passed, because the tests used an in-memory fake.
Rule
No real people's names or data in seed files or migrations. Seed with obviously fake data.
Rule
The migration tool's journal must match the files. If you hand-write migrations, stop using the generated journal, and say so in the README.
Mistakes we made
- Every webhook was dropped in the first deployed environment while tests passed: tests ran as the migrator role, which bypasses row-level security, and never over HTTP.
- Seed rows with real-looking personal names sat inside schema migrations.
- Migration journals were frozen while new files kept arriving "post-journal", so nobody knew which tool was the source of truth.
- A migration at boot made a healthy deploy look like a 25-minute crash loop.
Checklist
- Use-case and flow tests run on real Postgres.
- Flow tests use the app's database role over HTTP.
- Migrations named, numbered, locked, run before deploy, backward compatible.
- No seed or personal data in migrations.