Versioning databases

By  | August 2, 2011 | Filed under: Software development

I have a create script for the database. The last line in the create script sets the database version (this is stored in the system keys table).

The program has a database version constant that has the same value as the version number in the create script.

I have an update file per database version (update-1.sql, update-2.sql and so on) and the last line in each file is a statement that updates the database version. The last line in the most recent file updates theĀ  database version to the same number as the current version number in the create script.

When I want to alter the database I make the change to the create script and add statements to the most recent database update file to effect the change on existing databases. I put the statements just above the line that sets the database version. Also, I manually run the statements on the development databases.

After I release a new version I increment the database version in the program and create script and create a new update file and put the update version statement in it.

When the program runs it checks the database version and if it has the version update files then it runs them in order, starting with the number above the current database version and continuing until the number of the required database version.

There’s only about 50 lines of code in the entire application that is related to updating the database.

Leave a Reply

Your email address will not be published. Required fields are marked *