EF Core migrations in production: bundles, scripts, rollbacks

There are three sound ways to apply Entity Framework Core migrations to a production database: call Database.Migrate() when the app starts, run a migration bundle (efbundle, a self-contained executable produced by dotnet ef migrations bundle) as a step before the app starts, or generate an idempotent SQL script and run it yourself. For a single app on a single server, applying at startup is acceptable and simple; a bundle run from the start command is the better default because it separates "migrate" from "serve" without adding infrastructure; a reviewed SQL script is the right answer when someone other than the app owns the database. Whichever you pick, the migrations themselves have to be safe to run against a live app, and that is a discipline in how you write them, not a tool setting.

This post covers each method with the exact commands, then the parts that decide whether a deploy goes well: reviewing what EF generated, changing schema without breaking the running version, and what "rollback" really means.

How EF Core migrations work#

A migration is a C# class with an Up method that changes the schema and a Down method that reverses it, generated by comparing your current model with a snapshot of the model at the previous migration.

$ dotnet tool install --global dotnet-ef$ dotnet ef migrations add AddOrderStatus$ dotnet ef migrations list$ dotnet ef database update

The tools need the Microsoft.EntityFrameworkCore.Design package in the startup project. You can also install dotnet-ef as a local tool in a tool manifest, which pins its version with the repository - better for a team, because the tool's version should match the EF Core version the project uses.

migrations add writes three things into the Migrations folder: the migration class, a designer file with metadata, and an updated ModelSnapshot. Commit all three. The snapshot is how the next migration knows what changed, and merge conflicts in it are how two developers adding migrations on separate branches find out about each other.

The database side is a table, __EFMigrationsHistory, holding the ID of each applied migration and the EF Core version that applied it. Every method of applying migrations reads that table, works out which migrations are missing, and applies them in order. Nothing else tracks state, which is why manual changes to the schema - an index added by hand in a hurry - are invisible to EF and tend to collide with a later migration that tries to add the same thing.

Reviewing what EF generated#

EF generates migrations from a diff, and a diff does not know your intent. Read every migration before you commit it.

Renames. Renaming a property may be scaffolded as a rename, or as dropping the old column and adding a new one, depending on what else changed. A drop and an add loses every value in the column. When EF scaffolds an operation that can lose data, it prints "An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy." Treat that line as a stop sign, and replace the generated operations with migrationBuilder.RenameColumn(...) if a rename is what you meant.

New non-nullable columns. Adding a required property to a table with rows produces a column with a default value of the type - an empty string, a zero, 0001-01-01. That is rarely what the data should say. Either make the column nullable first and backfill it (see the expand-contract section below), or give it a deliberate default with HasDefaultValue or defaultValue: in the migration.

Data changes. Migrations can run SQL as well as change schema:

protected override void Up(MigrationBuilder migrationBuilder){    migrationBuilder.AddColumn<string>(        name: "Status", table: "Orders", nullable: true);    migrationBuilder.Sql(        "UPDATE Orders SET Status = 'paid' WHERE PaidAt IS NOT NULL");}

Keep data changes small and set-based. A migration that loads entities into C# and loops over them is slow, depends on model classes that will change in later migrations, and breaks in ways that are hard to fix once it has been applied somewhere.

Statements that cannot run in a transaction. Some operations - CREATE INDEX CONCURRENTLY on PostgreSQL, some ALTER DATABASE statements on SQL Server - refuse to run inside a transaction. Pass suppressTransaction: true to migrationBuilder.Sql(...) for those, and accept that the migration is no longer all-or-nothing.

Four ways to apply migrations#

dotnet ef database update against production from a laptop is the method to avoid. It needs the production connection string on a developer machine, it uses whatever code happens to be checked out locally, and nobody else knows it happened. Use it for development databases only.

Applying migrations at startup#

The simplest production method is one line before the app starts serving:

var app = builder.Build();using (var scope = app.Services.CreateScope()){    var db = scope.ServiceProvider.GetRequiredService<ShopDb>();    db.Database.SetCommandTimeout(TimeSpan.FromMinutes(5));    await db.Database.MigrateAsync();}app.Run();

What it gets right: the schema always matches the code that is starting, and there is no separate step to forget. What it gets wrong:

• The app's database user needs rights to change the schema. Normally an application should only read and write data. Migrating at startup means the account your web app uses every day can also drop tables.
• A failed migration is a failed start. The app does not start, the platform restarts it, and the migration fails again - a restart loop whose real cause is buried in a stack trace.
• Several instances race. Two instances starting at once may both try to apply the same migration. EF Core 9 added a database-wide lock around migrations to protect against this, but on older versions it is a real risk.
• Long migrations hit timeouts. The default command timeout is 30 seconds; adding an index to a large table can take longer, hence the SetCommandTimeout above.

EF Core 9 also changed Migrate() to throw if the model has changes that no migration covers (PendingModelChangesWarning). That catches the "forgot to add a migration" mistake at startup rather than at the first failing query, which is useful, but it surprises people upgrading from EF Core 8.

For one app on one server, with a database that the app owns, applying at startup is a reasonable choice. Use a separate, privileged connection string for the migration and the normal one for the app, so the everyday account stays limited.

Migration bundles#

A bundle is a single executable containing your migrations and everything needed to apply them. It needs neither the SDK nor your source code where it runs:

$ dotnet ef migrations bundle --self-contained -r linux-x64 -o efbundle --force$ ./efbundle --connection "Host=203.0.113.10;Database=shop;Username=shop_owner;Password=..."

Without --connection, the bundle uses the connection string your DbContext is configured with, read from the app's configuration. --self-contained -r linux-x64 makes it independent of the .NET runtime on the server; --force overwrites a previous bundle.

Build the bundle in CI next to the app, ship both, and run the bundle first in the start command:

./efbundle --connection "$MIGRATIONS_CONNECTION" && exec dotnet Shop.Web.dll

If the migration fails, && stops the app from starting against a half-migrated schema, and the failure is the last thing in the log rather than buried in startup noise. The migration runs under its own connection string with schema rights, read from an environment variable, while the app uses an account that can only read and write data. On RE:NODE, environment variables go on the Startup tab, and the start command runs on every start - so the bundle runs on every restart, finds nothing to do, and exits in about a second. Deploy an ASP.NET Core app from GitHub covers publishing in CI and deploying the output from a branch.

Idempotent SQL scripts#

A script turns migrations into plain SQL that anyone can read before it runs:

$ dotnet ef migrations script --idempotent -o migrate.sql$ dotnet ef migrations script AddOrders AddOrderStatus -o step.sql

The first form produces every migration from the beginning, each wrapped in a check against __EFMigrationsHistory, so it can run against a database at any version and applies only what is missing. The second produces the SQL between two named migrations, which is what you hand to a reviewer for one release.

Scripts are the right choice when a database administrator owns the production database, when changes must be reviewed by a person, or when the app's account must never hold schema rights. They are also the honest way to see what a migration will do: reading the SQL often reveals that a "small" change rebuilds a table. Idempotent scripts have limitations on some providers - certain statements behave differently when wrapped in a conditional block - so run the script against a copy of production before running it against production.

On SQL Server you run the result with sqlcmd or SSMS; on PostgreSQL with psql -f; on MySQL with the mysql client. If your database is on a separate database hosting plan, you connect with the host, port and credentials from the panel. The connection side for each engine is in .NET with PostgreSQL, MySQL or SQL Server.

Changing schema without breaking the running version#

During a deploy, the old version of the app runs against the new schema for a moment - or, if migration runs before the restart, for the whole time the restart takes. A migration that the old code cannot tolerate breaks the site for that window. The pattern that avoids this is expand, then contract:

1. Expand. Add the new column as nullable, or the new table. Old code ignores it. Deploy.
2. Migrate the data. New code writes both old and new columns; a migration or a background job backfills existing rows. Deploy.
3. Switch. New code reads from the new column only. Deploy.
4. Contract. Make the column non-nullable if it should be, and drop the old column in a later migration, once nothing reads it. Deploy.

Four deploys for a rename feels excessive until the first time a one-step rename takes the site down for the duration of a restart. On a small app with a short restart and quiet hours, compressing it to two steps (expand plus switch, then contract) is a reasonable compromise. Dropping a column the running code still selects is never reasonable. Migrations without downtime goes through the pattern with more examples, and zero-downtime deploys on a small server covers the application side.

Rollbacks and failed migrations#

EF Core can run Down methods: dotnet ef database update AddOrders reverts every migration after AddOrders, and dotnet ef migrations script AddOrderStatus AddOrders produces the SQL for that reversal. That is a rollback of schema. It is not a rollback of data: a Down that re-adds a dropped column re-adds it empty.

The practical rules:

• Take a backup before every migration that drops or rewrites anything. A dump you have restored at least once is the only real rollback for data. Database backups and restores covers doing it properly; on RE:NODE's database hosting plans, backup slots are included and a backup can be taken on demand from the panel before you deploy.
• Prefer rolling forward. If a migration was wrong, a new migration that fixes it is usually safer than reverting, because it is tested like any other change and does not depend on a Down method nobody has ever run.
• Know what a partial failure looks like. On SQL Server and PostgreSQL each migration is applied in a transaction, so a failure leaves that migration unapplied. On MySQL, DDL statements commit implicitly, so a migration that fails half way can leave some of its changes in place with no history row. Fixing that means reading the schema, finishing or undoing the change by hand, and only then retrying.
• Never edit a migration that has been applied anywhere shared. Change it after it has run on production and the next environment gets a different schema from the one production has. Add a new migration instead. dotnet ef migrations remove is only for migrations that have not been applied.

FAQ#

Should I use EnsureCreated instead of migrations?

Only for throwaway databases in tests or prototypes. EnsureCreated builds the schema from the current model and creates no history table, so migrations cannot be applied to that database later without recreating it.

Can I squash many old migrations into one?

Yes, carefully: remove the old migration files, add one new migration that creates the current schema, and insert its ID into __EFMigrationsHistory on existing databases so it is not applied twice. Most projects never need to; hundreds of migrations cost little.

Why does the bundle fail with "unable to create an object of type DbContext"?

The tools could not build your context at design time, usually because it depends on configuration that only exists when the app runs. Add an IDesignTimeDbContextFactory<T> that constructs the context with a connection string from the environment.

Do migrations work the same on PostgreSQL, MySQL and SQL Server?

The commands are identical; the generated SQL and the transactional behaviour are not. Generate a script once per provider you target and read it, especially for MySQL, where DDL cannot be rolled back.

Where should the migration connection string live?

In an environment variable on the server, separate from the app's own connection string, so the account with schema rights is used only for migrating. ASP.NET Core configuration and secrets covers how the two are read.