Database versioning is hard.

Even in this age of source control, CI/CD pipelines or Infrastructure as code, it seems like an unsolved problem.

I am not saying that there are no solutions, it’s just that all of them have quite significant downsides and there is no silver bullet. Of course, the solution I am going to suggest isn’t the one. It’s just another one, with its own downsides. However, I still believe that for some cases, it can be useful.

Two approaches to database versioning

There are two main approaches for database versioning: state-based and migration-based.

The state-based approach (e.g. using SSDT) gives you an instant overview of how your current database version looks like – you see the tables, their column, constraints etc. But it won’t give you an easy way how to store the actual changes in the state.

The migration-based approach (e.g. using DbUp or EF Core migrations) makes versioning the changes easy.

On the other hand, there is no single view of the actual state – it is scattered through the migrations and you need to run all of them to get to the current state.

If you use code-first migrations, then this doesn’t fully apply – you see the current state (at least tables and their columns) in the code.

However, there is still another problem: if two or more developers simultaneously create conflicting migrations, you may end up with an inconsistent state of the database. (The same conflict can also happen in the database project, but it’s much easier to spot.)

Let’s use both!

To leverage both approaches, let’s combine them together!

This way, we get the benefits:

current state in one place,
uniform way to get to the current state from any past state.

Of course, there is a cost for having all the benefits: we need a process to keep migrations and database project synchronized when making a change in the database.

How we make a change in the database

There are two essential steps:

Perform the actual change in the database project.
Add a migration script for the change.

If you do step 2 manually, then you are clearly doing the work twice. There might be times when you have no other choice, but most of the time, the database project may do some or even all of the work for you.

When you make a change to the database project, it automatically generates (or amends) a .refactorlog file that contains a representation of the changes.

Those changes are then translated to SQL and added to the publish script. And those SQL scripts are exactly what we want – they are our migration scripts! Let’s make use of that:

Generate publish script for the project.
In bin/Debug folder, open the file [name of the project].publish.sql and find the section "The following operation was generated from a refactoring log file [some guid]".
This section contains the SQL to apply the changes. Do the sense check: Does this script look reasonable? Does it do what it’s supposed to do? For example, it is possible that when you rename a column, SSDT will think that you removed it and added a new one (don’t ask me why). Without a human intervention, that would mean losing all data in the column.
Create a new migration file in [name of the project]/MigrationScripts (or other arbitrary folder) and put the SQL there. Name it Migration_YYYYMMDD.sql so that the alphabetical and chronological order are the same.
Delete the .refactorlog file from the project. You only want it to contain changes that are not yet reflected in the migration scripts.

What we need to do

1. Set up DbUp with ASP.NET Core

Of course, there is some documentation, but it is not very detailed. I ended up reading through source code at least three times because the documentation didn’t contain a piece of information I needed.

Part 2: Set up DbUp is dedicated to the setup, so that you can quickly get everything up and running.

2. Deploy the database (with migrations applied)

The database project represents the most current version of your database – all the existing migrations are actually applied to it.

If you simply deploy the database, the table that contains applied migration scripts will be empty. That means that when you run your ASP.NET application, DbUp will attempt to apply the migrations that are already reflected in the database, and we definitely don’t want that. We need to populate the migrations table and we will do it in Part 3: Deploy the database.

What’s next

Read the next posts in the series:

Last modified on 2021-03-19