Handling Entity Framework Core database migrations in production – Part 2

Last Updated: August 9, 2019 | Created: January 30, 2019

This is the second in the series on migrating a database using Entity Framework Core (EF Core). This article looks at applying a migration to a database and follows on from part 1 which covered how to create a migration script. If you haven’t read the part 1 some parts of this article won’t make sense, so here is a very quick review of part 1.

  • There are two types of migrations that can be applied to a database:
    • Adds new tables, columns etc, known as a non-breaking change (easy).
    • Changes columns/tables and needs to copy data, known as a breaking change (hard).
  • There are two main ways to apply a migration to a database
    • Use EF Core migration feature
    • Use EF Core to create a migration and then hand-modify the migration.
    • Use a third-partly migration builder write in C# the migration.
    • Use a SQL database comparison tool to compare databases and output a SQL changes script.
    • Write your own SQL migration scripts by copying EF Core’s SQL.

So, now that you know how to create migration scripts now I’m going to look at the different ways you can apply a migration to a production database, with all the pros, cons and limitations.

TL;DR – summary of the content

NOTE: Click the links to go directly to the section covering that point.

Setting the scene – What sort of application have you got?

In part 1 we were focused on creating migrations that were “valid” and whether the migration is a non-breaking change or breaking change (see quick definition at start of this article, or this link for section in part 1).

Now we are looking at applying a migration to a database, but the options we have depends on the application (or applications) that are accessing the database. Here are questions you need to think about.

  1. Is there only one application accessing that database, or is your application a web app which is scaled-out, i.e. there are multiple versions of your application running at the same time. If your application is scaled-out, then this removes one of the options.
  2. Can you stop your application while you apply a migration to the database, or is your application providing a continuous (24/7) service? Updating continuous service applications bring some challenges when it comes to applying a breaking change.
When it comes to migrating a production database being a bit paranoid is OK.

As I said at the end of part 1 – the scary part comes when you apply a migration to a production database. Changing a database which contains business-critical data needs (demands!) careful planning and testing. You need to think about what you are going to do if (when!) a migration fails with an error.

When considering the different ways to apply a migration you should have in the back of your mind “what happens if there is an error?”. This might push you to a more complex migration approach because its easier to test or revert. I can’t give you rules or suggestions as each system is different but being a bit paranoid about failures isn’t a bad thing to have. I should make you build a system for migrating your application and its database that is more robust.

PART2: How to apply a migration to a database.

The list below gives the different ways you can apply a migration to a database. I list three options for the EF Core case: the first being the simplest, but it has limitations which the other two options don’t have. The SQL migration has no real limitations, but it does need a database migration application tool to apply the SQL scripts only once and in the right order.

Here is the list of ways you can apply a migration.

  1. EF Core migration
    1. Calling context.Database.Migrate() on startup
    2. Calling context.Database.Migrate() via a console app or admin command
    3. Outputting the migration as a SQL script and execute that script on the target database.
  2. SQL migrations
    1. Use a database migration application tool.

In the end, how you apply your migration depends on the type of migration (breaking or non-breaking) and the type of application you are updating (single app, multiple apps running in parallel or an app that mustn’t stop). Here is a diagram to try and convey all these permutations.

The outer dark blue shows that SQL migrations can be applied in all cases, then the lighter, inner boxes show where different types of EF Core migrations can be added. Here are some clarifying notes about the diagram:

  • The diagram shows standard EF migration and hand-modified EF migration, but when I am talking about applying the migration then there is no distinction between the two – we are simple applying an EF Core migration.
  • The “five-stage app update” red box in the diagram represents the complex set of stages you need to apply a breaking change to a application that cannot be stopped. I cover that at the end of the article.

Now I will go through each of the ways of applying a migration in detail.

1a. Calling context.Database.Migrate() on startup

This is by far the easiest way to apply a migration, but it has a big limitation – you should not run multiple instances of the Migrate method at the same time. That can happen if you scale-out a web application. To quote Andrew Lock, “It’s not guaranteed that this will cause you problems, but unless you’re extremely careful about ensuring idempotent updates and error-handling, you’re likely to get into a pickle” – see this section of his post “Running async tasks on app startup in ASP.NET Core”.

Good parts ·         It is relatively easy to implement (see tips)
·         It ensures the database is up to date before your application runs.
Bad parts ·         You must NOT run two or more Migrate methods in parallel.
·         If the migration has an error, then your application won’t be available.
·         It’s hard to diagnose startup errors
Limitations Does not work with continuous service systems
Tips I quite like this option in Andrew Lock’s article for running a migration on startup. I use a similar approach in some of my demo systems that use in-memory databases that need initializing (see this example)
My verdict If you are running a single web app or similar and you can update the system when no one is using it then this might work for you. I don’t use this as many of my systems I work on use scale-out.

1b. Calling context.Database.Migrate() via a console app or admin command

If you can’t run multiple Migrate methods in parallel, then one way to ensure this is to call the Migrate method inside a standalone application designed to just execute the Migrate method. You might add a console application project to your main web app solution which has access to the DbContext and can call Migrate. You can either run it yourself or let your deployment system run it (Note to EF6.x users – this the equivalent of running Migrate.exe, but with the application dll compiled in).

Good parts ·         It works in all situations.
·         Works well with deployment systems.
Bad parts A bit more work.
Limitations – none – , but watch out for continuous, five-stage app update
Tips If your console application takes in a connection string to define which database to apply the migration to, then it will be easier to use in your deployment pipeline.
My verdict A good option if you have a deployment pipeline, as you can execute the console application as part of the deployment. If you are manually applying the migration, then there is the command Update-Database.

1c. Turning EF Core migration into a script and applying it to the database

By using the Script-Migration command EF Core will convert a specific migration, or by default all your migrations, into a SQL script. You can then apply this using something that can execute SQL on the specific database you want updated. You can manually execute the SQL in SQL Server Management Studio, but typically you have something in your release pipeline to do that at the right time.

Good parts ·         It works in all situations.
·         Works well with deployment systems which can use SQL scripts.
·         You can look at the SQL before its run to see if it looks OK.
Bad parts ·         More work than the console app (2b)
·         You need some application to apply the script to the correct database.
Limitations – none – , but watch out for continuous, five-stage app update
Tips The SQL contains code to update the migration history, but you must include the idempotent option in the Script-Migration command to get the checks that stops a migration from being applied twice.
My verdict If you want to use EF Core’s Migrate method, then I would suggest using 2b, the console app. It’s as safe as using the scripts and does the same job. But if you pipeline already works with SQL change scripts then this is a good fit for you.

2a. Using a migration tool to apply a SQL script

If you create a series of SQL migrations scripts, then you need something to a) apply them in the right order and b) apply them only once. EF Core’s migrations contain code that implments the “right order” and “only once” rules, but when we write our own migration scripts we need a tool that will provides those features.

I, and many others, use an open-source library called DbUp that provides these features (and more) and also supports a range of database types. I order my migration scripts alphabetically, e.g. “Script0001 – initial migration”, “Script0002 – add seed data” for DbUp to apply. Just like EF Core migrations, DbUp uses a table to list what migrations have been applied to the database and will only apply a migration if it isn’t in that table.

Other migration tools are available, for instance Octopus Deploy, and various RedGate tools (but I haven’t used them so check they have the correct features).

Good parts ·         It works in all situations.
·         Works well with deployment systems.
Bad parts ·         You have to manage the scripts.
Limitations – none – , but watch out for continuous, five-stage app update
Tips
(for DbUp)
·         I make a console application that takes in the connection string and then runs DbUp, so I can use it in my deployment pipeline.
·         For testing I make the method that runs DbUp available to my unit test assembly in a “only run in debug mode” unit test that migrates my local database correctly using my CompareEfSql tool (see the section about testing migrations in part 1 of this series).
My verdict I use this approach on projects that use EF Core.

The application and applying migrations

When you apply a migration to the database you can stop the application or in some circumstances you can apply the migration while it is running. In this section I look at the different options available to you.

1. Stopping the application while you migrate the database

This is the safest option and works with breaking and non-breaking changes, but your users and your business might not be so happy. I call this the “site down for maintenance” approach. In the “site down” approach is you don’t want to stop an application while users are inputting data or maybe finished an order. That’s how you or your company gets a bad reputation.

I had this problem myself back in 2015 and I created a way to warn people that the site was going to close and then stopped all but the admin person from accessing the application. I chose this approach because for the web application I was working on it was a less costly approach than supporting breaking changes while keeping the web app running (I cover applying breaking to a continuous service application later). You may have come across “this site is down for maintenance” on services you use, normally at weekends and overnight.

NOTE: I wrote an article called How to take an ASP.NET MVC web site “Down for maintenance” which you might like to look at – the code was for ASP.NET MVC5 so it will need some work to get it to work with .NET Core, but the idea is still valid.

Applying non-breaking migrations while the application is running

With non-breaking changes you can, in theory, apply them to the database while the old application is running, but there are some issues that can catch you out. For instance, if you added a new, non-null column with no SQL default and old software, which doesn’t know about that new column, tries to INSERT a new row you will get a SQL error because the old software hasn’t provided a value for a non-null column.

But if you know your non-breaking migration doesn’t have a problem then applying the migration while the old application is running provides continuous service to your users. There are various ways to do this, depending on which of the migration application approach you have chosen, one that come to mind are Azure’s staging slots, which have been around for ages, and the newer Azure Pipelines.

Applying breaking changes to a continuous running application: The five-stage app update.

The hardest job is applying a breaking change to a continuously running application. In the diagram showing the different approaches to will see a red box called “five-stage app update” in the top-right. The name comes from the fact that you need to migrate in stages, typically five, as shown in the diagram below.

NOTE: Andrew Lock commended that my “add a non-nullable column” problem I described in the last section can be handled in three stages: a) add new column but as nullable, b) deploy new software that knowns about that column, and c) alter the column to be non-nullable.

Here is a diagram taken from in section 11.5.3 of my book “Entity Framework Core in Action” which shows the five stages needed to add a breaking change that split an existing CustomerAndAddress table into two tables, Customers and Addresses.

As you can see an update like this is complex to create and complex to apply, but that’s the cost of running a continuous system. There aren’t any real alternatives to the five stages, other than you never apply a breaking change to a continuous running system (I have heard one person who said that was their approach).

NOTE: I cover the continuous, five-stage app update in section 11.5.3 on my book “Entity Framework Core in Action” and you can also find a coverage of this in chapter 5 of the book “Building Evolutionary Architectures” by Neil Ford et al.

Conclusion

If the data in your database and the availability of your service is important to your organisation, then you must take a database migration seriously. In part 1 I covered the different ways create a migration script and this article covers how you might apply those migrations to a production database. The aim of this series is to provide you with the options open to you, with their pros, cons and limitations, so that you can take an informed decision about how to handle migrations.

As I said in the first article my first run-ins with EF migrations was with EF6. I know EF6 very well and having written the book “Entity Framework Core in Action” I know EF Core even better. The change from EF6 to EF Core around migrations typifies the change in the whole approach in EF Core.

EF6 had lots of “magic” going on to make it easier to use – automatic migration on startup was one of them. The problem was, when EF6’s “magic” didn’t quite work, then it was hard to sort it out. EF Core’s approach to migrations is that its up to you where and how you use it – no automatic “magic”. And lots of other small changes to migrations in EF Core come from listening to users of EF4 to 6.

So, migrations on production database is scary, but its always been scary. I have given you some insights into the options but that’s only really the minimum for production database changing. Backups, policies, pre-prod testing and deployment pipelines need to be added as required to make a reliable system.

Happy coding.