The lab#
Three migrations move orders from status to state:
-- V1__orders.sql
CREATE TABLE orders (id bigserial PRIMARY KEY, status text NOT NULL);
-- V2__add_state.sql: expand, with a trigger that fills state for old writers
ALTER TABLE orders ADD COLUMN state text;
CREATE FUNCTION orders_fill_state() ...;
CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders ...;
-- V3__state_required.sql: the new release writes state itself
ALTER TABLE orders ALTER COLUMN status DROP NOT NULL;
DROP TRIGGER orders_fill_state ON orders;
ALTER TABLE orders ALTER COLUMN state SET NOT NULL;
Release 2026.11.3 has a test that creates and ships an order through its own SQL:
def test_create_and_ship(self):
oid = self.db.execute("INSERT INTO orders (status) VALUES ('paid') RETURNING id").fetchone()[0]
self.db.execute("UPDATE orders SET status = 'shipped' WHERE id = %s", (oid,))
self.assertEqual(self.db.execute("SELECT status FROM orders WHERE id = %s", (oid,)).fetchone()[0], "shipped")
We created a fresh database on PostgreSQL 16.15, applied the migrations up to V2, ran the old test, then repeated with V3:
| Migrations applied | Previous release's tests |
|---|---|
| V1, V2 | OK |
| V1, V2, V3 | NotNullViolation: null value in column "state" of relation "orders" violates not-null constraint |
V3 is correct for the new release and wrong to ship while 2026.11.3 can run. The test says so before the deploy, in the words of the database error the old release would have hit in production.
In the pipeline#
The job needs the previous release's code and the new release's migrations:
# migrate and run-tests stand for your migration tool and test runner
git worktree add ../previous "$(git describe --tags --abbrev=0 HEAD^)"
createdb previous_release_test
migrate --database previous_release_test --dir ./migrations up # this branch's migrations
(cd ../previous && TEST_DSN=... run-tests --only database) # the previous release's tests
Pick the previous release from the deploy record, not from tag order, when the two differ. Run only the tests that touch the database: unit tests of pure functions say nothing about the schema, and slow end-to-end suites may not fit a pull request check. If the rollback target is older than the previous release, test that one too.
What it does not cover#
The test reaches only the paths the old suite exercises. Jobs, reports and other services that use the table need their own checks; find every query that still reads the old column finds them. It runs against an empty or fixture database, so it does not show lock times or row-dependent failures, which the migration rehearsal labs cover. And it tests the schema at the end of the migrations, not the moment between two statements.
The check#
Pass when the previous release's database tests pass against the schema after every migration in the pull request. A failure is a release-order finding: move the failing migration to a later release, as decide when to stop writing the old column and how long the old column has to stay describe.