Testing Migration Scripts with Docker

SQL migrations are code. Yet on many teams they are the last artifact still shipped on faith — written once, skimmed in review, and executed for the first time against real data in production. The safest way to test them is the same way you test everything else: against a real database that mirrors production.

Migrations deserve more suspicion than regular code, not less. They run exactly once, they mutate state that is hard to un-mutate, and they fire in the middle of a deploy — the one moment when everyone is watching and nobody wants surprises. That risk profile calls for a boring, repeatable test setup, and Docker delivers exactly that.

Spin up a disposable database

Docker makes it trivial to test migrations in isolation. One command gives me a clean Postgres that lives exactly as long as the test run:

# Start a disposable Postgres instance
docker run --rm -e POSTGRES_PASSWORD=pass -p 5432:5432 postgres:16

The --rm flag is the point. The container dies when the test ends, and every run starts from a known-empty state — no leftover tables from last week's experiment, no schema drift between my machine and a colleague's. Against that clean instance I replay the entire migration chain from the very first file. This habit alone catches a whole class of bugs: missing column defaults that only bite on fresh installs, indexes that lock a table for minutes, and queries that flew on ten rows but crawl on ten million.

Two rules keep the container honest. First, pin the image to the exact major version production runs — postgres:16 means nothing if prod is on 14, because DDL one version accepts the other may reject. Second, load a realistic dataset: an anonymized dump or generated fixtures at production scale. ALTER TABLE on 200 rows tells you nothing about ALTER TABLE on 20 million.

Run the same checks you run in prod

Local testing is only half the job. The checks belong in CI, where they run on every pull request whether or not anyone remembers to run them. My migration job does five things:

• Migrate from zero: apply every migration in order to an empty database — proof the chain still works for a fresh environment.
• Migrate from latest: restore a schema snapshot of production and apply only the new files — proof the diff works on the state that actually matters.
• Smoke queries: run the handful of queries the application depends on and assert they still return sane results.
• Rollback: run the down migration, then repeat the smoke queries. Down migrations rot silently unless something exercises them.
• Time every statement: log how long each one holds its locks. A migration that blocks writes for forty seconds is not a migration, it is an outage with paperwork.

None of this is exotic. It is the same discipline we already apply to application code — automated tests, real dependencies, fast feedback — finally pointed at the one script that has root access to the data. The setup costs an afternoon; the first bad index it catches pays for it.

The goal is confidence, not luck. By the time the deploy pipeline reaches the migration step, the exact same script has already run against the exact same engine, over production-shaped data, in a container nobody had to clean up. The production run stops being an event and becomes a replay — and boring replays are precisely what you want your deploys to be.