Liquibase Changelog Formats: XML, YAML, JSON or Formatted SQL?

Liquibase reads the same changelog model from four syntaxes: XML, YAML, JSON and formatted SQL. The model is what matters, changesets with an id and an author that are applied once and tracked, and the syntax is a team preference with real consequences for tooling, rollback and portability. This article shows one table in each format, explains how a master changelog stitches files together, and ends with a recommendation per kind of team.

What every format shares#

A changelog is an ordered list of changesets. A changeset is identified by three things, its id, its author and the path of the file it lives in, and Liquibase records that triple in DATABASECHANGELOG the first time it runs it, together with a checksum. Contexts, labels, preconditions, runOnChange, runAlways and rollback instructions are attributes of a changeset regardless of syntax. Whatever you write is parsed into that model, which is why a project can mix formats: a YAML master changelog can include an XML file and a formatted SQL file side by side.

The master changelog is the entry point you hand to Liquibase. It usually contains nothing but includes:

<?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-latest.xsd">

    <include file="changes/001-owners-and-pets.xml" relativeToChangelogFile="true"/>
    <include file="changes/002-vets.xml" relativeToChangelogFile="true"/>
    <include file="changes/003-visits.xml" relativeToChangelogFile="true"/>

</databaseChangeLog>

include names one file; includeAll names a directory and runs every changelog in it in alphabetical order. includeAll saves an edit per migration but makes the order depend on file names, so teams that use it adopt a numeric or timestamp prefix. relativeToChangelogFile="true" is worth writing every time: without it, paths are resolved against the classpath or working directory, and the same changelog can behave differently from the CLI, from Maven and from a Spring Boot application.

The same table in XML#

<changeSet id="002-vets" author="schemadrawer">
    <createTable tableName="vets">
        <column name="id" type="INT">
            <constraints primaryKey="true" primaryKeyName="pk_vets" nullable="false"/>
        </column>
        <column name="first_name" type="VARCHAR(50)">
            <constraints nullable="false"/>
        </column>
        <column name="last_name" type="VARCHAR(50)">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

XML is the oldest format and the one with the most complete tooling. The XSD gives you validation and completion in any decent editor, every change type and attribute is documented against it, and most examples on the web are XML. It is also verbose, and a changelog with hundreds of changesets becomes hard to scan.

The same table in YAML#

databaseChangeLog:
  - changeSet:
      id: 002-vets
      author: schemadrawer
      changes:
        - createTable:
            tableName: vets
            columns:
              - column:
                  name: id
                  type: INT
                  constraints:
                    primaryKey: true
                    primaryKeyName: pk_vets
                    nullable: false
              - column:
                  name: first_name
                  type: VARCHAR(50)
                  constraints:
                    nullable: false
              - column:
                  name: last_name
                  type: VARCHAR(50)
                  constraints:
                    nullable: false

YAML maps one to one onto the XML model with less noise. Its weak point is the format itself: indentation errors are easy to make and produce confusing parse errors, and there is no schema validation in the editor unless you add one. Teams already living in Kubernetes manifests and CI pipelines tend to be comfortable with it.

The same table in JSON#

JSON follows the YAML structure exactly, with braces and quotes instead of indentation. It is rarely written by hand; it appears when changelogs are generated by another program, because every language has a JSON writer and none of them can get indentation wrong. If humans will edit the changelog, YAML or XML is the kinder choice.

The same table in formatted SQL#

--liquibase formatted sql

--changeset schemadrawer:004-vets
CREATE TABLE vets
(
    id         INT         NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name  VARCHAR(50) NOT NULL,
    CONSTRAINT pk_vets PRIMARY KEY (id)
);
--rollback DROP TABLE vets;

The first line declares the format; each --changeset author:id comment opens a changeset that runs until the next one; --rollback lines supply the inverse, because Liquibase cannot derive a rollback from arbitrary SQL. Attributes go on the changeset line (--changeset me:5 context:prod runOnChange:true), and --precondition-sql-check covers simple preconditions.

Formatted SQL is the format DBAs accept most readily: what you review is what runs. The trade-offs are that the SQL is written for one engine, that rollback is manual, and that an open-source formatted SQL changelog cannot include other files, so it is either one growing file or many files included from an XML or YAML master changelog. The second arrangement is the common one and works well.

Abstract change types or raw SQL#

The real decision hides behind the syntax question. Liquibase's abstract change types (createTable, addColumn, addForeignKeyConstraint, createIndex, insert and many more) are translated into the dialect of the target database at run time, and most of them know their own inverse, so rollback comes for free. Raw SQL, whether in a formatted SQL changelog or in a sql or sqlFile change inside XML or YAML, is exact but engine-specific and needs a hand-written rollback.

Abstract change types earn their keep when one changelog must run on several engines, which is the situation of a product installed at customers or of a library that ships its own tables. A single-engine product gains little from them and loses precision: engine-specific features such as partial indexes, generated columns, table options or specific types eventually force a sql block anyway, and a changelog that is half abstract and half raw is the worst of both. The dbms attribute lets a changeset run only on some engines, and modifySql can patch generated statements, but both are signs that the abstraction is being fought.

Preconditions and rollback blocks#

Preconditions make a changeset conditional on the state of the database, and they look the same in every format. The typical use is adopting Liquibase on an existing schema, where a table may or may not exist already:

<changeSet id="001-owners" author="schemadrawer">
    <preConditions onFail="MARK_RAN">
        <not><tableExists tableName="owners"/></not>
    </preConditions>
    <createTable tableName="owners">
        <column name="id" type="INT"><constraints primaryKey="true" nullable="false"/></column>
    </createTable>
    <rollback>
        <dropTable tableName="owners"/>
    </rollback>
</changeSet>

onFail="MARK_RAN" records the changeset as applied without running it when the table is already there. The explicit rollback element is redundant for createTable, which Liquibase can reverse on its own, and mandatory for a sql change; an empty rollback element is the way to say "nothing to undo" for changes such as data fixes that must not be reverted.

Which format for which team#

Whichever you choose, keep the master changelog boring, keep changesets small (one logical change each), give ids that mean something (002-vets, not 2), and never edit a changeset that has been applied anywhere. If you are weighing Liquibase against its main alternative, Flyway vs Liquibase covers that comparison.

Where SchemaDrawer fits#

A changelog in any of these formats is a complete description of a schema, but only once it has run. SchemaDrawer runs it with Liquibase against a throw-away PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite of a chosen version and returns an ER diagram of the tables, columns, primary keys and foreign keys that came out, so an abstract createTable and a raw CREATE TABLE end up in the same picture, in the types the engine actually reports. The Liquibase page explains which file is taken as the master changelog and what to upload; the databases page lists the engines and versions available.

Key takeaways#

• All four formats parse into one model: changesets identified by id, author and file.
• Use include with relativeToChangelogFile; use includeAll only with sortable file names.
• Formatted SQL is exact and engine-specific; abstract change types are portable and self-reversing.
• Write rollback blocks for every raw SQL changeset, even if the block is empty.
• Pick the format by team and portability needs, then keep changesets small and immutable.