All demos/ Existing database

DEMO-01-DBJUL 2026

Import and evolve an existing PostgreSQL database.

From a schema-only dump of a database that has data in it, retrofit imports the schema as operations. The data stays in PostgreSQL through column and table refactors, and changes made directly in the database are read back into the log. The starting point can be a database dump, a set of migration scripts, or an empty log; this demo starts from the dump.

Stack: PostgreSQL
Change: op add, drift
Reverse: ExtractTable, Seal, op log

Chapters

01 Import an existing database26 secImport the schema from a dump, then check the running database against the log. 02 Refactor a live table1 min 50 secChange the schema without losing the data. 03 Reconcile a direct database change32 secSomeone ran ALTER TABLE directly on the database. Detect the schema drift from a dump and record it in the log. 04 Prove the complete walkthrough2 min 31 secImport, refactor, and reconcile the populated database without a cut.

Summary

You have a populated database, with or without a migration history.

We start from the Chinook sample database running in PostgreSQL: 11 related tables with their sample data. pg_dump reads its schema, retrofit import writes that schema to the operation log, and identify reports that the database matches the log. The rows never leave the database. Then we refactor live tables: rename track.name to title, convert track.milliseconds to an interval, rename invoice.total to amount, and split invoice into invoice_core and invoice_billing.

Each change is declared as a schema operation, with its risks reported, before retrofit renders any SQL. All 412 invoices rejoin row for row across the split, and all 2,240 invoice_line rows still point at the invoice core.

The warnings are part of that proof, not noise removed for the recording: the rename and cast risks while those changes are authored, and the foreign-key validation lock when the split is applied. The chapter asserts exactly those codes and nothing else.

Finally, we add track.download_count and an index directly in PostgreSQL. identify reports that the database no longer matches the log and names both changes, and drift records them, so the database and the log agree again.

Chapter 01Import an existing database

Can retrofit adopt a database I already have, and leave its data where it is?

Import the schema of Chinook’s 11 related tables as 138 operations from a schema-only dump, and confirm with identify that the running database matches the log. Its 3,503 tracks and 412 invoices stay in PostgreSQL.

26 sec space to pause f for fullscreen

Read transcript

import-drift-demo: import existing database

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 import the schema of a database we already have

Chinook is running in PostgreSQL with data in it, and it has no migration history. pg_dump reads its schema, and import writes that schema to the log as operations:

pg_dump --schema-only --no-owner --schema=chinook "$DB" | retrofit import - --seal='chinook as found' -q | grep '^imported'

2/3 read the log

the log is plain text, one operation per line, reviewable in a diff:

head -5 chinook.oplog

3/3 check the database against the log

identify reports where in the log a database’s schema is from. pg_dump reads the database, and retrofit reads the dump:

retrofit identify -q <(pg_dump --schema-only --schema=chinook "$DB")

the database matches the log at the seal we just made, and its data never left it:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.track' | sed 's/^/  tracks:   /'

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice' | sed 's/^/  invoices: /'

The log has the schema. The database has the data.

Chinook is running in PostgreSQL with data in it and no migration history. pg_dump reads its schema, and import writes that schema to the log as 138 operations and seals them as chinook as found.
The log is plain text, one operation per line, reviewable in a diff.
identify reports where in the log a database’s schema is from: pg_dump reads the database, and retrofit reads the dump. The database matches the log at the seal import made, and its 3,503 tracks and 412 invoices never left it.

Chapter 02Refactor a live table

What does retrofit tell me before I change a populated table?

Rename a column in use, convert 3,503 stored durations, and split 412 populated invoices. Three risk advisories, two while authoring and one when the generated SQL pays the lock cost.

1 min 50 sec space to pause f for fullscreen

Read transcript

import-drift-demo: refactor a live table

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 rename a column that has readers

rename track.name to track.title. PostgreSQL renames in place, so the 3,503 existing values stay attached. op add records it, and migrate renders its SQL both ways:

retrofit op add RenameColumn track.name title -q

retrofit migrate -qq | grep '^ALTER'

retrofit migrate --down -qq | grep '^ALTER'

the warning came with op add, while the change was one line in the log. the application is outside what retrofit reads, so it names the risk instead of deciding for us. we apply it:

retrofit migrate -qq | psql "$DB" -v ON_ERROR_STOP=1 -q

psql "$DB" -tAc 'SELECT title FROM chinook.track ORDER BY track_id LIMIT 1' | sed 's/^/  first title: /'

retrofit op seal --reason 'rename track.name -> title' -q

2/3 change a column’s type with the data still in it

convert track.milliseconds from integer to interval. the USING transform is ours to assert, so retrofit warns that rows failing the cast fail the migration:

retrofit op add CastColumn track.milliseconds interval 'using=milliseconds * interval '\''1 ms'\''' -q

retrofit op add RenameColumn invoice.total amount -q

one migrate covers both changes:

retrofit migrate -qq | psql "$DB" -v ON_ERROR_STOP=1 -q

psql "$DB" -tAc 'SELECT milliseconds FROM chinook.track ORDER BY track_id LIMIT 1' | sed 's/^/  a real interval now: /'

retrofit op seal --reason 'milliseconds -> interval; invoice.total -> amount' -q

3/3 split a populated table in one operation

move five billing-address columns out of invoice into a table of their own. one operation records it. op add reports what it derived, and what the reverse cannot put back:

retrofit op add ExtractTable chinook invoice --into invoice_billing=billing_address,billing_city,billing_state,billing_country,billing_postal_code --rest invoice_core -q

two notes, before any SQL exists. the split added a key so the two tables join back: invoice_id is invoice_billing’s primary key and a foreign key to invoice_core. and the reverse appends the five columns, so their position in the table is not restored. that one operation renders as ten statements:

retrofit migrate -qq | grep -E '^(ALTER|CREATE|INSERT)' | cut -c1-100

PostgreSQL validates the new foreign key against all 412 rows, and the scan holds a lock that blocks writes. retrofit reports it here, at apply time, where the cost is paid:

retrofit migrate -q | psql "$DB" -v ON_ERROR_STOP=1 -q

the key is the one the split added. ExtractTable has no NOT VALID form for it, so what is left to choose is when we apply it: a window with few writes. no row was lost in the move, and the two halves still rejoin row for row:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_core' | sed 's/^/  invoice_core:       /'

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_billing' | sed 's/^/  invoice_billing:    /'

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_core c JOIN chinook.invoice_billing b USING (invoice_id)' | sed 's/^/  rejoined:           /'

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_line il JOIN chinook.invoice_core ic ON ic.invoice_id = il.invoice_id' | sed 's/^/  lines still joined: /'

retrofit op seal --reason 'split invoice -> invoice_core + invoice_billing' -q

identify reads a schema dump of the database and reports where in the log its schema is from:

retrofit identify -q <(pg_dump --schema-only --schema=chinook "$DB")

Three refactorings on populated tables, 3,503 tracks and 412 invoices intact. Each risk was named when it mattered: two while writing, one while applying.

op add appends one operation to the log; advisories arrive here, before any SQL exists. PostgreSQL renames in place, so all 3,503 values stay attached. The column’s readers are outside what retrofit can see, and that is what the warning names.
We write the USING transform and retrofit cannot check it, so it warns that a row the cast rejects fails the migration. One migrate covers this and the invoice.total rename.
ExtractTable moves five billing columns into a new invoice_billing and renames what remains to invoice_core. op add notes the key it derived to join them back, and that the reverse does not restore the columns’ position. The lock the new key’s validation takes is reported at migrate, and since ExtractTable has no NOT VALID form for that key, the remedy it names is timing. All 412 invoices rejoin row for row and 2,240 lines stay joined, and identify reports the database at the split’s seal.

Chapter 03Reconcile a direct database change

Someone patched production directly. Now what?

Add a column and index directly to PostgreSQL, detect both from an ordinary schema-only dump, and bring the database and operation log back into agreement.

32 sec space to pause f for fullscreen

Read transcript

import-drift-demo: reconcile drift

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 someone changed the database directly

not every change arrives through the log. a hotfix adds a column and an index straight to PostgreSQL, with no operation recorded anywhere:

psql \"\$DB\" -v ON_ERROR_STOP=1 <<'SQL

2/3 find the difference

pg_dump reads the database, and identify compares the dump with the log:

pg_dump --schema-only --schema=chinook "$DB" > /tmp/chinook.dump.sql

retrofit identify -q /tmp/chinook.dump.sql

exit status 2

the database matches no point in the log. identify names the column and the index that are in the database and not in the log.

3/3 record the change in the log

drift reads the same dump and appends both as operations, and we seal them:

retrofit drift /tmp/chinook.dump.sql --seal='hotfix recorded' -q

the check again:

retrofit identify -q /tmp/chinook.dump.sql

The database matches the log again, and the hotfix is two operations in it.

A hotfix adds track.download_count and track_composer_idx straight to PostgreSQL, with psql. No operation records it.
pg_dump reads the database, and identify compares the dump with the log. The database matches no point in the log, and identify names what differs: one column and one index.
drift reads the same dump and appends both changes to the log as operations, and --seal seals them. identify then reports that the database matches the log again.

Chapter 04Prove the complete walkthrough

The uninterrupted run exercises all six acts end to end: import, identify, rename, cast, table split, and drift, with the row counts and the closing identify intact.

2 min 31 sec space to pause f for fullscreen

Read transcript

import-drift-demo: complete walkthrough

Generated from the demo script. Every command below is run verbatim by the recording.

docker run -d --name '$DB_NAME' -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=chinook -p '$DB_PORT:5432' postgres:17

what we’ll do

Chinook is a sample database: 11 related tables with data in them. we import its schema, change columns and tables with the data in place, then record a change made directly in the database.

retrofit --version

bring up a disposable PostgreSQL 17 database:

import the schema

retrofit init -q

name the schema first; it becomes the default, so the rest just work:

retrofit schema create chinook -q

pg_dump reads the schema of the running database, and import writes it to the log as operations:

pg_dump --schema-only --no-owner --schema=chinook "$DB" | retrofit import - --seal='chinook as found' -q | grep '^imported'

the operation log is plain text, one operation per line:

head -5 chinook.oplog

check the database against the log

identify reports where in the log a database’s schema is from. pg_dump reads the database, and retrofit reads the dump:

retrofit identify -q <(pg_dump --schema-only --schema=chinook "$DB")

the database matches the log at the seal we just made, and its data never left it:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.track'

rename a column

rename track.name to track.title without dropping and recreating the column:

retrofit op add RenameColumn track.name title -q

‘migrate’ writes the SQL since the last seal; pipe it at psql:

retrofit migrate -q | psql "$DB" -v ON_ERROR_STOP=1 -q

all 3,503 titles survived:

psql "$DB" -tAc 'SELECT track_id, title FROM chinook.track ORDER BY track_id LIMIT 1'

seal it, with a reason:

retrofit op seal --reason 'rename track.name -> title' -q

batch a few changes

convert track.milliseconds from integer to interval and rename invoice.total to amount:

retrofit op add CastColumn track.milliseconds interval 'using=milliseconds * interval '\''1 ms'\''' -q

retrofit op add RenameColumn invoice.total amount -q

one migrate covers both. apply it the same way:

retrofit migrate -q | psql "$DB" -v ON_ERROR_STOP=1 -q

a real interval now, all 3,503 converted:

psql "$DB" -tAc 'SELECT track_id, milliseconds FROM chinook.track ORDER BY track_id LIMIT 1'

retrofit op seal --reason 'milliseconds -> interval; invoice.total -> amount' -q

split a table

move five billing-address columns out of invoice into a table of their own. one operation records it. op add reports what it derived, and what the reverse cannot put back:

retrofit op add ExtractTable chinook invoice --into invoice_billing=billing_address,billing_city,billing_state,billing_country,billing_postal_code --rest invoice_core -q

the split added a key so the two tables join back: invoice_id is invoice_billing’s primary key and a foreign key to invoice_core. that one operation renders as ten statements:

retrofit migrate -qq | grep -E '^(ALTER|CREATE|INSERT)' | cut -c1-76

PostgreSQL validates the new foreign key against all 412 rows, and the scan holds a lock that blocks writes. retrofit reports it at apply time:

retrofit migrate -q | psql "$DB" -v ON_ERROR_STOP=1 -q

the key is the one the split added. ExtractTable has no NOT VALID form for it, so what is left to choose is when we apply it: a window with few writes. both tables carry all 412 rows. no row was lost in the move:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_core' | sed 's/^/  invoice_core:    /'

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_billing' | sed 's/^/  invoice_billing: /'

both tables retain invoice_id, so core and billing rejoin row for row:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_core c JOIN chinook.invoice_billing b USING (invoice_id)' | sed 's/^/  rejoined: /'

invoice_line still references the renamed invoice_core; every line joins:

psql "$DB" -tAc 'SELECT count(*) FROM chinook.invoice_line il JOIN chinook.invoice_core ic ON ic.invoice_id = il.invoice_id' | sed 's/^/  lines joined: /'

retrofit op seal --reason 'split invoice -> invoice_core + invoice_billing' -q

capture a direct database change

simulate a direct patch: add track.download_count and index track.composer:

psql \"\$DB\" -v ON_ERROR_STOP=1 <<'SQL

dump the schema and run the same check:

pg_dump --schema-only --schema=chinook "$DB" > /tmp/chinook.dump.sql

retrofit identify -q /tmp/chinook.dump.sql

exit status 2

identify names the new column and index. drift reads the same dump and appends both to the log, and we seal them:

retrofit drift /tmp/chinook.dump.sql --seal='hotfix recorded' -q

the check again:

retrofit identify -q /tmp/chinook.dump.sql

the log

the operation log is one text file. it diffs, branches, and merges in Git alongside the code. there is no migrations directory.