Databases and Version Control

When you are working on a software project, a Version Control System is typically used to keep track of the source code files and their changes.

But what do you do with the database related to the project?

It would be necessary to script the database schema, the changes since the previous version, as well as all the magic content that your application relies on.

Jeff Atwood over at Coding Horror (blogrolled right now) reminds us to get the database under version control, too. This post deals with versioning database change scripts. Here is a strategy to write idempotent change scripts to update your database from one version to the next.

Personally, I prefer the following strategy, using tools to

• generate the database version completely
• generate a change script from one version to the next
• generate stored procedures for
  1. generating C# code to reflect the database model
  2. generating C# code to check whether the database model is the one expected
  3. generating C# code to map all “magic” database values to C# constants
  4. generating T-SQL code to insert/update the “magic” values into production

And yes, that’s no typo: the tool generates stored procedures which in turn generate code.

Keep in mind: The more steps you can automate, the less likely errors will occur during migrations or upgrades (with the assumption that the tool works correctly :d)