A typical pain point in continuous delivery is the database schema and contents. The database schema changes over time and can not be deployed like bundles. Sometimes even the database contents have to be adapted when the schema changes. In a modern deployment pipeline we of course also want to automate this part of the deployment. This tutorial builds upon the db tutorial and shows how to leverage liquibase to automatically keep the db schema up to date with the java code. We assume familiarity with how to create DataSources using pax-jdbc-config and declarative services.
The full code of this example can be found at github in repo Karaf-Tutorial liquibase.
Declare schema using liquibase changesets
Liquibase manages a database schema over time using changesets. A changeset is created at least for every release that needs to change the schema.
In our example the first changeset creates a simple table and populates it with a record.
The changeset can be stored in different places. For me the schema is closely related to the application code. So it makes sense to store it inside a bundle. In the example the changesets can be found in migrator/src/main/resources/db/changesets.xml.
Applying the changeset
Liquibase provides many ways to apply the schema. It can be done programmatically, as a servlet filter, from spring or from maven. In many cases it makes sense to apply the schema changes before the application starts. So when the user code starts it knows that the schema is in the correct state. In case the application has no db admin rights liquibase can also create a SQL script tailored to the database that an administrator can apply. While this is necessary in some settings it breaks the idea of fully unattended deployments. In our example we want to apply the schema to the DataSource that is given to our application and we want to make sure that no user code can work on the DataSource before the schema is updated. We create the DataSource from an OSGi config using pax-jdbc-config. Luckily pax-jdbc-config 1.1.0 now supports a feature called PreHook. This allows to define code that runs on the DataSource before it is published as a service.
Using PreHook to apply the database changes
To register a PreHook we implement the PreHook interface and publish our implementation as an OSGi service. We also give it a name using the service property "name".
Our PreHook to do the Liquibase schema update looks like this:
By itself this service would not be called. We also need to reference it in our DataSource config using the property "ops4j.preHook":
Accessing the DB
The PersonRepo class uses simple jdbc4 code to query the person table and return a List of Person objects. Person is a imply bean with id and name properties.
Testing the code
Download and start Apache Karaf 4.1.1. Then install the example-lb feature
This shows the list of tables for the DataSource person. In our case it should contain a table person with the columns id and name.
This should display one person named Chris with id 1. The schema as well as the data was created using liquibase.
Introducing a new column
Now a typical case is that we want to add a new column to a table in the next release of the software. We will do this in code and schema step by step.
After our first test run with the old code the database will exist in the old state. So we of course want all data be preserved when we update to the new version.
Add a new changeset to liquibase
We add the new changeset to the file net.lr.tutorial.lb.migrator/src/main/resources/db/changesets.xml
When liquibase updates the database it will see that the current state does not include the new changeset and apply it.
So all old data should still be present and the person table should have a new column age with all ages of persons set to the default value 42.
Use the new column in the code
The Person model object is already prepared for the new property to keep things simple.
So we only need to adapt the PersonRepo. We add age to the select:
and also make sure we read the age from the resultset and store it in the person record:
Note that this code will break if there is no age column. So this will show that the new column is applied correctly.
Test the new code
Then we update the service bundle to pick up the db changes and the new code
First we do a quick check to see the column is actually added
The person table should now have three columns id, name and age
The person Chris should name have the default age of 42.