Using FluentMigrator with nAnt

On a project I was working on a while ago, we all had local copies of the DB in versioned .sql files, and then every time we did an update we would run the sql in the DB files and hope it build the DB.
More often then not it would blow up and so I would spend the first 1/2 hour every time I worked on the project getting things to work again. Well no more! 
FluentMigrator is based on Ruby Migrations, with a fairly simple syntax. The db classes have the attribute [Migration()] that specifies the version, and inherit from the Migration class, and override the two methods Up and Down.

   1: [Migration(20110624232410)]

   2: public class AddUserTables : Migration

   3: {

   4:     public override void Up()

   5:     {

   6:         Create.Table("Users")

   7:             .WithColumn("Id").AsInt32().Identity().NotNullable()

   8:             .WithColumn("Name").AsString().NotNullable()

   9:             .WithColumn("Password").AsAnsiString().NotNullable();

  10:     }

  11:  

  12:     public override void Down()

  13:     {

  14:         Delete.Table("Users");

  15:     }

  16: }

Migrating Up and Down

The Reason for the Up and Down methods is very simple; if the version your migrating to is higher then the version of this class it executes the code in the Up method and if the version is lower then the code in the Down method executes.  Basically whatever you do in the Up is undone in the Down.

Setting The Version Number

Something else you may have noticed is in the Migration tag the version number is fairly large, to make versioning easier on a team, I’m not using consecutive numbers but the date and time(using 24 hour time) of when the I added this class, so the the example class I created was on 6/24/2011 at 23:24 10. The current version number is also stored in the DB, making it very simple to see what DB version you’re on, this can come in handy if your code is based on a specific DB version you can do a simple check to see if you have the correct DB version.

Keeping version iterations small

Just like when creating a business logic class, you should follow the single responsibility principal, and have each class do only one thing, this may mean having a lot of classes but on the other side it will make it a lot easier to keep track of what each version added, modified, or removed.

Adding stuff to the Data Base

Using the Fluent api allows you to very smoothly add tables, columns etc. in a very readable way.  If you look at the Up method in the above example you can see how easy it is to add a table with columns.  This makes it very easy to add your own extension methods to short had it even further.  Here is an example for creating an identity column and a table with time stamps:

   1: internal static class MigrationExtensions

   2: {

   3:     public static ICreateTableColumnOptionOrWithColumnSyntax WithIdColumn(this ICreateTableWithColumnSyntax tableWithColumnSyntax)

   4:     {

   5:         return tableWithColumnSyntax

   6:             .WithColumn("Id")

   7:             .AsInt32()

   8:             .NotNullable()

   9:             .PrimaryKey()

  10:             .Identity();

  11:     }

  12:  

  13:     public static ICreateTableColumnOptionOrWithColumnSyntax WithTimeStamps(this ICreateTableWithColumnSyntax tableWithColumnSyntax)

  14:     {

  15:         return tableWithColumnSyntax

  16:             .WithColumn("CreatedAt").AsDateTime().NotNullable()

  17:             .WithColumn("ModifiedAt").AsDateTime().NotNullable();

  18:     }

  19: }

But the api doesn't have everything you need so you have the option of adding in strait sql using Execute.Sql(). like this example:

   1: Execute.Sql("DELETE FROM `Users` WHERE Name = 'TestUser'");

also you can call sql files like this:

   1: Execute.Script("myscript.sql");

Adding Data

The next step is to add some data. This could be some sample data for development, a larger dataset for QA, QA data for a specific type of customer, or base data for production.  By adding profiles you can abstract away what data you want to add for specific function. This is not where you add data for things the should be constant, things like lookup tables should be handled by a versioned migration class.  Here is an example of a profile class:

   1: [Profile("Development")]

   2: public class DevelopmentProfile : Migration

   3: {

   4:     public override void Up()

   5:     {

   6:             

   7:         Insert.IntoTable("Users").Row(new {Name = "TestUser", Password="12345"});

   8:     }

   9:  

  10:     public override void Down()

  11:     {

  12:         Execute.Sql("DELETE FROM `Users` WHERE Name = 'TestUser'");

  13:     }

  14: }

Just like a Version class it inherits from the Migration class and has an up and a down method, this way when you add one profile it will remove the other profiles.

Deploying/Migrating Your Data Base

Deploying or Migrating your data can be done with a console app or with a build runner like MSBuild, nAnt, or Rake making it easy to integrate into your existing build process.  For this example we are going to use nAnt.  Here is an example nAnt build file:

   1: xml version="1.0" encoding="UTF-8" ?>

   2: <project name="FluentMigratorProof" xmlns="http://nant.sourceforge.net/release/0.90/nant.xsd" default="migrate-Production">

   3:   <loadtasks assembly="./FluentMigrator.NAnt.dll" />

   4:   <target name="Reset" description="Migrate the database to the base version">

   5:     <migrate

   6:     database="sql"

   7:     connection="Data Source=DevBOX\SQLEXPRESS;Initial Catalog=FluentMigratorProof;User Id=BuildUser;Password=xxxxxxx;"

   8:     namespace="FluentMigratorProof"

   9:     target="./FluentMigratorProof.dll"

  10:     task="rollback:all"

  11:     />

  12:   target>

  13:   <target name="migrate-Production" description="Migrate the database to the latest version">

  14:     <migrate

  15:     database="sql"

  16:     connection="Data Source=Production\SQLEXPRESS;Initial Catalog=FluentMigratorProof;User Id=BuildUser;Password=xxxxxxx;"

  17:     namespace="FluentMigratorProof"

  18:     target="./FluentMigratorProof.dll"

  19:     profile="Production"

  20:     />

  21:   target>

  22:   <target name="migrate-Development" description="Migrate the database to the latest version">

  23:     <migrate

  24:     database="sql"

  25:     connection="Data Source=DevBOX\SQLEXPRESS;Initial Catalog=FluentMigratorProof;User Id=BuildUser;Password=xxxxxx;"

  26:     namespace="FluentMigratorProof"

  27:     target="./FluentMigratorProof"

  28:     profile="Development"

  29:     />

  30:   target>

  31: project>

To use FluentMigrator with nAnt you use loadtask to specify the FluentMigrator.dll then you add a migrate task in your target.  Your migrate task will need to have the following properties:

• database – this specifies the type of DB (sql, oracle, sqllight, mysql, etc.)
• connection – this specifies the DataBase your going to connect to and with what permissions
• namespace – this is the application namespace your going to run allowing you to migrate multiple DB in the same project
• target – this is the .Dll you’re calling to migrate from
• Profile – this is what profile your loading
• task(optional) – this is how you specify what version you want to build, or if you want to roll back, etc.

Then to run your migration simply run NAnt.exe with the name of your build file something like:

   1: NAnt.exe -buildfile:ProofBuild.nant.build

A full explanation of using nAnt is a little out of scope for this blog post so if you want more info on nAnt see nAnt Help Page.

As always here is a sample project using this.