Ben Scheirman

These fickle bits...

Menu

.NET Database Migration Tool Roundup

A very long time ago I posted about my excitement that the .NET community was starting to see some love in terms of database migrations.

Fast forward more than a year and we have the following options for doing database migrations in .NET.

If I’ve left off one, I apologize.

That’s a crazy amount of options, and of course it beg’s the question: Which one should I use?

There are pros and cons of each, and for my specific needs I wound up choosing Migrator.NET.  I thought I’d post my thoughts on each tool here.

Rails Migrations

When I first raised the question of the lack of good migration tools for .NET a few people pointed out that there’s nothing stopping me from running the standard Rails migrations on SQL Server.  This is done through installing a gem.  However at 90% of my clients (or more) the use of Ruby is way too Alpha Geek to actually gain buy-in from the business.  This is a shame, but a current reality.  In addition, Rails migrations get really powerful when you leverage ActiveRecord classes within your migrations.  Since we aren’t using Rails at all, this isn’t possible.  Additionally, earlier versions of Rails migrations did not support foreign keys.  That’s a serious killer for a lot of folks.  I’m not sure if it has changed.

Rails Migrations look like this:

1
class AddSystemSettings < ActiveRecord::Migrationdef self.upcreate_table :system_settings do |t|t.string:namet.string:labelt.text:valuet.string:typet.integer:positionendSystemSetting.create :name => "notice", :label => "Use notice?", :value => 1enddef self.downdrop_table :system_settingsendend<br>


taken from: http://api.rubyonrails.org/classes/ActiveRecord/Migration.html

Running migrations is as easy as the command:

1
rake db:migrate 

RikMigrations

RikMigrations was the first one I tried.  They have an excellent fluent API, and allows you to build tables and columns by chaining together commands.  Unfortunately, they didn’t seem to have SQL 2005 Schema support and that is something that I require.  I could probably send them a patch, but I decided to continue my search.

1
<span style="color: blue;">using </span>RikMigrations;[assembly: <span style="color: rgb(43, 145, 175);">Migration</span>(<span style="color: blue;">typeof</span>(BlogMigration1), 1)][assembly: <span style="color: rgb(43, 145, 175);">Migration</span>(<span style="color: blue;">typeof</span>(BlogMigration2), 2)]<span style="color: blue;">namespace </span>Blog.Migrations{<span style="color: blue;">public class </span><span style="color: rgb(43, 145, 175);">BlogMigration1 </span>: <span style="color: rgb(43, 145, 175);">IMigration</span>{<span style="color: blue;"></span><span style="color: blue;">public void </span>Up(<span style="color: rgb(43, 145, 175);">DbProvider </span>db){<span style="color: rgb(43, 145, 175);">Table </span>t = db.AddTable(<span style="color: rgb(163, 21, 21);">"Blog"</span>);t.AddColumn(<span style="color: rgb(163, 21, 21);">"ID"</span>, <span style="color: blue;">typeof</span>(<span style="color: blue;">int</span>)).PrimaryKey().AutoGenerate();t.AddColumn(<span style="color: rgb(163, 21, 21);">"Name"</span>, <span style="color: blue;">typeof</span>(<span style="color: blue;">string</span>), 64);t.Save();}<span style="color: blue;"><br></span><span style="color: blue;">public void </span>Down(<span style="color: rgb(43, 145, 175);">DbProvider </span>db){ db.DropTable(<span style="color: rgb(163, 21, 21);">"Blog"</span>);}<br>}<span style="color: blue;"></span>
1
<span style="color: blue;">public class </span><span style="color: rgb(43, 145, 175);">BlogMigration2 </span>: <span style="color: rgb(43, 145, 175);">IMigration</span>{<span style="color: blue;"></span><span style="color: blue;"> public void </span>Up(<span style="color: rgb(43, 145, 175);">DbProvider </span>db) {<span style="color: rgb(43, 145, 175);"> Table </span>t = db.AlterTable(<span style="color: rgb(163, 21, 21);">"Blog"</span>); t.AddColumn(<span style="color: rgb(163, 21, 21);">"Description"</span>, <span style="color: blue;">typeof</span>(<span style="color: blue;">string</span>), <span style="color: blue;">int</span>.MaxValue); t.Save(); }<span style="color: blue;"> <br> public void </span>Down(<span style="color: rgb(43, 145, 175);">DbProvider </span>db) {<span style="color: rgb(43, 145, 175);"> Table </span>t = db.AlterTable(<span style="color: rgb(163, 21, 21);">"Blog"</span>); t.DropColumn(<span style="color: rgb(163, 21, 21);">"Description"</span>); t.Save(); }<font color="#0000ff"> </font>}}

Running this migration is done like so:

Rikmigrations-cmd-line

Question:  How did it know which migrations to run?  Answer:  They created a table to keep track of it for you…

Rik-migrations-module-versions

The cool thing about RikMigrations is you can have versions of different modules, which could really help if you have unrelated areas of the application that each need their own version.  My current project could use something like this to separate unrelated application schemas, while maintaing only a single migration process.


Tarantino

Tarantino also seemed compelling, and it’s the tool we’re using with CodeCampServer, however I think that using a fluent API in C# is a little more compelling (however you’re at the whim of the API’s feature-set, unless they let you execute raw sql also, which many do).  Tarantino works by storing scripts that are named like 0001_add_customers_table.sql and it runs them in the right sequence.  This works well if you would like to continue to leverage your GUI tools such as SQL Server Management Studio.  Then you leverage a tool such as RedGate SQL Compare to generate the change scripts.  These scripts are generally large and not very readable, however this method is quite common and effective.

Here an example of what this looks like:

Tarantino-scripts

And these files are run using a NAnt task…

Tarantino-nant-task

Running this task gives output similar to this:

Tarantino-cmd-line

You can see that it – like RikMigrations – knew that it had already exceuted Versi on 1 of the database schema, so it could safely skip the script.  This is done in a similar manner to the other tools by using a table to record the current version of the database.

SubSonic Migrations

SubSonic migrations seemed like the perfect choice for my project, since we use Subsonic already.  However I’m running a bastardized version and it’s preventing me from updating to the latest trunk.  Having two different versions of Subsonic as dependencies feels dirty and confusing to other developers.

With Subsonic Migrations you don’t have a project that contains your migrations, you just have a folder with classes.  The filename carries the weight here, not the class name or attribute.  This appeals to me and feels a bit liberating, with my rails-envy and all.  However it does feel a bit unnatural to handle migrations outside of a project in our solution.

The API in Subsonic migrations is my least favorite so far, but not by much.  All of these tools have similar API’s, it just took me longer to grok this one.

For an intro on how to use this tool, check out Rob Conery’s post on the subject.

Migrator.NET

This project has had an… interesting lifecycle.  Originally I believe it was part of the Castle Project, maintained by Macournoyer, then it drifted over to Nick Hemsley.  The project is now up at google code.  Migrator.NET was easy to get into and the API was very simple and easy to grok.

The API for Migrator.NET isn’t as fluent as RikMigrations, but it did have builtin schema support for SQL Server 2005, which I needed.  The Attribute on the class also seemed a lot cleaner than the assembly attribute in RikMigrations.

Migratordotnet-syntax

Running the migrations was easy as well with the Migrator.Console.exe command line utility.  The output is nothing new, so I’ll spare you the screenshot.

Machine Migrations

This one looks pretty good, however not as mature as some of the other tools (I think it’s the newest on the scene).  Machine Migration relies on an MSBuild task to migrate your database.  It is .NET 3.5 only and thus, not available for my project It is 2.0, my mistake!Hammet has a short review here, and subsequently patched it to provide a few additional features.

I may start using Machine Migrations on a side-project I’m working on so that I can get a better idea of the differences it has with other frameworks.

Where’s DB Pro / Data Dude / Visual Studio 2005 Team Edition for Database Professionals ?

Ahh, the tool that loves to piss me off continually is somehow missing from this list?  That’s because this tool just doesn’t facilitate a good evolutionary style of database development.  I have honestly tried to work effectively with it and it consistently adds friction to our process.  I’ve talked to a bunch of other users and they all pretty much say the same thing:  it doesn’t work like that.  And that’s really sad, because 2008 is no different.  Sure it’s better, and as far as MSFT V2 products go I think this will be the poster child for v1 suckage, but 2008 still doesn’t have migrations.  Today I saw on Gert Draper’s blog some info on the latest out of band release, which includes some awesome changes, however there is no peep of anything resembling evolutionary development with the database.

This omission tells me that the DB Pro team is only listening to one community (the large enterprise customer) and completely ignoring what’s going on in the agile camp.  Maybe Phil Haack & Scott Gu could take over once MVC ships and provide some real change in that organization.

Anyway, rant over.

Migrations are an effective tool for frictionless automation & evolutionary database development especially with multiple developers.  With all of these tools out there, it’s hard to choose which one will fit your process.  I think the best advice is to just pick a tool and spike it, see what works and what’s painful.

Comments