Database Migrations In .NET Core

There are two types of people when it comes to Database Migrations. Those that want a completely automated process in their CI pipeline and have very strong opinions on what tool to use it, and then there are those who couldn’t care less and just want to run things by hand (Seriously, those still exist).

This article will try and focus on the former, on what tools best fit into the >NET Core ecosystem. I think most of it isn’t specific to .NET Core, but there are a few extra considerations to take into account.

First let’s take a look at what I think are the 4 main principles of a database migration suite (Or any step in a CI pipeline).

Repeatable

You should be able to standup a database from scratch and have it function exactly as the existing production database does. This may include pre-seeding data (such as admin users) right from the get go. Repeatable also means that if for some reason the migration runs again, it should be “aware” of what it’s already done and not blow up trying to run the same scripts over and over.

Automated

As much as possible, a good database migration strategy should be automated. When trying to reduce the element of human error, you should just remove the humans all together.

Scalable

There are actually two parts to this. Scalable means that as the database gets bigger, your migration doesn’t start falling over. This also means that the tooling you might be using to generate diffs or actually run the migrations doesn’t start dying too. But scalable also has a second meaning, and that is scalable with your team. That means as your team reaches large proportions on a single project (Say close to a dozen developers), does managing migrations and potential code conflicts get out of control.

Flexible

For database migrations, flexible is all about is the tooling good enough to handle all sorts of database changes, not just a schema change. If you ever split a field, you will have to migrate that data somehow. Other times you may want to mass update data to fix a previous bug, and you want this to be automated along with your database rollout, not run manually and forgotten about.

Database Project/SQL Project in Visual Studio

I remember the first few times I used a database project to update a database schema, it felt like magical. Now it feels like it has it’s place for showing a database state, but it’s migration ability is severely lacking. In terms of how a dbproj actually processes an update, it compares the existing database schema to the the desired state, and then generates scripts to get it there and runs them. But let’s think about that for a second, there may be many “steps” that we want to do to get us to the desired schema, not a huge leap forward. It’s less of a step by step migration and more of a blunt tool to get you to the end result the fastest.

Because of the way a dbproj processes migrations, it also makes updating actual data very hard to do. You may have a multi step process in which you want to split a column slowly by creating a temporary field, filling the data into there, and then doing a slow migration through code. It doesn’t really work because a dbproj is more of a “desired state” migration.

That being said, database projects are very good at seeing how a database has evolved over time. Because your database objects become “code” in your source control, you can see how over columns/views/stored procedures have been added/remove, and with the associated check in comment too.

It should also be noted that DB Projects are not multi platform (Both in OS and database). If you are building your existing .NET Core project on Linux then database projects are out. And if you are running anything but Microsoft SQL Server, database projects are also out.

Repeatable : No (Migration from A -> B -> C will always work the same, but going straight from A -> C may give you weird side effects in data management).
Automated : Yes
Scalable : Yes
Flexible : No – Data migrations are very hard to pull off in a repeatable way

Entity Framework Migrations

EF Migrations are usually the go to when you are using Entity Framework as your data layer. They are a true migration tool that can be started from any “state” and run in order to bring you to the desired state. Unlike a dbproj, they always run in order so you are never “skipping” data migrations by going from state A -> C. It will still run A -> B -> C in order.

EF Migrations are created using their own fluent API in C# code. For some this feels natural, but others feel limited in what they can achieve trying to control a complex database with a subset of SQL commands that have been converted to the DSL. If you think about complex covering indexes where you have multiple columns along with include columns etc, there is probably some very complex way to do it via C# code, but for most people it becomes a hassle. Add to the fact that ORM’s in general “hide” what queries they are actually running, so now you are also hiding how your database is actually tuned and created,  it does make some people squirm.

But the biggest issue with EF Migrations is the actual migration running itself. Entity Framework is actually “aware” of the state of the database and really throws its toys out of its cot if things aren’t up to date. It knows what migrations “should” have run, so if there are pending migrations, EF really does have a hissy fit. When you are running blue/green deployments, or you have a rolling set of web servers that need to be compatible with your old and new database schema, EF Migrations simply do not work.

Repeatable : Yes
Automated : Yes
Scalable : No
Flexible : Sort Of – You can migrate data and even write custom SQL in a EF Migration, but for the most part you are limited to the EF fluent API

DB Up

Full disclosure, I love DB Up. And I love it even more now that it has announced .NET Core support is coming (Or already here depending on when you read this).

DB Up is a migration framework that uses a collection of SQL files, and runs them in order from start to finish. From any existing state to any desired state. Because they are just plain old SQL files, every SQL command is available to you, making DB Up easily the most powerful migration tool in your arsenal for dealing with extremely complex databases. Data migrations also become less of a pain because you simply have every single SQL tool available.

In a CI scenario, DB Up is able to build a database from scratch to any point in time, meaning that testing your “new” C# code on your “old” database is now a breeze. I can’t tell you how many times this has saved my ass in a large team environment. People are forever removing columns in a SQL database before removing them from code, causing the old code to error out and crash. In large web deployment scenarios where there are cross over periods with a new database schema being in play, but old web code running on top of it, it’s a god send.

The other great thing about DB Up is that it’s database agnostic (To a degree). It supports Postgres, MYSQL, Firebird, SQL Azure and of course SQL Server. This is great news if you are running your .NET Core code on something like Postgres.

But, there is a big flaw when using something with DB Up. It becomes hard to see how a particular table has changed over time. Using DB Up all you have is a collection of scripts that when run in order, give you the desired result, but using source control it becomes difficult to see how something has evolved. In terms of migrations, this isn’t a big deal. But in terms of being able to see an overall picture of your database, not so great.

Repeatable : Yes
Automated : Yes
Scalable : Yes
Flexible : Yes

Hybrid Approach

I tend to take a hybrid approach for most projects. DB Up is far and away the best database migration tool, but it is limited in seeing the overall state of the database. That’s where a database project comes in. It’s great for seeing the overall picture, and you can check back through source control to see how things have changed over time. But it’s migration facilities are severely limited. With that, it becomes a great fit to use both.

And You?

How do you do your migrations? Do you use one of the above? Or maybe something like Fluent Migrator? Drop a comment below!