Combining Declarative Diffs with Versioned Migration Files

Your team likes the declarative model — one readable schema file, no hand-maintained chain of ALTER statements — but every serious migration ends up needing something the diff engine cannot produce: a CONCURRENTLY, a batched backfill between two DDL steps, a NOT VALID constraint that is validated in a later release. Pure declarative applies cannot express that sequencing; pure versioned migrations lose the single source of truth. The hybrid workflow keeps both: the desired state stays authoritative, the tool generates a versioned migration file from the diff, an engineer edits that file into a zero-downtime sequence, and the pipeline applies versioned files exactly like Flyway or Liquibase would. This guide sets that up and closes the loop so the edited files and the desired state cannot silently diverge. It is the pattern Declarative Schema Management recommends for most production teams.

The Hybrid Loop Five steps. Edit the desired schema; generate a versioned migration file from the diff; edit the file for online DDL and staging; commit it with an updated integrity sum; CI replays all versioned files on a scratch database and checks the result equals the desired state. The Hybrid Loop STEP 1 Edit desired state schema.sql STEP 2 Generate file migrate diff add_region STEP 3 Edit for online DDL CONCURRENTLY, NOT VALID STEP 4 Commit + re-hash integrity sum updated STEP 5 Replay check history == desired state
The generated file is a draft; the closing check proves that the edited history still produces exactly the schema the desired state describes.

Symptom / Error Signatures

Teams usually arrive at this workflow after one of these:

  • A declarative apply ran a blocking statement because the change needed an online form the tool could not infer.
  • A change needed a data backfill between two schema steps, and there was nowhere in the declarative flow to put it.
  • Versioned migration files were edited by hand after generation and no longer produce the schema the desired-state file describes — discovered months later when a fresh environment built from migrations differs from production.
  • The migration tool refuses to run with a checksum error such as Atlas’s checksum mismatch on atlas.sum, or Flyway’s Validate failed: Migration checksum mismatch, because a file was edited after it was hashed.

Root Cause Analysis

The two models answer different questions. A desired-state file answers what should the schema be; a versioned migration answers what sequence of operations gets us there safely from where we are. Only the second can encode time: “add the column now, backfill over the next hour, add the constraint next release”. A diff engine sees only two snapshots, so it can generate a correct first draft of the sequence, but not the staging, online options and data steps that make it safe.

The hybrid model assigns each concern to the right artefact. Atlas supports it natively with atlas migrate diff, which writes a new versioned file containing the statements needed to move from the current migration directory’s end state to the desired state, and maintains an atlas.sum integrity file. The same pattern works with other generators — for example prisma migrate dev --create-only or drizzle-kit generate — followed by manual edits. The risk the model introduces is divergence: once you edit a generated file, nothing forces it to still converge on the desired state unless you add a check.

Artefact Owns Edited by
schema.sql (desired state) the target schema engineers, reviewed in PRs
migrations/NNN_*.sql the safe path, including online DDL and data steps generated, then engineers
integrity sum (atlas.sum, Flyway checksums) tamper-evidence for applied files the tool, on every change
replay check in CI proof that path and target agree automated
Generated Draft vs Edited Migration Two panels. The generated draft contains ADD COLUMN region text NOT NULL, CREATE INDEX idx_orders_region, in one file. The edited version splits it into ADD COLUMN nullable, a backfill in batches, CREATE INDEX CONCURRENTLY in a non-transactional file, and a NOT VALID check constraint validated separately. Generated Draft vs Edited Migration Generated by the diff ALTER TABLE orders ADD COLUMN region text NOT NULL CREATE INDEX idx_orders_region ON orders (region) correct end state, unsafe path Edited for zero downtime 20260918_01: ADD COLUMN region text (nullable) 20260918_02: batched backfill of region 20260918_03: CREATE INDEX CONCURRENTLY (no txn) 20260918_04: CHECK (region IS NOT NULL) NOT VALID, then VALIDATE same end state, online path
Editing turns one blocking draft into a sequence of online steps; the replay check then confirms the sequence still lands on the same end state.

Immediate Mitigation

1. Generate instead of applying. Replace any direct declarative apply in the pipeline with file generation. With Atlas:

# Shell · engineer workstation · needs Docker for the dev database
# WARNING: the dev database is wiped and reused; never point --dev-url at a real environment.
atlas migrate diff add_region \
  --dir "file://migrations" \
  --to "file://schema.sql" \
  --dev-url "docker://postgres/16/dev"
# writes migrations/<timestamp>_add_region.sql and updates migrations/atlas.sum

2. Edit the draft into online steps. Split statements that need different transaction handling into separate files, add CONCURRENTLY, and insert data steps. For PostgreSQL concurrent index builds, mark the file to run without a transaction using the tool’s directive (in Atlas, -- atlas:txmode none at the top of the file).

-- PostgreSQL · migrations/20260918120300_add_region_index.sql · runs outside a transaction
-- atlas:txmode none
-- WARNING: CREATE INDEX CONCURRENTLY cannot run inside a transaction block; keep it alone in this file.
SET lock_timeout = '2s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_region ON orders (region);
-- ROLLBACK PATH: DROP INDEX CONCURRENTLY IF EXISTS idx_orders_region;

3. Re-hash after editing. Editing a file invalidates the integrity sum. Regenerate it so the tool will accept the directory, and commit both together.

# Shell · after editing files in migrations/ · commits the new sum with the edits
atlas migrate hash --dir "file://migrations"
git add migrations/ && git commit -m "Add region column as online migration sequence"

4. Add the replay check before anything else ships. In CI, apply the whole migration directory to an empty scratch database and diff the result against the desired state. Any difference means the edited path no longer reaches the declared target.

# Shell · CI job · Docker available · no production credentials needed
atlas migrate diff --dir "file://migrations" --to "file://schema.sql" \
  --dev-url "docker://postgres/16/dev" check_sync
# If a new file appears, history and desired state have diverged: fail the job.
test -z "$(git status --porcelain migrations/)" || { echo "migrations do not converge on schema.sql"; exit 1; }

Permanent Fix / Long-Term Pattern

Codify the division of labour. The desired-state file is the only place anyone describes the target schema. Versioned files are generated from it, edited only to change how the target is reached — online options, transaction boundaries, data steps, staging across releases — never what it is. Applied files are immutable; the integrity sum and the migration tool’s own checksum validation enforce that, as covered in squashing migration history safely for the rare legitimate rewrite. CI replays the directory and diffs against the desired state on every pull request.

Staging across releases fits naturally. A change that needs expand-and-contract becomes several pull requests, each moving the desired state one step and generating one small set of files: first the additive step, then — after the application release that stops using the old structure — the contract step. The versioned directory then reads as a faithful log of the path production actually took, which is valuable for audits and for rebuilding environments, and which a pure declarative workflow cannot provide. Lint the generated-and-edited files with the same rules you apply to hand-written migrations, as described in Migration Linting & Static Analysis.

Two Sources, One Check The desired state schema.sql and the versioned migrations directory both feed a CI replay job. The replay applies all migrations to an empty scratch database, then diffs that result against the desired state compiled on the dev database. Equal means pass; any difference fails the pull request. Production only ever receives the versioned files. Two Sources, One Check schema.sql desired state migrations/ generated + edited files Replay + diff scratch DB vs desired state PR check equal → pass Production migrate apply (versioned only) compare apply
Production is migrated from versioned files alone; the desired state's job is to be the reference those files are checked against.

Verification Checklist

Frequently Asked Questions

Why not just apply the desired state directly and add online options in the tool config? Tool configuration can cover some cases, such as concurrent index builds, but it cannot express data backfills or staging across releases. Versioned files can, and the hybrid model keeps the declarative file as the reference so you do not lose the single source of truth.

Is it safe to edit a generated migration file? Yes, before it has been applied anywhere shared. Edit, re-hash, and let the replay check confirm it still converges on the desired state. Never edit a file that has already run in a shared environment; write a new one instead.

What if the replay check keeps failing on formatting differences? Make sure the scratch database uses the same engine, major version and extensions as production, and that the desired state is compiled on the same kind of dev database. Genuine normalisation noise usually disappears once the environments match.

Does this work with ORMs rather than Atlas? Yes. Prisma’s migrate dev --create-only, Drizzle Kit’s generate, Alembic’s autogenerate and TypeORM’s migration:generate all produce editable drafts from a model diff. The same rules apply: edit for online DDL, keep applied files immutable, and verify that the history converges on the model.

Related Articles