Rails has an extremely interesting idea for keeping a database up to date, no matter where it is (development, staging, production, etc.). Its called Migrations.
I’ve dabbled in this idea before – there is an open source project out there called dbdeploy.net, which is based on the java project dbdeploy. What it does is run all of your database update scripts in sequence, and theoretically allow you the capability to roll back to a previous version. I used it a part of our build process to keep our unit test database (there’s an anti-pattern we feel for) up to date. It worked fairly well, but wasn’t perfect.
What happens with Migrations is you write Ruby code that tells how you want to transform your database, and it will get handled no matter what kind of supported database you have. Its completely SQL syntax agnostic. It also is easier to create the rollback aspect, because it is just another method you have to write. And numbering is made easier because Rails takes care of numbering them for you.
The first thing I wanted to do when I saw this concept was port it to .Net. Yeah, except its been around for a long time, so of course someone beat me to it. Actually, a couple of people did.
So, I figured I’d do the next best thing and maybe write a post about how to use one of these ports. But Justin Etheredge beat me to it (long ago, in internet time). So, if you don’t know anything about Migrations or you do but you didn’t know you could use them from .Net, head on over to Justin’s post now.