How to apply EF migrations

The picture and title are, as usual, clickbait-y, because apparently people don’t click on videos that sound educational unless you promise them ground-breaking learnings. Still, I don’t hate the player; I hate the game. But it’s the world we have.

The video is quite informative and is 90% not the guy pictured. Instead, it’s another guy called Gui Ferrera, who is quite competent.

The Correct Way to Run Database Migrations in EF Core by Nick Chapsas / @gui.ferreira (YouTube)

He starts by explaining how to deploy migrations in production—you don’t just run them, as you would in development. Why not? Because, if you run the migrations as part of your application startup, then your application implicitly has permissions to modify the database schema—permissions that you are unlikely to revoke or downgrade.

As an alternative, you can use the dotnet-ef tool to generate an ef migrations bundle (he names the file efbundle), which is an executable that you can then just run, using the pipeline secret that has administrator access to the target database. This executable runs separately and is only in charge of migrating the database to a particular version. Your application will run and fail if the schema is not correct, which is the desired behavior. If it is correct, your application will run with a database user with much lower permissions—at the very least, it won’t be able to issue DDL commands.

The bundle option generates a binary; there is also a script option, which generates SQL. This is pretty neat and there’s even a flag called idempotent, which allows you to generate a script that will ensure that previous migrations have been applied before continuing with subsequent migrations.

The implementation isn’t as obviously straightforward as it advertises itself. There must be limitations for custom-migration behavior that uses program logic. I know, because Quino[1] had a very similar feature and, although we could generate SQL for some user customizations to the migration process, there was no way to support everything.

It’s nice to see how solid the EF migrations story has gotten, even though I think the design still suffers when switching branches. You need much more developer discipline to keep your local database usable and in-sync. Anecdotally, I hear that most developers just trash their local database all the time, and rely much more on seeding functions to restore the state. You tend to lose your local ad-hoc-created data and it takes a bit more time, but it works with EF.

It’s actually not a bad alternative since it forces you to focus more on the seeding function, which will also benefit you while writing tests. For prototyping, though, there was nothing as fast as Quino or Atlas[2], both of which, instead of using a metadata table in the database, read the database schema, compared against the application model and applied custom migrations to address differences.

Ferrera finished up with by-now standard advice for adding required columns (and other, similar types of breaking changes): you have to add the column as nullable with a default in the first migration, then get rid of the unwanted default value and nullability in a subsequent migration. You can only remove an unwanted field once the deployed application isn’t using it. That is, you have to drop the column for the deployed version after the version that no longer needs the column. Otherwise, you run the risk of breaking the application that is still running against that database (especially if you have multiple clients/API servers running against the same database instance).