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:
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:
idis sortable —YYYYMMDDHHMMSS_<slug>is canonicaldescriptionis short and human-readableupis the SQL applied forward, sent as a single batch to Postgres and run inside one transactiondownis recorded but never executed by the runner — irreversible-by-default is the safe banking posture
Running
use cratestack::{apply_pending, status, ensure_migrations_table};
ensure_migrations_table(&pool).await?;
let applied: Vec<String> = apply_pending(&pool, &migrations).await?;The runner:
- compares each input migration against
cratestack_migrations - skips already-applied rows whose checksum matches
- aborts with
CratestackError::Internalif an applied row's checksum has drifted - for each pending row: opens a transaction, sends the entire
upscript in one batch viasqlx::raw_sql(&migration.up), inserts the record intocratestack_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:
migration `20260101000000_create_accounts` is recorded as applied but its
SQL has changed; resolve drift before continuingThe 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:
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:
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
- not a
down/rollback engine —downis recorded for audit but never run - not a parallel-applier — migrations are sequential and serialized through the tracking table
- not a long-running-migration coordinator — banks executing a 6-hour
ALTER TABLEuse 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.cstackagainstmigrations/<backend>/schema.snapshot.jsonand writes a new migration directory.cratestack migrate baseline— adopts a database that already has tables and no priorcratestackmigration 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 frommigrate baselineabove, which introspects a live database too but only for the one-time act of adoption — it writes a new snapshot and acratestack_migrationsrow, wheremigrate driftwould 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:
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:
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:
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:
model Category {
id Int @id
label String
@@rename(from = "categorys")
}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:
model Application {
id String @id
tenantId String
tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade, onUpdate: Restrict)
}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:
onDelete/onUpdatecan only be declared on the relation's owning side — theList-typed "many" side has no physical column of its own to attach a constraint to.onDelete: SetNull/onUpdate: SetNullrequires the local foreign-key field to be optional (tenantId String?);SetDefaultrequires 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:
model Application {
id String @id
tenantId String
name String
environment String
@@unique([tenantId, name, environment])
}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
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.
Read Next
- 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 - ADR 0004: Schema diff and migration generation — how
.cstackchanges turn into the SQL this runner applies - Schema diff (CLI) —
cratestack diffchecks the same two.cstackversions for wire-contract breaking changes, independent of the DB migration this page describes - Composite keys —
@@id([...])/@@unique([...]), the multi-column constraints this generator emits - Field attributes — full
@relation/onDelete/onUpdate/@@uniquesyntax reference - Audit log — banks frequently land
@@auditretroactively via a migration - Soft delete —
deleted_atcolumns are typically added by a follow-up migration on existing models