Github Repository You Should Watch: FluentMigrator

Motivation

Jeff Atwood in his blog post Get Your Database Under Version Control wrote the following:

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.

Quick facts

Currently supported databases:

Sql Server 2000

Sql Server 2005

Sql Server 2008

Postgres

MySql

Oracle 11g

Sqlite

Currently supported runners in FluentMigrator:

Command Line Runner

NAnt Runner

MSBuild Runner

Rake Runner

Show me some code

Here follows short example of create table migration class: using FluentMigrator;

namespace Examples.Module.Blog.Migrations
{
    \[Migration(201207181531)\]
    public class M00001\_CreatePosts : Migration
    {
           private const string \_schema = "blog";
           private const string \_table = "posts";

           public override void Up()
           {
                   Create.Schema(\_schema);   

                   IfDatabase("SqlServer", "Postgres")
                       .Create.Table(\_table).InSchema(\_schema)
.WithColumn("Id").AsInt32().PrimaryKey("PK\_posts").Identity();

                     Alter.Table(\_table).InSchema(\_schema)
                            .AddColumn("Created").AsDateTime()
                            .AddColumn("Updated").AsDateTime()
                            .AddColumn("Deleted").AsDateTime().Nullable()
                            .AddColumn("Title").AsString()
                            .AddColumn("Content").AsString(int.MaxValue);
           }

           public override void Down()
           {
                   Delete.Table(\_table).InSchema(\_schema);
                   Delete.Schema(\_schema);
           }
     }
}

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.

Here is a short example of such a migration class:

using FluentMigrator;

namespace Examples.Module.Blog.Migrations
{
    \[Migration(201207181531)\]
    public class M00001\_CreatePosts : AutoReversingMigration
    {
            private const string \_schema = "blog";
            private const string \_table = "posts";

            public override void Up()
            {
                    Create.Schema(\_schema);   

                   IfDatabase("SqlServer", "Postgres")
                         .Create.Table(\_table).InSchema(\_schema)
                         .WithColumn("Id").AsInt32()
                         .PrimaryKey("PK\_posts").Identity();
                         .AddColumn("Created").AsDateTime()
                         .AddColumn("Updated").AsDateTime()
                         .AddColumn("Deleted").AsDateTime().Nullable()
                         .AddColumn("Title").AsString()
                         .AddColumn("Content").AsString(int.MaxValue);
           }
     }
}

Currently supported expressions for auto reversing are:

  • Create.Table

  • Create.Column

  • Create.Index

  • Create.ForeignKey

  • Create.Schema

  • Rename.Table

  • Rename.Column

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.

Here is a short example of such a migration class:

using FluentMigrator.VersionTableInfo;

namespace Examples.Module.Blog.Migrations
{
    \[VersionTableMetaData\]
    public class BlogVersionTableMetaData : IVersionTableMetaData
    {
           public string SchemaName
           {
                   get { return string.Empty; }
           }
       public string TableName
           {
                   get { return "blog\_version\_info"; }
           }

           public string ColumnName
           {
                   get { return "blog\_version"; }
           }
    }
}

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:

An error occurred executing the following sql:

CREATE UNIQUE INDEX "UC_Version" ON "public"."blog_version_info" ("blog_version" ASC)

The error was ERROR: 42P07: relation "UC_Version" already exists

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:

using FluentMigrator.VersionTableInfo;

namespace Examples.Module.Blog.Migrations
  {
    \[VersionTableMetaData\]
    public class BlogVersionTableMetaData : IVersionTableMetaData
    {
          public string SchemaName
      {
              get { return string.Empty; }            
          }
      public string TableName
          {                    
              get { return "blog\_version\_info"; }            
          }
      public string ColumnName
          {                    
              get { return "blog\_version"; }            
          }
            
          public string UniqueIndexName            
          {                    
             get { return "uc\_blog\_verion";     }            
          }     
      } 
   }

Zero version trick

I always create the first migration class as an empty one. This trick is handy when you want to revert all database changes (back to an empty database if you wish).

Here is a code example:

using FluentMigrator;

namespace Examples.Module.Blog.Migrations
{
  \[Migration(0)\]
  public class M00000\_EmptyDatabase : Migration
  {
     public override void Up()
     {
     }
    public override void Down()
     {            
     }     
  } 
}

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:

var runner = new MigrationRunner(assembly, migrationContext, processor); runner.RollbackToVersion(0);

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:

using System.Reflection;
using FluentMigrator;
using FluentMigrator.Runner;
using FluentMigrator.Runner.Announcers;
using FluentMigrator.Runner.Initialization;
using FluentMigrator.Runner.Processors;

namespace FluentMigrationsSample
{
  class Program
  {
      static void Main(string\[\] args)
      {
         var announcer = new TextWriterAnnouncer(s => 
System.Console.WriteLine(s));
     var assembly = 
Assembly.GetAssembly(typeof(Example.Module.Blog.Migrations.M00000\_EmptyDatabase));

       var migrationContext = new RunnerContext(announcer)
         {
       Namespace = "Example.Module.Blog.Migrations"
         };
     IMigrationProcessorOptions options = new ProcessorOptions
{
PreviewOnly = false,
Timeout = 60
};
     var factory = new 
FluentMigrator.Runner.Processors.SqlServer.SqlServer2008ProcessorFactory();
     var processor = factory.Create("Data Source=(local);Initial
Catalog=BetterCMSDev;Integrated Security=SSPI;", announcer, options);
     
     var runner = new MigrationRunner(assembly, migrationContext, processor);
          runner.MigrateUp();
     }
  }
}

Where do I go from here?

I recommend you to read wiki pages of FluentMigrator github project. Thank you for your time and happy migrations :)