Database versioning tools

August 26, 2015

The topic described in this article is a part of my Database Delivery Best Practices Pluralsight course

In the previous two articles, we looked at the theory behind the notion of database versioning. Today, I want to dive into practice and discuss the database versioning tools available at our disposal.

Database versioning tools classes

The tools on the market can be divided into two classes: those which follow the state-based approach and those that adhere to the migration-based principles. Check the previous post to learn more on the differences.

The tools that belong to the same class retain the same principles and ideas. If you are familiar with one of such tool, you will find it pretty easy to learn how to work with another one. Nevertheless, the functionality behind them might differ a lot, so it’s important to carefully choose one that fulfils your project’s needs the most.

State-based tools

There are two major choices in the space of the state-based versioning tools.

Visual Studio database project is shipped as part of Visual Studio. It has rich functionality which made it a default choice for many .NET developers. The only drawback is that it supports SQL Server only.

Comparison functionality in action

All database objects are stored as separate SQL files

Redgate is one of the oldest vendors on the market. The company develops a whole set of products to support state-based database versioning.

Migration-based tools

From a vendor’s perspective, a migration-based database versioning tool is much easier to implement. Perhaps, that is the reason why there is a broader range of such tools, including a lot of open source solutions.

Flyway is one of the most widely spread migration-based database versioning software. It supports multiple database management systems and is shipped with several options for the deployment execution, including direct object model API.

Unfortunately, it is aimed at the Java world primarily and doesn’t support .NET API but is still usable with plain SQL migrations.

Liquibase is another well-known solution with multiple DBMS support. It allows for defining migrations in plain SQL, as well as in XML, YAML, and JSON formats.

Fluent Migrations is one of my favorite products. As follows from its name, Fluent Migrations framework allows us to define migrations in C# code using fluent interface. Those migrations are automatically translated into SQL scripts during deployment. Powerful, strongly-typed object model in conjunction with flexible fluent-style interfaces forms a great tool.

Here’s some code to help you to grasp the idea:

[Migration(1)]
public class CreateUsersAndAddresses : Migration
{
    public override void Up()
    {
        Create.Table("Users")
            .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity()
            .WithColumn("Name").AsString(255).WithDefaultValue("(no name)");
 
        Create.Table("Address")
            .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity()
            .WithColumn("Name").AsString(255).Nullable()
            .WithColumn("UserId").AsInt32().NotNullable();
 
        Create.ForeignKey("fk_Users_Address")
            .FromTable("Address").ForeignColumn("UserId")
            .ToTable("Users").PrimaryColumn("Id");
 
        Insert.IntoTable("Users").Row(new { Name = "Test" });
    }
 
    public override void Down()
    {
        Delete.Table("Address");
        Delete.Table("Users");
    }
}

A lightweight migration-based tool

I personally prefer the use of as simple tools as possible for a particular task. For that reason, I developed my own database upgrade tool. It is extremely lightweight: it aims at .NET and SQL Server specifically and consists of only 4 classes including Program.cs:

The structure of the upgrade tool

You can find the full source code on GitHub.

The best way to use it is to copy it to your solution as a separate project. The project itself is a simple console application:

The tool in action

All you need to do is gather migration scripts in the Scripts folder. The tool uses a simple convention to determine the version of a script (first digits before an underscore sign) and employs transactional updates. It means that if any exception occurs, the entire migration is rolled back.

Summary

There are plenty of choices in the area of database versioning tools. I’m sure there are more of them on the market, and I covered only a small fraction of them. Nevertheless, in most cases, the tooling described in this article is enough for the vast majority of software projects.