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)

About these ads

4 Responses to Databases and Version Control

  1. hi,

    can you show us how your stored procedures look for a simple database-changing session?

    thank you!

  2. [...] are those about automatically building Visual Studio solutions and automated project releases here, here, here, and [...]

  3. [...] I mentioned in some of my previous posts, that I developed a database tool that I use for the development of other [...]

  4. [...] Databases under version control is a more difficult problem than version control for regular source code, as you not only need [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: