All demos/ Manual testing
DEMO-05-REVSEP 2026
Quickly test and merge a backlog of schema changes. Each migration runs on a production-shaped snapshot before production.
We have a handful of branches to review, each with a schema change, against a production-shaped snapshot we do not want to reload. Per branch, make db-up migrates the snapshot up, and after walking through the change we call make db-down to migrate it back. One branch would drop a column, which our policy forbids, so db-up stops it before anything runs. The two we could test are merged, and their combined migration runs against the snapshot as it will against production.
- Stack
- git + make + PostgreSQL
- Change
- RenameColumn, AddColumn, and a DropColumn stopped by policy
- Reverse
migrate --down --strict, as the check before each up and as the down after
Chapters
make db-up, walk through the change, make db-down. Each migration has now run on production-shaped data, and after each one the snapshot is where it started.
02
One of the branches would drop a column, and our policy says no35 secThe third branch would drop a column. make db-up stops before anything runs, and the branch goes back to its author.
03
Merge the tested branches, then run the combined migration53 secMerge the two tested branches. Retrofit confirms there are no structural conflicts, and make db-up runs the combined migration against the snapshot, the migration that will be executed against production.
04
Run it end to end2 min 50 secThree branches against one snapshot. Two are migrated up, looked at, and migrated down. One is stopped by policy. Then the two are rebased and fast-forwarded, and the combined migration runs against the snapshot.Summary
You review branches with schema changes against a snapshot of production, and restoring that snapshot is slow.
A migration that lands on main will run against production, and a
production-shaped snapshot is where we find out what it does to real data
before then. Restoring a snapshot is enough work that we expect to run every
branch’s migration against the same one. So each up is gated on its own down,
and each down is a rollback rehearsed on real data. A change our policy
forbids, one that would drop data, stops before it runs. The snapshot coming
through untouched is the expected outcome, not the goal.
make db-up and make db-down; a change that cannot be undone stops
before anything runs; and after the merge, the combined migration runs against
the snapshot the way it will run against production. We never put the
snapshot at risk, and we tried three branches against it in the time it takes
to read them.Retrofit never connects to a database. pg_dump and psql do, git carries
the operation log on every branch, and Retrofit reads files and writes SQL. The policy
is ours: a branch that would drop data goes back to its author, and db-up’s
check is where that policy is enforced. Nothing here needs a person at the
keyboard.
Below is the team’s Makefile in full, the file the recordings run. The
three targets are the whole mechanism: check-db is the gate, db-up asks
identify where the database is and stops unless the branch’s change can be
undone, and db-down migrates back from the same version.
The team's Makefile
# The team's Makefile. Three targets matter here.
#
# make run check the local database against the operation log,
# then start the app
# make db-up put this branch's schema change on the database, if it
# can come back off
# make db-down take it back off
#
# check-db is an ordinary prerequisite of run: one schema-only dump and one
# Retrofit command, and it stops with exit 2 when the database and the log
# disagree. db-up asks where the database is, refuses unless the branch's
# window comes back down clean, and applies it from the dump. db-down asks
# again and reverses from the dump.
#
# Retrofit reads the dump. pg_dump and psql are what touch the database.
# -q drops Retrofit's advisory tier; errors and results still print, and
# nothing here filters or rewords them.
DB ?= postgres://postgres@localhost:55447/chinook
SCHEMA ?= chinook
.PHONY: run check-db db-up db-down
run: check-db
./bin/app "$(DB)"
check-db:
pg_dump --schema-only --schema=$(SCHEMA) "$(DB)" > .db.dump.sql
retrofit identify .db.dump.sql --expect last -q
db-up:
pg_dump --schema-only --schema=$(SCHEMA) "$(DB)" > .db.dump.sql
retrofit identify .db.dump.sql --expect seal -q
retrofit migrate --down --strict --from seal --to last -q > /dev/null
retrofit migrate --from-dump .db.dump.sql -q | psql "$(DB)" -v ON_ERROR_STOP=1 -q
db-down:
pg_dump --schema-only --schema=$(SCHEMA) "$(DB)" > .db.dump.sql
retrofit identify .db.dump.sql --expect last -q
retrofit migrate --down --strict --from-dump .db.dump.sql -q | psql "$(DB)" -v ON_ERROR_STOP=1 -q
Chapter 01Review a backlog of branches with schema changes
How do we run each branch's migration against production-shaped data without reloading the snapshot?
On main, make run passes: the database is at main. We check out the first branch and make db-up asks identify where the database is, checks that the branch’s change can be undone, and applies one ALTER TABLE ... RENAME COLUMN from the version the snapshot is at. The app reads the renamed column and every row is still there: the migration has run on production-shaped data. make db-down migrates it back from the same version. The next branch adds a column and goes through the same four commands. Back on main, make run passes and the count is the snapshot’s.
Read transcript
manually-test-schema-changes-demo: review
Generated from the demo script. Every command below is run verbatim by the recording.
1/4 a backlog of branches
a catalog service. its schema is an operation log in git, beside the code, and the database on this machine is a snapshot of production we would rather not load again. every migration that lands on main runs against production, so each branch’s runs here first. make run checks the database against the log before it starts the app:
make run
three branches to review, each with a schema change:
git branch
2/4 the first branch, up
git checkout rename-track-name
db-up finds the version the database is at, checks that this branch’s change can be undone, and migrates it up:
make db-up
the app reads the renamed column, and every row is still there:
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
3/4 and back down
we have seen enough. db-down undoes it:
make db-down
4/4 the next branch
git checkout add-download-count
make db-up
make run
make db-down
and so on for each branch. the snapshot was never reloaded, and after every branch the database is where it started:
git checkout main
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
`check-db` is a prerequisite of `run`: one schema-only dump, and `identify` reports where in the operation log that schema is from. On `main` that is the head of the log, so the app starts. `git branch` lists the three branches to review.
`db-up` is four lines. `identify --expect seal` asserts the database is where `main` is. `migrate --down --strict` renders the reverse of the branch's window, or exits 1 when there is none it can stand behind. `migrate --from-dump` renders the forward from where the database is, and `psql` runs it. `make run` passes at the branch's operation, the app reads `title`, and the track count is the snapshot's: this migration has now run on production-shaped data.
`identify --expect last` asserts the database is at the branch's operation, then `migrate --down --strict --from-dump` renders the reverse from where the database is back to `main`, and `psql` runs it.
The same four commands for a branch that adds a column, the common case: the column is added, the app still runs, the column is dropped. And so on for each branch. Back on `main`, `make run` passes at `main` and the count is the snapshot's.
Chapter 02One of the branches would drop a column, and our policy says no
What happens to a branch that breaks our policy against destructive changes?
Two branches tested, and make run passes at main. The third branch would drop a column. make db-up stops at its check with migrate-non-reversible naming the DropColumn, and nothing ran. Rendered without --strict, the reverse it would have needed is an ADD COLUMN under an -- irreversible: marker: the column back, none of its rows. PostgreSQL cannot restore them, so Retrofit renders no reverse it can stand behind. Our policy is no destructive changes, and this check is where it is enforced: the branch goes back to its author. Back on main, the count is the snapshot’s.
Read transcript
manually-test-schema-changes-demo: defer
Generated from the demo script. Every command below is run verbatim by the recording.
1/3 one branch drops a column
two branches reviewed, and the database is back at main:
make run
the third branch would drop a column, which our policy does not allow. db-up is where that policy is enforced:
git checkout drop-composer
make db-up
exit status 2
2/3 the reverse it would need
db-up stopped at its check, before anything ran. this is the reverse it would have needed:
retrofit migrate --down --from seal --to last -q
the column would come back with none of its rows. PostgreSQL cannot restore them, so Retrofit renders no reverse it can stand behind. our policy is no destructive changes, so this branch goes back to its author.
3/3 the snapshot, untouched
git checkout main
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
`identify` confirms the database is at `main`, then `migrate --down --strict` exits 1 with `migrate-non-reversible`: one op in the window has no clean inverse, `DropColumn track.composer`. `make` stops with exit 2 before the forward is rendered.
Rendered without `--strict`, the reverse is `ADD COLUMN composer` under an `-- irreversible:` marker naming what is lost. PostgreSQL cannot restore a dropped column's rows, so Retrofit renders no reverse it can stand behind. Our policy is no destructive changes; the branch goes back to its author.
The check ran against the log alone, so the snapshot was never touched. Back on `main`, `make run` passes at `main`, and the track count is what was loaded.
Chapter 03Merge the tested branches, then run the combined migration
Does the migration that will be executed against production work on production-shaped data?
The first branch fast-forwards main. The second started from the same point, so we rebase it onto main; git merges the operation log as text and leaves both changes side by side. validate reports the fork, and reconcile replays the two changes in each order against the schema; both orders hold, so it settles one and rewrites the chain. Git merged the text; the reconcile is what tells us the rebase introduced no structural schema conflicts. We fold it into the branch’s commit and fast-forward main, one straight line in git and in the log. Then make db-up runs the combined migration against the snapshot with every row intact, the migration that will be executed against production.
Read transcript
manually-test-schema-changes-demo: merge
Generated from the demo script. Every command below is run verbatim by the recording.
1/4 merge the first
two branches tested, and the third went back to its author. we merge the two. the first is a fast-forward, and the operation log is still one straight line of operations:
git merge --ff-only rename-track-name
retrofit validate -q
2/4 rebase the second
the second branch also starts where main did, so we rebase it onto main. git merges the operation log as text, so both changes are in it, each right after that point: a fork. validate exits 2 on a fork:
git checkout add-download-count
git rebase main
3/4 reconcile the fork
reconcile picks an order, rewrites the chain so the tail is linear again, and shows what it chose. we fold that into the branch’s commit and fast-forward main:
retrofit reconcile --all --clean -q
retrofit validate -q
git commit -q --amend --no-edit -a && git checkout -q main && git merge --ff-only add-download-count
one straight line, in git and in the operation log:
git log --oneline && retrofit op show -q | cut -c1-72
4/4 the snapshot follows main
main’s window is now both changes: the migration that will be executed against production. db-up runs it against the snapshot:
make db-up
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
we never put the snapshot at risk by making a change blindly, we tried three branches against it in the time it takes to read them, and the migration that will be executed against production has already run on production-shaped data. nothing here needs a person at the keyboard.
The first tested branch fast-forwards `main`. `validate` reads the operation log and says nothing: one straight line of operations, one longer than before.
The second branch also starts where `main` did, so we rebase it onto `main`. Git merges the operation log as text, so both operations name the same predecessor: a fork, on the branch, before anything reaches `main`. `validate` exits 2 with `structural-gate`, naming both operations and the `reconcile` that closes it.
`reconcile --all --clean` replays the two changes in each order against the schema and writes only if an order exists in which every operation still resolves. Two renames of the same column, or a dropped table the other branch altered, would stop it here. Both orders are clean, so it keeps the first-seen one, rewrites the second operation's predecessor, and prints the before and after. We amend the branch's commit with the reconciled log and fast-forward `main`, so every commit on `main` carries a log that replays, and `git log` and `op show` are both one straight line.
The same `db-up`: `identify` at `main`, the two-op reverse renders clean, and the forward applies the rename and the column. `make run` passes at the new last op and the track count is the snapshot's. This is the migration that will be executed against production, and it has now run on production-shaped data.
Chapter 04Run it end to end
Start on main with make run passing, run two branches’ migrations with make db-up and make db-down each, watch the branch that would drop a column stop at the check, then rebase and fast-forward the two tested branches, let validate and reconcile sort out the fork, and run the combined migration against the snapshot with the same db-up.
Read transcript
manually-test-schema-changes-demo: complete walkthrough
Generated from the demo script. Every command below is run verbatim by the recording.
a backlog of branches
a catalog service. its schema is an operation log in git, beside the code, and the database on this machine is a snapshot of production we would rather not load again. every migration that lands on main runs against production, so each branch’s runs here first. make run checks the database against the log before it starts the app:
make run
three branches to review, each with a schema change:
git branch
the first branch, up
git checkout rename-track-name
db-up finds the version the database is at, checks that this branch’s change can be undone, and migrates it up:
make db-up
the app reads the renamed column, and every row is still there:
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
and back down
we have seen enough. db-down undoes it:
make db-down
the next branch
git checkout add-download-count
make db-up
make run
make db-down
and so on for each branch. the snapshot was never reloaded, and after every branch the database is where it started:
git checkout main
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
one branch drops a column
the third branch would drop a column, which our policy does not allow. db-up is where that policy is enforced:
git checkout drop-composer
make db-up
exit status 2
the reverse it would need
db-up stopped at its check, before anything ran. this is the reverse it would have needed:
retrofit migrate --down --from seal --to last -q
the column would come back with none of its rows. PostgreSQL cannot restore them, so Retrofit renders no reverse it can stand behind. our policy is no destructive changes, so this branch goes back to its author.
the snapshot, untouched
git checkout main
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
merge the first
two branches tested, and the third went back to its author. we merge the two. the first is a fast-forward, and the operation log is still one straight line of operations:
git merge --ff-only rename-track-name
retrofit validate -q
rebase the second
the second branch also starts where main did, so we rebase it onto main. git merges the operation log as text, so both changes are in it, each right after that point: a fork. validate exits 2 on a fork:
git checkout add-download-count
git rebase main
retrofit validate -q
exit status 2
reconcile the fork
reconcile picks an order, rewrites the chain so the tail is linear again, and shows what it chose. we fold that into the branch’s commit and fast-forward main:
retrofit reconcile --all --clean -q
retrofit validate -q
git commit -q --amend --no-edit -a && git checkout -q main && git merge --ff-only add-download-count
one straight line, in git and in the operation log:
git log --oneline && retrofit op show -q | cut -c1-72
the snapshot follows main
main’s window is now both changes: the migration that will be executed against production. db-up runs it against the snapshot:
make db-up
make run
psql "$DB" -Atc 'SELECT count(*) FROM chinook.track'
we never put the snapshot at risk by making a change blindly, we tried three branches against it in the time it takes to read them, and the migration that will be executed against production has already run on production-shaped data. nothing here needs a person at the keyboard.