Choosing Between Formatted SQL and Structured Changelogs in Liquibase

The Decision You Face

When a team adopts Liquibase, the first architectural choice is the changelog format. Structured formats (XML, YAML, JSON) give you database‑agnostic change types such as createTable and addColumn. Formatted SQL changelogs let you write raw DDL and control every vendor‑specific clause. The right choice depends on three constraints: database heterogeneity, team SQL expertise, and reliance on generated rollbacks. This guide states the decision, compares the options, explains trade‑offs, and shows a concrete validation workflow you can run today.

Quick Comparison

Criterion	Structured (YAML/XML/JSON)	Formatted SQL
Portability	One changelog runs on PostgreSQL, MySQL, Oracle, etc.; Liquibase emits dialect‑specific SQL.	SQL is passed through verbatim; you must maintain separate files per dialect or accept lock‑in.
Rollback support	Automatic for most change types (createTable → dropTable, addColumn → dropColumn).	Requires explicit --rollback comment per changeset; missing or wrong rollback SQL is a common production incident source.
Reverse engineering	generateChangeLog and diffChangeLog emit structured changelogs you can commit directly.	Those commands do not produce formatted SQL; you would have to hand‑craft each changeset.
Vendor‑specific DDL	Limited to what Liquibase’s change types expose (partitioning, storage clauses often unavailable).	Full control: hints, tablespace, partitioning, custom index options, etc.
Team skill fit	Friendly for developers comfortable with declarative config; IDE autocomplete via XSD (XML) or JSON schema.	Best when DBAs write and review raw SQL daily.
Mixed strategy	Supported: include a formatted‑SQL changeset inside a structured changelog file.	Same; you can embed structured changesets in a SQL file using --changeSet directives, but tooling is weaker.

Trade‑off Deep Dive

Portability vs. Control

If your product must run on at least two RDBMS families, structured changelogs are the only practical path. Liquibase’s createTable will generate SERIAL on PostgreSQL, AUTO_INCREMENT on MySQL, and an identity column on Oracle without any extra work. Formatted SQL forces you to maintain parallel scripts or sprinkle --runOnChange logic, which quickly becomes a maintenance burden.

Rollback Reliability

Automatic rollback works for additive changes. Destructive changes (dropTable, dropColumn) cannot restore data, so you must test rollbacks on a copy of production data. With formatted SQL you write the rollback yourself; a typo in the --rollback block means a failed rollback in production. The research shows this is a frequent incident source.

Reverse Engineering

Teams that start Liquibase on an existing schema benefit enormously from generateChangeLog. It emits a structured changelog that you can version‑control immediately. If you standardize on formatted SQL you lose that bootstrap capability and must manually translate each object.

YAML vs. XML vs. JSON

YAML is terser and reads like a config file; XML validates against a published XSD, giving IDE autocomplete and CI schema checks. JSON is supported but rarely used because it lacks comments and is verbose. Choose YAML for readability, XML if you want strict validation in the pipeline.

Concrete Validation Workflow

Before committing to a format, run the following steps against a throwaway database (Docker container, test schema, or a dedicated CI database). All commands assume you have the Liquibase CLI installed and a liquibase.properties or command‑line parameters for connection details.

1. Inspect Generated SQL

# Run from the project root where changelog.yaml (or changelog.sql) lives
liquibase \
  --changelog-file=changelog.yaml \
  --url=jdbc:postgresql://localhost:5432/testdb \
  --username=liquibase_user \
  --password=changeMe \
  updateSQL > generated.sql

Where to run: Developer workstation or CI job with network access to the test DB.
Permissions: DB user needs CREATE TABLE, ALTER TABLE, DROP TABLE on the target schema.
Expected check: Open generated.sql and verify dialect‑specific syntax (e.g., BIGSERIAL on Postgres). If you see generic placeholders, the structured changelog is not being translated correctly — review change type usage.

2. Apply and Verify Idempotency

liquibase \
  --changelog-file=changelog.yaml \
  --url=jdbc:postgresql://localhost:5432/testdb \
  --username=liquibase_user \
  --password=changeMe \
  update

# Second run should be a no‑op
liquibase \
  --changelog-file=changelog.yaml \
  --url=jdbc:postgresql://localhost:5432/testdb \
  --username=liquibase_user \
  --password=changeMe \
  update

After the second update, query DATABASECHANGELOG and confirm no new rows were inserted. This proves changeset identifiers (author + id + file path) are stable and checksums match.

3. Test Rollback Paths

# Roll back the last changeset
liquibase \
  --changelog-file=changelog.yaml \
  --url=jdbc:postgresql://localhost:5432/testdb \
  --username=liquibase_user \
  --password=changeMe \
  rollbackCount 1

Inspect the schema: the objects created by the rolled‑back changeset should be gone. For formatted SQL changelogs, ensure each changeset contains a --rollback comment; otherwise the command will fail with "No rollback statement provided".

4. Cross‑Dialect Portability Check (Structured Only)

# Spin up MySQL container
# docker run -d --name mysql-test -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 mysql:8

liquibase \
  --changelog-file=changelog.yaml \
  --url=jdbc:mysql://localhost:3307/testdb \
  --username=root \
  --password=root \
  updateSQL > mysql_generated.sql

Compare generated.sql (Postgres) and mysql_generated.sql. Differences should be limited to dialect‑specific type names and auto‑increment syntax. If you see structural divergences (missing indexes, different constraint names), adjust the structured change types or add modifySql overrides.

Mixed Strategy Example

Most production teams end up with a hybrid approach. Below is a YAML changelog that uses structured changes for the core schema and a single formatted‑SQL changeset for a PostgreSQL‑specific partitioned table.

databaseChangeLog:
  - changeSet:
      id: 1-create-core-tables
      author: alice
      changes:
        - createTable:
            tableName: app_user
            columns:
              - column:
                  name: id
                  type: BIGINT
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: email
                  type: VARCHAR(255)
                  constraints:
                    unique: true
                    nullable: false
  - changeSet:
      id: 2-partitioned-events
      author: dba
      # Formatted SQL changeset embedded via sqlFile
      sqlFile:
        path: db/changes/partitioned_events.sql
        relativeToChangelogFile: true
        splitStatements: false
        endDelimiter: "\n"

The referenced partitioned_events.sql contains:

--changeset dba:2-partitioned-events runOnChange:false
CREATE TABLE events (
  id BIGSERIAL,
  tenant_id INT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

--rollback DROP TABLE events;

Notice splitStatements: false and an explicit endDelimiter to avoid splitting the PARTITION BY clause on semicolons. This pattern gives you portability for 95 % of the schema while preserving vendor‑specific power where needed.

Limitations & Gotchas

• Checksum stability: Editing an already‑applied changeset changes its MD5 and Liquibase will refuse to run. Add a new changeset instead, or deliberately use validCheckSum / runOnChange after review.
• Destructive rollbacks: Automatic rollback of dropColumn cannot restore data. Always test on a restored backup, never on production.
• Version differences: Default contexts, checksum algorithms, and YAML parsing have shifted between Liquibase 4.x releases. Verify behavior on the exact version you ship.
• Formatted SQL semicolons: Stored procedures or functions with internal semicolons break the default splitStatements:true. Set splitStatements:false or define a custom endDelimiter.

How to Confirm You Chose Correctly

1. Run the validation workflow above on at least two target dialects.
2. Confirm every changeset rolls back cleanly (including the formatted‑SQL ones).
3. Ensure a second update produces zero new rows in DATABASECHANGELOG.
4. Document the chosen convention (structured‑first, formatted‑SQL‑only‑for‑exceptions) in your CONTRIBUTING guide so future contributors follow the same pattern.

If all four checks pass, you have a changelog strategy that balances portability, rollback safety, and team productivity.