Archive for February, 2012
There’s an easy misconception to have that having fancy java or c# compilers makes it easy to write more robust code. In reality there’s still a lot that can fail when running the application the compiler doesn’t know about. Here’s a bunch I’ve seen, but this list could go on forever.
- Invalid cast errors at runtime
- Does your application break when your database schema changes?
- Does your application fail when incoming XML or JSON changes slightly?
- Do your reports die when a column is renamed?
- Do you fall over when someone types a number that’s too large? When it’s left blank?
- What if someone attempts to do 30% inflation for 50 years?
I guess in short, writing good software is hard. Everything wants to break.
I’ve been looking for a good way to handle our database upgrades and track their changes so that I can see what changes have happened to table structure and stored procedures, when and why, and easily deploy new releases of our applications to different environments. Here’s a bunch of things I’ve tried or heard of. I’m not sure where I will end up yet.
- Use the same environment and database for development and production.
- Use a database comparison tool to copy changes from the dev database to the production database.
- Write scripts that make your database updates, and run these manually in production. Hopefully the databases don’t diverge.
- Simultaneously try to remember to script out and commit updated stored procedure scripts in a second place that isn’t the same as the release update script.
- Use a tool that makes it easier to make small update scripts, and keep track of the updates that have been run. There’s a bunch of examples:
- South (typically used with django) http://south.aeracode.org/
- ActiveRecord migrations (typically used with rails) http://api.rubyonrails.org/classes/ActiveRecord/Migration.html
- Manatee https://github.com/robconery/Manatee
- Entity Framework Migrations
- Use the visual studio 2010 database projects. This project type saves create tables and create proc statements as individual files, and will calculate the changes to make that happen on your target database. Unfortunately moving changes made in your database first back into the database project is a manual process unless you have premium or ultimate edition of Visual Studio. Also, deploying these changes may require someone with visual studio to be able to connect to the production database.
- Use a tool that integrates with sql management studio (or replaces it entirely) that can track your changes and merge other changes.
I don’t really understand why this isn’t a solved problem yet. Perhaps the best answer is in a completely different direction: a schema-less document database where the application manages the updates to the schema as documents are loaded based on a version field on each document.