Liquibase : Database migration

Database migration is an important part of most java based applications. It can be hassle to maintain different versions of database scripts and the corresponding application versions. Liquibase solves this issue by integrating database migration within the application codebase. So, let’s jump right in and see how its done.

For the purpose of this demo, we will be using a spring boot app with an embedded in-memory H2 database. We will be starting the H2 database in server mode, to be able to connect to it remotely.

To start, please clone the below repo from github :

codepilot-medium/liquibase-demo

Below is the config to be added to start H2 in server mode:

Set up h2 databasein application.yml : Enabling h2 console will let us browse the database using the web console.

With the database set up complete, it’s time to configure liquibase.

Liquibase set up on spring boot application is easy and straight forward. All you need for the basic configuration is 2 steps :

1. Add the required dependencies in pom.xml :

2. Let spring boot know that you want liquibase to be enabled.

Now that liquibase is configured, it’s time to create some changesets to test the configuration.

By default, liquibase scans for the changelog master file in the path : {project_dir}/src/main/resources/db/changelog/. You can override this using the application properties.

Create a file db.changelog-master.yaml under this path. You can create this file in xml format too, but yaml is recommended.

This file will list all the changesets in the order of execution :

The actual changeset file (changeset-1.0.0.yaml) will contain the script that needs to be executed on the target database. Below is the changeset script for creating a table. The same script can also be created in liquibase formatted sql script or an XML script.

Now, let’s start the app using mvn spring-boot:run

You should see liquibase execute the changeset on the H2 database during the application start up : The key logs have been highlighted below. Please note that liquibase creates two tables during your first execution on a target database : DATABASECHANGELOG and DATABASECHANGELOGLOCK. These tables are used by liquibase for preserving history of changeset execution. So, please make sure that the user you configure to execute liquibase on the target database has ‘create table’ access.

l.lockservice.StandardLockService:Successfully acquired change log lock
l.c.StandardChangeLogHistoryService: Creating database history table with name: PUBLIC.DATABASECHANGELOG
l.c.StandardChangeLogHistoryService: Reading from PUBLIC.DATABASECHANGELOG
liquibase.changelog.ChangeSet: Table EMPLOYEE created
liquibase.changelog.ChangeSet: ChangeSet classpath:/db/changelog/changes/changeset-1.0.0.yaml::1.0.0-001::Codepilot ran successfully in 3ms
l.lockservice.StandardLockService: Successfully released change log lock

Now, you can open the h2 web console, to see the new table :

H2 Console

Now, let’s dive into some of the other cool features that liquibase has to offer.

Liquibase maven plugin can be used for different purposes. First lets configure the plugin on the pom :

As you can see, we configured the H2 database in the plugin. Now we can run the plugin to :

1. Generate the current change log from the target database. This is very useful when you want to copy the database structure during migration of the database to a different host. You can generate using the below command. Please note that the application should still be running while you do this because we started the H2 DB as an in-memory database. If you quit the app, the DB will be destroyed. This will generate the file db.changelog-init.yaml in path src/main/resources/db/changelog/

mvn liquibase:generateChangeLog -Dmaven.test.skip=true

2. Generate the diff changeset by comparing the target database with the hibernate entities (check referenceUrl value in the plugin config) or another remote database. For the purpose of this demo, lets add an entity that does not exist in the H2 database. We added a new entity PAYROLL as below. Now when we generate the diff, its going to generate the changeset for the Payroll table since it does not exist in the target database. Keep the application running in one terminal and run mvn install -Dmaven.test.skip=true in a different terminal window. Then execute the diff command as mentioned below :

mvn liquibase:diff -Dmaven.test.skip=true

Below is the generated changeset :

As you can see, the plugin generated a changeset for the table from the entity and also for the foreign key constraint. This feature can be very useful for developers to generate the changeset from entities there by eliminating the process of hand coding the changeset.

3. Now, for the generated changeset above, we can generate the sql script using the plugin. This can come in handy when you want the SQL script to be reviewed by the DBA. For this purpose, lets copy the contents of the above generated changeset yaml into a file changeset-1.0.1.yaml and place it under the path : src/main/resources/db/changelog/changes/ and add the corresponding reference to the master yaml :

- include:
    file: "classpath:/db/changelog/changes/changeset-1.0.1.yaml"

Again, with the application running in one terminal window, execute the mvn install -Dmaven.test.skip=true command in a separate window and then execute the updateSQL command as below :

mvn liquibase:updateSQL -Dmaven.test.skip=true

This should generate a migration.sql file in the changelog directory.

Note: Spring boot provides an actuator endpoint that can be used to check the list of changesets executed till date on the datasource. It can be enabled through application properties and can be accessed at http://localhost:8080/actuator/liquibase

management:
  endpoints:
    web:
      exposure:
        include: 'health, info, metrics, liquibase'

Sample response from actuator for liquibase

Liquibase provides a host of features that can make database migration seamless for the developers. I shall try to explain the process of tagging and rollback in my next post.