Generate Idempotent SQL Scripts and Separate Build-Time Migration Authoring from Deployment

Learning outcomes

1. The practical problem: the application artifact and schema artifact travel on different schedules

ServiceHub can build successfully while a target database is one, two, or ten migrations behind. Production change control may require SQL review by a database administrator (DBA), a maintenance window, a separate DDL credential, or an archived change ticket. Treating dotnet ef database update on an application host as the only deployment strategy collapses authoring, approval, credentials, and execution into one opaque step.

2. Bounded scripts make the source state explicit

mkdir -p artifactsdotnet ef migrations script PreviousMigration RenameDispatchToRouting   --project src/ServiceHub.EfLab   --output artifacts/Previous_to_RenameRouting.sql

A bounded script says: “apply this transition only if the database is at PreviousMigration.” That is appropriate for SQLite, whose provider cannot generate EF idempotent scripts because SQLite lacks the procedural IF/THEN mechanism EF uses for history-aware conditional migration execution.

SELECT "MigrationId", "ProductVersion"FROM "__EFMigrationsHistory"ORDER BY "MigrationId";

3. Idempotent scripts are useful—but provider support is not universal

On providers that support them, --idempotent emits history checks so only missing migrations are applied. This is useful when a fleet of databases may start at different known migration points. The same flag is not a valid mandatory SQLite path.

dotnet ef migrations script --idempotent   --project src/ServiceHub.EfLab   --output artifacts/servicehub-idempotent.sql

4. Review SQL as privileged deployment code

Do not infer performance from SQL text alone. For production-sized changes, test against realistic data volume and capture database-side timing/locking evidence using the production-like provider.

5. Separate identities: runtime DML is not deployment DDL

The application identity normally needs data access—SELECT/INSERT/UPDATE/DELETE and whatever stored routines are intentionally exposed. A deployment identity may need CREATE/ALTER/DROP permissions. Giving every application instance schema-owner permissions expands blast radius and makes startup migration tempting. The deployment pipeline should retrieve a short-lived or otherwise protected schema credential, apply the reviewed artifact, verify history/schema, and release that credential.

BUILD/CI identity  -> compile model and migration code  -> fail on pending model changes  -> generate reviewed script or bundle  -> sign/hash/archive artifactDEPLOY identity (DDL-capable, controlled)  -> verify target migration  -> apply approved artifact  -> verify history + smoke queryRUNTIME identity (least privilege)  -> normal application DML  -> no routine schema modification

6. Deliberately wrong: every pod calls MigrateAsync at startup

using var scope = app.Services.CreateScope();var db = scope.ServiceProvider.GetRequiredService<ServiceHubContext>();await db.Database.MigrateAsync(); // do not make every app instance the migration owner

EF Core 9+ added migration locking, which reduces concurrent migration corruption risk for Migrate, CLI updates, and bundles. That does not erase the other operational problems: the app needs DDL permissions, running binaries may be incompatible with an in-progress schema change, generated SQL receives no deployment-time review, and a blocked/long migration can delay or fail many instances. Prefer one coordinated migration owner.

7. CI should prove both migration completeness and artifact identity

dotnet restoredotnet build -c Releasedotnet ef migrations has-pending-model-changes --project src/ServiceHub.EfLabdotnet ef migrations list --project src/ServiceHub.EfLabdotnet ef migrations script PreviousMigration RenameDispatchToRouting   --project src/ServiceHub.EfLab   --output artifacts/schema.sql

Archive the migration IDs, EF/tool/provider versions, target provider, source/target migration, script hash, and review approval. If the migration artifact changes after review, its hash should change and approval should be repeated.

8. Hands-on lab: reviewed SQLite script deployment

1. Create two copies of the disposable DB at the same known previous migration.
2. Generate a bounded PreviousMigration → RenameDispatchToRouting script.
3. Review the SQL for rename/rebuild/drop operations and calculate a SHA-256 hash.
4. Apply the script to one copy using a SQLite CLI/library path, not EF.
5. Verify __EFMigrationsHistory and data preservation.
6. Attempting to apply the same bounded script blindly to a database at an unexpected state should be treated as an operator error—not as “idempotence.”
7. Document that external script execution does not acquire EF's migration lock.

sha256sum artifacts/schema.sql  # Linux/macOS/Git Bash# PowerShell alternative:# Get-FileHash artifacts/schema.sql -Algorithm SHA256

9. Production judgment and bridge to bundles

Use reviewed SQL when humans or database change-control systems need to inspect/modify/archive the exact DDL. Use source/target bounds when the target state is known. Use idempotent scripts only on providers that support them and only after reviewing their generated logic. The next lesson covers migration bundles: executable deployment artifacts that retain EF's migration engine and locking behavior without requiring the SDK/source code on the target host.

Authoritative references

Use primary documentation when regenerating or adapting these deployment steps; migration behavior is provider- and version-sensitive.

• EF Core migrations overview
• Managing migrations
• Applying migrations
• EF Core .NET CLI tools
• SQLite provider limitations