Incremental upgrade of an embedded relational database


Several years ago I worked on a project that required incrementally upgrading an application and its embedded relational database. What follows is an overview of some best practices we used. Several open source projects, books and articles use or cite similar practices.

A full create script and incremental upgrade scripts

It is vital that you have a full create script for fresh installs, and an incremental upgrade script per application version. The create script has CREATE SQL statements mostly. The upgrade script mostly has ALTER statements, for the changes required from a version of the application to the next.

Maintain your scripts under version control

This ensures that you always know the changes that have been made, can rollback changes, and easily identify changes to create incremental update scripts.

Traceability between database scripts and application code

You should use the same label to tag the database scripts and the application code in the version control system, so you know which script versions match an application release.

Installation

The installer should upgrade the database from an older version of the application to the newest. You’ll need to have a procedure in place, a batch script for instance, that knows all application versions and can apply corresponding upgrade scripts in sequence.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s