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">
  <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.


MigratorDotNet is a continuation of code started by Marc-André Cournoyer and Nick Hemsley.

Published by

Geoff Lane

I’m Geoff Lane and I write Zorched.net as I figure things out about software development in the hopes that it can help other people facing similar situations. Also as a thanks to the larger web community for all of the information and knowledge that they have shared. I’ve been a professional software developer since 1999 working with a variety of different technologies. I’ve worked for startups in the Silicon Valley and Chicago, IL and now work as a consultant building custom applications for clients.

5 thoughts on “Database Migrations for .NET”

  1. Question – does Migrator support timestamps in the table creation? I wanted to use that to do dirty checks, but I’m not seeing an option anywhere.
    Thanks in advance…

  2. Spencer,
    It does not currently support automated timestamps or anything. You can of course create your own timestamp tables using the framework, but the framework won’t do anything special with them.

  3. Thanks. I ended up creating an extension that is called in the AfterUp method. It is SQL-specific, because that is what will be used for this system; I need to look into making it useful for the other supported systems and then proposing a patch. In case anyone else wants to see it, here is the class that handles my stamp-like columns:

    public static class MigrationHelper
    {
        /// 
        /// This SQL Server-Specific method creates a Timestamp column.
        /// 
        /// 
        /// 
         public static void AddConcurrencyChecking(ITransformationProvider database, string tableName)
        {
              string addTimestampQuery = "ALTER TABLE " + tableName + " ADD timestamp TIMESTAMP NOT NULL;";
              IDbCommand cmd = database.GetCommand();
              cmd.Connection.Open();
              database.ExecuteNonQuery(addTimestampQuery);
              cmd.Connection.Close();         
        }
         public static void AddTimeStamps(ITransformationProvider database, string tableName)
        {
                AddColumn(database, tableName, "created_at", DbType.DateTime);
                AddColumn(database, tableName, "updated_at", DbType.DateTime);
        }
         public static void AddUserStamps(ITransformationProvider database, string tableName)
        {
              AddColumn(database, tableName, "created_by", DbType.Int32);
              AddColumn(database, tableName, "updated_by", DbType.Int32);
        }
         private static void AddColumn(ITransformationProvider database, string tableName, string columnName, DbType dbType)
        {
              database.AddColumn(tableName, new Migrator.Framework.Column(columnName,dbType));
        }
    }
  4. Hello spencer, I am new to using Migrator.Framework please I would like you explain what you’re trying to achieve from each method(AddConcurrencyChecking, AddTimeStamps, AddUserStamps and AddColumn).

    Thanks for your time

Leave a Reply

Your email address will not be published. Required fields are marked *