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:







NAnt:



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.

5 Replies to “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 *