Database versioning tools

By Vladimir Khorikov

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.

Database versioning tools: Comparison functionality in action

Comparison functionality in action

Database versioning tools: All database objects are stored as separate SQL files

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:

Database versioning tools: The structure of the upgrade tool

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:

Database versioning tools: The tool in action

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.

Other articles in the series

LinkedInRedditTumblrBufferPocketShare




  • Anders Baumann

    Hi Vladimir.
    Like you I also prefer the simplest tool possible and I think that your database upgrade tool looks fine.

    I am starting a new project with Entity Framework and I am considering using either the built-in code first migrations or something like your tool? As far as I can tell the code first migration tool has become quite robust. Any thoughts on “Entity framework code first migrations”?

    Thanks,
    Anders

    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      Hi Anders, I didn’t work with the EF migration tool myself, so I can’t advise here, unfortunately. But from what I heard – it might be indeed a solid solution, especially when choosing EF as the primary ORM.

      So I think it’s worth to give it a try. There’s one concern I would investigate beforehand, though, – how well it deals with data migrations. Some of the tools I used did a great job migrating the database schema but didn’t work so well with data in it. It might become an important concern after your application goes to production.

      • Anders Baumann

        Hi Vladimir.
        Thanks. That is a very good point. I just investigated this and it looks like it is possible to execute custom SQL inside the migrations. Both inline and from a file. I think that should be good enough for also doing good data migrations.

  • Richard Brinkman

    I’m in a similar situation where I work on a live website and several development branches in which I need to change the database schema.

    I solved it by writing a post-checkout and a post-merge hook that can be used nicely with git. I store all my migrations in the form of SQL files in a separate directory and commit them alongside the changed PHP code. Each time I perform a

    git checkout

    or a

    git merge

    git will automatically call the appropriate up- and down-migrations. See my implementation on [Github](https://github.com/richardbrinkman/git-migration-hook).

  • MacGyver
    • http://enterprisecraftsmanship.com/ Vladimir Khorikov

      That’s a very good light-weight tool too!