Source control is a great thing for a variety of reasons, among them the ability to work concurrently on a codebase without worry of overwriting each other's changes and the ability to go back to a codebase as it existed in a point in time. Subversion, and more recently git, have this problem solved. (Harper has recently been into git and has been impressed so far.) What has traditionally been overlooked, however, is how to keep the state of your database in sync with your codebase.
It's not that developers don't see the need to keep their database changes in sync with their code. (Stored procedures are nothing more than code stored on the database anyway!) The problem has been how to accomplish the task. Executing incremental DDL scripts to make every database change and saving those in a meticulous order is ornery in itself and it still doesn't give you the ability to undo one change easily...
Fortunately, several camps have started to address this issue. The Ruby community brought the world migrations, which solves the problem through bringing database structure completely under the control of the source code using their ActiveRecord ORM.
As the saying goes, imitation is the highest form of flattery...
The Ruby on Rails methodology has been ported to .NET through an open source project (http://code.google.com/p/migratordotnet/).
We use this methodology to keep our database structure in sync with our codebase during development. We've found it works well and is very little overhead to your development process. An added benefit of this methodology is the ability to port the backend database for a given application to MySQL, SQL Server or other supported relational database in a matter of minutes. (We don't like being tied to any one platform...)
For full details on how to get this setup refer to the author's instructions here (http://code.google.com/p/migratordotnet/wiki/GettingStarted).
Essentially you add a class library project to your solution and set a reference to the Migrator.Framework.dll. Whenever a change is made to your database you create a new class in this migrations project. Each of these classes only ever have 2 methods, an up and a down. Up is a sequence of code run to execute the desired change and down is the logical reversal of those changes. The Migrator.Framework assembly provides methods to translate your change intent to the database format you've selected. Here's an example of a simple class:
[Migration(20100121080900)]
public class migration20100121080900_Add_SentAt_To_Invoices : Migration
{
public override void Up()
{
Database.AddColumn("Invoices",new Column("SentAt",DbType.DateTime,ColumnProperty.NotNull));
}
public override void Down()
{
Database.RemoveColumn("Invoices","SentAt");
}
}
In general we've found this means of tying database structure to code revisions to be very useful and helps us rest easy knowing that our database structure doesn't keep any "tribal knowledge" that exists outside of our source code revisions...
Tags: