Tag Archives: Database

Structuring your database update scripts

I’m currently working on a project that will serve as the back-end for my adult hockey teams website (more on that to come). I’m also using this project as a test bed for some ideas I’d like to try at work for upcoming development.

One of the big problems we face with our current iteration of our product is that we need to manage the structure of the database. We have a .NET app that has a database back-end powering some web services and a web based interface to the product. The problem is we have many customers (well, I guess that’s not a problem) and they tend to be running different versions of the product, and hence different version of the database. As part of customization services we offer, we may need to make minor alterations to the database structure that later get rolled into the next major product release.

Back to the hockey info manager. I’ve been researching best practices on how to structure your development environment to help manage all these challenges, and I’ve been basing a lot of what I’m doing on Jean-Paul S. Boodhoo‘s work, as posted previously. For these database problems, I’m working with this post in particular.

Specifically, Jean-Paul advocates splitting database creation scripts into multiple templated files to handle creating the various aspects of the database. I’ve generalized this a bit more so that there are multiple phases to the database, the initial phase and the update phase.

The database scripts are setup with the following naming convention:

YYYYY.##.ZZZZZ.sql.template

Where each of the sections means the following:

  • YYYYY – The phase of the database creation this script applies to
  • ## – Within the phase, the order in which the script should be executed
  • ZZZZZ – A human friendly description of what the script addresses

The database creation scripts are divided up into three phases: Initial,
Update, and Test.

The Initial phase create the database fresh for the current major release. These
scripts deal with dropping the existing database if it exists, and creating
everything from scratch.

The Update phase moves the database from it’s initial phase to the most current
configuration. Once the product has been released for use, the Initial phase
scripts should no longer be updated, but rather the Update phase scripts should
contain all changes to the database as part of patches, hotfixes, etc.
Furthermore, scripts in the updates phase should assume that data already
exists within the system and nothing should be broken by running the upgrade
scripts. This includes customers who may have previously had their database
updated (inidicating the script is being run on a database that has been
partially updated). By combining (in order) the scripts in the update phase,
a master database update script can be generated that will take the database
from any state on or after the initial state to the most current phase.

When the product is being prepared for a major version release, a master upgrade
package is created that combines all the updates from the previous version.
This script is kept separately as part of a migration package, and all the
changes in the updates are incorporated into the initial creation scripts. At
this point the update scripts are wiped clean and the process begins again.

The Test phase does not include any changes to the structure of the database
but rather only adds data to the database that can be used in testing.

Below is the list of files currently in use in the project:

Initial.00.Database.sql.template
Initial.01.Views.sql.template
Initial.02.StoredProcedures.sql.template
Initial.03.Security.sql.template
Initial.04.Data.sql.template
Update.00.Database.sql.template
Update.01.Views.sql.template
Update.02.StoredProcedures.sql.template
Update.03.Security.sql.template
Update.04.Data.sql.template
Test.00.Data.sql.template

Obviously, I have yet to test this structure in practice, but I’ll keep the blog up to date with how well this structure ends up working.