retrofit
0.13.0
retrofit.sh

Emit the schema as SQL

retrofit snapshot prints your schema as SQL: its DDL, the rows of its reference tables, and any seed datasets you name. It reads the oplog, the log of operators (ops) your schema is recorded in, and never changes it. Pipe the output to psql to build a database, or keep the file as a schema dump.

retrofit snapshot is the reference: every flag, the order of the output, and each refusal and exit code.

Emit the current schema

retrofit snapshot > schema.sql
psql -v ON_ERROR_STOP=1 -d <database> -f schema.sql

The file holds every live table, column, constraint, index and view, in an order that applies to an empty database, and the rows of the tables you declared as reference data. Read the warnings on stderr before you apply it (see Check what was skipped).

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

Choose the point in the log

By default the output is the schema after the last op. To emit the schema as it stood earlier, name a point with --at:

retrofit snapshot --at seal          # as of the last Seal
retrofit snapshot --at last^         # one op before the last
retrofit snapshot --at 988edcb612ab  # after this op

retrofit op show --include-sealed lists each op with its id, the sealed ones included. A Seal marks the end of the history a migration shipped (Sealed and unsealed ops). The cut lists every form --at takes.

Reconcile a merge first

After a merge in which both branches appended ops, the oplog can hold two histories side by side. snapshot then prints an error and no SQL; its hint names the retrofit reconcile command for that fork. Run it to see the order it would write (it writes nothing), then write the order Reconcile after a merge gives, and emit again. A point before the fork, named with --at, still emits.

Emit only some tables or schemas

retrofit snapshot --table users --table orders   # only these tables
retrofit snapshot --exclude-table audit_log      # every table but this one
retrofit snapshot --exclude-schema staging       # leave a schema out

These match pg_dump’s -t, -T and -N, with exact names and no patterns. A table left out loses its rows too. When a table you keep has a foreign key to a table you leave out, keep both. Filters has the rest.

Prepare the target database

By default the output only creates objects, which suits an empty database. To apply it over a database that already holds the schema, add a drop step with --include-drop:

retrofit snapshot --include-drop=scoped    # drop each emitted table first
retrofit snapshot --include-drop=schema    # drop each whole schema first

scoped drops the tables, indexes, foreign keys, views and materialized views the output recreates, and leaves everything else in place. Another table’s foreign key into those tables, or an object left in place that depends on a table or view it drops (a view the output does not write, a function, a policy), stops the drop when you apply; use schema then. schema drops each schema with CASCADE, which removes every object in it, so use it only on a schema you own outright. Both run the script in one transaction, so a failed apply under psql -v ON_ERROR_STOP=1 leaves the database as it was. The drop prologue has the details.

Include reference and seed rows

Rows come from the data files kept beside the oplog (the data corpus). The rows of tables declared as reference data are always in the output. A seed dataset, such as test fixtures, is in the output only when you name its label:

retrofit snapshot --label env:dev                  # add the env:dev dataset
retrofit snapshot --label base --label env:dev     # layer two datasets, in that order

To see what a label selects without emitting SQL, run retrofit data list --label env:dev.

When a dataset is left out, a warning on stderr names its label. To leave one out on purpose and quiet the warning, name it with --allow-unnamed-label <label>. The data band has the rules for layers, exclusions and ordering.

Emit the schema without rows

retrofit snapshot --ddl-only > schema.sql

--ddl-only leaves every row out, which is the shape of pg_dump --schema-only; pg_dump parity compares the two option by option. The data files are still read, so a problem in them stops this run too. The first line of the file ends in data=omitted.

Load rows into an existing database

retrofit snapshot --data-only --label env:dev > rows.sql
psql -v ON_ERROR_STOP=1 -1 -f rows.sql

--data-only emits the rows alone, for a database whose tables already exist. A table captured as a complete set of rows is brought to exactly that set: rows with a matching key are updated, missing rows are added and the rest are removed. Apply it in one transaction (-1) under a role that may create temporary tables. Applying the same file again changes nothing once the database matches it.

To load only the tables the top layer touches, add --over with the last label:

retrofit snapshot --data-only --label base --label env:dev --over env:dev

A full snapshot and a data-only band describes what each table’s block does.

Check what was skipped

A run that leaves something out still prints the rest of the schema and reports each omission on stderr. Read those lines before you apply.

  • A view is missing. Its body could not be rebuilt at that point in the log, often a SELECT * over a subquery, another view or a NATURAL JOIN. Give the view an explicit column list (CREATE VIEW v (a, b, c) AS …) or name its columns in the body, or recreate it after you apply.
  • A function or trigger is missing. Its body names an element that no longer exists at that point. At an earlier point that is expected; at the last op, the body needs revising.
  • A sequence is not created. A column default draws from a sequence the oplog does not hold. Create the sequence before you apply; the warning prints the statement.
  • A raw script is not in the output. A DO block recorded in the oplog is left out, and stderr gives the count; retrofit migrate emits it.

To keep a list of the skipped views and functions in a file:

retrofit snapshot --skip-manifest skips.jsonl > schema.sql

What the output leaves out and Render failures list every case.

Emit for another PostgreSQL major

retrofit snapshot --using=postgres:16

--using changes the target for one run and leaves retrofit.toml as it is. To change it for good, run retrofit schema engine <version>. PostgreSQL versions lists the supported majors.

  • retrofit snapshot: the reference for every flag, the output and each refusal.
  • The oplog: the oplog, replay and identity.
  • pg_dump parity: snapshot’s flags beside pg_dump --schema-only’s.
  • migrate: emit the script between two points in the log.
  • drift: the other direction, recording a schema changed outside the oplog.
  • retrofit data: the data files and their labels.

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

Published .