Skip to content

Migrations ​

Banks ship database changes the same way they ship code: a reviewable SQL diff, recorded in source control, applied once, never edited after the fact. CrateStack's migration runner enforces that contract.

Shape ​

A migration is a struct, not a file convention — banks integrate it into whatever build tooling they already use:

rust
use cratestack::Migration;

let migrations = vec![
    Migration {
        id: "20260101000000_create_accounts".to_owned(),
        description: "create accounts table".to_owned(),
        up: r#"
            CREATE TABLE accounts (
                id BIGINT PRIMARY KEY,
                balance NUMERIC NOT NULL DEFAULT 0,
                version BIGINT NOT NULL DEFAULT 0
            );
            CREATE INDEX accounts_balance_idx ON accounts (balance);
        "#.to_owned(),
        down: None,
    },
    Migration {
        id: "20260201000000_add_currency".to_owned(),
        description: "add currency to accounts".to_owned(),
        up: "ALTER TABLE accounts ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';".to_owned(),
        down: None,
    },
];

Conventions banks adopt:

  1. id is sortable — YYYYMMDDHHMMSS_<slug> is canonical
  2. description is short and human-readable
  3. up is the SQL applied forward, sent as a single batch to Postgres and run inside one transaction
  4. down is recorded but never executed by the runner — irreversible-by-default is the safe banking posture

Running ​

rust
use cratestack::{apply_pending, status, ensure_migrations_table};

ensure_migrations_table(&pool).await?;
let applied: Vec<String> = apply_pending(&pool, &migrations).await?;

The runner:

  1. compares each input migration against cratestack_migrations
  2. skips already-applied rows whose checksum matches
  3. aborts with CratestackError::Internal if an applied row's checksum has drifted
  4. for each pending row: opens a transaction, sends the entire up script in one batch via sqlx::raw_sql(&migration.up), inserts the record into cratestack_migrations, commits

A failure anywhere in the batch rolls the whole migration back. A multi- statement script with a broken second statement leaves zero artefacts — the first CREATE TABLE rolls back with the failed CREATE INDEX, and cratestack_migrations does not record the partial attempt.

Checksum drift ​

Each migration's checksum is SHA-256(id || \0 || description || \0 || up). Editing an already-applied migration in source control changes the checksum:

text
migration `20260101000000_create_accounts` is recorded as applied but its
SQL has changed; resolve drift before continuing

The runner refuses to apply anything until the drift is resolved. Banks treat this as a release-process failure to escalate to humans — there is no --force flag. Restoring the original SQL or rolling forward with a new migration are the two acceptable resolutions.

Inspecting state ​

status(&pool, &migrations) returns one MigrationState per input:

rust
pub struct MigrationState {
    pub id: String,
    pub status: MigrationStatus,
}

pub enum MigrationStatus {
    Pending,
    Applied,
    ChecksumMismatch,
}

Banks plug this into a deployment dashboard so operators see drift before the next deploy attempt.

Multi-statement scripts ​

An earlier version of the runner split up on ; client-side before executing each statement. That approach broke on dollar-quoted PL/pgSQL bodies, which routinely contain their own semicolons (issue #270), so as of v0.6.0 the runner no longer parses or splits up at all: the entire script is sent to Postgres in a single batch via sqlx::raw_sql(&migration.up), which uses the simple-query protocol and lets Postgres itself handle statement boundaries — dollar-quoting included. Common patterns this enables:

sql
CREATE TABLE transfers (
    id BIGINT PRIMARY KEY,
    amount BIGINT NOT NULL,
    status TEXT NOT NULL
);
CREATE INDEX transfers_status_idx ON transfers (status);
INSERT INTO transfers_status_lookup (key, label)
    VALUES ('pending', 'Pending'), ('settled', 'Settled');

All three statements land atomically. A failure in the INSERT rolls the CREATE TABLE and CREATE INDEX back together.

What the runner is not ​

  1. not a down/rollback engine — down is recorded for audit but never run
  2. not a parallel-applier — migrations are sequential and serialized through the tracking table
  3. not a long-running-migration coordinator — banks executing a 6-hour ALTER TABLE use their own backfill tooling and record the migration as a no-op when the backfill is done

Generating migrations from .cstack ​

The runner consumes SQL migrations identically whether they are hand-written or generated. CrateStack ships a separate schema diff generator that produces those migrations from .cstack against a committed schema snapshot — see ADR 0004 for the full design.

Today, two subcommands are shipped:

  • cratestack migrate diff — offline. Diffs the current .cstack against migrations/<backend>/schema.snapshot.json and writes a new migration directory.
  • cratestack migrate baseline — adopts a database that already has tables and no prior cratestack migration history, by introspecting it directly instead of assuming an empty starting point. See Adopting an Existing Database for a full walkthrough against a real database.

Two more are planned but not yet implemented, per the "shipping order" list:

  • cratestack migrate verify — deferred. Intended as a CI gate that replays the full migration history against an ephemeral DB and checks the result matches the snapshot; blocked on ephemeral-DB spawning support.
  • cratestack migrate drift — deferred. Intended as a read-only ops tool reporting differences between the committed snapshot and a live database, without writing anything. Distinct from migrate baseline above, which introspects a live database too but only for the one-time act of adoption — it writes a new snapshot and a cratestack_migrations row, where migrate drift would only report.

Generated migrations remain reviewable SQL diffs — that property is preserved. The generator just removes the hand-translation step from .cstack to SQL. Destructive operations (column drop, lossy type change) still require explicit opt-in, and renames still require an explicit @@rename (table) / @rename (column) annotation.

Hand-written migration steps coexist with generated ones via optional up.pre.sql / up.post.sql files inside the migration directory; the generator never overwrites them. Use these for backfills, lookup-table seeds, materialized-view refreshes, and any transform the diff engine cannot infer.

Splitting a schema changes no migration ​

(since 0.19.0) Splitting one schema into files with part / part of, or moving shared types behind an import, produces no migration: the generator works on the merged schema, and the snapshot of a split schema equals the snapshot of the same declarations in one file. cratestack migrate diff against a committed snapshot reports nothing after the split. See Multi-file Schemas.

Renaming a table or column with @@map ​

(since 0.20.1, cratestack#1375). @@map("<table>") and @map("<column>") set the SQL name without touching routes, op ids or clients (see Storage names). Because the diff matches tables and columns by name, changing a storage name is refused unless you say it is a rename:

cstack
model Bank {
  code String @id
  bic  String @map("bic_code") @rename(from = "bic")

  @@map("bank")
  @@rename(from = "banks")
}

This generates ALTER TABLE banks RENAME TO bank and ALTER TABLE bank RENAME COLUMN bic TO bic_code. Without the @@rename / @rename, migrate diff refuses rather than drop and recreate. It names the exact marker to add:

text
model `Bank`: its table changed from `banks` to `bank` (`@@map`), and a changed name would drop
`banks` and create an empty `bank`. To rename the table, add `@@rename(from = "banks")` to the
model; to keep it, restore the old `@@map`. To adopt a database that already has `bank`, run
`cratestack migrate baseline` after writing the `@@map`.

The column refusal has the same form (field bicis stored in columnbic_code, not bic (@map)…). Adding @@map to a model that already has a table is such a change too. Remove the @@rename marker once the migration has shipped.

To adopt an existing database, do not rename its tables: write @@map / @map to match them and run migrate baseline. Index and foreign-key names are generated from the mapped names, so the first diff after a baseline may drop and re-add an index whose live name differs; review it.

migrate baseline stamps which model and field each table and column stores onto the snapshot, so a @@map / @map changed after the baseline is refused the same way (TableStorageNameChanged / ColumnStorageNameChanged). Where a snapshot records no such identity (a baseline whose table name differed from the schema's, or one from before this was recorded), a mapped model or column that is new while an unrecorded table or column disappears is refused as TableStorageNameUnrecorded / ColumnStorageNameUnrecorded:

text
model `Bank` is stored in table `bank` (`@@map`), which the previous snapshot does not have, and a
table the snapshot has is gone from the schema. That snapshot does not record which model each
table stores, so this cannot be told from a `@@map` change, and a changed name would drop the old
table and create an empty `bank`. If it is one of the tables that is gone, add <@@rename(from = "…")>
to the model; to keep it, restore the old `@@map`; to drop and create deliberately, do it in two
migrations.

The column form reads the same (table bank(modelBank): field bicis stored in columnbic_code (@map), which the previous snapshot does not have…) and names the @rename(from = "…") to add. This has a known false positive: dropping one table and adding a differently named mapped model in the same diff is refused, so do it in two migrations.

Table naming, pluralization, and @@rename ​

cratestack migrate diff matches tables by name only — it never infers that two tables are "the same one, renamed." A table that disappears from one side and a differently-named table that appears on the other look, to the diff engine, exactly like an unrelated table being dropped and a new one being created. The only way to tell it otherwise is @@rename(from = "<old_table_name>"), declared on the model before running migrate diff:

cstack
model Category {
  id    Int    @id
  label String

  @@rename(from = "categorys")
}
sql
ALTER TABLE categorys RENAME TO categories;

Without the marker, the same situation produces DROP TABLE categorys followed by CREATE TABLE categories — a migration that, if applied against a real deployment, destroys the table's data.

A column is renamed the same way, with @rename(from = "<old_column>") on the field.

Write the marker exactly as shown. cratestack migrate reads only @@rename(from = "…") and @rename(from = "…"), and only the first marker of each kind. Through 0.14.0 any other form, such as @@rename(from: "documents"), @@rename("documents") or @rename(from: "name"), passed cratestack check and was read as no marker at all, so the generated migration dropped and re-created the table or column. Since 0.14.1 other forms are refused, as are a second marker on the same model or field and a @rename on a field of a view, type or auth block or on a relation field. If you generated migrations with a marker in another form, check them for a DROP TABLE or DROP COLUMN of the old name before applying them.

Why this matters right now: the y -> ies pluralization fix ​

A model's table name is derived by pluralizing its snake_cased name. Through v0.7.x that pluralizer only ever appended a bare s to a name ending in y, so model Category derived table categorys. As of cratestack#509 the pluralizer correctly turns a consonant + y ending into ies — Category now derives categories, matching normal English pluralization.

This is exactly the "table disappeared, differently-named table appeared" case above, and it applies with no .cstack change on your part — upgrading the framework version alone changes what table name your existing model derives. Any deployment with a model whose name ends in a consonant + y needs to add @@rename(from = "<old_pluralization>") before running migrate diff against the upgraded framework, or the generated migration will drop and recreate the table.

Affected: any model name ending in consonant + y — Category (categorys -> categories), Delivery (deliverys -> deliveries), Entry (entrys -> entries), Query (querys -> queries).

Not affected: a model name ending in vowel + y — Key (keys), Day (days) — the pluralization rule for those was already a bare s and hasn't changed.

If you're unsure whether a model is affected, compare the table name your currently-deployed schema uses against what cratestack check (or a fresh migrate diff against an empty snapshot) derives for the same model post-upgrade — a mismatch means @@rename is needed.

Foreign keys, referential actions, and composite uniqueness ​

The generator emits real integrity constraints for two .cstack attributes, not just the columns behind them.

@relation fields emit a FOREIGN KEY constraint. The owning side of a relation (the side with fields:/references:, not the has-many List side) is projected into an ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES ... statement, named <table>_<column>_fkey to match Postgres's own auto-generated name:

cstack
model Application {
  id       String @id
  tenantId String
  tenant   Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade, onUpdate: Restrict)
}
sql
ALTER TABLE applications ADD CONSTRAINT applications_tenant_id_fkey
  FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE ON UPDATE RESTRICT;

onDelete/onUpdate accept Cascade, Restrict, SetNull, SetDefault, or NoAction. Both are optional and default to NoAction — Postgres's own default — which the generator omits from the DDL rather than spelling out. Two rules are enforced at cratestack check time, before they'd otherwise surface as a Postgres ADD CONSTRAINT failure with no .cstack context:

  1. onDelete/onUpdate can only be declared on the relation's owning side — the List-typed "many" side has no physical column of its own to attach a constraint to.
  2. onDelete: SetNull / onUpdate: SetNull requires the local foreign-key field to be optional (tenantId String?); SetDefault requires it to declare @default(...).

Model-level @@unique([...]) emits a CREATE UNIQUE INDEX. Field-level @unique already did this for a single column; @@unique extends the same <table>_<col1>_<col2>_..._key naming convention across every listed column, in declaration order:

cstack
model Application {
  id          String @id
  tenantId    String
  name        String
  environment String

  @@unique([tenantId, name, environment])
}
sql
CREATE UNIQUE INDEX applications_tenant_id_name_environment_key
  ON applications (tenant_id, name, environment);

This matters beyond integrity: Postgres only accepts ON CONFLICT (a, b, c) DO UPDATE when a unique index over exactly that column tuple exists, so upsert-based idempotency (see Upsert) depends on this DDL actually being emitted.

SQLite has no ALTER TABLE ... ADD CONSTRAINT. The embedded backend can't retrofit a foreign key onto an existing table, so instead of silently producing no constraint, the generator emits a comment marking where the constraint would be, naming it explicitly so the gap is visible in the generated migration rather than invisible in the database.

Framework source of truth: issue #260 / PR #261 (foreign keys), issue #262 / PR #266 (@@unique indexes), and PR #268 (onDelete/onUpdate).

Schema ​

sql
CREATE TABLE cratestack_migrations (
    id TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    checksum BYTEA NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The DDL is exposed as cratestack::MIGRATIONS_TABLE_DDL and applied idempotently by ensure_migrations_table.

  1. Adopting an Existing Database — cratestack migrate baseline, a full walkthrough of pointing this migration runner at a real, already-populated database for the first time
  2. ADR 0004: Schema diff and migration generation — how .cstack changes turn into the SQL this runner applies
  3. Schema diff (CLI) — cratestack diff checks the same two .cstack versions for wire-contract breaking changes, independent of the DB migration this page describes
  4. Composite keys — @@id([...]) / @@unique([...]), the multi-column constraints this generator emits
  5. Field attributes — full @relation/onDelete/onUpdate/@@unique syntax reference
  6. Audit log — banks frequently land @@audit retroactively via a migration
  7. Soft delete — deleted_at columns are typically added by a follow-up migration on existing models

Released under the MIT License.