2012/11/30

Introduction

In an scenario of agile development, new versions are frequently released and deployed, and continuous changes in your database schema are frequent.

To deal with these database changes, a mechanism should be in place. In Ruby on Rails, you have it out-of-the-box and it works great. But in Java web apps, you have to find a solution and plug it in your own projects.

We will implement an automatic database update mechanism for Java web apps trying to meet the following goals:

Automatic: can be integrated for automatic updates as spring bean or as servlet listener

Our App Before Liquibase

If we use hibernate and the hibernate3-maven-plugin, during development our database schema is automatically kept up-to-date: hibernte3-maven-plugin extracts schema info from JPA annotations and hibernate configuration.

We will use a project based on the AppFuse framework, with flavours for Spring MVC, Struts2, Tapestry, JSF, but this is applicable to any java web app based on any framework.

From the AppFuse quickstart page, I copy the maven command to generate an initial Spring MVC app from AppFuse archetypes:

By inspecting the project's pom.xml, we can see the database schema is kept up-to-date during development by generating drop and create DDL commands, extracted from the JPA annotations in our model classes.

This is fine during development, because we do not need to worry about database migrations as yet. By running maven process-test-resources or maven jetty:run, we can see the tables are dropped and recreated each time we run our web app with jetty:

Managing db updates in our project

Liquibase at run-time: will automatically update the server schema as needed on deployment, including generation of the first database version for an empty schema

Liquibase at build-time

We will be evolving our app and when we have something to commit and push to our project's global repo, we can then generate the database migrations, if any.

We will add to our maven project the liquibase plugin and needed executions in order to:

generate database diff changelogs at any time when we want to consolidate our model updates

generate database production data dumps as changelogs at any time to consolidate app preloaded db data

exercise liquibase db migrations at any time for rapid testing (jetty:run)

Liquibase at runtime

The first time we deploy the app, it will contain the initial db changelog for an empty schema. Liquibase will generate all the db tables and populate with initial database data (default users, user roles, any lookup tables...). On subsequent deployments, our app will contain an additional db changelog to bring the server database schema up-to-date.

Integrating the db update at app startup

Liquibase can perform automatic db update at runtime by looking at the registered change sets in a changelog file and checking if they are applied against a table in our schema called DATABASECHANGELOG. It will create it automatically if it does not exist.

We can implement the automatic db update either with a Spring bean or with a Servlet listener.

Working on our app during development: evolving our model

During development, we will possible be making many changes to our app. We do not want to spend time for now on db migrations. Just evolve our model, annotate it with JPA and when doing rapid testing with jetty:run, generate the db from scratch.

Liquibase maintains a table with applied change sets to our database. Based on the contents of that table, on startup our app will run liquibase to apply any missing migration to our db.

Generating preloaded db data if any

Many web apps will have a set of data preloaded in the db: initial set of internal user accounts, available user roles, list of applicable taxes, ... whatever.

These can also be defined as a changesets so that Liquibase can update the database for us when running our app.

To generate data changesets, the maven liquibase plugin won't be of much help, as it does not include a goal for this. Instead, as it is also included in the plugin, we'll call directly the liquibase main java class as if we were using it from the command line. We'll do it with the exec-maven-plugin in a db-data maven profile so that we can generate the preloaded db data at any time:

In AppFuse, the profile prod feeds the database with production data instead of test data. We use this profile to regenerate the db and populate it with production data.
After this, we can use the db-data profile to generate our changelog for the initial db data.

Exercising the automatic db migration during rapid testing

Our app is now ready to perform all registered db migrations when deployed in a server. However, it would be nice too to exercise this during development when we launch a jetty:run for rapid testing of our unpackaged app.

You better erase these or you will run into trouble if you set a different schema name in your jdbc configuration file.

For a full list of the maven liquibase plugin goals and params, you can run this command:

mvn liquibase:help

It is up-to-date, as opposed to the liquibase site documentation.

Liquibase validates db change sets by comparing some attributes of the change sets present in the changelog file against those registered in the DATABASECHANGELOG table as applied change sets. It compares:

the full path filename that contains the change set

the MD5 checksum of the change sets

If any of these is different, it will try to re-apply the changeset. If you want to avoid this, you can clear the corresponding fields in the db table and liquibase will refill them with the actual values.

I also like to consolidate a set of related changesets from a changelog file in a single changeset. Instead of having many changesets, each one creating a table, creating an index, creating a referential integrity, etc, I tend to group many of these updates as a single changeset.

Liquibase autogenerates a numeric id to identify each changeset. I prefer to assign it a timestamp, as it gives more info and they still appear ordered.

2012/11/07

I have been working as developer for more than 20 years. I enjoy software development.

In these years I have worked with a variety of technologies: C, C++, ObjectStore and Poet at the beginning of my professional career. Later on, Java and the incipient servlets, Oracle, MySql. Then Struts, Hibernate, Lucene ... And lately, I am working with Java, Spring, Spring Security, Hibernate Search, Struts 2, Apache CXF, Bootstrap and jQuery, to name a few.

Same goes for Engineering practices: from cascade lifecycle to spiral to agile.