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 turns the schema into operators and sets the existing rows aside:

retrofit import chinook.sql --relaxed --seal --purpose=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 data rides alongside it in a sidecar, not buried in the schema:

ls chinook.oplog 0001-chinook.dml.sql

3/3 rebuild the database from the log

materialize the framed seed records as executable SQL:

awk '/^-- retrofit:dml-end/{print ";"} {print}' 0001-chinook.dml.sql > chinook.seed.sql && mv 0001-chinook.dml.sql chinook.seed.records

snapshot emits the schema; the preserved rows follow in the same pipe:

{ retrofit snapshot -q; echo 'SET search_path TO chinook;'; cat chinook.seed.sql; } | 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, and Retrofit flags who else may care:

retrofit op add RenameColumn track.name title -q

the warning arrives now, while the change is still just a declaration. Retrofit cannot see your application, so it names the risk instead of deciding for you. Apply it:

retrofit migrate -q 2>/dev/null | 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 -q 2>/dev/null | 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. the whole unsealed window 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

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

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 managed history reads as a list of reviewed cuts:

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.