Database Migrations in .NET: System Design & Tools

Keeping the database state in sync with the code evolution is a natural process in software development and should be straightforward most of the time. But, during my career, I have encountered that some teams are having real struggles putting this into the right workflow without creating additional overhead. Except for doing manual migrations by executing SQL scripts directly on the database every time before deploying a new version of code, there are also tools that provide automated way of syncing code with the database.

I have put here the most important questions which I think we need to ask ourselves when it comes to automatic migration tools:

• Which tool can effortlessly provide auto migrations and rollback?
• How does it behave when more people are working on the same codebase/db models?
• How does it behave when there are multiple app instances hitting the same database?

In the .NET world in general, we have the two most popular libraries: EF Migrations and Fluent Migrator competing with each other in different segments. Let us try to answer the above questions through some comparison between the two.

Auto Migrations and Rollback

We see that both libraries provide the same features, but in different ways. EF Migrations are model-driven and tightly integrated with rest of the .NET framework, while FluentMigrator is more lightweight when it comes to dependencies and gives more control over the code because migrations are explicitly defined in code.

More Branches vs Same Database

Here, I would give more points to FluentMigrator, since it offers greater flexibility compared to standard EF migrations, which are closely tied to EF entities and generated automatically, often accompanied by additional overhead, including extra snapshot files.

Things with snapshot files will become messy once there are more people working on db migrations at the same time. Imagine two of them creating two different branches out of one base snapshot, and then trying to update the db model independently on two different sides. When both are merged into the main branch, the migration history and snapshot can get out of sync, and the second migration may require additional changes or reconciliation before it can be applied successfully.

On the other hand, FluentMigrator does not have such issues, because it does not have checks on model and db snapshots. It just executes the code which is needed for the db to get updated to the new version. And this is, in my opinion, one really big time saver in the first place and it also reduces the possibility of unintentional code breaks during the migration phase.

Multiple Application Instances vs Same Database

Here, things can quickly become very messy. When dealing with a real production environment, the setup mostly consists of multiple app instances targeting the same database, and migrations can become challenging. Imagine the following scenario:

We have three app instances and they are all running migration up at startup. We have published a new code version, and we want all our apps to update accordingly. In real life, it can happen that one of the app instances has not yet been updated while the database behind it has already been updated. As a result,old queries might fail against the new database setup.

It can also happen that two app instances are trying to update the database at the same time. Which migration will succeed and which one will fail? Do we have a locking mechanism inside the libraries? Libraries do provide some sort of locking mechanism, but still we need to rethink how the deployment architecture should be designed if we want to achieve seamless updates every time.

Let us take a look at the diagram below:

This architecture is imagined in such a way that first app instance ready for the update takes a migration lock and performs the update, while the other instances keep serving the old version in the meantime. A migration lock prevents multiple instances from running migrations at the same time. It does not prevent old application instances from sending queries that are incompatible with the new schema.

Once the migration is done, the instance that performed the migration will be the only one serving the traffic, while others will be temporarily removed from the load balancer and updated. Once they are up and running again, the load balancer resumes its normal configuration and we have all apps up and running with almost zero downtime.

True zero downtime cannot be guaranteed simply by using migration locks, since it will always be a period when some requests might be invalid. It requires both safe database changes and a deployment strategy that can switch traffic without interrupting requests. Of course, if migration is large, this potential downtime increases, but that is one more reason to keep updates smaller.

In the end, all depends on your setup, and the problems you are trying to solve. Here, I am only giving the general idea and some framework for thinking when it comes to real problems.

Final Words

When it comes to choice of a tool/library, I am in favor of FluentMigrator as it provides more flexibility and is not coupled with the rest of EntityFramework. I have explained the syntax and how the library can be used in FluentMigrator tutorial: creating and configuring database migrations. In that same article, I also touched on the key differences between EF Migrations and FluentMigrator, so it can be used as a checklist if you are in doubt which one to use. On top of that, there is also a different article on how it can be set up for .NET Core Web API project, so it is worth checking it out.