Proper strategy to version control database

First of all, DB upgrades are evil, but that blog describes a total nightmare.

The one can create a Programmer Competency Matrix based on the upgrade approach:

• Level 0: No upgrades at all. Customers are terrified and move data manually using either UI provided by an application or third-party DB management solutions (believe me, it is really possible).
• Level 1: There is a script to upgrade a DB dump. Customers feel safe, but they will fix tiny and very irritating issues for the next 1-2 years. System is working, but no changes allowed.
• Level 2: Table altering. Monstrous downtime, especially in case of an issue during upgrade. Huge problems and virtually no guarantees to get 100% safe result. Data conversion is managed by a buggy script. Customers are not happy.
• Level 3: Schema-less design: One-two hours downtime to let buggy scripts to translate the configuration in the DB (this step may damage the DB in many cases). Support guys have all coffee reserves completely exhausted.
• Level 4: Lazy transparent upgrades: Zero downtime, but still some issues are possible. Customers are almost happy, but still remember previous experience.
• Level 5: Ideal architecture, no explicit upgrade is needed. Total happiness. Customers do not know what upgrade procedure is about. Developers are productive and calm.

I will describe all technical issues, but before that let me state the following (please forgive me quite a long answer):

• nowadays development cycles are very compressed and DBs are big
• virtually any feature may introduce scheme changes and break compatibility so either we have a simple and stable upgrade procedure or we may postpone a feature
• an issues may be identified by a customer, so there is a chance to have an urgent hot-fix build with

Instead of Liquibase, you could use Flyway (http://flywaydb.org/) which allows you to write your own upgrade/downgrade SQL scripts. This provides more flexibility and also works for views and stored procedures.

Liquibase requires you to make schema changes using their own XML-based language, which might be somewhat limiting.

Keeping a version number in the database, and applying update scripts on startup, is an important part of this strategy.

Here's how startup works:

• checks DB_VERSION record in database,
• finds updates > current version; maybe by code.
• runs each applicable "update", script or programmatic actions..
• DB_VERSION is updated after each, so a failure partway thru can be re-run.

Example:

• find DB_VERSION currently = 789;
• sophisticated code, or a big long IF chain, finds updates 790 and up.
• update #790, upgrade Customer & Account tables;
• update #791, upgrade Email table;
• update #792, restructure Order table;
• database version now = 792.

There are a few caveats. This works reasonably well; people claim it should be 100% reliable, but it's not.

Issues of incomplete scripts, variations in field lengths or differences in server versions can occasionally cause scripts/SQL to pass on some databases, but fail on others.

Finding the scripts to run, can be as simple as a big single method with many IF statements. Or you could load the scripts via discovery or metadata, more elegantly. Sometimes it's useful to be able to include programmatic code, not just SQL.

public void runDatabaseUpgrades() {
    if (version < 790) {
      // upgrade Customer and Account tbls
      version = 790;
    }
    if (version < 791) {
      // upgrade Email tbl
      version = 791;
    }
}