What is the Liquibase formatted SQL changeLog format?

Formatted SQL changeLog is the option for teams that want to write pure SQL migrations while still using Liquibase's change tracking, rollback, and contextual features. Instead of XML or YAML wrappers, changeSet metadata is embedded as SQL comments in a specific format that Liquibase parses.

A formatted SQL changeLog file:

--liquibase formatted sql

--changeset mike:20240201-create-invoice
CREATE TABLE invoice (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total       DECIMAL(12, 2) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);
--rollback DROP TABLE invoice;

--changeset mike:20240202-add-invoice-status context:prod,staging
ALTER TABLE invoice ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'DRAFT';
--rollback ALTER TABLE invoice DROP COLUMN status;

--changeset mike:20240203-seed-test-data context:dev runOnChange:true
INSERT INTO invoice(id, customer_id, total, created_at)
VALUES (1, 100, 299.99, NOW());
--rollback DELETE FROM invoice WHERE id = 1;

The metadata in the --changeset comment supports the same attributes as XML: context, labels, runOnChange, runAlways, failOnError, runInTransaction. Rollback SQL goes in a --rollback comment immediately after the forward change.

The formatted SQL approach has a significant trade-off: it is not database-agnostic. Because you are writing raw SQL, you must write PostgreSQL SQL for PostgreSQL databases and Oracle SQL for Oracle. You lose Liquibase's cross-database abstraction. This is acceptable for projects locked to a single database but defeats one of Liquibase's core advantages for polyglot teams.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.