SQL Schema Upgrade Automation – How to Maintain Regular Updates

automationdatabase-developmentsql

I work on a project that uses a SQL database as a back end, and a desktop app as a front end. We do releases on a regular basis, and as part of a release, our client needs to update their SQL server.

Our business requires that we are upgradable from version 1.x to 1.y, and the way we've been doing it so far is by following a simple pattern for dozens/hundreds of tables:

  • IF NOT EXISTS (XXX) CREATE TABLE/VIEW YYY …
  • ALTER TABLE (XXX) ADD COLUMN (WWW) …
  • CREATE INDEX (ZZZ) on table XXX (if needed)
  • So on…

The problem I see with this is that the steps laid out above are all laid out in a one-for-one relationship with the table that's being created. This is fine for simpler databases that have no constraints, but becomes a pain when foreign keys/triggers/anything complex becomes involved.

I was wondering if there was a pattern out there that could help me clean this up and make modifications to my script easier. I was thinking something along the lines of following these steps for every table in the database:

  • perform cleanup from any "old" version
  • create/update all tables/columns (without constraints)
  • create/update all views based on tables
  • create/update all foreign keys / constraints
  • create/update all indexes
  • create/update all triggers
  • …

I use a tool to merge all of the SQL files in my repository into one, but the rhyme/reason to which file gets merged at what point is becoming unclear – often I just give up and hack something in that says "include file X directly before/after file Y". It's becoming unmaintainable (I can still fix problems, but I want to be able to hand off this responsibility).

It's this last annoyance that I want to be able to avoid. I want to be able to create a new table by just dropping a script with the content "CREATE TABLE X…" in a "Tables" directory and call it done.

Are there any patterns out there that can help me? I'm really looking for some sort of ammunition that I can use when I propose this to my colleagues by saying "see – there IS a solution to this cluster* and THIS is how other companies address it."

Best Answer

This is not originally my idea, but here's a manual way to do it:

  1. Create a "UpdateScripts" directory.
  2. Create a field in the database which tracks the ID# of the last-run script.
  3. Every time any change is made to the database structure, save it as a script in that directory, with a sequential number (such as 00001) in front.
  4. Every time the customer launches, run every script in order between the stored ID and the last script in the directory.
  5. Update the last script field.

There are a number of alternatives, most more automated than this, discussed here.

Related Topic