Facilitating the spread of knowledge and innovation in professional software development



Choose your language

InfoQ Homepage News SubSonic Does Migrations

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()”);


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″);



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

//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;

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

We need your feedback

How might we improve InfoQ for you

Thank you for being an InfoQ reader.

Each year, we seek feedback from our readers to help us improve InfoQ. Would you mind spending 2 minutes to share your feedback in our short survey? Your feedback will directly help us continually evolve how we support you.

Take the Survey

Rate this Article


Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p


Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.