SubSonic Does Migrations

Rob Conery from the SubSonic project recently introduced migrations, which allow .NET developers to create database schema using code. 

In case you have not heard of the SubSonic project its worth taking a look.  It provides the .NET developer with a set of tools which create your Data Access Layer (DAL) for you, either at run time or from a set of generated classes.  The project was inspired from Ruby on Rails and provides Active Record support, scaffolding, ORM and now Migrations.

From Rob's blog, migrations are:

For those who don’t know, Migrations allow you to build out your DB schema using code. It’s a Rails Thing, and if you’re not familiar with them you can watch one of my webcasts where I use them (right around the 3 minute mark), or just read up on them here.

The concept is that you version the building of your DB into code files, called “Migrations”. Using code, you can build and change your DB as needed, and keep a reference of this in your app’s codebase. Since you’re using code to build out the schema, you can do all kinds of neat things like add data, ping services, send emails, etc whenever you change around the schema. This puts the DB work into your app and out of the designer, which in some cases can be neat.

Rob gives a nice example of what creating a migration looks like in C# code:

Currently, all migrations are built off of an abstract base class called, as you might guess, SubSonic.Migration. Each migration must inherit from this base class, and must also implement two methods: Up (versions your DB upward) and Down(versions it down). Here’s an example:

    public class Migration001:Migration {

        public override void Up() {
            TableSchema.TableCollection tables = new TableSchema.TableCollection();

            TableSchema.Table t = new TableSchema.Table(“Test1″);
            t.AddColumn(“Name”, System.Data.DbType.String);
            t.AddColumn(“Description”, System.Data.DbType.String,5000);
            t.AddColumn(“DateEntered”, System.Data.DbType.DateTime,0, false, “getdate()”);
            AddSubSonicStateColumns(t);

            tables.Add(t);

            t = new TableSchema.Table(“Test2″);
            t.AddColumn(“Name”, System.Data.DbType.String);
            t.AddColumn(“Friend”, System.Data.DbType.String);
            t.AddColumn(“FriendInt”, System.Data.DbType.Int32, 0,false,“50″);
            AddSubSonicStateColumns(t);
            tables.Add(t);

            Create(tables);

        }

        public override void Down() {
            Drop(new string[] { “Test1″, “Test2″ });
        }
    }

As the developer's schema changes over time and columns need to be added, changed or removed.  SubSonic's Migrations support this as well:

You can also add/remove columns, and alter their settings (without dropping and losing data). This next bit of code shows not only how to alter data, but also why you’d use migrations in the first place - it’s a road map of how your DB has changed over time:

    public class Migration002:Migration {
        public override void Up() {
            //alter the description field - change to 1200
            TableSchema.TableColumn col = DataService.GetSchema(“Test1″, “Northwind”).GetColumn(“Description”);
            col.MaxLength = 1200;

            //use AlterColumn to run an Alter statement - we don’t want to save the schema
            //since that would overwrite what we’re trying to do
            AlterColumn(col);

            //add a new column to Products
            DataService.GetSchema(“Products”, “Northwind”).AddColumn(“MyNewColumn”,System.Data.DbType.DateTime);

            //add another one and set the default, as well as non-nullable
            DataService.GetSchema(“Products”, “Northwind”).AddColumn(“MaxInventory”, System.Data.DbType.Int32,0,false,“100″);

        }

        public override void Down() {

            //the Down() method reverses what Up() did
            //reset Description to 9000
            TableSchema.TableColumn col = DataService.GetSchema(“Test1″, “Northwind”).GetColumn(“Description”);
            col.MaxLength = 9000;
            AlterColumn(col);

            //remove MyNewColumn and MaxInventory
            RemoveColumn(“Northwind”, “Products”, “MyNewColumn”);
            RemoveColumn(“Northwind”, “Products”, “MaxInventory”);

        }
    }

Anyone familiar with Ruby on Rails migrations you know a table call schema_info which keeps track of which migrations have been applied.   SubSonic handles versioning much the same way, storing schema version information in a table called Subsonic_Schema.  Migrations are run with simple commands from the command line.

Creating a migration:

sonic.exe migration /create

Running a migration:

sonic.exe migrate

The SubSonic project bring much of what Ruby on Rails developers like to .NET.  It is well worth checking the project out.  SubSonic supports more databases than Microsoft SQL Server, feel free to use Oracle or MySQL too.