Diff migrations for schema evolution

Snapshot-driven incremental migrations across Alembic (Python), golang-migrate (Go), and Prisma (TS)

At a glance

compile is no longer single-shot. On every run the compiler:

Writes .spec-snapshot.json at the emitted project root, a serialized DatabaseSchema with a schemaVersion envelope. Always overwritten, intended to be committed.
Reads any existing .spec-snapshot.json to 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.* where NNN = max(existingRevisions) + 1, containing only the delta. down_revision chains 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` + `down.sql` + `prisma/migrations/migration_lock.toml`	`prisma/migrations/NNN_schema_update/migration.sql` + `down.sql`

Revision numbering is 3-digit zero-padded across all three targets (001, 002, and so on). 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. Rollback is exposed as make migrate-down across all three targets, though Prisma's CLI constraints force a different ergonomic shape for TS:

Python (Alembic): alembic upgrade head ↔ alembic downgrade -1. No arguments; Alembic queries alembic_version to know what to undo.
Go (golang-migrate): migrate ... up ↔ migrate ... down 1. No arguments; golang-migrate queries schema_migrations.
TS (Prisma): prisma migrate deploy ↔ make migrate-down NAME=<migration-folder>. The explicit NAME= is required because Prisma's CLI does not expose applied-migration state (see Prisma specifics below).

Reversibility of the .down.sql / def downgrade() content itself (independent of the invocation ergonomics) is 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 as unchanged by compile --dry-run and diff)
No new migration file in alembic/versions/, migrations/, or prisma/migrations/

The initial migration file is never in the emit list once a snapshot exists. compile cannot overwrite it.

Prisma specifics

Prisma's CLI has no native migrate down command and exposes no machine-readable hook for "which migration was applied last." Two consequences shape the TS-side rollback story:

prisma migrate resolve --rolled-back <name> is only valid for failed migrations; running it against a successfully-applied migration is documented to error. Direct manipulation of _prisma_migrations is the only way to record a manual rollback of a successful migration.
Inferring "latest applied" from the filesystem (highest numeric folder prefix) is unsafe: a user can spec-to-rest compile multiple times without running make migrate-up in between, leaving high-numbered folders that were never applied. Rolling back the highest folder would attempt to undo work that never happened.

The emitted make migrate-down therefore requires an explicit NAME=<folder> argument (rather than implicit -1 semantics). The recipe:

Runs prisma/migrations/<NAME>/down.sql via prisma db execute --file.
Removes the row from _prisma_migrations via prisma db execute --stdin running DELETE FROM _prisma_migrations WHERE migration_name = '<NAME>';, portable across postgres/mysql/sqlite without dialect quoting.

make migrate-down invoked without NAME= prints a usage message and lists the migration folders on disk. This is the strongest safe contract that Prisma's tooling supports without introducing a Node script. Python (alembic downgrade -1) and Go (migrate ... down 1) keep their no-arg rollback semantics because Alembic and golang-migrate both consult their own applied-migration tables; Prisma's CLI does not expose this state.

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

Each regeneration folds all schema changes between the previous snapshot and the new schema into a single delta migration. We do not split changes into multiple migrations even when they would be logically separable.

Deltas always use the filename NNN_schema_update; the numbers differ but the slug is constant. For Alembic and golang-migrate, the file may be renamed post-emit; both tools key off the revision = "..." constant or the numeric prefix, not the slug. Prisma is different: prisma migrate deploy keys 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 first prisma migrate deploy.

The snapshot file is owned by the compiler and is never preserved like strategies_user.py. If you delete it, the next compile treats the project as first-time emission and will write a fresh 001_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 compile write 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.

Diff migrations for schema evolution

On this page