Testing destructive migrations

A migration that only ever runs against an empty schema is not tested. The interesting failures — a NOT NULL added to a column that has nulls, a type change that truncates, a backfill that takes locks it should not — need real data to show up. Prod-like data shows how to get a masked copy of it into the project.

The reason people do not test that way is the reset. Once the migration has run, getting back to "before" means dropping the database and reloading a dump, and after the fourth iteration that is where all the time goes.

A savepoint makes the reset one command.

The loop

Capture the pre-migration state once

arcave savepoint create pre-migration --description "schema v14, seeded"

Run it for real

./migrate up

Inspect what it did

docker compose exec -T db psql -U postgres -c '\d+ orders'
docker compose exec -T db psql -U postgres -c 'SELECT count(*) FROM orders WHERE tenant_id IS NULL;'

Reset and try the fixed version

arcave rollback pre-migration
./migrate up

Repeat until the migration is right. Every iteration starts from byte-identical data, so a difference in the outcome is a difference in the migration.

What this catches that a fresh schema does not

• Backfills that fail on real distributions — nulls, duplicates, encodings, rows that predate a constraint.
• Down migrations that lose data. Run up, run down, roll back to pre-migration, and compare. A down that silently drops a column is visible immediately.
• Migrations that are not idempotent. Run up twice from the same savepoint and see whether the second run is a no-op or a disaster.
• Cross-service drift. The migration changed the schema, but the cache still holds rows in the old shape and the object store still holds files named the old way. A savepoint covers all three, so the rollback puts all three back and the next attempt is genuinely clean.

Testing the down migration properly

arcave savepoint create before-up
./migrate up
arcave savepoint create after-up
./migrate down

# Now compare the state after `down` against the state before `up`.
# Then get back to a known point either way:
arcave rollback before-up          # to try `up` again
arcave rollback after-up           # to try `down` again

Two savepoints and two names give you both ends of the migration as fixed points, which is what makes the comparison meaningful. A name is unique within the compose project's session, so pick new ones here: savepoint create pre-migration a second time fails with savepoint name is already taken: "pre-migration" names savepoint <id>.

In CI

The same loop runs unattended. Every command prints JSON on stdout, so jq is all the parsing it needs:

SP=$(arcave --log-format json savepoint create "ci-${CI_RUN_ID}" | jq -r .id)
./migrate up || { arcave rollback "$SP"; exit 1; }
./run-integration-tests.sh || { arcave rollback "$SP"; exit 1; }
arcave savepoint remove --retain-recent 3

The run id keeps each run's savepoint name unique — a fixed name such as ci-baseline would be refused on the second run while the first one's savepoint is kept. Use whatever your CI provides, such as $GITHUB_RUN_ID or $CI_PIPELINE_ID.

note

Budget for the stop-and-start window. Every savepoint create and every rollback restarts the project and waits for health checks — up to --wait-timeout, two minutes by default. On a slow-starting project, raise it rather than letting a timeout read as a migration failure.

Related docs

• Prod-like data — a masked production copy to run the migration against.
• Resetting fixtures and demos — the same reset, for seeded data rather than schemas.
• savepoint create — flags and output.
• rollback — what a rollback restores, exactly.