
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:
- 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>
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):
- 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.
Resources
- Liquibase tips: http://tips.defun.work/liquibase.html
- Liquibase with Maven: http://www.yegor256.com/2014/07/20/liquibase-in-maven.html