Liquibase Formatted SQL: When Plain SQL Changelogs Beat Structured Change Types

The short answer

Use Liquibase formatted SQL when your team already writes and reviews SQL and you want migrations to travel through the same review flow as the rest of the schema work. You keep Liquibase's tracking, ordering, preconditions and rollback machinery, but the body of each migration stays plain SQL instead of XML, YAML or JSON change types. The cost is that you take on two things the structured formats hand you for free: an explicit rollback for every changeset, and SQL that has to be valid on every database you target.

How a formatted SQL changelog is read

Liquibase treats a changelog as an ordered list of changesets. Each changeset has an identity — author, id, and the file path it came from — plus a checksum computed from its contents. On an update, Liquibase compares the changesets in the file against the rows in its tracking table, DATABASECHANGELOG, and applies only the ones not yet recorded. A companion table, DATABASECHANGELOGLOCK, serializes concurrent runs so two deploys do not apply the same changeset at the same time.

The file opens with a marker line, and each statement group is introduced by a changeset header:

--liquibase formatted sql

--changeset alice:001-create-person
CREATE TABLE person (
    id   INT NOT NULL,
    name VARCHAR(100),
    CONSTRAINT pk_person PRIMARY KEY (id)
);
--rollback DROP TABLE person;

Running liquibase update records author alice, id 001-create-person, the changelog path, the checksum and the execution type. Running it again skips the changeset, because that identity already exists in the tracking table.

Why the identity matters more than it looks

Because identity is author plus id plus file path, moving a changelog file or reusing the same author:id pair in two files makes Liquibase see a brand-new changeset. The usual outcomes are a duplicate-execution error, since the DDL already ran, or a migration that appears to be skipped. Treat the header as a permanent key, not a label you can tidy up later.

Preconditions: gating a migration against a drifted database

Formatted SQL supports preconditions — checks that must pass before the changeset runs. They are the practical answer to "this migration assumes the previous one landed."

--liquibase formatted sql

--changeset alice:002-add-email
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM person WHERE email IS NOT NULL
ALTER TABLE person ADD COLUMN email VARCHAR(255);
--rollback ALTER TABLE person DROP COLUMN email;

The SQL check returns a value, and expectedResult states what that value must be for the changeset to proceed. Here the migration only runs when no rows already carry an email, which catches the case where someone added the column by hand. onFail and onError decide what happens when the check fails or when the check itself errors; HALT stops the run.

Rollback is your responsibility

Liquibase can derive a rollback for some structured change types — a createTable change type knows its inverse is dropTable. A raw SQL changeset has no inverse, because Liquibase does not parse your SQL to work one out. You declare it:

• --rollback DROP TABLE person; — the inverse statement.
• --rollback not required — documents that the changeset intentionally has no inverse, such as an irreversible data backfill.
• --rollback empty — a no-op rollback, for a change that is harmless to leave in place.

Without one of these, a rollback attempt on that changeset will fail or stop. In a pipeline where rollback is part of the release procedure, that turns a routine revert into an incident.

Editing an applied changeset is the classic mistake

Once a changeset is applied, its checksum is stored. Editing the file in place changes the checksum, and the next run reports a validation failure. The workable patterns are:

• Add a new changeset that makes the further change.
• Mark the changeset runOnChange when the content is genuinely re-runnable — a view, a stored function, a seed lookup table. Liquibase then re-executes it when the checksum changes.
• Mark it runAlways only for content that must run on every update, which is rare and easy to misuse.

runOnChange is the right tool for a view definition and the wrong tool for a CREATE TABLE, because re-running the latter fails on the second execution.

Where formatted SQL stops being a good fit

Situation	Why formatted SQL strains
Multiple database vendors	The SQL must be valid everywhere. You end up with dbms attributes or separate changesets per platform, which fragments the migration.
Stored procedures and functions	The default statement splitter breaks on internal semicolons. You need splitStatements:false plus an endDelimiter such as / or GO.
Rollback-heavy release process	Every changeset needs a hand-written inverse, and some data migrations genuinely have none.
Non-transactional DDL	Liquibase does not validate that your SQL is correct. A changeset that fails partway can leave a partially applied migration on databases without transactional DDL.

One more rollback caveat: changesets applied under a context or label that no longer matches the rollback invocation may block or be skipped, so test the revert path rather than assuming it.

Reviewing before you apply

The cheapest safety net is to render the SQL instead of running it. liquibase update-sql prints the statements Liquibase would execute, and liquibase rollback-sql prints the inverse. Run these against a scratch database during code review or in CI, and read them. Command spelling has changed across Liquibase major versions — older CLIs use updateSQL — so confirm the name against the version you actually run.

Verifying the result

After an update, check the bookkeeping rather than trusting the exit code:

1. Query DATABASECHANGELOG and confirm the expected author/id rows exist with the checksums you expect.
2. Query DATABASECHANGELOGLOCK and confirm no lock row is left behind from a crashed run.
3. Run liquibase validate to confirm the changelog parses and checksums match, and liquibase status (or history) to see what is still pending.
4. On a disposable schema, apply, roll back by tag or count, then re-apply, and compare the resulting schema to expectations.

Attribute defaults and some CLI spellings differ between Liquibase major versions. Verify the exact names against the documentation for your installed version before wiring them into a pipeline.