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.