Diff migrations for schema evolution
Edit on GitHubSnapshot-driven incremental migrations across Alembic (Python), golang-migrate (Go), and Prisma (TS)
Last updated:
At a glance
compile is no longer single-shot. On every run the compiler:
- Writes
.spec-snapshot.jsonat the emitted project root — a serializedDatabaseSchemawith aschemaVersionenvelope. Always overwritten, intended to be committed. - Reads any existing
.spec-snapshot.jsonto recover the schema as the project last saw it. - Reads the existing migration revisions in the target-specific directory.
- If no snapshot exists → emits the initial migration (
001_initial_schema.*) covering the full schema. - If a snapshot exists and the schema is unchanged → emits no new migration. The initial migration file is not in the emit list at all (it stays exactly as the user committed it).
- If a snapshot exists and the schema has changed → emits
NNN_schema_update.*whereNNN = max(existingRevisions) + 1, containing only the delta.down_revisionchains to the previous head (Alembic). The initial migration is still not re-emitted.
The diff algorithm operates on the target-agnostic DatabaseSchema. Only the rendering layer is target-specific.
Per-target file layout
| Target | Snapshot | Initial migration | Delta migration |
|---|---|---|---|
python-fastapi-postgres | .spec-snapshot.json | alembic/versions/001_initial_schema.py | alembic/versions/NNN_schema_update.py (Alembic op.* calls, def upgrade() + def downgrade()) |
go-chi-postgres | .spec-snapshot.json | migrations/001_initial_schema.up.sql + .down.sql | migrations/NNN_schema_update.up.sql + .down.sql (raw SQL DDL, Postgres/SQLite/MySQL dialect) |
ts-express-postgres | .spec-snapshot.json | prisma/migrations/001_initial_schema/migration.sql (+ advisory down.sql) + prisma/migrations/migration_lock.toml | prisma/migrations/NNN_schema_update/migration.sql (+ advisory down.sql) |
Revision numbering is 3-digit zero-padded across all three targets (001, 002, …). Prisma's lexicographic ordering is satisfied; Alembic and golang-migrate both accept arbitrary tokens as long as they are unique.
Diff coverage
| Spec change | MigrationOp | DDL (Go / Prisma) | Alembic op.* |
|---|---|---|---|
| New table | CreateTable | CREATE TABLE … | op.create_table(…) |
| Removed table | DropTable | DROP TABLE … | op.drop_table(…) |
| Added column | AddColumn | ALTER TABLE … ADD COLUMN … | op.add_column(…) |
| Removed column | DropColumn | ALTER TABLE … DROP COLUMN … | op.drop_column(…) |
| Changed column type | AlterColumnType | ALTER TABLE … ALTER COLUMN … TYPE … | op.alter_column(…, type_=…) |
| Changed nullability | AlterColumnNullable | ALTER TABLE … ALTER COLUMN … {SET,DROP} NOT NULL | op.alter_column(…, nullable=…) |
| Changed default | AlterColumnDefault | ALTER TABLE … ALTER COLUMN … {SET,DROP} DEFAULT … | op.alter_column(…, server_default=…) |
| Added CHECK | AddCheck | ALTER TABLE … ADD CONSTRAINT … CHECK … | op.create_check_constraint(…) |
| Removed CHECK | DropCheck | ALTER TABLE … DROP CONSTRAINT … | op.drop_constraint(…, type_="check") |
| Added FK | AddForeignKey | ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY … | op.create_foreign_key(…) |
| Removed FK | DropForeignKey | ALTER TABLE … DROP CONSTRAINT … | op.drop_constraint(…, type_="foreignkey") |
| Added index | AddIndex | CREATE [UNIQUE] INDEX … | op.create_index(…) |
| Removed index | DropIndex | DROP INDEX … | op.drop_index(…) |
Every op has a mechanical inverse, so the downgrade body / .down.sql is generated by reversing the op list and inverting each entry. This satisfies the AC: alembic upgrade head && alembic downgrade -1 is reversible for every diff category in the Python target, and migrate up && migrate down is reversible for the Go target. This reversibility is now enforced in CI: the per-stack build workflows (python-build.yml, go-build.yml, ts-build.yml) run the initial migration up → down → up against a real database for each supported (framework, dialect) — Alembic, golang-migrate, and Prisma respectively.
Destructive operations
DropTable and DropColumn are flagged destructive. On compile, the CLI prints a warning for each:
⚠ migration contains 1 destructive change(s); review the generated migration before applying to populated data
⚠ - drops column 'users.email'The migration is still emitted as specified. To gate on destructive changes in CI, use compile --dry-run or diff and inspect the plan before applying.
NOT NULL columns without defaults
Adding a NOT NULL column without a default value will fail at apply time against a populated table. The compiler emits exactly what the spec says — it does not auto-rewrite the migration into a nullable-then-not-null two-step. If you need to introduce a required column to live data, write a manual migration on top of (or alongside) the generated one to backfill, then ALTER COLUMN … SET NOT NULL in a follow-up.
Renames
Renames are not detected. Renaming a field in the spec produces a DropColumn + AddColumn pair, which means data in the old column is lost on apply. If you need to preserve data, either:
- Rename in two steps: spec-side rename + generated migration, then a hand-edited follow-up migration that copies data from the old to the new column before drop, or
- Add a convention override (
@db_rename) — not yet implemented; tracked as a follow-up to this milestone.
Idempotency
A no-op recompile (snapshot present, schema unchanged) writes:
.spec-snapshot.json(byte-for-byte identical, classified asunchangedbycompile --dry-runanddiff)- No new migration file in
alembic/versions/,migrations/, orprisma/migrations/
The initial migration file is never in the emit list once a snapshot exists. compile cannot overwrite it.
Prisma specifics
Prisma migrations are forward-only by convention. prisma migrate deploy applies pending migrations in lexicographic directory order and ignores any down.sql alongside migration.sql. We emit down.sql anyway as an advisory artifact for manual rollback — feed it to psql directly, or use prisma migrate diff to derive an equivalent.
The emitted Makefile / package.json use prisma migrate deploy rather than prisma migrate dev. The dev workflow conflicts with pre-generated migrations: it would try to diff schema.prisma against the live DB and write a new migration of its own.
Topological FK ordering
When multiple tables are created in a single migration and there are FK dependencies between them, the migration orders CreateTable ops so referenced tables come first. The existing topoSort from alembic.Migration is reused. Cycle detection raises a RuntimeException at emit time — the same behavior as before this milestone.
CLI surface
| Command | Effect on migrations |
|---|---|
compile | Emit initial-or-delta migration as described above. Always (re-)writes .spec-snapshot.json. |
compile --dry-run | Computes the plan including the would-be migration file. No writes. |
diff | Reads snapshot + revisions and compares; reports the plan; exits 1 if drift, 0 if clean. |
compile --ignore-verify does not affect migration generation — verification is upstream of emit. A spec that fails verification produces no migration at all.
Trade-offs
- Single delta per regen. All schema changes between the previous snapshot and the new schema are folded into one migration. We do not split changes into multiple migrations even when they would be logically separable.
NNN_schema_updateis the only filename used for deltas. Numbers differ but the slug is constant. For Alembic and golang-migrate, the file may be renamed post-emit — both tools key off therevision = "..."constant or the numeric prefix, not the slug. Prisma is different:prisma migrate deploykeys migration history off the directory name itself, so renaming a Prisma migration directory after it has been applied to any database (including dev) will produce a history-mismatch warning. If you want a friendlier slug for a Prisma migration, rename it before the firstprisma migrate deploy.- Snapshot file is owned by the compiler. Never preserved like
strategies_user.py. If you delete it, the next compile treats the project as first-time emission and will write a fresh001_initial_schema.*(which will collide with the existing one in the target directory — see "Recovering from a deleted snapshot" below).
Recovering from a deleted snapshot
If .spec-snapshot.json is deleted or unparseable but alembic/versions/001_initial_schema.py (or the equivalent Go / TS file) exists, the compiler:
- Re-emits the initial migration with the current schema and writes a fresh snapshot.
- The existing initial migration on disk gets overwritten with the new content (via the standard
compilewrite path) — which is fine if and only if the spec hasn't changed since the snapshot was deleted.
If the spec has changed since the snapshot was deleted, the safest recovery path is to revert the spec, run compile once to restore the snapshot, then re-apply the spec change so M7.5 can produce a clean delta.