Back

TechnologyFeb 04, 2015

Liquibase: Liqui-basics

Justin Wilson

This is part two in a three-part blog series on Liquibase, a database changeset management tool for Java applications. Part one shows the need for Liquibase in a continuous deployment environment and introduces the key concept of the “changeset.” This section covers additional core concepts, and the third part covers odd caveats that might be encountered while using Liquibase and how to work around them.

LIQUIBASE SPECIFIC TABLES

One of the best features about Liquibase is its ability to record which changesets have been executed against each database, which allows the update command to make sure only new changes are applied and that all old changes were applied correctly.

Liquibase does this by maintaining its own table within each database called “DATABASECHANGELOG“. When Liquibase applies database updates, it reads each changeset from the changelog and calculates an MD5 hash (without considering whitespace or comments). If it can’t find an entry in that table that matches a changeset’s ID and author, it will apply the changeset to the database and write its ID, author, and MD5 hash to the table. If it does find an ID and author match but there’s already a persisted MD5 sum that matches the newly generated one, it will skip the changeset since it knows it’s already been executed. However, if there’s already a persisted MD5 sum that doesn’t match, then the changeset in the XML file must have been modified from the last time it was run on that database. In this situation, Liquibase will roll back all of the changes it just made and provide an error message, preventing the application from starting or indicating to a continual deployment script that the build is not safe to deploy.

Thus, once a changeset has been committed to a repository and executed against non-local databases, changing it will cause Liquibase failures. Any commit that changes an existing changeset that has already been run against non-local databases will need to be reverted. If that’s not an option, clearing the contents of the MD5SUM column on the DATABASECHANGELOG row that corresponds to the changeset (or deleting the row entirely) will cause the changeset to be executed against the database again the next time Liquibase is run on it. Running the same changeset twice on a database is not likely to produce desired results, however. The third part in this blog series goes into additional details on how to recover from MD5 hash conflicts when reverting modifications to old changesets isn’t an option.

Adding a new changeset above an existing changeset can be dangerous. Because previously executed changesets are skipped, adding a new changeset above one that’s already been applied to a database will cause the new one to be executed after the old one on that particular database; but if Liquibase updates a new empty database, the newer one will run before the older one! This could cause Liquibase to be unable to apply updates if the changesets are order-dependent. If there’s a need to execute certain changesets on databases in different orders than other databases to recover from mistakes like this, changeset precondition elements can be used to conditionally apply changesets depending on the state of the database. The third part of this blog series will provide several precondition examples. Changesets support failure and rollback conditions as well, which can also aid in changeset conflict recovery.

SEED DATA

Liquibase is excellent at applying seed data. By using the loadUpdateData element within a changeset with the runOnChange attribute set to true, a CSV file’s contents can be mapped to a database table. If any CSV cells are edited after the changeset has been run on a database, they will automatically be updated without creating duplicates when Liquibase runs again, even without modifying the original changeset XML. If a row is added Liquibase will also insert the corresponding row. This allows the database to always map to the CSV’s contents, which makes managing seed data much more readable than using a large SQL file. This feature is database agnostic as well, unlike other typical CSV reading SQL commands such as “load data infile”.

PROPERTIES

Liquibase properties can be used to store common types for use in changesets. For example, instead of remembering that IDs in a database are generally 32 characters for a particular application, a property could be created and referenced every time an ID is used.

If this “type.uuid” property is present within the root databaseChangeLog element before a changeset element:

<property name="type.uuid" value="java.sql.Types.VARCHAR(36)" />

Then it can be referenced within a changeset by surrounding it by ${}, as follows:

<column name="ID" type="${type.uuid}" />

Properties can also be used to define different types for different databases. For example, defining a “now” property compatible with multiple database types could be performed using the following properties:

<property name="now" value="CURRENT TIMESTAMP dbms="h2"/><property name="now" value="now()" dbms="mysql"/><property name="now" value="sysdate" dbms="oracle"/>

If a property’s value is changed after a changeset that references it has already been executed against a database, it will cause the MD5 sum of the changeset to differ, and Liquibase update executions will fail. If you still want to update the value of the original property, one way around this is to make a new property that’s the same as the old one except for its name and to update all the old changesets to reference the new property. This way they’ll still perform the same action they did before, and their MD5 sums will not change even though they’re referencing a new property.

For example, consider the following changeset:

<property name="type.uuid" value="java.sql.Types.VARCHAR(36)" />   <changeSet author="jwilson" id="create-sample-table"> <createTable tableName="SAMPLE" > <column name="ID" type="<strong>${type.uuid}</strong>"> <constraints nullable="false" primaryKey="true" primaryKeyName="PK_SAMPLE"/> </column> </createTable></changeSet>

If the value of type.uuid is changed, making the changeset use a different property will still generate the same MD5 sum as the example above. This prevents “Validation failed” errors from occurring when executing the Liquibase update against the database again:

<property name="type.uuid" value="java.sql.Types.VARCHAR(64)" /><property name="type.uuid.legacy" value="java.sql.Types.VARCHAR(36)" />   <changeSet author="jwilson" id="create-sample-table" > <createTable tableName="SAMPLE" > <column name="ID" type="<strong>${type.uuid.legacy}</strong>"> <constraints nullable="false" primaryKey="true" primaryKeyName="PK_SAMPLE"/> </column> </createTable></changeSet>

You could then make a new changeset to update the length of the ID column to actually use the new property, which should be placed below the original changeset.

<changeSet author="jwilson" id="change-id-length-64"> <modifyDataType tableName="SAMPLE" columnName="ID" newDataType="${type.uuid}"/></changeSet>

CHANGING CONSTRAINTS

Foreign key constraints also need to be set up using Liquibase. The example below assumes that the “create-house-table” and “create-sample-table” changesets have been run and a type.uuid property has been set.

<changeSet author="jwilson" id="add-house-to-sample-fk"> <addColumn tableName="HOUSE"> <column name="SAMPLE_ID" type="${type.uuid}"> </column> </addColumn>   <addForeignKeyConstraint constraintName="FK_HOUSE_SAMPLE" baseTableName="HOUSE" baseColumnNames="SAMPLE_ID" referencedTableName="SAMPLE" referencedColumnNames="ID" onDelete="CASCADE" onUpdate="CASCADE"/></changeSet>

WHAT’S NEXT?

Knowing how to create and modify tables, add constraints, and set up seed data is a good baseline for using Liquibase to mirror JPA annotations. More complex operations can be found in the official Liquibase documentation. The next part of this blog series will cover how to deal with changesets that fail to apply correctly and other odd Liquibase issues. Be sure to stay tuned with us on LinkedIn and Twitter at @CrederaOpen for the final part in this series.