Handling Entity Framework Core database migrations in production – Part 1

Andrew Lock wrote an excellent series called “Running async tasks on app startup in ASP.NET Core” in which he used “migrating a database” as an example of something you could do on startup. In part 3 of the series he covered why migrating a database on startup isn’t always the best choice. I decided to write a series about the different ways you can safely migrate a database, i.e. change the database’s schema, using Entity Framework Core (EF Core).

This is the first part of the series, which looks how to create a migration, while part 2 deals how to apply a migration to a database, specifically a production database. I have used a similar approach to Andrew’s when writing this article, i.e. I try to cover all the way to create a migration script when working with EF Core with their pros, cons and limitations.

NOTE: Andrew and I know each other as we were both writing books for Manning Publications at the same time: Andrew’s book is “ASP.NET Core in Action” and mine is “Entity Framework Core in Action”. We shared the toils and joys of being an author, but Andrew has the harder job with ASP.NET Core – his book is 700 pages long and mine is “only” 500 pages long.

TL;DR – summary of creating a migration

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

• 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 five ways to create a migration in EF Core
  • Use EF Core to create a migration – easy, but doesn’t handle all possible migrations.
  • Use EF Core to create a migration and then hand-modify the migration – medium to hard, but handles all possible migrations.
  • Use a third-partly migration builder to write a C# migration – hard, as you need to write the migration yourself, but you don’t need to know SQL.
  • Use a SQL database comparison tool to compare databases and output a SQL changes script – easy, but you do need some understanding of SQL.
  • Write your own SQL migration scripts by copying EF Core’s SQL – hard-ish, gives great control but you do need to understand SQL.
• How to be sure your migration is valid – use CompareEfSql tool.

Setting the scene – what questions should we ask about creating a migration?

There are lots of ways to migrate a database’s schema, and in development you can use almost any approach. But when it comes to migrating a production database (i.e. the one that is being used by real users) then it becomes very serious. Getting it wrong will, at the minimum, inconvenience your users and at worse mangle or lose you (precious) data in your database!

But before we get the scary part of updating a database schema we need to build the migration script that will contain the schema, and possibly data, changes. To build the appropriate migration script we need to ask ourselves some important questions about the type of changes we need to apply to the database. Is the required migrations going to be:

1. A non-breaking change, i.e. it only adds new columns, tables, etc., which could be applied while the old software was still running, i.e. the old software would work (not break) with the database after the migration.
2. A breaking change, i.e. some data must be copies or transformed during the migration, could NOT be applied while the old software, i.e. the old software would encounter errors (break) with the database after the migration.

The other part in this article is that we are working with EF Core, which brings both some benefits and constraints. The benefits are that EF Core can, in most cases, create the required migrations automatically. The constraints are that the database after a migration has been applied must match the software Model of the database that EF Core builds by looking at your DbContext and mapped classes – I refer to EF Core’s Model with a capital M, because there is a property called Model in the DbContext that contains the complete mappings between classes and the database.

NOTE: I’m going to cover migrations where you are in control of the control of the classes mapped to the database and the EF Core configuration – sometimes known as the Code-First Approach. The other alternative, which I’m NOT going to cover, is where you directly control the database and you use an EF Core command called scaffolding to create the entity classes and EF Core configuration for you. That’s because migrations are trivial – just re-scaffold your database.

PART1. The five ways to create a migration script

As I said in the last section, any migration script we create must migrate the database to a state that matches EF Core Model. For instance, if a migration added a new column to a table then the entity class mapped to that table must have a property to match that new column. If EF Core’s Model of the database schema does match the database, then you can get errors occurring in queries or writes. I refer to the migration script as creating a “valid” database if it matches EF Core’s Model of that database.

The validity of a migrations created by EF Core isn’t in doubt – EF Core created it so it will be valid. But if we need to edit a migration, or we take on the job of building the migration ourselves, then we need to be very careful that the migration creates a “valid” database as far as EF Core is concerned. This is something I have thought about a lot.

This is the list of ways you can create a migration script.

• Creating C# migration script
  1. Standard EF Core migration script: use EF Core’s Add-Migration command to create a C# migration script.
  2. Hand-modified EF Core migration script: Use EF Core’s Add-Migration command to create a C# migration script and then hand-edit it to add the bits that EF Core missed.
  3. Use a third-partly migration builder, e.g. FluentMigrator. Tools like this allow you to write your own migration script in C#.
• Creating SQL migration script.
  1. Use a SQL database comparison tool. This compares the last database schema with the schema of a new database created by EF Core and produces a SQL script that will migrate the old database to the new database schema.
  2. Write your own SQL migration scripts. A competent writer of SQL could produce a SQL migration scripts, with some help by capturing the SQL EF Core would use to create a database.

Here is a summary diagram to give you an overall review of the five approaches, with my personal view on the ease of use and level of limitations.

Now let’s look at each one of these in turn.

1a. Standard EF Core C# migration script

This is the standard migration technique provided by EF Core. It’s well documented by Microsoft, but in summary you run a command called Add-Migration which adds three C# files to your application that contain the changes needed to migrate an existing database using migraions to match the current EF Core setup/configuration.

Reference: Microsoft’s documentation on creating a migration.

1b. Hand-modified EF Core C# migration script

The nice thing about EF Core’s Add-Migration command is it creates the C# migration files as the starting point, but you can then edit these files yourself to add code to handle breaking changes or add/update SQL parts of the database. Microsoft give an example of handling a breaking change with copying data.

Reference: Microsoft’s example of hand-modifying a migration.

1c. Use a third-partly C# migration builder

It was Andrew Lock who pointed out to me an approach using the FluentMigrator (see this documentation) to write your migrations. This works similarly to EF migrations, but you have to do all the hard work of detailing the changes. The good thing is FluentMigrator’s commands are very obvious.

Reference: FluentMigrator of GitHub.

2a. Use SQL database comparison tool

There are free and commercial tools that will compare two databases and create a SQL change script that will migrate the old database schema to the new database schema.

A “free” comparison tool is built into Visual Studio 2017 (all versions) called SQL Server Object Explorer, under the View tab. If you right-click on a database, then you can access the “Compare Schema” tool (see figure above right) which can produce a SQL change script.

The SQL Server Object Explorer tool is very good, but there isn’t much documentation on this (pity). Other commercial systems include Redgate’s SQL Compare.

2b. Hand-coding SQL migration scripts

This sounds really hard – writing your own SQL migrations, but there is plenty of help on hand, either from SQL compare tools (see above) or by looking at the SQL EF Core would use to create the database. This means I have SQL I can look at and copy to build my SQL migration script.

You can capture the SQL output by EF Core’s for creating a database but capturing the EF Core’s logging while calling the method EnsureCreated (the EnsureCreated method is meant for creating unit test database). Because setting up logging for EF Core is a little bit complex I added helper methods to my EfCore.TestSupport library to handle that. Here is an example unit test that creates a new SQL database and catches the SQL commands that EF Core produces.

[RunnableInDebugOnly]
public void CaptureSqlEfCoreCreatesDatabaseToConsole()
{
    //SETUP
    var options = this.CreateUniqueClassOptionsWithLogging<BookContext>(
        log => _output.WriteLine(log.Message));
    using (var context = new BookContext(options))
    {

        //ATTEMPT
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
    }
}

Let’s look at each line of this code

• Line 5. This is an EfCore.TestSupport method that creates the options for your DbContext. This version uses a database name that includes the class name. I do this because xUnit test classes are run in parallel, so I want a unique database for this unit test class.
• Line 6. I use the version of the option builder ending with …WithLogging, which allows me to capture the log outputs. In this case I output the Message part of the log directly to the unit test output window.
• Lines 11 & 12. First, I ensure the database is deleted so that when I call EnsureCreated a fresh database will be created with a schema defined by the current DbContext’s configuration and mapped classes.

Below is part of the output captured in the unit test output. This provides you with the exact SQL that EF Core would use to create the whole schema. You do need to extract just the parts that relate to your migration, but at least you can cut-and-paste the parts you need into your SQL migration script.

CREATE DATABASE [EfCore.TestSupport-Test_TestEfLogging];
Executed DbCommand (52ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
IF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
    ALTER DATABASE [EfCore.TestSupport-Test_TestEfLogging] SET READ_COMMITTED_SNAPSHOT ON;
END;
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Authors] (
    [AuthorId] int NOT NULL IDENTITY,
    [Name] nvarchar(100) NOT NULL,
    CONSTRAINT [PK_Authors] PRIMARY KEY ([AuthorId])
);
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Books] (
    [BookId] int NOT NULL IDENTITY,
    [Title] nvarchar(256) NOT NULL,
-- rest of SQL left out

How to be sure your migration is valid – use CompareEfSql tool

I have mentioned CompareEfSql a few times in the descriptions of creating migrations. This tool compares a database with the model of the database that EF Core creates on first use for a DbContext. This model, accessed via the Model property in the DbContext instance, is built up my EF Core by looking at the DbContext configurations and DbSet and DbQuery properties.

This allows a developer to test an existing database against EF Core Model and gives you error messages if they are different. I find this a very powerful tool which allows me to hand-code SQL migrations and be sure that they are correct (there are some small limitations). Here is an example unit test that will fail if the database schema doesn’t match EF Core’s Model.

[Fact]
public void CompareViaContext()
{
    //SETUP
    var options = … options that point to the database to check;
    using (var context = new BookContext(options))
    {
        var comparer = new CompareEfSql();

        //ATTEMPT
        //This will compare EF Core model of the database 
        //with the database that the context's connection points to
        var hasErrors = comparer.CompareEfWithDb(context);

        //VERIFY
        //The CompareEfWithDb method returns true if there were errors.
        //The comparer.GetAllErrors property returns a string
        //where each error is on a separate line
        hasErrors.ShouldBeFalse(comparer.GetAllErrors);
    }
}

I love this tool, which is in my EFCore.TestSupport open-source library. It allows me to build migrations and be sure they are going to work. I also run it as a normal unit test and it tells me immediately if I, or another team mate, has changed EF Core’s setup.

You can get a much longer description of this tool in the article called EF Core: taking full control of the database schema and its many features and configurations can be found in the CompareEfSql documentation pages.

NOTE: I build a version of this originally for EF6.x (see this old article), but it was limited because EF6.x didn’t fully expose its internal model. With EF Core I could do so much more (EF Core rocks!) and now I can check almost everything, and because I tap into EF Core’s scaffolder service it works for any database that EF Core supports (see this doc).

Conclusion – Part 1

This part of the series covers creating a valid migration, while part 2 deals with applying a migration to a database. This article lists all the applicable approaches to creating a database migration when working with EF Core – with their pros, cons and limitations. And as you can see EF Core’s Add-Migration command is really good, but it doesn’t cover every situation.

Its up to you to decide what types of migrations you might encounter, and what level of control do you want over the database schema. If you can get away with just using EF Core’s standard migrations (1a) then that makes life easier for you. But if you expect breaking-changes, or you need to set up extra SQL features then you now know the options available to you.

The scary part comes in the part2 – applying 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.

The original reason I moved away from EF migrations in EF6 was its automatic migration on startup was working fine, until it threw an error on deployment! It was really hard to track down an error in the migration – that alone got me moving away from using EF migrations (read this old article on my thoughts back then).

EF Core’s migration handling is better than EF6: automatic migrations have (thankfully!) gone and EF Core migration are more git-merge friendly, to mention just two changes. But the way I build SQL migration scripts makes me think much more carefully about what I am doing than just running Add-Migration. EF Core is a really great O/RM but it does hide the database too well sometime. Creating SQL migration scripts makes me think through a migration from the database point of view, and I often spot little tweaks to the database, and the C# code, to make the database better or more robust.

Now go to part 2 to see how to apply a migration to a database.