Quick and easy database version control with DbUp

Not many things can cause so much extra overhead and disruption to the development process as manually running SQL scripts on multiple environments. Naturally, you get in situations where:

You don’t know which scripts have run on which environment.
Different databases in different environments start subtly drifting apart.

Both are very undesirable. The debugging work associated with finding and correcting the differences shouldn’t be necessary. Making changes to our database would preferably be completely automated.

Databases version control for developers

Before getting into things any further I want to make one thing clear: I’m a developer at heart. This means that I’m approaching this problem from a ‘code-first‘ perspective.

There are other ways to solve the version control problem, but those usually involve third-party tools and apps. I just want my database migrations to deploy automatically using my pipelines and without human intervention.

That’s when I stumbled upon DbUp. A very lightweight library that simply takes a bunch of SQL scripts, checks which ones haven’t run on the database, and then runs those in order.

Can’t you just use Entity Framework Migrations?

The situation where I’m implementing DbUp already has a complex existing database with multiple applications running on it. There also wasn’t any source control on the database before, and the team is more familiar with SQL than Entity Framework Migrations.

And to be honest, I’ve always felt like migrations in Entity Framework can cause a lot of issues, especially when working in a team. Writing the SQL gives me a lot more control over what happens.

But could I go database-first in Entity Framework? Yes, probably. Do I want to? No, not this time.

Getting started

Start by creating a new C# console app.

Next, install the following NuGet packages:

Install-Package DbUp
Install-Package Microsoft.Extensions.Configuration

The first package is DbUp itself. The next one allows us to use an appsettings.json file to store a connection string for our database.

Next up is creating that appsettings.json file in the root folder of your project and adding the JSON below. Don’t forget to match the configuration for your database!

{
  "ConnectionStrings": {
    "MyDatabaseConnection": "data source=localhost;initial catalog=mydatabase trusted_connection=true;TrustServerCertificate=True"
  }
}

Next, add the following code to your Program.cs:

using System.Reflection;
using DbUp;
using Microsoft.Extensions.Configuration;

IConfiguration Configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
    .AddEnvironmentVariables()
    .AddCommandLine(args)
    .Build();


var connectionString = Configuration.GetConnectionString("MyDatabaseConnection");

var upgrader =
    DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .LogToConsole()
        .Build();

var result = upgrader.PerformUpgrade();

if (!result.Successful)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(result.Error);
    Console.ResetColor();
         
    return -1;
}

Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Success!");
Console.ResetColor();
return 0;

That’s it, you’re all set up! If you run your console app now, it DbUp should connect to your database and attempt to update it. To check which scripts have and haven’t run, DbUp will create a SchemaVersions table in your database to store the name of any executed migration scripts.

However, we don’t have any scripts right now, so nothing is going to happen. Let’s add our first script!

Adding a script

Create a folder called Scripts at the root of your project.

Now create a new SQL file in it. Keep in mind though that the names of your scripts must be unique! This is why I like using the following naming convention:

{date}_{time}_{description}.sql

E.g.

20230414_1738_CreateOrdersTable

A few advantages of using this format:

Scripts are always in order of creation.
The description makes it easier to navigate the list of scripts.
The chance of having duplicate script names is very small.

Finally, set the Build Action of your script file to Embedded Resource:

Property settings for the embedded file

Now run your console app again, et voila!

Console output when running the DbUp migrator

Any downsides?

Yes, you can’t migrate down. So once a script is applied, you can’t simply revert to a previous version of the database. Also, don’t go adjusting existing scripts that have already been applied. That’s a recipe for disaster.

So if you want to undo a previous change, your only course of action is to write a new script.

In Conclusion

DbUp is easy to set up and run. It’s not a very feature-rich library, but it does one thing and it does it well, and that’s something I can appreciate.