SubSonic: Using Migrations

I've spent the passed few weeks polishing up some features for our 2.1 release (aka Pakala), and I've been paying particular attention to Migrations - something I promised would be ready to go a few months back. As of changeset 452 (made today, just now), Migrations are reasonably solid.

As Always, There's a Video
I like blogs with code, but singalongs are more fun. I made a quick video today (about 15 minutes) of how you can use Migrations in your project.

If you're more into the code and how they work - read on! All the code you will see below is viewable in the video.

Give Shawn Some Love
I laid the groundwork for Migrations a while ago, but didn't have the bandwidth to give it the love it deserved. Frankly I don't know if I was smart enough.  I asked Shawn Oster to help out and he took his massive swingin geek smarts and put together a pretty cool set of functionality.

So if you see Shawn at the local Denver Gamer Shop, setting up for the Monday Night D&D Showdown (LARP-fest), give him a wizardly high-five!

The Code>>>
The Migration system works by basically "sucking" out the Migration classes from your project, reading in the code, and then executing it in a virtual compiler. This may seem complex, but it's actually pretty simple and at it's core is what we do to generate the code files anyway.

To create a Migration, you have to follow our Conventional system and create a class file, something like

001_Initial.cs

... and place that class file into a folder called "Migrations" off the root of your project (you can override this directory convention - see below).

This class file must have one class (call it whatever you like), and it has to inherit from SubSonic.Migration:

using System;
using System.Collections.Generic;
using System.Text;
using SubSonic;

namespace MigrationSample.Migrations {

    public class Migration001:Migration {
    }
}

Each "Migration" consists of changing from one version to another, either up or down. The code, therefore, follows this logic and offers two methods for you to tell the DB what to look like.

For the first example (001_Init), this is version one. The Up() method therefore is transitioning the DB from version 0 to version 1, so we need to put in some code to tell it what we want it to do:

        public override void Up() {

            //Create the Records Table
            TableSchema.Table records = CreateTableWithKey("Records");
            records.AddColumn("RecordName");
            records.AddColumn("GroupID", System.Data.DbType.Int32);
            records.AddColumn("LabelID", System.Data.DbType.Int32);

            AddSubSonicStateColumns(records);

            //Create the Groups Table
            TableSchema.Table groups = CreateTableWithKey("Groups");
            groups.AddColumn("GroupName");
            AddSubSonicStateColumns(groups);

            //Link them
            CreateForeignKey(groups.GetColumn("id"), records.GetColumn("groupID"));


        }

For the Down() method, we need to reverse, exactly, everything we did with Up():

        public override void Down() {
            TableSchema.Table records = GetTable("records");
            TableSchema.Table groups = GetTable("groups");

            //drop the FK
            DropForeignKey(groups.GetColumn("id"), records.GetColumn("groupID"));
            
            DropTable("Records");
            DropTable("Groups");
        }

*Note: I'm working on the syntax to drop the FK constraint. I know it's heavy.

If you needed to alter a column, you use:

    AlterColumn("records", "RecordName", System.Data.DbType.String, 800);

You can change the column's length, type, name, and nullability if you need to. You can also remove a column from a table:

    RemoveColumn("records", "groupid");

Iterations
If your client says to you "hey great, you made a Records table - but you forgot Labels! You have labelID, where's labels!" - it's time to write another Migration, and migrate from version 1 to version 2:

Following our convention, add another class file to the Migrations folder:

002_AddLabels

Next up, add the code to Up/Down our migration:

namespace MigrationSample.Migrations {
    
    public class Migration002:Migration {
        public override void Up() {

            //add the labels table
            TableSchema.Table labels = CreateTableWithKey("Labels", "labelID");
            labels.AddColumn("LabelName");
            AddSubSonicStateColumns(labels);


            Execute("INSERT INTO Labels(labelname) VALUES('Capitol')");
            Execute("INSERT INTO Labels(labelname) VALUES('Arista')");
            Execute("INSERT INTO Labels(labelname) VALUES('Virgin')");

            TableSchema.Table records = GetTable("records");
            CreateForeignKey(labels.GetColumn("labelID"), records.GetColumn("id"));

        }

        public override void Down() {
            TableSchema.Table records = GetTable("records");
            TableSchema.Table labels = GetTable("labels");

            //drop the FK
            DropForeignKey(labels.GetColumn("labelID"), records.GetColumn("id"));
            DropTable("labels");
        }
    }
}

Yes! Migrations also allow you to add data! I know that inline script is probably not what you had in mind ;) - I'll have this worked out by the time we go final with 2.1 (allowing you to use our query tool) but for now - the ability is there.

Now, to execute...

sonic.exe /migrate

You can set this up like you do with other SubCommander commands - please see the video for how to make this happen!

Comparisons
There are other things out there (like DB projects with VS) and I go into some of this in the video (nudge nudge). This isn't a versioning tool per se - it's a development tool. If you like scripts, more power to ya. Migrations take a little getting used to, that's for sure.

Caveats
This stuff may still be a tad rough. We've tested it a lot, but whenever you talk about tweaking DB schema and code... well there's a reason there's not many Migration solutions in .NET land right now :). Please be patient and help us to get this up to par.