Database Migrations for .NET

One of the more difficult things to manage in software projects is often changing a database schema over time. On the projects that I work on, we don't usually have DBAs who manage the schema so it is left up to the developers to figure out. The other thing you have to manage is applying changes to the database in such a way that you don't disrupt the work of other developers on your team. We need the change to go in at the same time as the code so that Continuous Integration can work.

Migrations

While I don't know if they were invented there, migrations seem to have been popularized by Ruby on Rails. Rails is a database centric framework that implies the properties of your domain from the schema of your database. For that reason it makes sense that they came up with a very good way of These are some example migrations to give you an idea of the basics of creating a schema.

001_AddAddressTable.cs:

using Migrator.Framework;
using System.Data;
[Migration(1)]
public class AddAddressTable : Migration
{
    override public void Up()
    {
         Database.AddTable("Address",
             new Column("id", DbType.Int32, ColumnProperty.PrimaryKey),
             new Column("street", DbType.String, 50),
             new Column("city", DbType.String, 50),
             new Column("state", DbType.StringFixedLength, 2),
             new Column("postal_code", DbType.String, 10)
    }
    override public void Down()
    {
        Database.RemoveTable("Address");
    }
}

02_AddAddressColumns.cs:

using Migrator.Framework;
using System.Data;
[Migration(2)]
public class AddAddressColumns : Migration
{
    public override void Up()
    {
        Database.AddColumn("Address", new Column("street2", DbType.String, 50));
        Database.AddColumn("Address", new Column("street3", DbType.String, 50));
    }
    public override void Down()
    {
        Database.RemoveColumn("Address", "street2");
        Database.RemoveColumn("Address", "street3");
    }
}

003_AddPersonTable.cs:

using Migrator.Framework;
using System.Data;
[Migration(3)]
public class AddPersonTable : Migration
{
    public override void Up()
    {
        Database.AddTable("Person",
            new Column("id", DbType.Int32, ColumnProperty.PrimaryKey),
            new Column("first_name", DbType.String, 50),
            new Column("last_name", DbType.String, 50),
            new Column("address_id", DbType.Int32, ColumnProperty.Unsigned)
        );
        Database.AddForeignKey("FK_PERSON_ADDRESS", "Person", "address_id", "Address", "id");
    }
    public override void Down()
    {
        Database.RemoveTable("Person");
    }
}

Run Your Migrations

The best way to run your migrations will be to integrate it into your build automation tool of choice. If you are not using one, now is the time.

MigratorDotNet supports MSBuild and NAnt.

MSBuild:

<Target name="Migrate" DependsOnTargets="Build">
    <CreateProperty Value="-1"  Condition="'$(SchemaVersion)'==''">
        <Output TaskParameter="Value" PropertyName="SchemaVersion"/>
    </CreateProperty>
    <Migrate Provider="SqlServer"
            Connectionstring="Database=MyDB;Data Source=localhost;User Id=;Password=;"
            Migrations="bin/MyProject.dll"
            To="$(SchemaVersion)"/>
</Target>

NAnt:

<target name="migrate" description="Migrate the database" depends="build"></p>
<property name="version" value="-1" overwrite="false" />
  <migrate
    provider="MySql|PostgreSQL|SqlServer"
    connectionstring="Database=MyDB;Data Source=localhost;User Id=;Password=;"
    migrations="bin/MyProject.dll"
    to="${version}" />
</target>

So You Want to Migrate?

Some more documentation and example are available MigratorDotNet. Some of the changes represented are still in an experimental branch that is in the process of being merged.