Rails Style Database Migrations in .NET

by Al Tenhundfeld on Jan 17, 2009 |

Versioning database schema along with your .NET code is essential for managing volatile codebases especially when employing continuous integration. Many teams in the .NET space use handwritten scripts or schema comparison tools. Ruby on Rails accomplishes this with a popular solution of abstracting DDL SQL into Ruby commands called migrations.

The following Rails migration, written in Ruby, defines the actions for creating and dropping a Users table in a database:

Rails Migration

Using the RikMigrations library, similar code can be written in C#:

RikMigration in C#

The important concept to understand is that all of the data definition language defining the database schema has been abstracted and moved into the application code. This has several advantages:

Database platform neutrality
Integrated versioning
Automatic upgrade and downgrade paths

Migrations are still not widely used within the .NET community. Unfamiliarity with the approach accounts for much of that, but there are some valid arguments against migrations. Many .NET teams make wide use of database stored procedures. For systems using stored procedures, a versioned script approach might work better, and it is likely platform neutrality is not a concern. Also, for large applications that have databases managed by DBA's, moving DDL into .NET code may not be an option.

There are two .NET migration libraries that have growing communities: RikMigrations (code) and Migrator.NET .

RikMigrations has been the more popular library, supporting a more fluent interface and a command line interface. However, the main developer stopped contributing to it in the middle of last year. Migrator.NET is growing in popularity and maturing quickly with a new fluent interface and automation integration. Both are small open source projects that could use more support from the developer community.

Justin Etheredge, C# MVP, has a written a useful tutorial on getting started with RikMigrations, including useful pointers on configuration.

Related Editorial

Infographic: Just How Big is the Nerd Economy

eBook: The New Kingmakers - How Developers Conquered the World

Building Content-Driven Apps - Couchbase and Elasticsearch for Content and Metadata Stores

Faster PHP Through Java

ICEmobile in a Minute - The Easiest Way to Mobilize Your Enterprise Apps

Related Sponsor

Get code level visibility into your web apps. All for free.

Try New Relic Today.

Hello stranger!

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

Get the most out of the InfoQ experience.

Tell us what you think

Community comments

Code wrapping vs. scripts by Anders Sveen Posted

Re: Code wrapping vs. scripts by Francois Ward Posted

Re: Code wrapping vs. scripts by Al Tenhundfeld Posted

tarantino by J House Posted

Re: tarantino by Al Tenhundfeld Posted

Wizardby by Anton Gogolev Posted

Code wrapping vs. scripts by Anders Sveen

DBDeploy is a similar tool that enables this with pure SQL scripts. I originally like the code wrapping and the platform independence created by a framework like the ones you mention. But most of them falls short when it comes to doing migrations that need to change existing data in the database. I haven't really looked into the frameworks you mention, but we found that some of the stuff we needed to do with out data when refactoring was quite heavy, even when it comes to SQL.

See blog.f12.no/wp/2009/01/03/migrations-for-java/ for a similar article about DBDeploy and focused on Java. I think there is a .NET version of DBDeploy too.

tarantino by J House

Also worth looking at is the tarantino project - Database change management; nant task to handle database updates, it does not abstract away the sql but I have found it to be a very good tool for managing database changes
code.google.com/p/tarantino/

Re: Code wrapping vs. scripts by Francois Ward

Since its C# code, there's nothing stopping you from using a helper function to abstract a connection to the provider, and executing SQL.

When Migration was gaining popularity and there was no mature versions of it for .NET, I took it upon myself of writing one. It took about 2 days, but I took a shortcut: I used SMO, which tied it to SQL Server, with some helper methods (it predated .NET 3.5, today using extension methods would make it vastly cleaner) to duplicate most of the functionality (improved on it in a few cases too, hehe).

So for some stuff that was easier done in SQL, we just executed scripts, or inline SQL if it was minimal (though using scripts can let you have platform independance: if you have different folders for different databases...sure you need to duplicate the work, but it shouldn't happen too often).

So point is, in the end, its still C# code, and you can do everything, you're not tied to using the migration framework alone... That is actually why we went the C# route in the first place: We could easily execute SQL from C#. Executing C# from SQL is trickier unless you're already investing in CLR within your database server.

Re: Code wrapping vs. scripts by Al Tenhundfeld

I haven't heard of DBDeploy. Thanks for bringing it to my attention.

RikMigration does support changing existing data. In fact, it does so by using anonymous types, which is a pretty cool idea in my opinion. In the comments of Justin's post, one of the RikMigrations developers gives an example.

Building on my sample from above, you could issue these commands:
usersTable.Insert(new{ ID = 1, first_name = "Al"});
usersTable.Insert(new{ ID = 2, first_name = "Anders"});

Re: tarantino by Al Tenhundfeld

Cool. I think for a lot of shops that already have a collection of scripts, moving to a migration approach may be more than they want to tackle, but organizing their scripts into a framework like tarantino or DBDeploy may be very helpful.

Thanks for bringing this to my attention.

Wizardby by Anton Gogolev

octalforty Wizardby is kinda similar, but it uses a special language (call it a DSL if you like), which is much expressive than C# is for this particular purpose. It can also generate "downgrade" migrations automatically and has a clever compiler which allows for some type inference and intelligent naming of FK references/indexes.

See code.google.com/p/octalforty-wizardby/ for more on that.