retrofit
0.14.0
retrofit.sh

Emit a migration script

retrofit migrate prints the SQL that takes a database from one point in the oplog, the log of operators (ops) your schema is recorded in, to another: the ALTER, CREATE and DROP statements, with the data rows recorded between the two points. It reads the oplog and never changes it. Pipe the output to psql, or keep the file for your deploy pipeline to apply.

retrofit migrate is the reference: every flag, the shape of the output, and each refusal and exit code. How a migration applies gives the model.

Emit what is new since the last release

retrofit migrate > migration.sql
psql -X -v ON_ERROR_STOP=1 -f migration.sql

By default the script starts at the latest Seal, the end of the history your last release shipped (Sealed and unsealed ops), and ends at the last op. With no Seal yet, it builds the whole schema from nothing. The file holds its own BEGIN; and COMMIT;, so a failure part way applies nothing, unless stderr says the file commits in parts. Read the warnings on stderr before you apply it.

When nothing has changed since the Seal, the script is an empty transaction and stderr says nothing to migrate. That is a success, not an error.

Pass --schema <name> when the project registers more than one schema; without it, migrate picks the schema the way every command does (The resolver chain).

Choose the two points

Name the start with --from and the end with --to:

retrofit migrate --from 988edcb6 --to 7a6b1234   # between two ops
retrofit migrate --from root --to last           # the whole history
retrofit migrate --from seal^ --to seal          # the last sealed release

--from is the point the database is at now: the script is only correct against a database in exactly that state. retrofit op show --include-sealed prints each op, the sealed ones included, with its id first, as in AddColumn(988edcb612ab, prev=…, …); any unique prefix of four or more characters names it. --tail, --filter and --touching narrow the list (retrofit op show --help). root, last and seal name the start, the last op and the latest Seal, and ^ steps back one. The window lists every form.

Start from a Seal where you can. An unsealed op can move as the log grows, and migrate prints a note when --from names one.

Start from where the database is

When you do not know which point a database is at, dump its schema and let migrate find it:

pg_dump --schema-only -d <database> > db.sql
retrofit migrate --from-dump db.sql > migration.sql

The script then starts where the dump places the database and ends at the last op, so it also applies every unsealed op. Add --to seal for a database that takes only sealed releases.

To check the point without emitting anything, as a gate in a deploy job, run identify:

retrofit identify db.sql --expect seal

It exits 0 only when the dump is at the point you name. When the dump holds objects the project does not own, such as a runner’s history table, list them under [[exclude]] in retrofit.toml so they are not read as differences (Exclude objects the project does not own shows the entry). Starting from a dump covers a dump that matches no point.

Read the script before you apply it

Every script opens with a header:

-- engine: postgres 17
-- assumes database is at 0f3c1a9b2d4e
-- envelope: own; apply this file outside any open transaction (psql only: it carries \set ON_ERROR_STOP; use --envelope caller for a driver)

The assumes line names the point the script starts from, and the envelope line says how the file must be applied. A statement that discards data has a -- destructive: comment above it. stderr lists each warning: a lock that blocks writers while it is held, rows a data record left out, and the like. Output describes every line.

Choose how the file is applied

The default file opens its own transaction and runs only under psql. If your runner opens a transaction around the file, sends the file as one query, or sends each statement on its own, emit for that instead:

retrofit migrate --envelope=caller > migration.sql        # the runner opens one, or sends one query
retrofit migrate --envelope=independent > migration.sql   # the runner sends each statement alone

Choose who opens the transaction covers each case. To make a statement give up rather than wait behind a long query for a lock, see Bound how long a migration waits for a lock. A file that stopped part way is covered in Recover after a failed apply or a merge.

Include seed rows

Reference rows recorded in the data corpus are always in the script, at the point they were recorded. Seed datasets, such as test fixtures, are included only when you name them:

retrofit migrate --label env:dev                 # include the env:dev dataset
retrofit migrate --label base --label env:dev    # layer env:dev over base

Seed rows follow all of the schema changes. A dataset you did not name is listed in a warning on stderr; pass --allow-unnamed-label <label> to say a dataset is left out on purpose. Preserved DML says where each kind of record goes, and retrofit data describes the corpus.

Roll a deploy back

retrofit migrate --down --from seal^ --at seal   # undo the last sealed release
retrofit migrate --down --from-dump db.sql       # from where the database is, back to the latest Seal

--down prints the reverse of the window: the statements that take a database at the end of the window back to its start. --at names the point the database is at now (default: the last op), and --from the point to return to (default: the latest Seal). With --from-dump, the dump names where the database is.

A down drops what the window created, so a column the window added loses the rows written since, and a table the window dropped comes back empty. The script’s header lists each such loss, every rule it puts back, and what it cannot restore. Read it before you apply the script; where the loss is not acceptable, write a forward change that undoes the release instead. Back to the latest Seal means back to the Seal’s schema: a change you adopted into the log after the Seal is undone too.

migrate never writes the log, so a down nobody records leaves the log ahead of the database. That suits a system you rebuild from the log. When the log is shared, or describes a database that has to stay at a cut, record the down with retrofit op revert over the same range:

retrofit migrate --down --from seal^   # print the reverse
retrofit op revert --from seal^        # append it to the log

op revert prints the op a database the down took back now names as its cut.

A down prints a hint that names how to catch the log up, and the choice follows from whether the window holds a Seal. Unsealed ops can still be taken out or changed, so the hint names op remove or op revise, then drift for what remains. A Seal freezes the ops before it, so over a window that holds one the hint names op revert, which appends the reversal. op revert runs over either kind of window, and the ops it reverses stay in the log. Do not use drift for a step that moved rows: it records the schema a dump shows, so it logs that step as drops and adds, and a database migrated over them loses the rows the reverse kept.

When a window cannot be reversed, migrate prints no script and names a way back, usually a --from that leaves the blocking op outside the window. A raw script op reverses only if it declares how: add its reversal with retrofit op add DeclareReversal target=<opid> and one of reverse_body=, reverse_noop= or reverse_blocked=. The reverse migration covers each case, and How a reverse script is built the model.

To inspect the earlier shape before you roll back, --down --into-schema <schema> writes a copy of it beside the live schema, filled from the live rows.

Rehearse in a copy of the schema

retrofit migrate --into-schema staging > copy.sql

The script creates a copy of the end point under the schema staging, beside the live one, and fills its tables from the live rows. The copy leaves out secondary indexes, policies and comments, and migrate prints no script for a window that moves rows between tables, or for a schema with functions, triggers or views. For a full rehearsal, restore a dump of the database into a scratch database and apply the in-place script there. The copy script lists what the copy holds.

Rebuild a schema from scratch

A migration only holds the changes between two points. To drop a schema and build it again at one point, use snapshot, which re-creates everything it drops:

retrofit snapshot --at <opid> --include-drop=scoped   # drop each object, then create
retrofit snapshot --at <opid> --include-drop=schema   # drop the schema, then create

Emit the schema as SQL covers it.

Emit for another PostgreSQL major

retrofit migrate --using=postgres:18

The output is valid at the major the project pins (engine_version). --using emits for another supported major for one run and writes nothing to retrofit.toml. PostgreSQL versions says what each major supports.

Splitting an optional attribute into a sparse child table

A hand-written split often moves an optional attribute into its own table and keeps that table sparse: a child row only where the attribute is present. ExtractTable does not split that way. Its child that copies the source primary key takes a row for every source row, including rows whose moved columns are all NULL, so on a populated table a moved column can never take NOT NULL. For a sparse child, author the split as a keyed MoveColumn per column.

The worked example is the shape of Concourse’s separate_pins_from_resources migration. It takes a resource’s pin out of

CREATE TABLE resources (
  id integer PRIMARY KEY,
  name text NOT NULL,
  api_pinned_version jsonb,
  pin_comment text
);

into resource_pins (resource_id, version jsonb NOT NULL, comment_text text NOT NULL), keyed by resource_id and holding a row only for a pinned resource.

1. Create the child and its key. The key column, its NOT NULL and primary key, and a foreign key back to the source. Choose on_delete for the relationship; Concourse cascades.

retrofit op add CreateTable public resource_pins
retrofit op add AddColumn public.resource_pins resource_id integer
retrofit op add AddNotNull public.resource_pins.resource_id
retrofit op add AddPrimaryKey public.resource_pins resource_pins_pkey --column=resource_id
retrofit op add AddForeignKey public.resource_pins referenced_table=public.resources \
  on_delete=cascade name=resource_pins_resource_id_fkey --column=resource_id --referenced_column=id

2. Clear the values the domain considers invalid. A pin_comment on an unpinned resource has no pin to belong to, and Concourse discards it. The operators do not decide that, so author it as its own step, ahead of the moves, and say what it drops. Its reverse is empty: on --down the cleared comments do not come back.

retrofit op add RunRawScript \
  'DO $$ BEGIN UPDATE public.resources SET pin_comment = NULL WHERE api_pinned_version IS NULL AND pin_comment IS NOT NULL; END $$;' \
  'reverse_body=DO $$ BEGIN END $$;' \
  'reason=drop the pin_comment of every unpinned resource'

3. Move each column along the key.

retrofit op add MoveColumn public.resources.api_pinned_version public.resource_pins --key_pair="from=id,to=resource_id"
retrofit op add MoveColumn public.resources.pin_comment public.resource_pins --key_pair="from=id,to=resource_id"

The pair runs from the source primary key to the child key. Where a moved value IS NOT NULL and the child has no row for that key yet, the move inserts one, and reports it as a padded N row(s) notice. It then copies each value, checks the copy, and drops the source column. A source row with no value gets no child row. migrate --down moves every value back.

4. Rename to the target shape, then add NOT NULL.

retrofit op add RenameColumn public.resource_pins.api_pinned_version version
retrofit op add RenameColumn public.resource_pins.pin_comment comment_text
retrofit op add AddNotNull public.resource_pins.version
retrofit op add AddNotNull public.resource_pins.comment_text "fill=''"

fill= is the hand-written COALESCE(pin_comment, ''): it rewrites the NULLs left to '' before SET NOT NULL. op add warns that this loses data (each NULL becomes ''), and the down leaves the '' in place.

Without step 2 the child is not sparse in version. The pin_comment move pads a child row for each unpinned resource holding a comment, and that row’s version is NULL. AddNotNull on version then fails at apply: PostgreSQL reports column "version" of relation "resource_pins" contains null values. Add that NOT NULL between the two moves instead and the pin_comment move stops at apply, before it writes a row, with a hint to clear the unpartnered values. Either way, step 2 is the way out. Nothing in retrofit drops those comments for you.

Rolling back. A --down across the whole window prints no script, because the renames and NOT NULLs change the moved columns the moves read. Its hint names a rollback in two steps, split at the last MoveColumn.

Before 1.0, any release may change behavior; the release notes list what changed.

Published .