Import and evolve an existing PostgreSQL database.

import-drift-demo: import existing database

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

1/3 start with a database you already have

Chinook: 11 related tables and real sample data. No migration history to replay:

grep -c 'CREATE TABLE' chinook.sql | sed 's/^/  tables: /'

grep -c 'INSERT INTO' chinook.sql | sed 's/^/  insert statements: /'

2/3 import it as schema operations

one import reads the DDL and writes it to the log as operations; the rows are set aside as data:

retrofit import chinook.sql --allow-capability-gaps --seal --label seed -q > import.log

138 schema operations; the rows from 24 INSERT statements are preserved:

grep -E 'imported [0-9]+ ops|dml-preserved' import.log

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

head -5 chinook.oplog

and the rows ride alongside it in the data corpus, one file per table:

ls chinook.oplog data/seed/

3/3 rebuild the database from the log

snapshot emits the schema and the named dataset in one pipe:

retrofit snapshot --label seed -q | psql "$DB" -v ON_ERROR_STOP=1 -q

3,503 tracks and 412 invoices, live. schema and data both rebuilt from the log:

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

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

There is no migrations/ directory. The oplog is the source and psql is the applier.

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. Retrofit cannot see your application, so it names the risk instead of deciding for you. 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 caller-asserted, 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. one operator creates both tables, moves all 412 rows, and re-points invoice_line’s foreign key. Retrofit names the lock that re-pointing will take:

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

one intent, one op. everything since the last seal is that single record:

retrofit op show -q | sed 's/^/  /'

the key that makes the split rejoinable rides on the op itself: rejoin-col-ids, rejoin-pk-ids, rejoin-fk-ids. it is not spilled into separate AddColumn / AddPrimaryKey / AddForeignKey ops you could edit away and silently break. applying it re-points the foreign key, and validating that key takes a brief lock. Retrofit reports it here, at apply time, where the cost is actually paid:

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

nothing was dropped 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

Three refactorings on a populated table, 3,503 tracks and 412 invoices intact. Retrofit named each risk at the moment it mattered: two while writing, one while applying.

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 tool. a hotfix adds a column and an index straight to PostgreSQL, with no operation recorded anywhere:

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

the database and the operation log now disagree.

2/3 find the difference

take an ordinary schema-only dump. no special access, no agent:

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

compare that dump against the log, and report before recording anything:

retrofit drift db.dump.sql --dry-run -q

both changes identified, by type and by name: a column and an index.

3/3 adopt the change as managed history

record them as real operations and seal the reconciled state:

retrofit drift db.dump.sql --seal='drifted in track.download_count + composer index' -q

they are in the log now, as if they had been typed there:

retrofit snapshot -q | grep -E 'download_count|track_composer_idx' | sed 's/^/  /'

and the log reads as a list of seals, each with its reason:

retrofit op show --include-sealed -q | grep -oE 'reason="[^"]*"' | awk -F'"' '{print "  v"NR"  "$2}'

The out-of-band change is no longer drift. It is version five.