Rails Style Database Migrations in .NET

Posted by Al Tenhundfeld on Jan 17, 2009

Sections

Architecture & Design,

Development,

Operations & Infrastructure

Topics

.NET ,

Versioning ,

Ruby ,

Ruby on Rails

Tags

C# ,

migration ,

Database Management

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:

Using the RikMigrations library, similar code can be written 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.

1. Back to top

   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.

   Reply

2. Back to top

   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/

   Reply

3. Back to top

   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.

   Reply

4. Back to top

   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"});

   Reply

5. Back to top

   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.

   Reply

6. Back to top

   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.

   Reply

• Older >