Database migrations

ship db diff turns a database declaration change into three checked-in artifact families:

migrations/app/
  snapshots/
    <schema-state-id>.json
  shp/
    20260915070300_rename_email.shp
  sql/
    20260915070300_rename_email.sql

Snapshots are immutable normalized database states. A migration is a typed, ordered edge between two states. The .shp file records semantic intent; the .sql file is its reviewable, editable database program.

Explicit Start and End

Every migration spells its bookends. Inside the migration, Start and End are compiler-provided namespaces generated solely from those snapshots. Historical application source is not loaded.

migration AppDb(
  from = snapshot("8ef3df296ea9afda"),
  to = snapshot("34d7cbaea7145b91"),
) {
  rename(
    from = Column(Start.Users, .email),
    to = Column(End.Users, .contact_email),
  );
}

Table, Column, Index, and Constraint are typed migration selectors, not strings. Entities have hidden stable lineage identities in snapshots. A rename preserves that identity while changing its SQL name.

The generated SQL is ordinary PostgreSQL:

ALTER TABLE users RENAME COLUMN email TO contact_email;

Rename-or-create intent

A diff cannot infer whether one missing column and one new column represent a rename. Interactive use asks. Agents and CI can resolve the same typed question without a terminal:

ship db diff AppDb \
  --name rename-email \
  --rename column:Users.email=Users.contact_email \
  --no-input \
  src/Schema.shp

--json --no-input returns exact candidate choices and runnable CLI arguments. --answers and --answers-file accept the same resolution data in bulk. Temporary answers are not migration history: the generated rename(...) operation is.

Required columns

Adding a required column without a database default cannot invent values for existing rows. Ship writes the safe structural sequence and leaves one explicit hole for the business decision:

add(
  column = Column(End.Users, .handle),
  nullable = True,
);

backfill(
  column = Column(End.Users, .handle),
  sql = todo "write SQL to backfill existing Users rows",
);

set_not_null(
  column = Column(End.Users, .handle),
);

Replace the hole with raw dialect SQL:

backfill(
  column = Column(End.Users, .handle),
  sql = sql {
    UPDATE users
    SET handle = contact_email
    WHERE handle IS NULL;
  },
);

The resulting SQL keeps the authored operation order:

ALTER TABLE users ADD COLUMN handle text;

UPDATE users
SET handle = contact_email
WHERE handle IS NULL;

ALTER TABLE users ALTER COLUMN handle SET NOT NULL;

No SQL artifact is written while a migration contains todo. Rerunning ship db diff after filling the hole compiles the pending migration before considering later schema changes.

Complete operation programs

Migration source contains every semantic operation, not only exceptional hints. The compiler checks the ordered program as a transition:

interpret(Start, operations) == End

SQL transaction boundaries, locks, temporary SQLite rebuild tables, and other dialect mechanics are derived. Durable creates, renames, conversions, backfills, constraints, and drops remain visible in migration source.

SQL-oriented vocabulary keeps the boundary honest:

set_data_type(
  from = Column(Start.Events, .occurred_at),
  to = Column(End.Events, .occurred_at),
  using = sql {
    timestamp with time zone 'epoch'
      + occurred_at * interval '1 second'
  },
);

Known assignment casts may omit using; other conversions receive a generated todo.

Parallel PRs may create several edges from one Start state. Changes to different tables, changes to one table, and migration conflicts walk through that workflow.

Applying

ship db apply reads the applied-node ledger and current frontier, then topologically plans every missing ancestor of the declaration's target. Parallel compatible leaves may coexist in the frontier until a unify checkpoint collapses them. One connection holds a PostgreSQL advisory transaction lock or SQLite immediate-write lock across the read, plan, and apply phases. The complete run and every ledger/frontier update commit in one transaction:

BEGIN
  <reviewed migration SQL>
  INSERT _ship_schema_migrations ...
  DELETE consumed _ship_schema_frontier parents ...
  INSERT produced _ship_schema_frontier head ...
COMMIT

Migration SQL cannot issue BEGIN, COMMIT, ROLLBACK, savepoint, or related transaction-control statements. Ship scans with the selected dialect's quoting and comment rules before execution so reviewed SQL cannot escape the owning lock and ledger transaction.

Edited SQL remains allowed. ship db status validates graph structure and source completeness rather than rejecting reviewed SQL by checksum. ship db verify --live checks typed queries against a connected target.

Recovery is forward by default. Destructive row changes do not acquire a fictional inverse merely because a schema graph can point backward.