The Magic of Using XML Changelogs in Liquibase

There are so many scenarios where using XML changelogs can be extremely useful. Yes, you can use plain old SQL. And yes, sometimes it seems like formatted SQL changelogs are an easier way to write your source code. But I’m going to show you the magic of using XML changelogs in Liquibase.

The XML format offers more flexibility and many more features over a formatted SQL changelog. (Note: Everything I say about XML in this blog also applies to JSON and YAML formats, too.) What many people don’t realize is that you can use BOTH types of changelogs!

• XML explains what you want to do.
• SQL defines what you want to do.

Think about it this way: Using XML in Liquibase is like calling a 3rd party library function. It’s like calling a stringUtils replace or making a post request to a URL. You don’t know or care to know exactly what it’s doing (for better or for worse). It’s an easy function to call that does what it’s supposed to do. You have control over the arguments that are passed in, but the logic that happens is fully encapsulated in there. So giving up some control makes it easier for you.

Here are some examples highlighting these key benefits of using XML changelogs:

1. Auto rollback

If you use XML (or YAML or JSON), many of the changesets can be automatically rolled back — meaning there is no need to provide a rollback script corresponding to the changeset. It’s zero work for you. It rolls back the script automatically when you want it to!

Example: For a CREATE TABLE changeset, Liquibase will automatically know to apply the inverse DROP TABLE statement as a rollback action.

Learn more about Liquibase auto rollback including which change types are supported.

2. Access powerful change types and parameters

By using XML, you’ll be able to use powerful change types like addLookupTable which provides a series of SQL statements in a single call. You’ll also get more options when applying preconditions.

Validate your changesets

In the following changelog example, a precondition ensures that the DBMS is Oracle and can only be run by the user SYSTEM for the entire changelog. Next, in the following changeset, the precondition will run a SQL check to WARN if the table that is going to be dropped contains any data prior to being dropped.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog  xmlns_ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns_pro="https://www.liquibase.org/xml/ns/pro" xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog-ext https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro https://www.liquibase.org/xml/ns/pro/liquibase-pro-4.8.xsd http://www.liquibase.org/xml/ns/dbchangelog https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.8.xsd">
 
   <preConditions> 
       <dbms  type="oracle"  /> 
       <runningAs  username="SYSTEM"  /> 
   </preConditions> 
 
   <changeSet  id="1"  author="bob"> 
       <preConditions  onFail="WARN"> 
           <sqlCheck  expectedResult="0">select count(*) from oldtable</sqlCheck> 
       </preConditions> 
       <comment>Comments should go after preCondition. If they are before then liquibase usually gives error.</comment> 
       <dropTable  tableName="oldtable"/> 
   </changeSet> 
</databaseChangeLog>

3. Cross-platform compatibility

This one is at the heart of why Liquibase was created in the first place. Nathan needed a solution for applying the same scripts and having them work on multiple database platforms.  Since many developers need to support cloud-based architectures and microservices, using a structured format like XML, JSON or YAML, allow your changelogs to work automatically across different database types. If you use SQL, it’d be up to you to make sure that the SQL will work across all types.

Example

Your company is changing database platforms, going from Oracle to PostgreSQL. Along with this change, you are now required to maintain some of the objects that existed in the previous Oracle database schema and in your current PostgreSQL database schema.

This is where the XML changelog becomes your best friend!

Here is your current Oracle changelog that you have been using to apply some previous changes:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog  xmlns_ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns_pro="https://www.liquibase.org/xml/ns/pro" xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog-ext https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro https://www.liquibase.org/xml/ns/pro/liquibase-pro-4.8.xsd https://www.liquibase.org/xml/ns/dbchangelog https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.8.xsd">
   <changeSet author="bob" id="1">
       <createTable tableName="department">
            <column name="id" type="int">
                 <constraints primaryKey="true" nullable="false"/>
        </column>
            <column name="firstname" type="varchar(50)"/>
            <column name="lastname" type="varchar(50)">
                 <constraints notNullConstraintName="JOB_TITLE_NN" nullable="false"/>
            </column>
            <column name="active" type="boolean" defaultValueBoolean="true"/>
       </createTable>
       <modifySql dbms="oracle">
           <append value=" initrans 9999"/>
           <append value=" pctfree 9999"/>
       </modifySql>
   </changeSet>
</databaseChangeLog>

Notice that the modifySql has the attribute dbms="oracle". This ensures that the string values specified in the append properties will only apply with Oracle deployments.

You can potentially take this changelog and safely deploy it to your PostgreSQL database schema with the liquibase update command.

Let’s say that you need to roll back this changeset for some reason. There’s no need to create a rollback script! Liquibase already understands that the inverse statement for a CREATE TABLE change type is DROP TABLE. Just run the liquibase rollback command and you are good to go!

Dynamic substitution

Liquibase allows dynamic substitution of parameters in the changelog. You can use this capability to determine which value will be substituted to a variable during runtime. One example where this feature can be useful is when you wish to use your changelog over multiple database platforms. You can predetermine which data types to substitute for the appropriate database.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog  xmlns_ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns_pro="https://www.liquibase.org/xml/ns/pro" xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog-ext https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro https://www.liquibase.org/xml/ns/pro/liquibase-pro-4.8.xsd https://www.liquibase.org/xml/ns/dbchangelog https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.8.xsd">
 
 
   <property  name="clob.type"  value="clob"  dbms="oracle,postgresql"/> 
   <property  name="clob.type"  value="longtext"  dbms="mysql"/> 
   <property  name="table.name"  value="tableA"/> 
 
   <changeSet  id="1"  author="joe"> 
        <createTable  tableName="${table.name}"> 
            <column  name="id"  type="int"/> 
            <column  name="column1"  type="${clob.type}"/> 
            <column  name="column2"  type="int"/> 
        </createTable> 
   </changeSet> 
</databaseChangeLog>

4. Easy unit testing

Even if you’re targeting one database, like SQL Server, it’s super handy to run your integration tests against an H2 in-memory database versus spinning up a SQL Server instance. You’ll know that your changes are working before they move on to the production environment.

5. You can do both

Arguably the coolest thing about Liquibase is that you can use XML format for most changes and still use platform-specific SQL for the rest. (Or you can use only a formatted SQL file. Or mostly SQL with some XML. It’s really up to you.) Even if you’re using XML, Liquibase has always had a SQL tag that you can use in the XML file. The SQL tag is a great fallback.

Manage external SQL files with the sqlFile changeset

You may have a complex SQL script that is not supported by the XML change types. No worries! With a Liquibase sqlFile changeset, you can point to an external SQL file while still having the benefits of using all the great changeset attributes.

A note about managing large projects
As your projects get larger, the changelogs will get larger. As a best practice, we advise our users to create a primary/central changelog and then utilize the include tag to manage a group of smaller changelogs. The smaller, included, changelogs can represent specific batches of changes associated with different structures for the various projects, such as releases, features, etc.

This can be achieved with the help of the include tag.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog  xmlns_ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns_pro="https://www.liquibase.org/xml/ns/pro" xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro https://www.liquibase.org/xml/ns/pro/liquibase-pro-4.8.xsd https://www.liquibase.org/xml/ns/dbchangelog https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.8.xsd">
 
   <include file="feature_A1469.xml" relativeToChangelogFile="true"/>
   <include file="feature_TL412.xml" relativeToChangelogFile="true"/>
   <include file="feature_AAC67.xml" relativeToChangelogFile="true"/>
 
</databaseChangeLog>

Drop your SQL in a directory. Liquibase will take care of the rest for you.

Another way you can refer to external SQL files with an XML changelog is with the includeAll tag. This is similar to the include tag, but instead of referring to a specific SQL file you can drop multiple files (or changelogs) in a folder and have Liquibase deploy them for you. The files will be deployed in an alphanumeric order, so be mindful about having a naming strategy for the files. Also, once the files are deployed, Liquibase includes their metadata in the DATABASECHANGELOG tracking table and marks them as deployed.

Learn more about Include and IncludeAll tags.

Summing it up

Liquibase is at its most powerful when you take advantage of the abstraction of XML, JSON, and YAML formats. When it makes more sense to use plain SQL, you can mix and match to get the best of both worlds. Give it a try and save yourself a lot of time down the road.