Database Migration With Spring

One thing I've often found plaguing software updates during infrequent or less-than-trivial deploys is that invariably a small, but important schema change gets lost, run out of order, or skipped entirely; even when the individual deployment goes sound, ensuring the changes between development, qa, staging, and the final production environment stay in sync is bound to go wrong somewhere.

In the past I've seen (or even added myself) JIRA tickets with a mixture of 3-4 'apply this sql' type ticket comments, sprinkled with an 'apply the attached sql file', after awhile the ticket starts to look like The Land of Confusion. One solution to this is linking to a deployment notes page in the wiki which is then updated; this only lowers the risk of problems but leaves the changes localised to the "single deployment" - it's a change thats not automated, audited, logged, tested, and has possibly already been run.

Just as we keep version numbers for our software, keeping version numbers for your database schemas is a solution I've used in various previous projects (the infrastructure on those projects was already in existence when I came to the project). Along with a dedicated table to track the databases version, a migration application is used to automatically apply updates before starting the application. It's a common enough solution, but one I've not often seen employed.

Before I launched into reinventing the wheel I took a quick look at Hibernates built in schema generation/migration capabilities but quickly came to the conclusion that as it ONLY handled schema migrations, and provided no hooks for additional data manipulation it would very quickly be limiting.

Enter the NIH infection...

My needs were simple, and simple needs require simple solutions. As my project was already based on Spring, the solution that presented itself was just that - simple. The mini-migration-framework consists of a 2 interfaces, and 2 classes:

• Migration
  An interface describing an individual migration.
• MigrationManager
  An interface describing the public API of the migration manager.
• MigrationManagerImpl
  An implementation of the MigrationManager.
• MigrationComparator
  A simple comparator keep migrations in order.

An instance of MigrationManagerImpl is defined in the spring context along with a list of Migration's to run:

<bean id="dataMigrationManager" class="updates.manager.MigrationManagerImpl"
    init-method="migrateDataBase">
  <property name="jdbcTemplate" ref="jdbcTemplate"/>
  <property name="updates">
    <set>
      <bean class="updates.UpdateBuild1"/>
      <bean class="updates.UpdateBuild714"/>
    </set>
  </property>
</bean>

After (unsuccessfully) trying to find a way to automatically discover all classes that implement the Migration interface, I opted for simply declaring them in the context file.

As we want the database schema migrated before Hibernate kicks in, a dependency on the MigrationManager is declared on the LocalSessionFactoryBean:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"
  depends-on="dataMigrationManager">

Now we have everything in place to automagically process migrations whenever the application (or the spring context) is started.

But what actually happens during a migration? The UpdateBuild1 class looks like:

public class UpdateBuild1 implements Update {
  public long getVersion() {
    return 1;
  }

  public String getDescription() {
    return "Some database change";
  }

  public void migrateDatabase(MigrationManager manager) {
     manager.update("CREATE TABLE reminders (\n" +
              " reminder_id SERIAL,\n" +
              " user_id INTEGER,\n" +
              "    uid varchar(255),\n" +
              ...
              ")");
  }
}

The class is pretty straight forward: it returns a version number, description, and performs some DDL manipulation. The MigrationManager bean keeps Migration instances in order, and runs them all within a transaction, updating the database's version after each Migration is complete.

The MigrationManagerImpl bean is declared to initialize with the migrateDataBase() method which is the heart of the migration process:

public void migrateDataBase() throws IllegalAccessException, InstantiationException {
  Set sortedMigrations = new TreeSet(new MigrationComparator());
  sortedMigrations.addAll(migrations);

  // Find current version number
  long currentVersion = jdbcTemplate.queryForLong("SELECT dbversion FROM dbversion");
  long initialVersion = currentVersion;

  LOG.info("DataMigrationManager:");
  LOG.info("  * Current database version is " + currentVersion);
  LOG.info("  * Found " + sortedMigrations.size() + " migrations, looking for updates to run...");

  try {
    jdbcTemplate.update("BEGIN");
    for (Iterator iterator = sortedMigrations.iterator(); iterator.hasNext();) {
      Migration migration = (Migration) iterator.next();
      if (migration.getVersion() > currentVersion) {
        LOG.info("  * Found migration " + migration.getVersion() + ": "
          + migration.getDescription() + ", processing...");
        migration.migrateDatabase(this);
        currentVersion = migration.getVersion();
        jdbcTemplate.update("UPDATE dbversion SET dbversion = ?", new Object[]{
          new Long(currentVersion)});
      }
    }
    jdbcTemplate.update("COMMIT");
    if (currentVersion != initialVersion) {
      LOG.info("  * Database version is now: " + currentVersion);
    }
  } catch (DataAccessException e) {
    jdbcTemplate.update("ROLLBACK");
    throw e;
  }
}

If any exceptions are thrown during the migration, the transaction is rolled back and the exception passed up the chain to be handled by your application, or testing framework.

Since integrating the above migration framework with my existing Spring-aware TestNG tests (which run against a default baseline database) I see the schema migrations run one after the other every time the tests run.

If the migration contains broken SQL they're picked up before they get anywhere near a deployment ticket.

tags: migration  java  spring  deployment  database  sql  

links: digg this    del.icio.us    technorati    reddit