Managing database schemas can be a challenging problem. It is also an area where there is no one size fits all solution (not sure that is ever really true for anything). Solutions range from manual script management, database vendor-specific options (such as DACPAC for Microsoft SQL Server), to migrations. This post will be looking at one of the options for a migration based approach using DbUp.

Sample Database

I needed a sample database to start off with so I dug up one of my posts for Getting a Sample SQL Server Database to guide me through getting Microsoft’s WideWorldImporters sample database downloaded and restored.

DbUp Console Application

There are quite a few ways DbUp can be used, for this example, we are going to be using it from a .NET Core Console application. From a terminal use the following command to create a new console application in the directory you want the application in.

dotnet new console

Now we can use the following command to add the DbUp NuGet package to the sample project. In this case, we are using the SQL Server package, but there are packages for quite a few database providers so install the one that is appropriate for you.

dotnet add package dbup-sqlserver

Next, open the project in Visual Studio (or any editor but part of how this example is setup is easier in Visual Studio). In the Program class replace all the code with the following. We will look at a couple big of this code that below.

using System;
using System.Linq;
using System.Reflection;
using DbUp;

namespace DbupTest
{
    class Program
    {
        static int Main(string[] args)
        {
            var connectionString =
                args.FirstOrDefault()
                ?? "Server=localhost; Database=WideWorldImporters; 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;
        }
    }
}

The bit below is trying to pull the connection string for SQL Server out of the first argument passed from the terminal to the application and if no arguments were passed in then it falls back to a hardcoded value. This works great for our sample, but I would advise against the fallback value for production use. It is always a bad day when you think your migrations have run successfully but it was on the wrong database because of a fall back value.

var connectionString = 
    args.FirstOrDefault() 
    ?? "Server=localhost; Database=WideWorldImporters; Trusted_connection=true";

This next section is where all the setup happens for which database to deploy to, where to find the scripts to run, and where to log. There are a lot of options provided by DbUp in this area and I recommend checking out the docs under the More Info section for the details.

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

Finally, the following is where the scripts are actually executed against the database.

var result = upgrader.PerformUpgrade();

The rest of the function is dealing with displaying to the console the results of the scripts running.

#database #dbup #database-migration