Generating Idempotent SQL Scripts from EF Core

The release pipeline for a .NET service needs to migrate four databases — two staging environments, a production primary and a disaster-recovery copy — each sitting at a different migration version because of past hotfixes and skipped deploys. Running dotnet ef database update from the pipeline means installing the SDK and the source code on the runner and trusting whatever EF decides at run time. What you want instead is a single artefact, reviewed once in the pull request, that brings any of those databases up to date and does nothing to a database that is already current. dotnet ef migrations script --idempotent produces exactly that. This guide shows how to generate it, what the generated SQL does, the edge cases around non-transactional statements, and how to wire it into a deploy. It is a core practice in Entity Framework Core Migrations.

One Script, Databases at Different Versions A single idempotent script built in CI is applied to four databases. Staging A is at migration 12 and receives 13 to 15. Staging B is at 14 and receives 15. Production is at 13 and receives 14 and 15. The DR copy is already at 15 and receives nothing. Each migration block checks __EFMigrationsHistory first. One Script, Databases at Different Versions migrate.sql idempotent, built in CI Staging A (at 12) applies 13, 14, 15 Staging B (at 14) applies 15 Production (at 13) applies 14, 15 A DR copy already at 15 runs the same file and changes nothing.
Each migration block checks the history table before running, so the same file converges every database on the latest version.

Symptom / Error Signatures

You need an idempotent script when you see these:

  • A plain (non-idempotent) script, generated from migration 0 to latest, fails on an existing database with There is already an object named 'Orders' in the database (SQL Server) or 42P07: relation "orders" already exists (PostgreSQL).
  • Scripts generated for a specific from/to range have to be regenerated per environment because each database is at a different version.
  • Deploy runners need the .NET SDK, the dotnet-ef tool and the application source just to run database update.
  • Reviewers approve C# migration classes without ever seeing the SQL that runs.

Root Cause Analysis

A normal dotnet ef migrations script emits the statements for a range of migrations and assumes the target database is exactly at the start of that range. That assumption breaks whenever environments diverge. The --idempotent flag changes the generated SQL: every migration’s statements are wrapped in a conditional that checks whether its ID is already in __EFMigrationsHistory, and the insert into that table is inside the same block. The script can therefore be generated once, from the beginning of history to the latest migration, and run anywhere.

On PostgreSQL the conditionals are DO $EF$ BEGIN IF NOT EXISTS (SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '...') THEN ... END IF; END $EF$; blocks; on SQL Server they are IF NOT EXISTS (...) BEGIN ... END; batches separated by GO. Both work well for ordinary DDL. The catch is statements that cannot run inside those wrappers or inside a transaction — CREATE INDEX CONCURRENTLY in PostgreSQL cannot run inside a DO block at all, because a DO block executes within a transaction — so migrations that use migrationBuilder.Sql(..., suppressTransaction: true) for such statements need attention in idempotent scripts.

Script type Assumes Safe to rerun Works with non-transactional SQL
plain, full range empty database no yes (statements run as written)
plain, from–to database exactly at from no yes
--idempotent, full range nothing yes needs care (see below)
migration bundle nothing yes yes (bundle runs migrations like database update)
Applying the Script to a Database at Version 13 Sequence between the deploy job and PostgreSQL. For migrations 1 to 13, each block checks __EFMigrationsHistory, finds the ID, and skips. For migration 14, the check finds nothing, so the block runs the DDL and inserts the ID. The same happens for 15. Applying the Script to a Database at Version 13 Deploy job PostgreSQL block 1–13: ID in history? yes → skip block 14: ID in history? no ALTER TABLE … ; INSERT INTO __EFMigrationsHistory block 15: same check, apply, record
Already-applied migrations cost one indexed lookup each; missing ones run with their history insert in the same block.

Immediate Mitigation

1. Generate the idempotent script in CI. Build it from the start of history to the latest migration, so it is valid for any database.

# Shell · CI build job with the .NET SDK and dotnet-ef · reads the compiled model; no database connection
dotnet tool restore
dotnet ef migrations script --idempotent \
  --project src/Shop.Data --startup-project src/Shop.Api \
  --output artifacts/migrate.sql

2. Review the script, not the C#. Publish migrate.sql as a build artefact and link or paste the new blocks into the pull request. Scan it mechanically for dangerous statements:

# Shell · CI step after script generation · touches no database
# WARNING: a pattern check is a backstop; human review of new blocks is still required.
if grep -nPi "DROP (TABLE|COLUMN)|ALTER COLUMN .* TYPE|CREATE (UNIQUE )?INDEX (?!CONCURRENTLY)" artifacts/migrate.sql; then
  echo "review required: destructive or blocking statement in migrate.sql"; exit 1
fi

3. Apply it once from the pipeline with a lock timeout. Any SQL client works; for PostgreSQL, psql with ON_ERROR_STOP makes failures fail the job.

# Shell · deploy job · migration login · PostgreSQL
# WARNING: the lock_timeout applies to this session only.
PGOPTIONS="-c lock_timeout=3s" psql "$MIGRATION_URL" -v ON_ERROR_STOP=1 -f artifacts/migrate.sql

4. Handle non-transactional statements outside the script. If a migration uses CREATE INDEX CONCURRENTLY, either apply that migration with a bundle or database update (which honours suppressTransaction), or keep such statements out of EF migrations and run them as a separate, idempotent pipeline step (CREATE INDEX CONCURRENTLY IF NOT EXISTS ...) — the approach described in running concurrent index builds outside migration transactions. Test the idempotent script against a copy of production before relying on it.

Permanent Fix / Long-Term Pattern

Make the idempotent script the contract between development and operations. CI generates it on every build, uploads it as an artefact, and fails the build when the script contains unreviewed destructive or blocking statements. Reviewers see the SQL diff for new migrations. The deploy job applies the artefact from the build being deployed — never a freshly generated one — so what runs in production is byte-for-byte what was reviewed, the same property described in blocking deploys on failed migration dry runs.

Add a rehearsal: before production, apply the same artefact to a database restored from a recent production snapshot and time it, per testing migrations against production-like snapshots. Then apply it a second time; an idempotent script must be a no-op on the second run. Keep migrations that need special execution — concurrent indexes, long backfills — out of the idempotent script and in dedicated steps, and choose EF Core migration bundles when you want EF’s own transaction handling with the same apply-anywhere property.

Idempotent Script Pipeline Pipeline. CI builds the idempotent script; a gate scans it for destructive and blocking statements; the script is applied twice to a snapshot restore to prove idempotency and measure time; a gate checks the second run changed nothing; the deploy job applies the same artefact to production. Idempotent Script Pipeline Build script --idempotent, artefact scan drops / blocking? Snapshot rehearsal apply twice, time it no-op 2nd run empty? Apply to prod same artefact review + edit fix idempotency fail
Applying the script twice to a snapshot is a cheap proof that it really is idempotent before production depends on it.

Verification Checklist

Frequently Asked Questions

Is the idempotent script slower than a normal script? Marginally. Each already-applied migration costs one lookup in __EFMigrationsHistory, which is negligible even for hundreds of migrations. The DDL itself runs exactly as in a normal script.

Does --idempotent make individual statements idempotent? No. It makes each migration run at most once by checking the history table. If a migration fails halfway and leaves partial changes without recording its ID, rerunning will attempt the whole migration again, so raw SQL inside migrations should still use guards such as IF NOT EXISTS.

Can I use the idempotent script with SQL Server’s online index options? Yes. CREATE INDEX ... WITH (ONLINE = ON) can run inside a transaction on SQL Server editions that support online operations, so it works within the script’s conditional batches. Test on the same edition as production, since online index operations are not available on all editions.

Should I commit the generated script to the repository? It is usually better as a build artefact, regenerated from the migrations on each build. Committing it invites hand edits that diverge from the migration classes; if you do commit it, add a CI check that regenerating it produces no diff.

Related Articles