How do you manage database schema changes in your development environments ?

Originally we opted to record sql statements that modify the database schema in a version controlled file called changes.sql.

When changes were committed to changes.sql each developer would bring their development database up to date by cutting and pasting the sql statements into psql.

The initial assumption was that commits to changes.sql would be cumulative schema changes which could be applied sequentially.

Unfortunately the most recent commits to changes.sql were often in a state of flux as code was reviewed and tested.

As a result keeping track of changes made by other developers became quite tricky.

Another issue we encountered with this approach was in bringing our development databases back up to date after a long period of neglect (after a few weeks holiday).

Developers would have to manually inspect their development environment to determine the set of changes they needed to apply.

To solve this issue we now implement the following…

The Solution

To help us determine the state of our databases in respect to commits in changes.sql we created a new table in the database called schema.

create table schema (
    version int primary key
insert into schema (version) values (1);

When commits to changes.sql are stable and ready to be rolled out to the production environment our release engineer adds the following version comment and update statement into changes.sql.

-- version 2
update schema set version=2;

Each developer can then perform the following procedure to bring their own development databases up to date:

  1. Check the current schema version of their database.
  2. Locate the matching version comment in changes.sql
  3. If this is the last version comment in changes.sql The database is already up to date Else Run the sql between this version comment up to and including the last version comment in changes.sql

Much Simpler !