Database version control

LabelEd Database version control
15th October 2010

The problem with a database is that you can't just sync changes across. It's fine as far as columns that have been added, removed or modified, but what about renamed? The synchronisation process doesn't necessarily know the difference between a column being renamed vs one being removed and a new one added.

There are various ways to manage database changes - i'm going to discuss our strategy with LabelEd and then leave it to you to decide whether this may or may not be applicable to your own application.

The environment

Every LabelEd site uses the same code base but has it's own database which must be kept in sync with a single schema from the application. To make this slightly more tricky, there may also be different versions of the code running in different environments - development machines, QA, staging, production, etc.

Our solution

The LabelEd application has a version number associated with it from a configuration file. We also keep a persistent database which contains records for all registered environments and servers, then a link table that associates every site with each environment or server ID, which becomes an instance, each of which holds a record of the current database version number.

So any time we want to make any schema changes, we increment the version number in the main configuration and add an update script for that version. This update script makes changes such as modifying tables and/or migrating data from one location to another. These update scripts always check if a change has already been made, ie. before adding a column to a table, first check whether or not the same column is there, and every script also includes the previous version script to make sure that the database was previously up-to-date and runs older updates first if not.

The application basically performs the following steps when run:

  • Compares current application version with instance version
    • If update needed, flag instance as updating (which blocks any other attempts)
    • Run update
    • When complete, remove updating flag

The instance system ensures that no single developer or environment has any effect on another, and if during development multiple changes are made to the schema at different times, all anyone needs to do is commit their updated code alongside the updated version update script and roll-back any other instances which have already updated, which forces them to re-update and apply any new changes.

We keep an up-to-date master schema for the creation of new projects and there are a few additional measures in production such as the ability to restore from backup in the event of a failure, but touch wood we've never had any issues to date when deploying new versions of LabelEd.

Well I hope that makes sense - comments welcome @labelmedia on Twitter. :-)

Like this? Sign up to our newsletter for more!

Add your own comment

© 2015 Label Media