Original link: https://www.thereformedprogrammer.net/handling-entity-framework-core-database-migrations-in-production-part-2/
Author: Jon P Smith
Practical patterns for using EFCore in production
This is the second article in a series on migrating databases with EF Core. This article looks at applying migrations to the database, and starts from Part 1, which introduced how to create migration scripts. If you haven't read Part 1, some parts of this article will be meaningless, so here's a quick recap of Part 1.
- Two types of migrations can be applied to a database:
- Adding new tables, columns, etc., called non-breaking changes (easy).
- Changing columns/tables and requiring data copying, called breaking changes (hard).
- There are two main ways to apply migrations to a database:
- Using EF Core migration functionality
- Using EF Core to create migrations, then manually modifying them.
- Using a third-party migration builder to write migrations in C#.
- Using a SQL database comparison tool to compare databases and output SQL change scripts.
- Writing your own SQL migration scripts by replicating EF Core's SQL.
So, now that you know how to create migration scripts, I'll look at the different ways to apply migrations to a production database and the pros and cons of each.
TL;DR – Summary
Note: Click a link to go directly to the section covering that point.
- The type of application you have influences which migration method can be used. • You must consider what happens if an error occurs and have a plan. • There are four ways to apply migrations to a database:
- Calling context.Database.Migrate() at startup – Very Easy, but has some serious issues that limit its usefulness.
- Calling context.Database.Migrate() via a console application – Easy and works well, especially in deployment pipelines.
- Outputting EF Core migrations as SQL scripts and executing them on the target database – Hard, but provides good control.
- Using a database migration application tool to apply your own SQL scripts – Hard, but you have good control.
- Three different levels of applying migrations:
- Stopping the application while migrating the database is the safest option, but not always possible.
- Some (but not all) non-breaking changes can be applied while the application is running.
- For continuously running services (24/7 services), applying breaking changes requires five steps.
Scenario Analysis – What kind of application is your product?
In Part 1, we focused on creating "effective" migrations, and whether a migration is a non-breaking change or a breaking change (see the quick definitions at the beginning of this article, or this link in Part 1).
Now, we are considering applying migrations to the database, but the options we have depend on the application(s) accessing the database. Here are the questions you need to consider.
Is there only one application accessing the database, or is your application a horizontally scaled web application, i.e., multiple versions of the application running simultaneously? If your application is horizontally scaled, one option is removed.
Can you stop the application while applying migrations, or does your application provide 24/7 continuous service? Updating continuously running applications poses some challenges when applying breaking changes.
Being a bit paranoid when migrating a production database is okay.
As I said at the end of Part 1 – the scariest part comes when you apply migrations to the production database. Changing a database containing critical business data (requirements!) requires careful planning and testing. You need to consider what to do if (when!) the migration fails due to an error.
When considering different methods of applying migrations, you should have the thought "What happens if there's an error?" in mind. This may lead you to choose a more complex migration method because it's easier to test or roll back. I can't provide rules or advice because each system is different, but being a bit paranoid about failure is not a bad thing. It should drive you to build a more robust system for migrating your application and its database.
Part 2: How to apply migrations to the database
The list below provides different methods for applying migrations to the database. I list three options for the EF Core case: the first is the simplest, but it has limitations that the other two options don't have. SQL migrations have no practical limitations but do require a database migration application tool to apply SQL scripts in the correct order.
Here's a list of ways you can apply migrations.
EF Core migrations
- Calling context.Database.Migrate() at startup
- Calling context.Database.Migrate() via a console application or admin command
- Outputting migrations as SQL scripts and executing them on the target database.
SQL migrations
- Using a database migration application tool.
Finally, how you apply migrations depends on the migration type (breaking or non-breaking) and the type of application to be updated (single application, multiple applications running in parallel, or an application that must be stopped). Here's a diagram of all these permutations.
The outer dark blue indicates that SQL migrations can be applied in all cases, while the inner lighter boxes indicate where different types of EF Core migrations can be added. Here are some clarifications about the diagram:
- The diagram shows standard EF migrations and hand-modified EF migrations, but when I talk about applying migrations, there is no difference – we are simply applying EF Core migrations. • The "Five-phase application update" red box in the diagram indicates the complex phases needed for a breaking change on an application that cannot be stopped. I'll cover this at the end of the article.
Now, I'll go into detail about each way of applying migrations.
1a. Calling context.Database.Migrate() at startup
So far, this is the simplest way to apply migrations, but it has one big limitation – you should not run multiple instances of the Migrate method concurrently. This can happen if you have a horizontally scaled web application. Quoting Andrew Lock: "
We cannot guarantee that this will cause trouble, but unless you are very careful to ensure idempotent updates and error handling, you are likely to run into problems
" – See this part of his post "Running async tasks at app startup in ASP.NET Core".
| Recommendation | Details |
|---|---|
| Benefits | · Relatively easy to implement (see tips) · Ensures the database is up-to-date before the application runs. |
| Drawbacks | · Must not run two or more Migrate methods in parallel. · If a migration has an error, your application will be unavailable. · Difficult to diagnose startup errors. |
| Limitations | Not suitable for continuously running systems. |
| Tips | I really like the option of running migrations at startup from Andrew Lock's article. I've used a similar approach in some demo systems using in-memory databases that need initialization (see this example). |
| My suggestion | If you are running a single web application or similar, and you can update the system while no one is using it, this might work for you. I don't use it as much because many systems I work with use horizontal scaling. |
1b. Calling context.Database.Migrate() via a console application or admin command
If you can't run multiple Migrate methods in parallel, one way to ensure this is to call the Migrate method inside a standalone application designed only to execute the Migrate method. You can add a console application project to your main web application solution that can access the DbContext and call Migrate. You can either run it yourself or let your deployment system run it (EF6.x users note – this is equivalent to running Migrate.exe, but with compiled application dlls).
| Recommendation | Details |
|---|---|
| Benefits | · Works in all cases. · Works well with deployment systems. |
| Drawbacks | More work. |
| Limitations | – None – but watch out for the continuous five-phase application update. |
| Tips | If your console application uses a connection string to define which database to apply the migration to, it will be easier to use in deployment pipelines. |
| My suggestion | If you have a deployment pipeline, this is a good option because you can execute the console application during deployment. If you apply migrations manually, there is the command Update-Database. |
1c. Converting EF Core migrations into scripts and applying them to the database
By using the script migration command, EF Core converts a specific migration or, by default, all migrations into a SQL script. You can then apply this using a method that can execute SQL on the specific database you want to update. You can execute SQL manually in SQL Server Management Studio, but usually, your release pipeline contains something to execute it at the appropriate time.
| Recommendation | Details |
|---|---|
| Benefits | · Works in all cases. · Works well with deployment systems that can use SQL scripts. · You can view the SQL before running it to see if it looks okay. |
| Drawbacks | · More work than console application (1b) · You need some application to apply the script to the correct database. |
| Limitations | – None – but watch out for the continuous five-phase application update. |
| Tips | SQL contains code to update the migration history, but you must include the idempotent option in the Script-Migration command to get checks that prevent applying a migration twice. |
| My suggestion | If you want to use EF Core's Migrate method, I recommend using the console application 1b. It is just as safe as using scripts and does the same work. However, if your pipeline already works with SQL change scripts, this fits you well. |
2a. Applying SQL scripts using a migration tool
If you create a series of SQL migration scripts, you need steps to: a) apply them in the correct order, and b) apply them only once. EF Core's migrations contain code to enforce the "correct order" and "only once" rules, but when we write our own migration scripts, we need a tool that provides these capabilities.
I and many others use an open-source library called DbUp, which provides these features (and more), and also supports multiple database types. I arrange migration scripts alphabetically, e.g., "Script0001 – Initial migration", "Script0002 – Add seed data" for DbUp to apply. Like EF Core migrations, DbUp uses a table to list which migrations have been applied to the database, and only applies a migration if it's not in that table.
Other migration tools exist, such as Octopus Deploy and various RedGate tools (but I haven't used them, so check if they have the right features).
| Recommendation | Details |
|---|---|
| Benefits | · Works in all cases. · Works well with deployment systems. |
| Drawbacks | You have to manage scripts. |
| Limitations | – None – but watch out for the continuous five-phase application update. |
| Tips *(for DbUp) | I made a console application that takes a connection string and then runs DbUp, so it can be used in a deployment pipeline. · For testing, I made the method that runs DbUp available to my unit test assembly in a "debug-only mode" unit test, which properly migrates the local database using my CompareEfSql tool (see the section on testing migrations in Part 1 of this series). |
| My suggestion | I use this approach on projects that use EF Core. |
Application and application migration
When applying migrations to the database, you can stop the application, or in some cases, apply migrations while the application is running. In this section, I'll cover the different options available to you.
1. Stopping the application while migrating the database
This is the safest option and works with both breaking and non-breaking changes, but your users and your business may not be very happy. I call this "maintenance site". In the "site shutdown" approach, you don't want to stop the application while users are entering data or completing orders. That's how you or your company gets a bad reputation.
I ran into this issue back in 2015, and I created a way to warn people that the site was about to go down, then stop everyone except admins from accessing the application. I chose this method because for a web application in use, it has less overhead than supporting breaking changes while keeping the web application running (I'll cover breaking changes for continuously running applications later). Often on weekends and evenings, you might encounter a "This site is down for maintenance" message for services you use.
Note: I wrote an article called "How to take an ASP.NET MVC website down for maintenance" that you might want to look at – the code is for ASP.NET MVC5, so it needs some work to make it work in .NET Core, but the idea is still valid.
Applying non-breaking migrations while the application is running
In theory, with non-breaking changes, you can apply them to the database while the old application is running, but some issues can trip you up. For example, if you add a new non-nullable column without a SQL default value, and the old software that doesn't know about the new column tries to insert a new row, you'll get a SQL error because the old software didn't provide a value for the non-nullable column.
However, if you know that non-breaking migrations are problem-free, applying migrations while the old application is running will provide continuous service to your users. There are several ways to do this, depending on which migration application method you choose; Azure's staging slots (which have been around for a long time) and the newer Azure Pipelines come to mind.
Applying breaking changes to continuously running applications: The five-phase application update
The most difficult task is applying breaking changes to a continuously running application. In the diagram showing the different methods, there is a red box in the upper right corner called "Five-phase application update". The name comes from the fact that you need staged migrations, typically five phases, as shown in the diagram below.
Note: Andrew Lock praised the "adding a non-nullable column" problem I described in the previous section, which can be handled in three phases: a) add the new column but make it nullable, b) deploy new software that knows about the column, and c) change the column to non-nullable.
Here is a diagram from Section 11.5.3 of my book on EF Core, showing the five phases required to add a breaking change that splits an existing CustomerAndAddress table into two tables, Customers and Addresses.
As you can see, such an update is complex to create and apply, but that's the cost of running a continuously running system. There is no real alternative to these five phases, except to never apply breaking changes to a continuously running system (I've heard some say that's their approach).
Note: I cover the continuous, five-phase application update in Section 11.5.3 of my book "Entity Framework Core in Action", and you can also find content on this in Chapter 5 of Neil Ford's "Building Evolutionary Architectures", etc.
Conclusion
If the data in the database and the availability of the service are important to the organization, you must take database migrations seriously. In Part 1, I covered different ways to create migration scripts, and this article introduced how to apply those migrations to a production database. The purpose of this series of articles is to give you a range of options, along with their pros and cons, so you can make an informed decision on how to handle migrations.
As I said in the first article, my first encounter with EF migrations was with EF6. I know EF6 very well and have written the book "Entity Framework Core in Action" – I have an even better understanding of EF Core. The changes around migrations from EF6 to EF Core represent a change in the overall approach in EF Core.
EF6 did a lot of "magic" to make it easier to use – automatic migrations at startup is one example. The problem is that when EF6's "magic" didn't work well, it was hard to unravel. EF Core's migration approach leaves it up to you to decide how and where to use it – there's no automatic "magic". Many other small changes in EF Core migrations come from listening to users of EF4 through 6.
So, migrations on a production database are scary. I've given you some insight into the options, but this is just the bare minimum for changing a production database. You need to add backups, policies, pre-production testing, and deployment pipelines to build a reliable system.
Happy coding!