In my search for the one-command automated build, I’ve often stumbled upon how to deal with the database while writing code on a multi-developer project.
Most of us use source control, however only a small fraction of us actually store the database scripts in source control. This is a powerful tool that is often missed completely.
This is a development process I see frequently:
- get the latest version of the source from VSS
- exclusively check-out the code files you need
- make changes to the code and the database to enable your new features
- check in the code once it is all done
- do a sql compare to the dev/test databases to get a delta script
- run the compare script in the test environment and deploy the project to test.
There are a number flaws in this process, but it doesn’t stop people from continuing along this path for an entire project. Rather than point out everything that scares me from that list, I’ll focus on the database portion.
What’s wrong with just making changes and then doing a SQL compare? Well, for starters… that compare script is used once and thrown away. It is never checked into source control. If we need to restore the state of an application as of 4 months ago, we can do it with the code, but not the database. The database only stays on the current version, and there’s no going back. Another drawback is that the process is a manual one, and we want to get into automation. Lastly, SQL Compare (and SQL Delta) are both licensed tools, and not all of us can purchase them. At home I cannot justify the cost, so I am forced to come up with something else.
The Ruby on Rails community is lucky. Baked into the Rails framework is something called migrations.
Let’s say you’re on a ruby project for managing houses for sale. You get a new feature request where you want to know how many bathrooms a house has (strange request, huh?). You make the changes to model (adding a bathrooms property) but now you need the database to support it. So you type rails script/generate migration Add_House_Bathrooms. This will generate a ruby code file in the /project/db/migrations/xxx_Add_House_Bathrooms.rb file. (the xxx will be replaced with sequential ordering, so rails knows what order to execute the migrations) Inside this file are 2 methods: up and down. This is, how to I create the changes, and how do I remove the changes. Inside these methods you can write code that adds and removes the column you want.
So then when you need to deploy, rails knows what version the database is on (by having a version table), and can apply the migrations since that version to bring the database up to speed with the code. If something goes wrong, it can roll the changes back to the original version (by using the down methods).
You can even create migrations that load dummy test data, or default values for static lookup tables.
It usually only takes a little while for a cool idea to make its way into the .NET world, and migrations are no exception. The Castle Project is a collection of many RoR-esque utilities packaged into one. One of those utilities is called Migrator, which lives in the Generator project.
To generate a migration, you type:
generate migration Add_House_Bathrooms
With this, you can enable this style of migration in .NET. You create migrations, these end up as C# code files in your db/migrations folder. The same concept applies.
The migrator is fairly young, but it was extracted from CastleContrib into the main Castle trunk, so that is a good sign that it is here to stay and will have active support.
When I have more time to play with this, I’ll try and post a little demo. For now, check out the castle project at http://www.castleproject.org
What does your team do to manage database changes and enable versioning?