In software development where the system persists data in a relational database, it is important to keep track of changes to the versions of the schema.
The importance comes from that you always have several database instances to keep track of. There is the production database, the database for system tests, the database for acceptance test, the database for performance test, the database for development team and the database each developer has.
All these will be at different versions and aligned with different versions of the code.
In this blog I will describe how you come about to control your schema versions. I have tried it in two completly different settings.
What you do is to create a table called, say, “version”. Here you store all versions that have affected the database schema and preloaded control data. Thus, when in doubt you can always check the table to see which version a particular database is in.
You may use any notation and number of columns to describe your versions, but each must be unique and guranteed through a unique index.
Furthermore, all changes to the schema are described in SQL script files. For every version or change, there is one file. You can never change the file once it has come to use. There must be no doubt how the file relates to the contents of the version table.
The file should start with a begin and end with a commit to make running of the script atomic. The first line should be an insert into the version table. Since the lines are unique, you are guarded against applying the same SQL script twice.
insert into version values (‘0,1’, 2);
The files are named after the version. You should use numbers with leading zeroes padded so they line up nicely in correct order when you list them.
Order matters, since some changes depend on earlier ones.
By this simple technique you get a database where you know which version of the schema it has and you are never afraid of running the database script too many times.
And, besides, at Spa Franchorchamps, keep your foot down through Eau Rouge… 😉