.NET Database Migrations Explained using EF Core

Introduction

Almost every EF Core tutorial ends the migration story at app.Services...Database.Migrate() in Program.cs. That is fine on your machine. In production it means your web app needs schema-altering permissions on the database, every instance races to apply migrations on deploy, and a bad migration takes the app down instead of failing a deploy step.

This video is about the parts that come after "add-migration works". On .NET 10 with SQL Server, it covers how to apply migrations in a real deployment, how to give the migration process its own limited-permission login, why you should read the generated migration before trusting it, and how to handle the changes EF cannot figure out on its own.

🎬 Watch the full video here:

Always read the generated migration

dotnet ef migrations add <Name> writes a C# file with Up and Down methods. Open it before you do anything else. EF Core infers the migration by diffing the model against the last snapshot, and its inference is not always what you want:

• A renamed property looks like drop column + add column - that is data loss. You have to change it to a RenameColumn by hand.
• A new non-nullable column with no default fails on a table that already has rows.
• A changed column type may generate an ALTER that the database rejects or that silently truncates.

The migration is a starting point you are responsible for, not a finished artifact.

Do not migrate from application startup in production

Database.Migrate() at startup is convenient and wrong for production because:

• Every app instance runs it. Two instances applying the same migration at the same time is a race.
• The app's runtime login now needs DDL permissions - a much bigger blast radius if the app is compromised.
• A failed migration crashes the app on boot instead of failing a pipeline step you can roll back.

Keep it for local development if you like. For deployed environments, applying migrations is a separate, deliberate step.

The three ways to apply migrations for real

1. Idempotent SQL script. Generate a script that checks the migrations-history table and only applies what is missing:

dotnet ef migrations script --idempotent --output migrate.sql

Hand migrate.sql to a DBA, run it in a release pipeline, check it into the repo for review. It is just SQL - reviewable, diffable, and it runs anywhere without the .NET SDK.

2. Migration bundle. A self-contained executable that applies pending migrations:

dotnet ef migrations bundle --self-contained -r linux-x64

./efbundle --connection "<conn>" applies everything outstanding. No SDK on the target, no source checkout - one binary your pipeline runs against each environment.

3. dotnet ef database update from the pipeline. Fine for small setups; needs the SDK and the project on the runner.

For a CI/CD pipeline, the bundle or the idempotent script are the two solid choices.

Give migrations their own login

The demo's connection string uses a dedicated MigrationLogin, separate from the login the running app uses. This is the least-privilege idea applied to schema changes:

• The app login gets db_datareader + db_datawriter (or execute on specific procs). It cannot alter the schema.
• The migration login gets db_ddladmin (or db_owner) and is used only by the migration step.

If the app is ever exploited, the attacker cannot drop your tables, because the credentials the app holds cannot do that.

Changes EF cannot infer

Some things are invisible to the model differ and need migrationBuilder.Sql(...) inside a migration:

• Data transformations (backfilling a new column, splitting a name field).
• Renames, as noted above.
• Raw SQL objects - views, stored procedures, functions, triggers, computed columns with specific SQL.

Write these in the Up method (and reverse them in Down), so the change ships atomically with the schema change that needs it.

Common pitfalls

• Trusting the generated migration blind. Rename detection, data loss, non-nullable columns - always review.
• Database.Migrate() in production startup. Race conditions plus over-privileged app login.
• Editing an already-applied migration. Once a migration has run somewhere, treat it as immutable and add a new one.
• Deleting a migration file without reverting it first. The snapshot and history get out of sync. Use dotnet ef migrations remove while it is still the last, unapplied one.
• EnsureCreated() and migrations in the same project. EnsureCreated bypasses migrations entirely; the two do not mix.
• No Down method. You cannot roll back what you did not write a reverse for.

Key Takeaways

• Open and review every generated migration - EF infers renames as drop/add, and that is data loss.
• Do not call Database.Migrate() on startup in production; make applying migrations a separate deploy step.
• Apply migrations with an idempotent SQL script or a self-contained migration bundle - both are SDK-free and reviewable.
• Use a dedicated, DDL-capable migration login that is separate from the app's read/write login.
• Put data backfills, renames, and raw SQL objects into migrationBuilder.Sql(...) so they ship with the schema change.
• Once a migration has run anywhere, treat it as immutable.

Get the Full Source Code

The complete runnable solution - the demo API, the migrations, the idempotent script and bundle commands, and the split app/migration login setup - is available to Patreon supporters. If you want to walk the whole apply-in-a-pipeline flow instead of rebuilding it from the walkthrough above, you can find it on Patreon.

Resources

• Applying migrations (EF Core docs)
• Migration bundles
• Managing migrations
• Custom migration operations