retrofit
0.14.0
retrofit.sh

Import existing SQL

retrofit import turns .sql files you already have (a schema file, a pg_dump --schema-only, a set of migration scripts) into an oplog, the log of operators (ops) your schema is recorded in. Each statement it understands becomes an op, in the order the files state them (a drop a function body reads, and a function created before what its body binds, can move; see Drops read by function bodies and Functions created before what their bodies bind); it reads the files and never changes them, and never touches a database.

retrofit import is the reference: every flag, every statement import recognizes, and each refusal and exit code.

Import a schema file

retrofit import --schema sales schema.sql

The output lists each op as it is appended, then a summary line. When sales is not registered yet, the run registers it and writes sales.oplog, so nothing has to be set up first beyond retrofit init. Without --schema, import picks the schema the way every command does (The resolver chain).

- reads one source from standard input:

retrofit import --schema sales - < schema.sql

Import several files

retrofit import --schema sales 01_tables.sql 02_views.sql

The files are imported as one batch, in the order you pass them: if any file fails to parse or validate, nothing is written. A batch that skips content retrofit cannot represent still writes the ops it recognized before it fails (Skipped statements). A migration tree whose order matters is covered in Import a migration tree in order.

Preview with --dry-run

retrofit import --schema sales schema.sql --dry-run

--dry-run runs the whole import, prints the ops it would append and stops on the refusals the real run raises, and writes no ops. The two guards on --force print a preview instead of refusing. A schema not registered yet stays unregistered. Dry run has the details.

Keep the rows your SQL inserts

INSERT, UPDATE, DELETE and MERGE statements are not ops. import writes each one, as written, to a data file beside the oplog, one file per table (The data file), which snapshot and migrate read when they emit SQL. Say what the rows are when you import them:

retrofit import --schema sales lookups.sql --ref currency     # reference rows, in every emit
retrofit import --schema sales seed.sql --label env:dev       # a dataset you name at emit time
retrofit import --schema sales backfill.sql --purpose transition  # a one-time data change

A one-time data change (--purpose transition or --purpose cleanse) is experimental: it works only in a project whose retrofit.toml opts into it (Experimental purposes). A backfill written as a DO block needs no opt-in.

A statement you give no such answer for is listed in the output and written nowhere. To import the DDL alone, add --ddl-only. Preserved DML has the classification rules.

Check what was skipped

Statements outside retrofit’s vocabulary, such as standalone sequences and GRANT, are listed in the output and not recorded. The summary line counts them by kind:

imported 12 ops; skipped 2 statements (1 sequences, 1 GrantStmt)

To keep the list in a file:

retrofit import --schema sales schema.sql --skip-manifest skips.jsonl

When a skip drops something no op can hold, the run still writes the other ops and ends with an error naming the kinds. Read the skipped statements, then decide whether to accept the loss. Skipped statements lists every case.

Mark the baseline stable

retrofit import --schema sales schema.sql --seal

--seal adds a Seal after the imported ops, which marks them as a shipped baseline later work builds on (Sealed and unsealed ops). Its reason names the files in the order you imported them. To seal each file of a migration history on its own, see Seal each migration.

Extend or start over

Importing into a schema that already has ops extends it: the new ops follow the existing ones.

retrofit import --schema sales 03_indexes.sql

This gives the same ops as importing all the files in one run, apart from their ids. To start over from the files instead, add --force, which discards the oplog’s existing ops:

retrofit import --schema sales schema.sql --force

A sealed history, or data files tied to the old ops, stop a --force run until you name them; Extending and replacing has the flags. A branch that kept adding to the old history cannot merge with the new one: add its ops again on the new history.

Import a dump of a schema you already track

A pg_dump --schema-only of a database describes where the schema is now, not the steps that got it there. Offered to a schema that already has ops, import prints an error and writes nothing, since appending it would record every object twice. The error’s hint names the command for what you meant:

  • The database changed outside the oplog and the oplog should catch up: run retrofit drift <source>, which records the difference as new ops. This is the usual answer.
  • You want to start over from the dump: run retrofit import --force <source>. The existing history is gone.
  • You wanted the rows, not the schema: run retrofit data capture <source>, which writes the dump’s rows to data files and leaves the oplog as it is. A dump whose rows are COPY blocks needs pg_dump --inserts or --column-inserts first.

Authoring ops: import vs drift explains the difference between the two commands.

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

Published .