Database Migration Scripts

I recently added database migration functionality to my Sleet SMTP project. This means that, if I release a new version of the application that includes a change to the database schema, the existing databases of deployed applications will be migrated to the new schema automatically. Before, you would have had to wipe the database completely or apply the schema changes manually, so this is a big improvement.

The way it works is as follows. I created a table in the database whose sole purpose is to store the schema version of the database. This is just an integer that starts at "1" and increments every time the schema changes. The source code also contains a version number, which is the schema version that the source code is programmed to use. When Sleet starts up, it compares the version number in the database with the version number in the source code to determine if the schema is out of date.

If the schema is out of date, it runs a series of migration scripts. Each migration script contains the SQL code necessary to migrate the database from one version to the next. For example, if the latest database schema version is "4", then the application will contain three migration scripts: 1-to-2, 2-to-3, and 3-to-4. By chaining these scripts together, the database schema can be updated no matter what version it currently is. For example, if the schema version of my database is "2", it will first execute the 2-to-3 migration script and then execute the 3-to-4 migration script. If it's "3", then it will just execute the 3-to-4 script. If it's "1", then it will execute all of them. All of this is done within a database transaction, so if something goes wrong during the migration process, the database will be restored to its previous state.

The psuedo-code below shows how this is done in code.

//connect to the database
Connection db = ...
db.setAutoCommit(false);

int schemaVersion = 4;
int curSchemaVersion = //"SELECT db_schema_version FROM sleet"
if (curSchemaVersion < schemaVersion) {
  //schema is outdated, run the migration script(s)
  Statment statement = db.createStatement();
  while (curSchemaVersion < schemaVersion) {
    String script = "migrate-" + curVersion + "-" + (curVersion + 1) + ".sql";
    SQLStatementReader in = new SQLStatementReader(new InputStreamReader(getClass().getResourceAsStream(script)));
    String sql;
    while ((sql = in.readStatement()) != null) {
      statement.execute(sql);
    }
    curSchemaVersion++;
  }

  //update the version number in the database
  //"UPDATE sleet SET db_schema_version = [schemaVersion]"

  //commit the transaction
  db.commit();
}