Safely manage the database for your eXo add-ons with Liquibase Maven Plugin

A while ago, eXo introduced Liquibase to evolve the database schema safely in the product. A built-in component has been provided within the eXo commons project to let Liquibase perform database schema changes between versions of eXo Platform. However, it was designed to automate upgrades at runtime and not necessarily to support all kinds of operations that are needed at development time as an add-on developer iterates on his code.

At the heart of Liquibase is the Database Change Log File. It’s an xml file that contains instructions to track successive database schema updates. As an add-on developer, I found that manually maintaining it can be time consuming and error prone. Fortunately, I found a very handy maven plugin is available that made my life easier. If you are an eXo add-on developer, read on to learn how I remained highly productive, while dealing with a volatile database schema in an early-stage add-on project.

So what do you have for us?

Well, to be honest, there is no a silver bullet. BUT, Liquibase itself comes with a set of awesome features such as the Liquibase Maven plugin.

Instead of writing the change log file manually, we can use the Liquibase Maven plugin to generate it and save ourselves a lot of work. I want to illustrate this by means of an example. In the early stage of the development of a new eXo add-on I am currently working on, the data model was not always stable. It had to evolve frequently as I was implementing some moving requirements. Agility is cool, but updating the change log file quickly became a painful task for me.

With my team, we decided to use Liquibase Maven plugin as a ‘Swiss Army Knife’ for several common tasks, to:

Generate a changelog from an existing database
Generate a changelog from diff between two databases
Generate a changelog from diffs between a database and JPA entities

Generate a changelog from an existing database

The first step that every developer wants to test/validate is to generate a changelog from an existing database. Typically, you create your database schema with SQL commands or using an UI frontend and you need a way to easily replicate this in your development environment. In this situation liquibase-maven-plugin comes with a built-in Maven goal, called generateChangeLog

Command Line

mvn liquibase:generateChangeLog

Plugin Configuration

Configuring the plugin comes down to adding the following entry:<outputChangeLogFile> to the plugin’s configuration:

				
					<outputChangeLogFile>

${basedir}/src/main/resources/db/changelog/myaddon.db.changelog.xml</output

ChangeLogFile>

The expected output is a changelog file that we can use to create an initial database schema or to populate data as well.

				
					<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">
 
   
   <property name="now" value="now()" dbms="mysql"/>
   <property name="autoIncrement" value="true"/>
 
   
   <changeSet author="myaddon" id="1.0.0-1">
       <createTable tableName="BADGE">
           <column name="ID" type="bigint" autoIncrement="${autoIncrement}">
               <constraints primaryKey="true" nullable="false"/>
           </column>
           <column name="TITLE" type="NVARCHAR(32)">
               <constraints nullable="false" unique="true"/>
           </column>
           <column name="DESCRIPTION" type="NVARCHAR(255)">
               <constraints nullable="false"/>
           </column>
           <column name="NEEDED_SCORE" type="BIGINT">
               <constraints nullable="false"/>
           </column>
       </createTable>
        <modifySql dbms="mysql">
           <append value=" ENGINE=INNODB CHARSET=UTF8 COLLATE utf8_general_ci"/>
       </modifySql>
   </changeSet>
</databaseChangeLog>

Generate a changelog from diff between two databases

In some situations, typically when we need to update a database schema already used in production, we can use the plugin to generate a changelog file from the differences between two existing databases:

The development database which contains the adaptation
The production database which contains the latest version of the schema

Command Line

mvn liquibase:diff

Plugin Configuration

Liquibase’s plugin configuration should be adapted as follows:

Add an entry <diffChangeLogFile> to specify where the changeLog will be generated
Configure base DB

				
					 <driver>com.mysql.jdbc.Driver</driver>
              <url>jdbc:mysql://localhost:3306/tribe</url>
              <defaultSchemaName>tribe</defaultSchemaName>
              <username>root</username>
              <password></password>

Configure reference DB

				
					<referenceUrl>jdbc:mysql://localhost:3306/local</referenceUrl>
<referenceDriver>com.mysql.jdbc.Driver</referenceDriver>
<referenceUsername>root</referenceUsername>
<referencePassword></referencePassword>

Here’s a snippet of the plugin’s configuration we used within my addon :

				
					<configuration>
   <changeLogFile>${basedir}/src/main/resources/db/changelog/
myaddon.db.changelog-master.xml</changeLogFile>
   <diffChangeLogFile>${basedir}/src/main/resources/db/changelog/myaddon.db.changelog-${maven.build.timestamp}.xml</diffChangeLogFile>
   <outputChangeLogFile>${basedir}/src/main/resources/db/changelog/myaddon.db.changelog.xml</outputChangeLogFile>
   <driver>com.mysql.jdbc.Driver</driver>
   <url>jdbc:mysql://localhost:3306/tribe</url>
   <defaultSchemaName>tribe</defaultSchemaName>
   <username>root</username>
   <password></password>
   
   <referenceUrl>jdbc:mysql://localhost:3306/tribe-ref</referenceUrl>
   <referenceDriver>com.mysql.jdbc.Driver</referenceDriver>
   <referenceUsername>root</referenceUsername>
   <referencePassword></referencePassword>
   <verbose>true</verbose>
   <logging>debug</logging>
</configuration>

The output is a changelog file that contains changes against the old schema.

Generate a changelog from diffs between a database and JPA entities

The most expected feature provided by Liquibase Maven Plugin when coupled with the Liquibase Hibernate Plugin is the way of generating the changelog from JPA entities. The goal is to generate a changelog file from the differences between an existing database (for example production) and our new persistence entities.

Note: It is important to emphasise that developers have to check the consistency of generated changesets and adapt them if necessary.

Command Line

mvn liquibase:diff

Plugin Configuration

The configuration is quite similar to the conf for 2 different databases, except for referenceUrl in which we are using JPA persistence unit instead of database url:

				
					<referenceUrl>hibernate:ejb3:exo-pu?hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy</referenceUrl>

liquibase-hibernate comes with 3 flavours of configuration; please refer to official doc: https://github.com/liquibase/liquibase-hibernate/wiki

Note: It is worth to note that this configuration needs version 3.5.5 of liquibase-core instead of version 3.4.2.

The expected output is a changelog file as follows:

				
					<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">

   <changeSet author="exo (generated)" id="1522331789463-190">
       <dropForeignKeyConstraint baseTableName="ntf_email_notifs_params" constraintName="FK_1vfme84il3ryw4e1podyom1gj"/>
       <addForeignKeyConstraint baseColumnNames="email_notif_id" baseTableName="ntf_email_notifs_params" constraintName="FK_1vfme84il3ryw4e1podyom1gj"referencedColumnNames="email_notif_id" referencedTableName="ntf_email_notifs"/>
   </changeSet>
   <changeSet author="exo (generated)" id="1522331789463-191">
       <dropForeignKeyConstraint baseTableName="ntf_web_notifs_users" constraintName="FK_7dpbsl3tb0ij340uwnplhlv39"/>
       <addForeignKeyConstraint baseColumnNames="web_notif_id" baseTableName="ntf_web_notifs_users" constraintName="FK_7dpbsl3tb0ij340uwnplhlv39"referencedColumnNames="web_notif_id" referencedTableName="ntf_web_notifs"/>
   </changeSet>
   <changeSet author="exo (generated)" id="1522331789463-192">
       <dropForeignKeyConstraint baseTableName="ntf_web_notifs_params" constraintName="FK_8q0py1eo4lgwqdx0n96tv3iww"/>
       <addForeignKeyConstraint baseColumnNames="web_notif_id" baseTableName="ntf_web_notifs_params" constraintName="FK_8q0py1eo4lgwqdx0n96tv3iww"referencedColumnNames="web_notif_id" referencedTableName="ntf_web_notifs"/>
   </changeSet>

</databaseChangeLog>

Master changelog generation

Use a standard entry point

When using Liquibase as part of a build automation scenario, I think it makes sense to create a single entry point for Liquibase to manage all generated changelog files that we will call XYZ.db.changelog-master.xml. The aim is to start from this file and load all other changesets available in src/main/resources/db/changelog.

The following snippet illustrates how we use a master changelog file within our add-on:

				
					<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">
   <includeAll path="src/main/resources/db/changelog" />
</databaseChangeLog>

Thus, each time a developer generates a new changelog using liquibase-maven-plugin, the change will be included seamlessly on server startup.

Maven dependency

The Maven dependencies we need to add into our pom.xml:

				
					<dependency>

<groupId>org.javassist</groupId>

<artifactId>javassist</artifactId>

<version>${org.javassist.version}</version>

</dependency>

<dependency>

<groupId>org.liquibase</groupId>

<artifactId>liquibase-core</artifactId>

<version>${org.liquibase.version}</version>

</dependency>

<dependency>

<groupId>org.liquibase.ext</groupId>

<artifactId>liquibase-hibernate4</artifactId>

<version>${org.liquibase-hibernate4.version}</version>

</dependency>

<dependency>

<groupId>javax.validation</groupId>

<artifactId>validation-api</artifactId>

<version>${validation-api.version}</version>

</dependency>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>



<version>${mysql.version}</version>

</dependency>

Create a specific Maven profile

Using Maven profile we can further simplify the management of the database for developers. Below is a snippet of a Liquibase profile we use within our add-on.

				
					<profile>
           <id>liquibase</id>
           <build>
               <plugins>
                   <plugin>
                       <groupId>org.liquibase</groupId>
                       <artifactId>liquibase-maven-plugin</artifactId>
                       <version>${org.liquibase.version}</version>
                       <configuration>
                           <changeLogFile>services/src/main/resources/db/changelog/myaddon.db.changelog-master.xml</changeLogFile>
                           <diffChangeLogFile>services/src/main/resources/db/changelog/myaddon.db.changelog-${maven.build.timestamp}.xml</diffChangeLogFile>
                           <outputChangeLogFile>services/src/main/resources/db/changelog/myaddon.db.changelog.xml</outputChangeLogFile>
                           <driver>com.mysql.jdbc.Driver</driver>
                           <url>jdbc:mysql://${db.host}:${db.port}/${db.schema}</url>
                           <defaultSchemaName>tribe</defaultSchemaName>
                           <username>${db.username}</username>
                           <password>${db.password}</password>
                           <referenceUrl>hibernate:ejb3:exo-pu?hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy</referenceUrl>
                           <verbose>true</verbose>
                           <logging>debug</logging>
                       </configuration>
                       <dependencies>
                           <dependency>
                               <groupId>org.javassist</groupId>
                               <artifactId>javassist</artifactId>
                               <version>${org.javassist.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>org.liquibase</groupId>
                               <artifactId>liquibase-core</artifactId>
                               <version>${org.liquibase.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>org.liquibase.ext</groupId>
                               <artifactId>liquibase-hibernate4</artifactId>
                               <version>${org.liquibase-hibernate4.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>javax.validation</groupId>
                               <artifactId>validation-api</artifactId>
                               <version>${validation-api.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>mysql</groupId>
                               <artifactId>mysql-connector-java</artifactId>
                               
                               <version>${mysql.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>org.exoplatform.commons</groupId>
                               <artifactId>commons-component-common</artifactId>
                               <version>${org.exoplatform.platform.version}</version>
                           </dependency>
                           <dependency>
                               <groupId>xml-apis</groupId>
                               <artifactId>xml-apis</artifactId>
                               <version>${xml-apis.version}</version>
                           </dependency>
                       </dependencies>
                   </plugin>
               </plugins>
           </build>
           <properties>
               <profile.liquibase>liquibase</profile.liquibase>
               <db.host>localhost</db.host>
               <db.port>3306</db.port>
               <db.schema>tribe</db.schema>
               <db.username>root</db.username>
               <db.password></db.password>
           </properties>
       </profile>

To check that it works, run :

mvn liquibase:help

FREE WHITE PAPER

Digitization of Human Resources

Digitization of Human Resources is a hot topic these days. What do companies expect out of it?

Summary

To summarise what a developer needs to know in order to better use liquibase-maven-plugin through the interaction with a database (during dev lifecycle):

Starting from an existing database, run the following CLI: mvn liquibase:generateChangeLog -Pliquibase
Generate a changelog diff file between 2 DB (dev and prod): mvn liquibase:diff -Pliquibase
Generate a changelog diff file between your prod/dev environment and your JPA entities: mvn liquibase:diff -Pliquibase

Note: As you may have noticed, we haven’t talked about liquibase:update option. Quite simply, this option is used to perform the schema changes listed in the changelog on the target database. For eXo projects, this use case was wrapped up within a core service called org.exoplatform.commons.persistence.impl.LiquibaseDataInitializer which is triggered at server startup.

Conclusion

Liquibase is a powerful tool to keep control of your database schema changes during your development lifecycle. Coupled with liquibase-maven-plugin, it offers a very useful way of generating changelog files seamlessly. Instead of writing the file manually, developers rely on this plugin to do the job, which is a huge time saver. The Liquibase Maven Plugin allows you to safely manage the database for your eXo add-ons, and with the help of a SQL text editor, you can easily make changes to your database schema.