Simple Database Migrations with DbUp

Keeping your database up-to-date with your code is an important task. There are many different approaches and even more tools to support you. However, they often come with a price you may not be willing to pay.

A different way goes DbUp. This little tool helps you to update your database in a simple, understandable manner. It can’t do everything, but what it can works great. Let’s have a deeper look at DbUp.

Installation

The simplest and most convenient way to use DbUp is within a command line application. That project can be added to your solution and then only cares about modifying your database. The installation itself is done by NuGet by entering this command into the Package Manager Console:

Install-Package DbUp

Setup

DbUp needs some code to configure the connection string to your database and the location of the scripts you want to run. You can take the code from the official documentation or use mine with an exactly specified connection string name:

static int Main(string[] args)
{
  var connectionString = 
      ConfigurationManager.ConnectionStrings["Blog"].ConnectionString;

  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;
}

Your DB Scripts

The next step is to create a folder called Scripts where you can put your migration scripts. You can change the name, but then you need to update the ScriptProvider as well.

The migration scripts are simple text files with SQL commands directly written to it. By using this simple approach, you can write your scripts in the SQL Server Management Studio and then copy and paste them into a migration file. Another great plus is that your database administrator could create the scripts for you, without any prerequisite knowledge for writing code.

If you start with an empty database, then your first migration may create a new table:

CREATE TABLE Book
(
    Id      INT IDENTITY(1,1),
    Title   VARCHAR(255),
    ISBN    VARCHAR(13),
    Pages   SMALLINT,
    Summary VARCHAR(MAX),
    Rating  NUMERIC(2,1),
    CONSTRAINT pk_Book_Id PRIMARY KEY (Id),
);

With the current configuration of DbUp your scripts must be embedded resources. You therefore now need to change the Build Action to Embedded Resource:

The naming of the files is up to you. If you start the name with Script* it will be automatically detected. If you use a number directly after that, then the alphabetical sort order in Visual Studio matches the order of execution in DbUp. While this is not a must have, it makes the handling of those files much simpler.

Running the Migrations

To update your database, you need to build your command line application project and execute it. DbUp will connect to the database and run your scripts:

Beginning database upgrade
Fetching list of already executed scripts.
The [SchemaVersions] table could not be found. The database is assumed to be at version 0.
Executing SQL Server script 'DBMigration.Scripts.Script0001 - Create Book Table.sql'
Creating the [SchemaVersions] table
The [SchemaVersions] table has been created
Upgrade successful
Success!

To prevent scripts from running over and over again, DbUp tracks the execution of your scripts in the table SchemaVersions. Every executed script will get an entry with the date and the script name:

On the next run DbUp will check which scripts need to be executed and only run them.

Limitations

DbUp is so simple because it only allows you to go forward with your migrations. There is no rollback to a specific version. While there are some discussions about changing that, so far you only can change the database from one state to the next.

Conclusion

DbUp is a great little tool to manage your database. Simple SQL commands added to text files in a Scripts-folder are all you need to keep your database up-to-date with your code. It’s so simple that you definitely should give it a try.