DbUp for Database Deployment and Versioning

As a person who has spent most of the years working in backend development, database deployments top the list of the most cumbersome tasks, if asked to create one. As applications evolve, so do their underlying databases, requiring frequent updates to schema, data, and sometimes even complex migrations. At the start of a project, or when the system is still small, traditional methods of managing database changes — manually scripting SQL commands — seem to do the job. But as events turn, this can lead to errors, inconsistencies, and deployment headaches. This brings me to tools like DbUp and SQL Server Data Tools for Visual Studio — these tools change database versioning and deployment in an amazing way, making it simpler, more reliable, and less error-prone.

For now, I'll focus on DbUp. So, what is DbUp? DbUp is a .NET library that helps you deploy changes to SQL Server databases. It tracks which SQL scripts have already been run, and runs the change scripts needed to bring your database up to date.

Potential database changes

• Add a column to an existing table (what will the default values be?)
• Split a column into two columns (how will you deal with data in the existing column?)
• Move a column from one table onto another (remember to move it, not to drop and create the column and lose the data)
• Duplicate data from a column on one table into a column on another, to reduce joins (don't just create the empty column — figure out how to get the data there)
• Rename a column (don't just create a new one and delete the old one)
• Change the type of a column (how will you convert the old data? What if some rows won't convert?)
• Change the data within a column (maybe all order numbers need to be prefixed with the customer code?)

What I want from a database deployment strategy

• Source controlled — Your database isn't in source control? You don't deserve one. Go use Excel.
• Testability — I want to be able to write an integration test that takes a backup of the old state, performs the upgrade to the current state, and verifies that the data wasn't corrupted.
• Continuous integration — I want those tests run by my build server, every time I check in. I'd like a CI build that takes a production backup, restores it, and runs and tests any upgrades nightly.
• No shared databases — Every developer should be able to have a copy of the database on their own machine. Deploying that database — with sample data — should be one click.
• Dogfooding upgrades — If one developer makes a change to the database, the next developer should be able to execute those transitions on their own database. If they had different test data, they might find bugs the first developer didn't. They shouldn't just blow away their database and start again.

Let's Do It

1. Installation of DbUp

Open your NuGet Package Manager Console and run:

Install-Package DbUp

Or search NuGet — depending on which database you're using you need dbup-core + one of dbup-sqlserver / dbup-mysql / dbup-postgresql / dbup-sqlite.

2. Setting up DbUp in your application

using DbUp;

class Program
{
    static int Main(string[] args)
    {
        string connectionString = "YourDatabaseConnectionString";
        EnsureDatabase.For.SqlDatabase(connectionString);
        // The line above checks if the database exists, or creates it

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

3. Sample database change scripts

Start with a first script named 001_CreateTestTable.sql, embedded as a resource in your project:

-- 001_CreateTestTable.sql
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TestTable')
BEGIN
CREATE TABLE [dbo].[TestTable]
(
    [Id] INT PRIMARY KEY,
    [Description] NVARCHAR(100) NOT NULL
);
END

Right-click the file → Properties, and set the Build Action to Embedded resource.

Run the program and you'll see it execute the script. Run it again, and you'll see there are no new scripts to execute. From here you can add further changes, for example:

-- 002_UpdateTestTable.sql
EXEC sp_rename 'dbo.TestTable.Description', 'Name', 'COLUMN';
-- This renames the column Description to Name

In your database you'll notice an additional table, SchemaVersions, which keeps track of all the scripts that have been executed.

4. Integrating with a CI/CD pipeline

You can automate the execution of the DbUp deployment process as part of your CI/CD pipeline, such as Azure DevOps or Jenkins — simply invoke the compiled application executable within your pipeline scripts.

Conclusion

By incorporating DbUp into your .NET application and versioning your database change scripts, you can automate the deployment process and ensure consistent database schema updates across different environments. This not only simplifies deployments but also enhances the reliability and traceability of database changes within your application ecosystem.