BT
x Your opinion matters! Please fill in the InfoQ Survey about your reading habits!

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
  • By abstracting the DDL into .NET methods, developers can write code to create a table once. The migration library will handle translating the .NET code into varying database platforms. There is no longer a need to keep a set of scripts for Oracle installations and a separate set of scripts for SQL Server installations.
  • Integrated versioning
  • Assuming the migration code is kept with the application code in a source control repository, the migration code will be easily branchable and taggable, ensuring a compatible database can be built for any version of the codebase.
  • Automatic upgrade and downgrade paths
  • Migrations typically have an Up method and a Down method. The Up method defines actions to modify the database, e.g., create a table, and the Down method defines actions to undo the operation, e.g., drop the table. If the migrations are versioned and labelled with the rest of the code, this means you will automically have an upgrade path to bring an older database up to current by running the gap migrations.

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.

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.

Tell us what you think

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

Email me replies to any of my messages in this thread

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.

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

Email me replies to any of my messages in this thread

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

Email me replies to any of my messages in this thread

6 Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT