Database / Liquibase interview questions
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.
More Related questions...
