Back in June I wrote a post explaining how our developers keep their development environments up to date with database schema changes. Since then we have made some improvements to automate the process which I’d like to share with you.

Heres how it works…

In the database we create a table to keep track of the current schema version.

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

Any database schema changes are then recorded in a version controlled file called changes.tcl

    schema_update 1 {
      db_dml {alter table customer add column firstname varchar(100) not null}
    }
    schema_update 2 {
      db_dml {alter table customer add column lastname varchar(100) not null}
    }

Schema updates can now be manually applied by copy and pasting schema update statements directly into the your server's control port.

Although we prefer to automatically source changes.tcl every time our server is restarted.

schema_update

To accomplish this we have implemented a simple TCL proc that checks whether a schema update script should be applied to the current schema version.

Here’s the source code for our schema_update TCL proc, you can also find it in our Qcode TCL Library.

If the first argument matches the current schema version then the script in the second argument is executed and the current schema version incremented.