Code-based Database Migrations with DbUp

In today’s post, we are going to check out the code-based migration feature of DbUp which allows code to run as part of the migration process instead of just SQL based scripts. The ability to run code as part of the migration provides a ton of flexibility in the migration process. This builds on last week’s post, Database Migrations with DbUp, make sure and check it out if you are new to DbUp.

Script Provider Change

In our example application from last week’s post in the Program class when setting up our upgrader we used the following setup.

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

The above only picks up embedded SQL type files. In order to also pick up code-based migrations we have to change from the WithScriptsEmbeddedInAssembly script provider to WithScriptsAndCodeEmbeddedInAssembly. The following is the upgrader with the new script provider highlighted.

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

Code-based Migrations

To add a code-based migration add a new class and set it to implement DbUp’s IScript interface. The script provider we changed to above will pick up any non-abstract class that implements IScript as well as SQL script files. The results are combined and sorted by name to determine execution order so make sure whatever naming convention you pick will sort properly for both your SQL scripts as well as code.

The IScript interface defines a single function, ProvideScript, that has a single parameter that provides a function to create a database command and returns a string. Depending on the need you can return a string with the SQL to be executed or you can create a command and execute any commands you need directly on the database or some combination of both if needed.

To test out code-based migrations, I added the following new class in the Scripts folder. It doesn’t actually change any data but instead logs some information to the console. Even without changing data it still conveys how code-based migrations work.

using System;
using System.Data;
using DbUp.Engine;

namespace DbupTest.Scripts
{
    public class Code0002Test : IScript
    {
        public string ProvideScript(Func<IDbCommand> dbCommandFactory)
        {
            using (var cmd = dbCommandFactory())
            {
                cmd.CommandText = "SELECT DeliveryMethodName FROM Application.DeliveryMethods";

                using (var dr = cmd.ExecuteReader())
                {
                    Console.WriteLine("  Delivery Methods");

                    while (dr.Read())
                    {
                        Console.WriteLine($"    {dr["DeliveryMethodName"]}");
                    }
                }
            }

            return string.Empty;
        }
    }
}

The following is the console output from running the application.

Beginning database upgrade
Checking whether journal table exists..
Fetching list of already executed scripts.
  Delivery Methods
    Air Freight
    Chilled Van
    Courier
    Customer Collect
    Customer Courier to Collect
    Delivery Van
    Post
    Refrigerated Air Freight
    Refrigerated Road Freight
    Road Freight
Executing Database Server script 'DbupTest.Scripts.Code0002Test.cs'
Checking whether journal table exists..
Upgrade successful
Success!

Wrapping Up

The ability to run code as part of the database migration process is an awesome set of functionality. I would advise sticking with SQL based migrations as default and only using code when SQL doesn’t provide a way to do what is needed or doing the migration in code is much more understandable. Make sure and check out the official DbUp docs on this feature for more information.

---

Also published on Medium.