Loading...
Skip to Content

Blog

Post: Using Database Project and DbUp for database management

Using Database Project and DbUp for database management

2018-11-26 Database  .NET  CI/CD  

Introduction

In previous post I described two popular ways to manage database changes.

The first one was state versioning where you keep whole current design of your database in repository and when you need change something then you need only change this state. Later, when you want to deploy changes, your schema and target database is compared and the migration script is generated.

The second way is to versioning transitions to desired state, which means creating migration script for every change.

In this post I wanted to show implementation of these two approaches in .NET environment combined together - what I think is the best way to manage database changes.

Step one - Database Project

The first thing to do is create Database Project. This type of project is available only when you have SQL Server Data Tools installed. It can be installed together with Visual Studio 2017 or separately - see this page for more information.

When you have SQL Server Data Tools you can add new Database Project the standard way:

Add project

Now we can add database objects to our project in the form of SQL scripts. Each script should define one database object - table, view, procedure, function and so on. It is common to create root folders as schemes are named.

TIP: I do not recommend creating database objects in “dbo” schema. I advise to create good named schemes per module/purpose/functionality. Creating your own schemes also allow you to better manage your object namespaces.

The sample database project may look like this:

Sample db project

What is worth to notice is the Build Action setting of every script is set to Build. This is the setting after which Visual Studio recognizes database objects from ordinary scripts and build them together. If we for example remove script defining orders schema, VS will not be able to build our project:

Schema error

This is great behavior because we have compile-time check and we can avoid more runtime errors.

When we finished database project, we can compare it to other project or database and create migration script. But as I described in previous post this is not optimal way to migrate databases. We will use DbUp library instead.

Step two - DbUp

DbUp is open source .NET library that provide you a way to deploy changes to database. Additionally, it tracks which SQL scripts have been run already, has many sql scripts providers available and other interesting features like scripts pre-processing.

You can ask a question why DbUp and not EF Migrations or Fluent Migrator? I have used all of them and I have to say that DbUp seems to me the most pure solution. I don’t like C# “wrapers” to generate SQL for me. DDL is easy language and I think we don’t need special tool for generating it.

DbUp is library so we can reference it to each application we want. What we need is simple console application which can be executed both on developer environment and CI build server. Firstly, we need reference DbUp NuGet package. Then we can add simple code to Main method:

// DbUp Migrator console application
class Program
{
    static int Main(string[] args)
    {
        if (args.Length != 2)
        {
            return ReturnError(
                "Invalid args. You have to specify connection string and scripts path");
        }

        var connectionString = args[0];
        var scriptsPath = args[1];

        Console.WriteLine("Start executing predeployment scripts...");
        string preDeploymentScriptsPath = Path.Combine(scriptsPath, "PreDeployment");
        var preDeploymentScriptsExecutor =
            DeployChanges.To
                .SqlDatabase(connectionString)
                .WithScriptsFromFileSystem(preDeploymentScriptsPath, new FileSystemScriptOptions
                {
                    IncludeSubDirectories = true
                })
                .LogToConsole()
                .JournalTo(new NullJournal())
                .Build();

        var preDeploymentUpgradeResult = preDeploymentScriptsExecutor.PerformUpgrade();

        if (!preDeploymentUpgradeResult.Successful)
        {
            return ReturnError(preDeploymentUpgradeResult.Error.ToString());
        }

        ShowSuccess();

        Console.WriteLine("Start executing migration scripts...");
        var migrationScriptsPath = Path.Combine(scriptsPath, "Migrations");
        var upgrader =
            DeployChanges.To
                .SqlDatabase(connectionString)
                .WithScriptsFromFileSystem(migrationScriptsPath, new FileSystemScriptOptions
                {
                    IncludeSubDirectories = true
                })
                .LogToConsole()
                .JournalToSqlTable("app", "MigrationsJournal")
                .Build();

        var result = upgrader.PerformUpgrade();

        if (!result.Successful)
        {
            return ReturnError(result.Error.ToString());
        }

        ShowSuccess();

        Console.WriteLine("Start executing postdeployment scripts...");
        string postdeploymentScriptsPath = Path.Combine(scriptsPath, "PostDeployment");
        var postDeploymentScriptsExecutor =
            DeployChanges.To
                .SqlDatabase(connectionString)
                .WithScriptsFromFileSystem(postdeploymentScriptsPath, new FileSystemScriptOptions
                {
                    IncludeSubDirectories = true
                })
                .LogToConsole()
                .JournalTo(new NullJournal())
                .Build();

        var postdeploymentUpgradeResult = postDeploymentScriptsExecutor.PerformUpgrade();

        if (!postdeploymentUpgradeResult.Successful)
        {
            return ReturnError(result.Error.ToString());
        }

        ShowSuccess();

        return 0;
    }

    private static void ShowSuccess()
    {
        Console.ForegroundColor = ConsoleColor.Green;
        Console.WriteLine("Success!");
        Console.ResetColor();
    }

    private static int ReturnError(string error)
    {
        Console.ForegroundColor = ConsoleColor.Red;
        Console.WriteLine(error);
        Console.ResetColor();
        return -1;
    }
}

This console application accepts two parameters: connection string to target database and file system path to scripts directory. It assumes following directory layout:

/PreDeployment
/Migrations
/PostDeployment

For “pre” and “post” deployment scripts we are defining NullJournal - in this way scripts will be run every time.

We should keep directory scripts in Database Project created earlier. DbUp executes scripts in alphabetical order. It can look like this:

Scripts directory layout

Finally, we run migrations running our console application:

Migrator app

Executed scripts are listed in app.MigrationsJournal table:

Journal

And that’s all! We can develop and change our database in effective way now. :)

Summary

In this post I described how to implement both state and transitions versioning using Database Project na DbUp library. What has been achieved is:

  • Compile-time checks (Database project)
  • Ease of development (Both)
  • History of definition of all objects (Database project)
  • Quick access to schema definition (Database project)
  • Ease of resolving conflicts (Database project)
  • IDE support (Database project)
  • Full control of defining transitions (DbUp)
  • Pre and post deployment scripts execution (DbUp)
  • Deployment automation (DbUp)
  • The possibility of manual deployment (DbUp)
  • History of applied transitions (DbUp).

Using this machinery the development of database should be definitely easier and less error-prone.

Comments

Related posts See all blog posts

Database change management
26 November 2018
Database change management is not an easy task. It is even more difficult when we are at the beginning of a project where the data model is constantly changing. In this post I will describe what we need to do to have a good database change management mechanism.
Read More