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

eXo Platform Blog

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">
 
   <!-- Managing auto generation of timestamp by Database -->
   <property name="now" value="now()" dbms="mysql"/>
   <property name="autoIncrement" value="true"/>
 
   <!-- Definition of BADGE table -->
   <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:

  1. The development database which contains the adaptation
  2. 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:

  1. Add an entry <diffChangeLogFile> to specify where the changeLog will be generated
  2. Configure base DB
 <driver>com.mysql.jdbc.Driver</driver>
              <url>jdbc:mysql://localhost:3306/tribe</url>
              <defaultSchemaName>tribe</defaultSchemaName>
              <username>root</username>
              <password></password>

3. 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>hibernate:classic:org.exoplatform.myaddon.entities.domain.configuration?dialect=org.hibernate.dialect.MySQLDialect</referenceUrl>
    -->
   <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>

<!-- Replace with the version of the MySQL driver you want to use -->

<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>
                               <!-- Replace with the version of the MySQL driver you want to use -->
                               <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

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):

  1. Starting from an existing database, run the following CLI: mvn liquibase:generateChangeLog -Pliquibase
  2. Generate a changelog diff file between 2 DB (dev and prod): mvn liquibase:diff -Pliquibase
  3. 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.

Resources

Related Posts
Guide to Integrate LinkedIn 2.0 Connect with the e...
How to use Vue.js hooks within exo portlet
Galen Framework: Advanced test automation techniqu...
Khemais Menzli

I am a technical lead at eXo. with over 12 year of experience in Web & IT technologies. I'm here to help you develop your next idea using eXo stack.

Comments
Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Contact us