Safely Applying EF Core Migrations in Production

At work we use DB First. I make a table change with a SQL script on the test database first, update the code to match, and the tests pass. Then release day comes. I deploy the application and it crashes. The reason is the same every time: I ran the script on the test database and forgot to run it on production.

I usually fix it within a few minutes. I run the script on production and the application comes up. But for those few minutes the site returns errors. The cause isn't a technical problem; it's my forgetfulness.

The real problem is this: the code goes live one way and the tables another. The only thing tying them together is my memory. In this article I explain how EF Core's migration tools turn that link into code, which method fits which situation, and what to watch out for in production.

What does a migration actually solve?

In Code First, every change I make to the model becomes a migration class. It has two methods: Up applies the change and Down reverts it. EF Core records which migrations have been applied in the __EFMigrationsHistory table in the database.

Thanks to that table, the database itself knows which changes have been applied. The answer to "did this script run on production?" is no longer in my memory; it's in the database.

I covered the basics of creating and managing migrations in Managing EF Core Code First Migrations, and how I choose between DB First, Code First and a mix of both in EF Core Relationships and the Mixed Approach. This article focuses on the production side.

Four ways

EF Core offers four ways to take migrations to production.

Microsoft's documentation recommends SQL scripts or bundles for production. In my own project, Elevare, I use Migrate(). I'll explain why below.

Migrate() at startup

using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await db.Database.MigrateAsync();

I choose this method because it makes me feel in control. When the application starts, I know the tables match the code. The forgetfulness I described at the start can't happen here, because the table changes ship together with the code.

Of course it has downsides:

• Permissions: The application's database user must be able to create and drop tables. Normally we don't want to give an application that much power.

• More than one server: If two servers start at the same time, both may try to run migrations. Since EF Core 9 a lock is taken in the database before migrations run. With logging turned on in PostgreSQL I see this line:

  OutputCopy

  LOCK TABLE "__EFMigrationsHistory" IN ACCESS EXCLUSIVE MODE

  The second server waits for the lock, then sees there is nothing left to apply and carries on. Startup can still take longer while it waits.

• If it fails: If a migration fails halfway, the application doesn't start at all. Startup also gets longer; if you use Docker, you need to set the health check's start period accordingly.

Elevare runs on a single server and only the application itself uses the database. Under those conditions Migrate() is the simplest path for me. But I don't use it in every project. In a project that runs on several servers, or where every change needs a DBA's approval, I would use a script or a bundle. I decide based on what the project needs.

Generating a SQL script

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

This command turns all migrations into a single SQL file. The --idempotent option matters: it adds a "has this already been applied?" check before each migration. Here is part of the script generated for PostgreSQL:

START TRANSACTION;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20261103004538_RenameName') THEN
    ALTER TABLE "Customers" RENAME COLUMN "Name" TO "FullName";
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20261103004538_RenameName') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20261103004538_RenameName', '10.0.14');
    END IF;
END $EF$;
COMMIT;

Each migration runs in its own transaction. The script first checks __EFMigrationsHistory; if the migration isn't there, it makes the change and records it. So running the same script twice breaks nothing. I can safely run it even on a database where I don't know which migrations have been applied.

For a specific range:

dotnet ef migrations script AddOrders AddInvoices

The biggest advantage of this method is that someone can read the SQL before the change goes live.

Migration bundles

dotnet ef migrations bundle --self-contained -r linux-x64
./efbundle --connection "Host=...;Database=...;Username=...;Password=..."

A bundle packages migrations into a single executable. When I ran the bundle I built for Windows on my own machine, the output was:

Acquiring an exclusive lock for migration application.
Applying migration '20261103004538_RenameName'.
Applying migration '20261103004542_DropPhone'.
Applying migration '20261103004552_RenameAndLimit'.
Done.

Running the same bundle a second time:

No migrations were applied. The database is already up to date.
Done.

A bundle built with --self-contained carries the .NET runtime inside it, so the server doesn't need .NET installed. The price is size: even for my tiny example the file was 107 MB. If .NET is already installed on the server you can leave this option out.

It fits well in CI: build it there and run it in the deploy step before the application. The application no longer needs permission to create tables; only the user running the bundle does.

Changes that lose data

When EF Core sees that a column will be dropped, it warns you while creating the migration:

An operation was scaffolded that may result in the loss of data.
Please review the migration for accuracy.

I never ignore this warning. To see how easily data can be lost, I ran a small experiment.

I renamed the Name property of Customer to FullName. EF understood it correctly and wrote:

migrationBuilder.RenameColumn(
    name: "Name",
    table: "Customers",
    newName: "FullName");

Then I changed two things at once: renamed it to DisplayName and added [MaxLength(200)]. This time EF didn't recognise the rename:

migrationBuilder.DropColumn(
    name: "FullName",
    table: "Customers");

migrationBuilder.AddColumn<string>(
    name: "DisplayName",
    table: "Customers",
    type: "character varying(200)",
    maxLength: 200,
    nullable: false,
    defaultValue: "");

I had added a customer named "Ayşe Yılmaz" to the table beforehand. After applying the migration the table looked like this:

 Id | DisplayName
----+-------------
  1 |

The name was gone. The warning said so, but the migration applied without an error. The right fix is to edit the migration by hand and write RenameColumn followed by AlterColumn instead of DropColumn and AddColumn. That's why I open and read every migration file before applying it. I also try to keep changes small: a rename is one migration, a type change another.

When I need to move data I use migrationBuilder.Sql(). In Elevare I wrote such a migration when moving preview drafts into separate columns. First I added the new columns. Then, for pages with no pending approval request, I copied the data from the old column into the new one and cleared the old one. That's a small example of the approach in the next section.

Zero-downtime deploys: expand and contract

Sometimes old code has to work with the new table, and new code with the old table, for a while. In those cases I split the change into three steps:

1. Expand: Add the new column and leave the old one alone. Copy the data.
2. Migrate: Deploy the code that uses the new column.
3. Contract: Once everything uses the new column, drop the old one in a separate migration.

It looks like more work. But no step risks losing data or crashing.

Rolling back

dotnet ef database update PreviousMigration
dotnet ef migrations script CurrentMigration PreviousMigration

The first command reverts directly; the second generates the rollback script. But keep this in mind: reverting a migration that dropped a column brings the column back, not the data in it.

In the experiment above I reverted two migrations:

Reverting migration '20261103004552_RenameAndLimit'.
Reverting migration '20261103004542_DropPhone'.
Done.

The columns came back; the data didn't:

 Id | FullName | Phone
----+----------+-------
  1 |          |

That's why my real safety net in production isn't the Down method; it's the backup I take before the migration. If something goes wrong, I usually don't roll back; I write a new fix migration and move forward.

If you use DB First

EF Core's migration tools are for Code First. But the forgetfulness from the introduction can be solved in DB First too:

• Keep table change scripts in the repository as numbered files.
• Record which script has been applied in a table, doing the job __EFMigrationsHistory does yourself. Small libraries like DbUp offer this out of the box.
• Check at startup that the database is at the expected version. If it isn't, fail with a clear error.

That way the answer to "did I run it on production?" is again in the database, not in your memory.

Checklist

• I read the migration file. There's no data loss warning, or I accepted it knowingly.
• I applied it to the test database and ran the application there.
• I backed up the production database.
• I know how I'll apply it: Migrate(), a script or a bundle.
• I know what I'll do if something goes wrong: restore the backup or write a fix migration.