Database Change Management

This document covers my search for a solution for database change management.

Document History

Background

The IRIDA NGS Archive and Bioinformatics Platform uses JPA and Hibernate for interacting with relational databases. In development, Hibernate provides a feature for dynamically generating a database schema. Hibernate also provides a feature to import the generated schema and a file with a SQL script with data to be inserted into the database.

These Hibernate features are suitable for development, but not suitable for production purposes (see: http://pragmatastic.blogspot.ca/2010/10/managing-database-changes-in-java.html and https://community.jboss.org/wiki/HibernateFAQ-MiscellaneousFAQs#jive_content_id_Hibernate_doesnt_generate_the_database_indexes_I_want_in_the_schema). Furthermore, these features cannot be adapted in a straightforward way to provide support for upgrading production instances from one version to the next.

Requirements

The change-management tool that we adopt must have the following features:

  1. Ability to create the latest database schema on a target database (any vendor).
    1. Ability to import data from a script into the target database (initial account data, …)
  2. Ability to upgrade from an arbitrary version of the database schema to the latest version.
  3. Works without requiring installing additional software (i.e., outside of the tools we already require; maven, java) for deployment.

The change-management tool that we adopt may have the following bonus features:

  1. Ability to upgrade from an arbitrary version to an arbitrary (i.e., not the latest) version.
  2. Ability to downgrade from an arbitrary version to an arbitrary version.
  3. Works automatically with JPA and/or Hibernate annotations (i.e., generates schema changes dynamically)

Solutions

LiquiBase

Site: http://www.liquibase.org/

Seems to be fairly well-supported. The user forums have posts (as of this writing) that were written within the last week. That said, many of the forum posts are left unanswered. A company exists around this project (Datical, see: http://www.datical.com/) that offers training and support agreements, so the product is not likely to disappear suddenly.

Additional considerations:

c5-db-migration

Site: http://code.google.com/p/c5-db-migration/, https://github.com/carbonfive/db-migration

First thought: abandoned. The last revisions to the version control are from 2010. This was released by a company called CarbonFive; CarbonFive seems to be a pretty strongly Ruby-oriented company. No apparent support forums (the Google Code page appears abandoned, the GitHub page looks like it was automatically migrated).

Additional considerations:

Flyway

Site: http://flywaydb.org/

Roll-your-own

Use some variation of SchemaExport to manage upgrades to the database. This is the least desirable option because it’s the most work. It also means that we personally have to support the code that we put together.

Decision

Choose to use LiquiBase because it satisfies all of the requirements, plus satisfies some of the bonus requirements. Furthermore, LiquiBase does not appear to be completely abandoned by its developers, like c5-db-migration. If we adopted c5-db-migration, we would be in almost the same place as if we had developed the software by ourselves.

By choosing to use LiquiBase, we do not claim that we will offer support for the bonus features (like downgrading), but having the option to provide that support is appealing.

Proceed with LiquiBase.