Create a project
A 5-minute walkthrough: build (or bootstrap) a small schema, look at the oplog file you just created, and emit DDL. It starts from an installed retrofit; Install covers that.
There are two ways to start an oplog:
- From scratch: register a schema and type each DDL change as a command, one at a time. Best when you’re designing a new schema or learning retrofit. Jump to Start from scratch.
- From an existing
.sqlfile: bootstrap an oplog from hand-written DDL or apg_dump --schema-only.importwalks the SQL and records the same changes as an oplog you can evolve from there. Jump to Start from existing SQL.
The two paths share init, snapshot, and op seal; only the
middle differs.
If you want the model first and the commands second, read The oplog before this page. If you want the rules of the road, read Principles.
Set up the project
mkdir myschema && cd myschema
git init
retrofit init
init writes two files:
retrofit.toml: project config. Engine, version, search path, schema registry. More[[schema]]rows get added as you register schemas..gitattributes: marks*.oplogas text so git diffs them cleanly, and marks*.data.sqltext eol=lfso a checkout rewrite (e.g.core.autocrlf=true) does not touch data file line endings.
# retrofit.toml
schema_version = "1"
[project]
engine = "postgres"
engine_version = "17"
[git]
# warn when `.gitattributes` lacks `*.oplog merge=union text eol=lf`.
warn_missing = true
[search_path]
schemas = ["public"]
engine_version is the PostgreSQL major production runs. PostgreSQL 15,
16, 17 and 18 are supported, and 17 is the default. Every verb works at
each of the four, and snapshot and migrate write PostgreSQL 17 SQL at
each, which every supported server accepts. The PostgreSQL 18 syntax
(VIRTUAL generated columns, NOT ENFORCED constraints, WITHOUT OVERLAPS and PERIOD keys, NO INHERIT on a NOT NULL) is not
available in this build: op add refuses it with syntax-not-available,
and import and drift exit with parse-error on it.
PostgreSQL versions says what each verb does at
each major. The project value applies to every schema; use
schema create --engine-version, import --engine-version, or
schema engine to set one schema’s major.
retrofit init --no-gitattributes scaffolds warn_missing = false
instead, so a project that deliberately declined the merge-driver line
is not warned about that same decision on every later command.
Start from scratch
You’ll register a schema, create a table, print its DDL, rename a column to see identity preservation in action, and protect what you’ve shipped.
Register a schema
retrofit schema create users
This is the first command that touches the oplog. The CLI prints the resolved schema to stderr and the change it appended to stdout:
note: schema-context: users
+ <schema-op> CreateSchema users
The stdout line is one operator (op): a single schema change, here
CreateSchema, appended to the oplog.
This page writes <schema-op>, <users-id> and similar placeholders
where your terminal shows 12-character hex.
Opids and ElementIDs are
random draws, and a seal digest covers bytes that contain them, so your
values differ on every run. The shape does not: which slot holds what,
and which reference points where.
Look at the file it just minted:
cat users.oplog
# retrofit-oplog-format: 1
# retrofit-model-version: 1
# engine-version: 17
# retrofit-oplog-form: merge
CreateSchema(<schema-op>, prev=000000000000, id=<users-id>, name="users")
That’s the entire on-disk format: four header lines, then one operator
per line. Long lines are wrapped here to make them easier to read. <schema-op> is the opid, a 12-character hex id drawn
at random when the op is minted. prev=000000000000 is the predecessor
opid; the first op in a schema points at the all-zero null.
id=<users-id> is the ElementID of the schema element this op
minted. Every element an op creates has one, and it stays the same for
the element’s whole life.
schema create also added a [[schema]] block to retrofit.toml
pointing at the new oplog file (<name>.oplog, derived from the
schema name), and auto-pinned it as the project default:
schema_version = "1"
default_schema = "users"
[project]
engine = "postgres"
engine_version = "17"
[git]
# warn when `.gitattributes` lacks `*.oplog merge=union text eol=lf`.
warn_missing = true
[search_path]
schemas = ["public"]
[[schema]]
name = "users"
engine_version = "17"
# oplog_root = "." # directory holding this schema's oplog (the file is <name>.oplog)
# data_root = "data" # directory this schema's data files are minted into
Every optional key is scaffolded as a comment naming its default and
what moving it moves, so the default applies by absence: the file
teaches you the knobs without pre-committing you to any of them. Here
oplog_root is commented because the oplog landed in the project root,
which is its default; schema create <name> <directory> writes the key
uncommented when you name somewhere else.
data_root is where this schema’s data files live: the .data.sql
files import preserves and migrate splices back in. It is scaffolded
commented; unset, it inherits [project] data_root, then defaults to
data under the project root. Uncomment it to site the corpus somewhere
else.
Beside data_root, a [[schema]] block may hold
data_band_max_bytes = <integer>: the largest source band (the sum of
every INSERT statement naming the table in one source file), in bytes
per table, that data capture, import and drift will ingest for
that schema. A table over the bound refuses data-band-over-cap
before anything is written. Absent, a built-in default applies; there is
no unlimited setting. 0, a negative value, or one too wide for a 64-bit
integer refuses config-invalid at load; a non-integer or a quoted
"4096" refuses config-malformed. The key is per-schema only.
Reference data (rows applied on every migration, not one-time seed) is
declared in the oplog, not in config: retrofit schema ref-tables add <table>
mints a SetReferenceData op, and import / drift take --ref <table>.
See The data file.
default_schema is auto-set to the first registered schema. In a
single-schema project you never type --schema again: every verb
resolves to this schema via the resolver
chain.
Create a table
A table with two columns and a primary key is four changes, one op add
each:
retrofit op add CreateTable people
retrofit op add AddColumn people id bigint identity=by_default
retrofit op add AddColumn people email text not_null=true
retrofit op add AddPrimaryKey people --column people.id name=people_pkey
Each op add prints one line per appended op to stdout:
+ <table-op> CreateTable users.people
+ <id-op> AddColumn users.people.id
+ <email-op> AddColumn users.people.email
+ <pk-op> AddPrimaryKey users.people.id (constraint people_pkey)
The + prefix means appended. The opid is what you’d reference if
you wanted to revise or remove this op later. Each command also prints
the note: schema-context: users line to stderr, as schema create
did; the output in the rest of this section leaves it out.
The oplog file now reads:
# retrofit-oplog-format: 1
# retrofit-model-version: 1
# engine-version: 17
# retrofit-oplog-form: merge
CreateSchema(<schema-op>, prev=000000000000, id=<users-id>, name="users")
CreateTable(<table-op>, prev=<schema-op>, in=<users-id>, id=<people-id>, name="people", reference_data=false)
AddColumn(<id-op>, prev=<table-op>, in=<people-id>, id=<col-id-id>, name="id", type="bigint", not_null=false, identity=by_default, sequence_name="people_id_seq")
AddColumn(<email-op>, prev=<id-op>, in=<people-id>, id=<col-email-id>, name="email", type="text", not_null=true)
AddPrimaryKey(<pk-op>, prev=<email-op>, in=<people-id>, id=<pk-id>, columns=[<col-id-id>], name="people_pkey")
Each line: operator name, opid, predecessor, target/payload. The
prev= chain walks straight back up the file, and every op binds to
its target by ElementID, not by name: AddPrimaryKey knows its
column as <col-id-id>, the id AddColumn minted, not as id.
That’s how renames preserve identity (next section).
Get DDL
retrofit snapshot
-- retrofit snapshot v1 opid=290cfe80f1b4 engine=postgres engine_version=17 include-drop=none
--
-- Schema: "users"
--
CREATE SCHEMA IF NOT EXISTS "users";
--
-- Table: "users"."people"
--
CREATE TABLE "users"."people" (
"id" bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"email" text NOT NULL,
CONSTRAINT "people_pkey" PRIMARY KEY ("id")
);
The snapshot is deterministic: the same oplog, engine version and
flags produce the same SQL, byte for byte, on every machine. The
banner’s opid= is not an oplog opid. It is a hash of the DDL body
below it, so two oplogs that converge on one schema have the same
banner opid=, a flag that changes the body changes it, and it is the
one hash on this page your terminal will match. The rest of the banner
echoes the run’s options.
Rename a column
retrofit op add RenameColumn people.email email_address
+ <rename-op> RenameColumn users.people.email
note: apply-risk-window-unsealed: schema users: no Seal, so the migration window begins at root and 1 table in it was treated as newborn (empty); seal what production carries so the apply-risk gate can see it
The note: line is an advisory, not a refusal: the op was
appended, and the exit code is unchanged. The rename is recorded
against the column’s identity. Advisories for op add are printed to stderr, keyed by a stable code you can switch on
(apply-risk-window-unsealed); the message text is not part of the
contract. Advisory note codes
has the full inventory and the JSON shape.
A rename can still break what lies outside the oplog: an application
still selecting email fails once the rename reaches the database. The
advisory for that (apply-risk-rename-in-use) is withheld here, because
with no Seal the migration window begins at root,
so people and its email column are new in it and treated as having
no readers yet. The note says so. Once you seal what production already
has, or pin the cut it is at with --database-at
(see Consents), renaming a column created before that
point warns about the readers still using its old name.
An advisory never fails a run by itself. To make CI fail on the
lock-taking hazards before they reach a populated table, add
retrofit validate --fail-on-apply-risk as the CI step: it evaluates
the migration window since the latest Seal (the one migrate emits by
default) and exits 2 when that window holds a validating
AddForeignKey or AddCheck, or an AddNotNull, on a table that may
hold rows. A table created earlier in the same window is empty and is
not counted, which is why the window must start at what production
already has. It is counted once an op gives it rows, such as a
RunRawScript (an opaque op the gate cannot see into) or a
MergeTable that moves rows into it. A Seal
records no deployment, so when production sits behind the latest Seal,
pass its cut with --from <cut>, as you would to migrate --from
(Where each database is), and the
gate evaluates the window from that cut to the head. When the window
begins at root (no Seal, or --from root), validate also names the
unsealed window (apply-risk-window-unsealed); that note never changes
the exit and can accompany counted hazards.
The gate also counts a column-type change (CastColumn) that rewrites the table or scans it for an index or
check, as apply-risk-window-table-rewrite, when its recovery is one retrofit
can express: re-author it as expand/contract (add the new column, copy writes
by a trigger, backfill in committed batches, re-create each dependent on the new
column, then drop the old and rename the new into its place), or, for a scan,
drop the dependent before the cast and re-add it after. A cast whose recovery
retrofit does not vouch for stays advisory, as do apply-risk-add-unique,
apply-risk-add-exclude and apply-risk-rename-in-use.
The new oplog line:
RenameColumn(<rename-op>, prev=<pk-op>, on=<col-email-id>, name="email_address")
on=<col-email-id> is the same ElementID minted at AddColumn. The
column’s identity is preserved. AddPrimaryKey (which still references
that column id) doesn’t break, doesn’t need rewriting, doesn’t get
confused with a drop-then-add. Snapshot now emits "email_address"
where it used to emit "email":
retrofit snapshot
CREATE TABLE "users"."people" (
"id" bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
"email_address" text NOT NULL,
CONSTRAINT "people_pkey" PRIMARY KEY ("id")
);
The two end states alone cannot tell this rename from dropping email
and adding email_address, a pair that loses the column’s rows. The
oplog records which one happened.
Protect what you’ve shipped
While the oplog tail is unsealed, you can edit it freely
(op revise, op remove, even editor-buffer line deletion). Once
you’re ready to call a range “done” (published, deployed, depended on
by other people), you seal it:
retrofit op seal
sealed 6 ops as <seal-op>
A Seal op gets appended at the end of the oplog. It pins a Merkle
digest over the sealed ops’ canonical bytes, so editing any sealed op
in place changes the digest and breaks verification.
Seal(<seal-op>, prev=<rename-op>, range=[{first=<schema-op>, last=<rename-op>, lines="6", digest="<seal-digest>"}])
After seal, op revise on a sealed op is rejected. New ops still go on
the unsealed tail behind the seal.
The tail is editable because nothing depends on it. That makes sealing
a range the thing you do before anyone else pulls it and applies it to a
database. If a teammate applied an unsealed op and you then op revise
it, the opid re-mints: the op they applied is no longer on the log, and
their database can’t be placed on it. The recourse is to rebuild that
database from the log (retrofit migrate --from root --to last
against a fresh database), not to reconcile it with retrofit drift,
which would adopt the revision backwards.
Start from existing SQL
If you already have a schema (hand-written DDL, a pg_dump --schema-only of a live database, a file checked in next to your
application), retrofit import walks it and mints the equivalent
oplog. From there you evolve the schema with the same op add /
snapshot / seal workflow as
Start from scratch.
Set up the project first, as Set up the project does, if you skipped that:
mkdir myschema && cd myschema
git init
retrofit init
Suppose you have a sales.sql file:
CREATE TABLE customers (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
email text NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
customer_id bigint NOT NULL REFERENCES customers(id),
placed_at timestamptz NOT NULL,
PRIMARY KEY (id)
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Import with --schema sales. If sales isn’t registered yet,
import lazy-creates it (mints the oplog, appends [[schema]],
auto-pins it as default if it’s the first schema):
retrofit import --schema sales sales.sql
import prints the resolved schema and its advisory notes to stderr,
one line per minted op to stdout, and a terminal summary:
note: schema-context: sales
+ <op> CreateTable sales.customers
+ <op> AddColumn sales.customers.id
+ <op> AddColumn sales.customers.email
+ <op> AddNotNull sales.customers.email
+ <op> AddPrimaryKey sales.customers.id (constraint customers_pkey)
+ <op> CreateTable sales.orders
# (orders columns, NOT NULLs, PK, FK elided for brevity)
+ <op> CreateIndex sales.orders.customer_id (index orders_customer_id_idx)
note: apply-risk-window-unsealed: schema sales: no Seal, so the migration window begins at root and 2 tables in it were treated as newborn (empty); seal what production carries so the apply-risk gate can see it
note: apply-risk-recorded-unsealed: schema sales: recorded sales.customers, sales.orders into the unsealed tail; apply-risk reads what the tail creates as unreleased and new
# (hint elided)
imported 14 ops; skipped 0 statements
As in Start from scratch, <op> stands in for a real 12-char opid; only the
column of operator names and targets is stable across runs.
The two note: lines are advisories, and neither changes the exit.
With no Seal, the migration window begins at root, so both tables this
import creates are new in it and treated as empty: the three
AddNotNulls and the validating AddForeignKey are not counted as
hazards. When a released database already holds these tables, the
elided hint says what to do: retrofit op seal once the unsealed tail
is what that database took, or point the CI gate at that database’s cut
with --from <cut>. An import that adds risky ops to tables the
window’s start already holds also prints one apply-risk ops minted
summary line on stdout per run;
Output contract gives its shape.
Each line names the most specific element the op touches:
CreateTable lands on <schema>.<table>, AddColumn on
<schema>.<table>.<column>, and constraint ops on the column they
bind. Where the constraint has a name, it follows as a parenthesized
annotation after the target. A constraint written
without one in the source (PRIMARY KEY (id), REFERENCES customers(id)) still gets annotated, because import derives
PostgreSQL’s own default (customers_pkey,
orders_customer_id_fkey). What has no name to show, like
AddNotNull, points at the column alone, and stays that way: it sets
the column’s NOT NULL bit rather than minting a named constraint, so
there is no name slot to fill in later. CreateIndex follows the
same rule with the index name.
(If you’d rather scaffold the schema first, retrofit schema create sales is the rich-form authoring path; see Work with several
schemas.)
From this point on the oplog is the source of truth, not the original
.sql. Commit sales.oplog (and any other registered oplog files;
each schema gets <name>.oplog) into git; treat the source SQL as a
starting fixture you imported from, not a thing to keep in sync by
hand.
Import several .sql files
If your schema is split across several .sql files (e.g.
01_tables.sql, 02_indexes.sql, 03_views.sql), pass them all to
one import invocation. Shell globs work directly:
retrofit import --schema sales schema/*.sql
All sources import as one batch in argv order, with one validate and one atomic write, so a file that fails to parse or validate leaves the oplog untouched. The result matches importing the files one at a time in the same order, apart from the minted ids.
import reads sources in argv order without re-sorting. If
your filenames are numbered without padding (V1__a.sql,
V10__b.sql, …), a bare shell glob can pass them out of order (V1, V10, V11, V2). See Import a migration tree in order
for how to pass them in order.
Verify the round-trip
Snapshot the new oplog and compare it with your source:
retrofit snapshot
What retrofit cannot model as operators is reported on the skip-manifest (below), not dropped.
Check what import skipped
The import vocabulary covers tables, columns, NOT NULL / CHECK /
PK / FK / UNIQUE constraints, indexes, identity & generated columns,
views, materialized views, functions, triggers, policies, extensions,
DO blocks, and table/column comments. It does not cover
standalone sequences, privileges, or cross-schema objects. Those are
reported on stdout and never written to the oplog:
imported 12 ops; skipped 2 statements (1 sequences, 1 object-privileges)
Pass --as=json to retain the per-statement skip detail.
Custom DML (INSERT/UPDATE/DELETE/MERGE) is not merely
reported: import sets it aside verbatim in a per-table data file
(<schema>.<table>.<YYYYMMDDHHMM>….data.sql) under the schema’s
data_root. migrate splices it
back into a migration automatically, and snapshot --label <l> embeds its
seed rows. See The data file.
For a complete list of what import does and doesn’t recognize, run
retrofit import --help-all.
A dump from a managed database may hold objects your application does not author, such as a provider’s extensions and schemas. Exclude objects the project does not own keeps them out of the oplog.
Continue from here
You now have an oplog you can evolve. Append new ops the same way as
in Start from scratch (retrofit op add ...), and you can rename, drop, and
restructure with full identity preservation, even though you didn’t
type the original CreateTable calls yourself.
A common next step after a clean import is to seal the imported prefix, so the bootstrap is a stable baseline and only your subsequent changes live on the unsealed tail:
retrofit op seal
You can also pass --seal to import to seal in one step:
retrofit import --schema sales sales.sql --seal
Bare --seal derives the Seal reason from the source basename
(reason="imported sales.sql"); pass --seal="<text>" to override
with a custom string (e.g. --seal="bootstrap").
What’s next says where to read from here.