Database migrations with DbUp

Database migrations are a way to maintain the database schema and data changes in a database in a repeatable, automated and incremental manner.

• Repeatable – if you run the same sets of migrations in two different databases (usually in different environments – i.e. staging, production), you will get the same outcome
• Automated – migrations can be executed automatically in a CI/CD environment, without having to log in to databases manually and execute SQL scripts
• Incremental – when developing a project, it is likely that you will have to make database changes even after deploying to production. Migration tools have a way to detect which migrations are already part of the database and execute only those that aren’t

This description will use a fork of DbUp that extends its core functionality with a redeploy feature useful for managing changes in programmability objects (stored procedures, views, functions, etc). Source code can be found here https://github.com/BitLoopTech/DbUp.OnChange and its related NuGet package here https://www.nuget.org/packages/dbup.onchange-sqlserver.

While DbUp supports multiple database engines, currently DbUp.OnChange supports only SQL Server.

Try it out

1. Clone the sample project from https://github.com/BitLoopTech/DbUp.OnChange-Sample.

2. Set the connection string in appsettings.json. By default it uses LocalDb but you can use any type of SQL Server instance. The database must exist before running the project.

3. Run the project. The result should be

• new table TestTable
• new stored procedure TestStoredProcedure
• new function TestFunction
• new view TestView

4. Make any change in the stored procedure, function, or view and run the project again. The change should be applied in the database. As opposed to these programmability objects, migrations are executed only once.

Project details

Upgrader.cs

The SetupMigrations method tells DbUp that files from Scripts\Migrations folder (including subfolders) should be treated as migrations. See documentation for ScriptOptions in the repo readme here.

The name of the files inside a folder must be unique, usually by adding the current date+time or a ticket number when using a ticketing system part of a project (e.g. Migration_20200430_1130_P0001_00001.sql)

private static UpgradeEngineBuilder SetupMigrations
(
    string folderPath, 
    string connectionString
)
{
    return DeployChanges.To
            .SqlDatabase(connectionString)
            .WithScriptsFromFileSystem
            (
                folderPath,
                new FileSystemScriptOptions 
                { 
                    IncludeSubDirectories = true 
                },
                new ScriptOptions()
                {
                    FirstDeploymentAsStartingPoint = false,
                    IncludeSubDirectoryInName = true
                }
            );
}

The SetupProgrammability method tells DbUp that files from Scripts\Programmability folder (including subfolders) should be treated as redeployable scripts,  meaning that they will be re-executed on the database if their content changes. This is enabled using the RedeployOnChange flag.

In the background, this is achieved by storing a hash of the contents in the database (SchemaVersions table) and comparing that with the hash created from the script’s current content.

private static UpgradeEngineBuilder SetupProgrammability
(
     UpgradeEngineBuilder upgrader, 
     string folderPath
)
{
    return upgrader.WithScriptsFromFileSystem
    (
        folderPath,
        new FileSystemScriptOptions() 
        { 
            IncludeSubDirectories = true 
        },
        new ScriptOptions()
        {
            RedeployOnChange = true,
            FirstDeploymentAsStartingPoint = false,
            IncludeSubDirectoryInName = true
        }
    );
}

If DbUp is enabled on an existing database and you just want to ‘import’ the programmability objects without actually redeploying them (since they haven’t actually changed compared to the database version), the FirstDeploymentAsStartingPoint flag can be set to true, this way the first time DbUp runs, it will just import them into the SchemaVersions table, without actually redeploying. See more details in the repo readme here.

The following code snippet from the end of the Setup method tells DbUp to wrap scripts inside a transaction, which means that if a single script fails during an execution, all other changes will be rolled back.

return upgrader
    .WithTransaction()
    .WithVariable("EnvironmentName", $"'{_appSettings.EnvironmentName}'") // this will be available in scripts as $EnvironmentName$
    .LogToConsole()
    .Build();

It also ‘injects’ a variable EnvironmentName that can be leveraged to run environment specific scripts or to define environment specific variables.

For example if there is a functionality that exports files to a given folder, but that folder should be environment specific, it can be achieved like this:

-- Note that the variable is wrapped with the $ character
DECLARE @EnvironmentName VARCHAR(250) = $EnvironmentName$ 
DECLARE @ExportPath VARCHAR(250)

IF @EnvironmentName = 'Production'
BEGIN
    SET @ExportPath = '{{path to production folder}}'
END
ELSE IF @EnvironmentName = 'Staging'
BEGIN
    SET @ExportPath = '{{path to staging folder}}'
END

UPDATE Settings SET ExportPath = @ExportPath

DbUpTutorial.csproj

There is a post build script that copies all files from the Scripts folder to the output, without having to mark every file as Copy to output. This is useful when building the project in a CI\CD environment (e.g. Azure DevOps)

The project is configured to copy the binaries and files into the output folder without appending the framework name (i.e. netcoreapp3.1).

false

If building in Release mode, the project will be published into the Release folder instead of Release\netcoreapp3.1. This is also useful when building the project in a CI\CD environment, to have a predictable path.