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.

Be Sociable, Share!

16 comments

  1. Great article Justin! I’ve also ended up with RikMigrations after trying a couple of the other ones out. I was going to do a little write up on that tonight but I guess I’ll just point to your post instead :)

  2. Great post Justin. I’ve been using RikMigrations since Richard Mason first created it a bit over a year ago and I couldn’t do without it.

    Unfortunately it seems that seems that Richard moved onto other things not long after he created the tool as he hasn’t committed a change since last December. Earlier this year Andy Stewart took the project over, but he seems to have moved onto to other things as well (no changes since August).

    I talked to Andy a couple months ago and he made me a contributor. I have no grand ambitions to move the project forward, but I did commit several fixes/changes to the trunk that I feel make it a solid release at this point. I HIGHLY recommend getting & building my R37 revision (Dec ’08) from the trunk rather than using the 0.2 release from the download sections (built in May ’08).

    I’ve also evaluated MigratorDotNet and SubSonic’s Migrations – nothing in the .Net world comes close to RikMigrations yet IMO. I wish there was more development going on with the project, but hey, at least it works as-is right?

  3. @Ryan Hate to steal your thunder! But I do love the tool.

    @Troy Yeah, I saw that you were a committer on the project. And I am using the trunk version. I was actually looking at adding index support to RikMigrations. Was there any features that you were looking to add?

  4. None that I can think of at the moment – Indexes are a [b]great[/b] idea though.

    While Andy doesn’t appear to be actively involved in the project at this point, he DOES respond pretty quickly to emails. I’m sure he would add you on as a contributor if you asked. I’ll email you his email address.

  5. Justin,

    I am one of the contributors for Migrator.net. Not to poop on RikiMigrations as it seems like a great tool as well, theres quite a few things there that seem to cause friction.

    1. you need to explicitly call .Save() on your migrations. This is not required in Migrator.Net

    2. There are parameters that are passed into the Up/Down methods. While seamless to the user, this is not required on Migrator as all work is done through a property on the Migration class that your migrations derive from.

    3. The attributes seem very confusing. Their very verbose. In Migrator this is the only attribute required on your Migration:
    [Migration(versionNumber)]

    That version number is a long as a user on the project requested.

    4. RikiMigrations only supports MsSql. Migrator.net supports MsSql, MySql, Oracle, PostgreSql and Sqlite. We are starting work on supporting VistaDB and perhaps Access as well.

    We also have a fluent api that is similar to RikiMigrations. We also have just started a new branch of work to improve the api of migrator to make it easier to implement more database dialects.

    Like I said, not to knock down another project but I just wanted to show more of the differences between the two projects.

    Is there a specific way you want to work with the migrations in the assemblies. Even if you don’t use Migrator I would still like to evaluate what Riki does that Migrator does not.

    Thanks!

  6. Sean,

    Even though I am a contributor to RikMigrations, I am very open to switching to another migration platform. As I said in my previous comment, MigratorDotNet was one of the frameworks I took a look when I noticed RikMigrations had stagnated, but decided against it. Here are a couple quick responses to your comment:

    1) This was removed from RikMigrations at some point, then worked its way back in. I believe it is a bug.

    2) The only parameter passed into the Up/Down method is a Schema object which you use as the entry point into RikMigrations. What this means is that you do not have to inherit from a base class, you only need to implement the IMigration interface [which requires only void Up(Schema) and void Down(Schema)].

    3) I agree that the assembly level attributes would be better served as class level attributes. The downside to this, of course, would be that the migration runner would have to iterate through ALL classes in a given assembly to find the attached migrations. The only other argument passed into Rik’s Migration attribute is a module name – one feature that I do not believe MigratorDotNet supports.

    4) Though I have not had the need to try it, RikMigrations supposedly supports SqlLite as well. There is a db provider model as well, so MySql/Oracle/PostgreSql is certainly doable if someone wanted to implement a provider for it.

    RE: Fluent API:
    Unless you have added a Fluent API that is not shown in the MigratorDotNet documentation, I do not think fluent means what you think it means.

    [b]MigratorDotNet Simple Example:[/b]
    Database.CreateTable("User",
    new Column("UserId", DBType.Int32, ColumnProperties.PrimaryKeyWithIdentity),
    new Column("Username", DBType.AnsiString, 25)
    );

    [b]RikMigrations Equivelant:[/b]

    using( var user = db.AddTable( "User" ) ){
    user.AddColumn<int>("UserID").PrimaryKey();
    user.AddColumn<string>("UserName",25);
    }

    I think the syntax thing is just a matter of preference.

    Btw, not sure if MigratorDotNet supports this, but Rik also supports data insertion via anonymous objects, like so (using our prior example):

    using( var user = db.AddTable( "User" ) ){
    user.AddColumn<int>("UserID").PrimaryKey();
    user.AddColumn<string>("UserName",25);
    user.Insert(new{ UserID = 1, UserName = "Troy"});
    user.Insert(new{ UserID = 2, UserName = "Sean"});
    }

    And adding a foreign key is as simple as:
    user.AddColumn<int>("AnotherID").References("AnotherTable","ID");

    Anyway, I really hope work continues on MigratorDotNet and that it continues to improve. I would switch in a heartbeat if it offered the things RikMigrations currently offers.

  7. Full Disclosure: I went and patched the Table class to implement IDisposable for the above comment, which is why you don’t see me calling user.Save(). It certainly did not work like that, oh, an hour ago. =)

  8. not quite as nice as rails migrations, but this will do quite nicely. thanks.

  9. Actually there was a fluent.interface branch that was just merged down into the trunk recently. So I do know what a fluent interface is, I wrote the one that is currently in Migrator.Net. The documentation hasn’t been updated yet to reflect the new fluent interface api.

    Heres how you would use it:

    _schemaBuilder
    .AddColumn("MyColumn")
    .OfType(DbType.Int32)
    .WithProperty(ColumnProperty.PrimaryKey);

    Schema builder is then passed to the existing Database property on Migration base class:

    Database.ExecuteSchemaBuilder(_schemaBuilder);

    This syntax is ok, but we are re-working it so you don’t even need to pass the constructed instructions to ExecuteSchemaBuilder. It will happen after the method is run automagically.

    The anonymous objects is actually pretty neat.

    One final thing, I don’t really see enumerating over the types in the migration assembly to find the migration attributes to be that big of a deal. I mean, you’re not really going to be running your migrations all the time. Even then I think worrying about enumerating types in your assembly to be premature optimization that is not warranted.

    Anyways, Just thought I’d add my 2 cents! Cheers!

    Sean

  10. Sean,

    Just wanted to note that the fluent api talk wasn’t meant to be a dig at you. You mentioned in your previous comment that it had a fluent api, but I wasn’t able to find examples of it in your documentation (which you’ve now explained).

    Agreed, re: enumerating the types in the assembly. I didn’t write it, so I was just guessing as to why it was done that way.

    The Fluent API you illustrate is a step in the right direction (though I’m not sure where you attach the column to a table, typo?). I would recommend updating the docs (which are also outdated on the RikMigrations side) so we can compare things apples-to-apples.

    Speaking for myself, if you were to improve the fluent API (which you mentioned you are doing) and add the data insertion mechanic of RikMigrations (or something comparable), I would be very close to abandoning RikMigrations.

  11. @Troy, I understand it wasn’t a shot. I’m sorry if I responded in a manner that was rude =)

    Myself and another person are playing around with some different fluent stuff and might actually fork Migrator into a seperate project so we can rework large portions of the code.

    Migrator works and is great but there are quite a few users of the codebase and I don’t want to break backwards compat. when I’m refactoring. I’ll post some tweets about it when i have a chance. I’m schambers if you’re on twitter. Would love to chat about migration stuff!

    Thanks for the discussion =)

  12. Evidently I was already following you on Twitter, small world. =) I guess I’ll give Justin his blog back and move the conversation over there; I’m TroyGoode on Twitter.

  13. @Sean and Troy Hey guys, you had a whole conversation while I was away! :-)

  14. Cool idea, but I think the abstraction falls apart when scaled to larger projects. My DBA would most likely have my head if I tried to use this type of migration instead of T-SQL. But again, I love abstractions and this is an intriguing concept.

  15. the download link does not work it gives a 404 IIS 7 generic error

  16. @Subnus Sorry about that, it is fixed now.

Leave a comment