Managing databases is difficult - it’s difficult because the changes to them are different than software changes; for example, if I have a method, and I want to change the name of the method, when I deploy that, the new method takes the place of the old. With a database, if you rename a column, the deploy may fail if the column doesn’t have the name that you expect.

There’s essentially two ways to deal with this problem. The first is the target state based approach that tools like SqlPackage uses - you tell the tool what you want the DB to look like, and it looks at it now, and then generates a script to get from here to there. I’ve found this to be a very nice approach in the past: however, it does mean that an automated tool is responsible for generating this code.

The second is what we’re discussing in this post: it’s the approach of maintaining a kind of master script. Typically this script must be idempotent (that is, you can run it twice and it will not have any adverse effects). The idea here being that, when you want to add a table, you add a line to the script that checks if the table exists, and if not, you add it. There’s only one golden rule here: you can never go back - if you’ve added a table and want to delete it, you must do the check and add the table, then do the check and delete the table. You don’t need any specific technology for this: after all, it’s just a sql script. However, there are tools available, and in this, I’m talking about DbUp.

What does DbUp do?

DbUp allows you to spread your SQL script, that we’ve mentioned, over many files; and it will track which ones you have run (in the target database). It also provides some tools to run the script.

Getting Started - A Basic Application

In this example, we’re dealing with MySql (although DbUp does support most of the relational databases).

Step One - Create a Console Application

The first step is to create a console application. Once you’ve done so, create a directory called Scripts (this will be where your scripts will go). Finally, you’ll need the following packages:

    <PackageReference Include="dbup-core" Version="4.5.0" />
    <PackageReference Include="dbup-mysql" Version="4.5.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="5.0.0" />
    <PackageReference Include="microsoft.Extensions.Configuration.Binder" Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="5.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="5.0.0" />

See here if you’re interested in the configuration packages. I won’t go over that again here, but you’ll need an appsettings.json with the following:

{
  "ConnectionStrings": {
    "Default": "Server=localhost;Port=3309;Database=TestDb;Uid=user;Pwd=pass;"
  }
}

Code

Now you have the basic console application, you’ll need some code - this is a slightly modified version of the code in the Getting Started link above.

        static int Main(string[] args)
        {
            IConfiguration configuration = new ConfigurationBuilder()
               .AddJsonFile("appsettings.json", true, true)
               .Build();

            string connectionString =
                args.FirstOrDefault()
                ?? configuration.GetValue<string>("ConnectionStrings:Default");

            EnsureDatabase.For.MySqlDatabase(connectionString);

            var upgrader =
                DeployChanges.To
                    .MySqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), a => {
                        // You can filter scripts here
                        if (eachscript.StartsWith('--')) return false;
                        return true
                    })
                    .LogToConsole()
                    .LogScriptOutput()
                    .Build();

            var scripts = upgrader.GetScriptsToExecute();
            foreach (var script in scripts)
            {
                Console.WriteLine(script.Name);
                Console.WriteLine(script.Contents);
            }

#if DEBUG
            Console.WriteLine("Apply changes? (Y/N)");
            var response = Console.ReadKey();
            if (response.Key != ConsoleKey.Y) return -1;
#endif

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

The method above simply scans the Scripts folder for anything to run. It ignores files with a double dash (—). If you’re running it locally, it will ask for confirmation, otherwise it will simply apply the changes.

Scripts

The next step is to add your migration scripts. This is almost just a matter of dragging them into the Scripts folder; with two exceptions:

1. The scripts must be idempotent; for example:

CREATE TABLE IF NOT EXISTS \`customer\` (

2. The scripts must be flagged as an Embedded Resource:

The Journal Table

When you run this, you’ll get a line that says:

Checking whether journal table exists..

The Journal table is actually schemaversions and it holds the data about the migrations that have run. You could, for example, interrogate it:

select \* 
from schemaversions