Choosing a Liquibase Changelog Format: Abstraction vs. Native SQL

The Migration Format Dilemma

When implementing Liquibase, the primary architectural decision is choosing the changelog format. This choice determines whether your schema migrations are database-agnostic (portable across different vendors) or database-specific (optimized for a single vendor). Selecting the wrong format often leads to either a lack of control over complex SQL execution or a high volume of runtime syntax errors due to poor validation.

Comparing Changelog Formats

Liquibase supports several formats, categorized into abstraction-based (XML, YAML, JSON) and native-based (SQL). The following table outlines the trade-offs for each.

Format	Validation	Portability	Reviewability	Best Use Case
XML	High (XSD)	High	Moderate	Enterprise apps targeting multiple DBs
YAML/JSON	Moderate	High	High	Developer-centric, concise configs
Formatted SQL	Low (DB-side)	Low	Very High	DBA-led projects; complex optimizations

Trade-offs and Decision Drivers

The Case for Abstraction (XML, YAML, JSON)

Abstraction formats use a Domain Specific Language (DSL) to describe changes. Instead of writing CREATE TABLE, you define a createTable object. Liquibase then translates this into the correct dialect for the target database (e.g., PostgreSQL, Oracle, or MySQL).

• Validation: XML is the gold standard here. Because it uses XML Schema Definitions (XSD), IDEs can provide autocomplete and catch syntax errors before the code is ever committed.
• Portability: If your application must support multiple database vendors, abstraction is mandatory. You write the logic once, and Liquibase handles the syntax differences.

The Case for Formatted SQL

Formatted SQL allows you to write raw SQL scripts while still utilizing Liquibase's tracking mechanisms (the DATABASECHANGELOG table). This is achieved by adding specific comment markers to the top of the SQL file.

• Precision: Some database features—such as specific partitioning strategies, complex indexing, or vendor-specific stored procedures—cannot be mapped to the generic Liquibase DSL.
• Auditability: Database Administrators (DBAs) can review raw SQL without needing to learn the Liquibase XML/YAML syntax.

Implementation Example: Comparison

To illustrate the difference, consider a simple task: creating a users table with an ID and a username.

Option A: XML Abstraction

Run this via the Liquibase CLI or Maven/Gradle plugin. This format is validated against the Liquibase XSD.

<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-4.0.xsd">

    <changeSet id="1" author="tech-editor">
        <createTable tableName="users">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="username" type="VARCHAR(50)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

Option B: Formatted SQL

This file is executed as raw SQL but tracked by Liquibase. Note the --liquibase formatted sql header.

--liquibase formatted sql

--changeset tech-editor:1
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    username VARCHAR(50) NOT NULL UNIQUE
);

Validation and Verification

To verify that your chosen format is working correctly, follow these steps:

1. Deployment: Run liquibase update from your terminal or CI/CD pipeline. Ensure you have the appropriate database driver in your classpath.
2. State Check: Query the internal tracking table to ensure the changeSet was recorded:
   

   SELECT * FROM DATABASECHANGELOG WHERE ID = '1';

3. Schema Check: Verify the table exists in the database using your preferred SQL client (e.g., \dt users in PostgreSQL).

Limitations and Risks

• Portability Lock-in: If you choose Formatted SQL, you cannot switch database vendors without rewriting every single migration file.
• Runtime Failures: YAML and JSON lack the strict XSD validation of XML. A typo in a property name may not be detected until the migration fails during deployment.
• Complexity Ceiling: Using XML/YAML for extremely complex migrations can lead to verbose, unreadable files. In these cases, using the ` tag within an XML changelog is a viable hybrid approach.

Rollback Strategy

If the liquibase update fails or creates an incorrect schema, you can revert the state using:

liquibase rollback-count 1

Note: For Formatted SQL, you must explicitly provide the rollback SQL using the --rollback comment marker within the file; otherwise, Liquibase will not know how to undo the raw SQL command.