Automated database deployments with DbUp

in Software Engineering

In any manual process, no matter how careful people are, mistakes will be made. That's down to the people part. That's why automated deployments are so important, it helps to reduce that risk of mistakes being made.

Thankfully automated deployments of code are relatively commonplace now. Especially thanks to awesome tools such as Jenkins and Octopus. However, database deployments can sometimes be the poor relation.

I think this is often simply down to them being more infrequent that code deployments. However I think that can make them even easier to be forgotten about during a deployment.

While there are several tools and techniques available to automate this, one of my favourites is DbUp.

What is DbUp?

DbUp is a .net library that allows you to add SQL scripts and it keeps track of which ones it has already run. It's as simple as that.

Installation

The easy setup is one of my favourite things about DbUp. To put automation in place for an infrequent problem, the setup has to be easy.

All you need to do is create a new console project and add your scripts to the project as Embedded Resources. It helps to prefix your scripts with a tag that will make sure they run in the correct order, such as "Script0001_".

Next, install the NuGet package;

Install-Package DbUp

Then in Program.cs add the following code;

static int Main(string[] args)
{
    var connectionString =
        args.FirstOrDefault()
        ?? "Server=(local)\\SqlExpress; Database=MyApp; Trusted_connection=true";

    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();
#if DEBUG
        Console.ReadLine();
#endif                
        return -1;
    }

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

Debug change

I usually make a small change to the provided code though.
After the lines;

Console.WriteLine("Success!");
Console.ResetColor();

I add the following;

#if DEBUG    
      Console.ReadKey();
#endif  

Its a small change and one most people probably won't need, but I like to see the success message for myself in debug to make sure everything is working as expected and not just not failing.

Why I like this approach

The ease of setup of DbUp is a big draw but I also like the control it gives me over database changes. I like having to write the scripts myself rather than relying on diff tools. I want my database deployment to fail if my databases don't have the same schema, I don't want a tool that is clever enough just to make the changes.

Another benefit of using scripts is that I can deploy schema changes or data with a single tool. I don't need one tool for schema and another for data.