Migrations in C# using RikMigrations

For those of you who have used Ruby on Rails before, you most likely already know what migrations are, and you probably love them! They are such a simple idea and once you start using them you’ll wonder why you never thought of them. Recently I was doing a bit of work which required me to do some build automation, and (after prodding from some co-workers) I decided that we needed to do some migrations. Migrations are easier to just show you than explain, so I am going to move right into some code. First though, I am going to explain to you the tool that I chose.

I searched around a bit, and found two different tools for doing Migrations in .NET. One was called MigratorDotNet and the other was called RikMigrations. I evaluated them both, and in the end I chose RikMigrations because I liked the way that it searched an assembly for embedded migrations (instead of pointing to a folder of source files) and also allowed you to have multiple sets of Migrations in the same project by identifying them with keys. I’m assuming that this could have been accomplished in MigratorDotNet by using multiple folders, but I chose not to take that approach.

Okay, so let’s see some code. First we are going to start off by creating a blank database called “TestMigratorApp”. Next I am going to pretend that I have received a requirement for creating a user table with just two columns. One for “Id” which is going to be an auto-incrementing integer column, and another for username. Obviously our user table would be a bit more complex than this, but we don’t want to clutter the example with too much extra crap.

So we are going to first start off by referencing the RikMigrations assembly. This is the first part of the process that threw me initially, because RikMigrations is an executable that also has the libraries in it. So you have to add a reference to the executable in order to pull in the types you need. This can be a bit confusing for some, as we are not usually accustomed to adding references to exe’s, even though it is perfectly valid.

Once you have referenced the RikMigrations.exe you will need to create a class that implements the IMigration interface. This interface has two methods, one called “Up” and the other called “Down”. So, our class will initially look like this:

public class Migration1: IMigration
{
    public void Up(Schema db)
    {
    }

    public void Down(Schema db)
    {
    }
}

The idea is that in the “Up” method we will put code to create our schema, and in the “Down” method we will put code to revert the schema. So, if we wanted to create a user table, we might do this:

public class Migration1: IMigration
{
    public void Up(Schema db)
    {
        Table usersTable = db.AddTable("Users");
        usersTable.AddColumn("Id", typeof (int)).PrimaryKey().AutoGenerate();
        usersTable.AddColumn("Username", typeof (string), 50).NotNull();
        usersTable.Save();
    }

    public void Down(Schema db)
    {        
    }
}

Here we are using the “Schema” parameter to add a table to the database and then we are adding two columns and saving the table. Pretty cool, huh? We get to create most of our schema using straight C# syntax. We still have the “Down” method empty though, so what would revert our “Up” method? Well, dropping the table, duuuuuuuuuuuh.

Here is the updated version with the delete:

public class Migration1: IMigration
{
    public void Up(Schema db)
    {
        Table usersTable = db.AddTable("Users");
        usersTable.AddColumn("Id", typeof (int)).PrimaryKey().AutoGenerate();
        usersTable.AddColumn("Username", typeof (string), 50).NotNull();
        usersTable.Save();
    }

    public void Down(Schema db)
    {
        db.DropTable("Users");
    }
}

Okay, so now that we have a complete migration, how do we tell the migration utility that this is a migration that needs to be run? We have to add an attribute to the assembly. We can add an assembly attribute anywhere though, and I choose to put it in the same file as my migration:

[assembly: Migration(typeof(Migration1), 1, "Module1")]

This tells the migration tools the type of this migration, the order of the migration (more on this in a second), and the module name that this migration belongs to. Now we can throw a bit of code in our sample application that will run this migration:

DbProvider.DefaultConnectionString = @"Data Source=localhost;Initial Catalog=TestMigrationApp;Integrated Security=SSPI";
MigrationManager.UpgradeMax(typeof(Migration1).Assembly);

Here I am specifying a connection string, and then passing the assembly that “Migration1” is part of to the “UpgradeMax” method on the MigrationManager. This will tell the migration manager to check the current version of the database and run all of the migrations up to the max version! But how does it know what migration the database is currently at? It creates a table. You will find a table called “ModuleVersions” along side any other tables that you created in your database. This table holds module names and current versions so that the migration manager can easily run only the migrations needed in order to get to the latest version.

Let’s say that now we need to have a password field added to the user table that we created in our first migration:

[assembly: Migration(typeof(Migration2), 2, "Module1")]

public class Migration2 : IMigration
{
    public void Up(Schema db)
    {
        Table usersTable = db.AlterTable("Users");
        usersTable.AddColumn("Password", typeof (string), 50);            
        usersTable.Save();
    }

    public void Down(Schema db)
    {
        Table usersTable = db.AlterTable("Users");
        usersTable.DropColumn("Password");
        usersTable.Save();
    }
}

All we have to do is just alter the table. Note that the assembly attribute must be at the top of the file, outside of any namespace declarations. In the “Up” we add a column, and in the “Down” we drop the column. Quite simple. Now, if I run my app, it will modify my database by only running the second migration. But if I go in and delete everything out of my database, it will run both migrations which gets me to the latest version of my schema!

This allows you database schema to be developed in a much more iterative manner, just like your software should be! In a later post I am going to take a look at some of the more advanced features of RikMigrations and delve into a few of the missing pieces and how you can get around them. Overall though RikMigrations is a wonderful tool and one that you should consider adding to your tool belt.

Click here to download the source.