Github Repository You Should Watch: FluentMigrator

Motivation

The database is a critical part of your application. If you deploy version 2.0 of your application against version 1.0 of your database, what do you get? A broken application. And that's why your database should always be under source control right next to your application code. You deploy the app, and you deploy the database. Like peanut butter and chocolate, they are two great tastes that taste great together.

This pretty much summarizes my motivation to use FluentMigrator.

FluentMigrator?

Here is an excerpt from FluentMigrator readme:

FluentMigrator (GitHub Repository) is a migration framework for .NET much like Ruby Migrations. Migrations are a structured way to alter your database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved. Migrations solve the problem of evolving a database schema for multiple databases (for example, the developer’s local database, the test database and the production database). Database schema changes are described in classes written in C# that can be checked into version control.

I have been playing with FluentMigrator for some time and this post is a compilation of my observations about it.

Migration attribute with a unique identifier

As you saw in the leading example, you also need to define the Migration attribute with a unique identifier. Commonly, this identifier is just an incrementing value, although the attribute accepts a Int64. I prefer using a numeric date format, for example 201207181531 (year: 2012 month: 07 day: 18 time: 15:31). Migrations are ordered according to this number (in ascending order - smaller numbers execute first and then larger). It also provides a way to target a specific migration.

Auto Reversing Migrations

FluentMigrator supports so called "Auto Reversing Migrations." Auto reversing migrations are migrations that only contain an up command and FluentMigrator figures out the down command.

VersionTableMetaData class

To keep track of applied migrations FluentMigrator by default creates table "VersionInfo" with two columns: "Version" and "AppliedOn." I want to have multiple version tables per database (for example one for framework and a separate one for each module). This is possible by creating VersionTableMetaData class.

Currently the only issue I am having with FluentMigrator: I want to have separate a version table per module and I am using custom VersionTableMetaData for each module. This works fine for Sql Server but fails on Oracle and Postgresql. You will get the following exception on Postgresql:

It seems like despite the custom version table, FluentMigrator tries to create the same unique index "UC_Version" for every version table and fails.

Luckily, the patch for this already exists here on GitHub. This patch allows one to customize the name of version unique index in VersionTableMetaData class.

** Update **
It looks like the patch was already merged into current Fluent Migrator version (1.0.3.0) and now I can customize the unique index name in VersionTableMetaData class like in the following example:

You can restore to an empty database by running migration command line tool with the option "rollback:all" and you don't need 0 (zero) version. But, if you run MigrationRunner from your code 0 (zero) this version becomes handy. The only way you can return to an empty database is by the following:

Install from NuGet and issues with running migrations from your application

One area of FluentMigrator I’d like to see improvements in is running migrations from my application. This can be done, but it’s not so clear as to how to do so. In the section below I will describe how I have done this.

Install from NuGet is as simple as this:

PM> Install-Package FluentMigrator

But then again if you want to execute migrations from your code you will also need FluentMigrator.Tools

Following this command does only half of the job

PM> Install-Package FluentMigrator.Tools

It will fetch the newest version of FluentMigrator tools and put those into the packages directory but will not reference the needed FluentMigrator.Runner.dll. You will have to add this reference to your project manually.

Then you have FluentMigrator.Runner.dll assembly reference in your code running migrations from your code is simple as this: