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 :)