Liquibase Formatted SQL Guide

Content updated September 2025

Key Takeaways

• Liquibase supports writing changelogs in Formatted SQL as an alternative to XML.
  
• Each changeset is defined using --changeset AUTHOR:ID with optional attributes like runOnChange, context, and dbms.
  
• The first line must include --liquibase formatted sql to enable parsing.
  
• Formatted SQL generates the same internal changelog structure as XML, so all Liquibase features (rollback, tagging, dbdoc, etc.) still work.
  
• This format gives teams flexibility to stick with raw SQL while benefiting from Liquibase’s tracking and automation.

A Deeper Dive into Liquibase Formatted SQL

SQL is the language of relational databases. It's standardized, familiar, and a direct way to interact with your data. That's why Liquibase Formatted SQL changelogs are a popular choice for managing database changes. It allows you to write plain SQL while still getting the benefits of a powerful database change management tool.

But there's more to it than just writing SQL. Let's explore how and why teams use Liquibase Formatted SQL, and what its advantages and disadvantages are.

What is Liquibase Formatted SQL?

At its core, a Liquibase Formatted SQL file is a plain .sql file with a few special comments that Liquibase uses to understand and manage your database changes. These comments allow you to group your SQL statements into changesets, which are the building blocks of a Liquibase migration.

Here’s a simple example:

--liquibase formatted sql

--changeset your.name:1
CREATE TABLE person (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address1 VARCHAR(50),
    address2 VARCHAR(50),
    city VARCHAR(30)
);

The first line, --liquibase formatted sql, tells Liquibase that this is a special file it can parse. The second line, --changeset your.name:1, defines a new changeset with an author ("your.name") and a unique ID ("1"). Everything after that, until the next changeset, is the SQL that will be executed.

Why (and How) Do People Use It?

So why would you choose this format over others like XML, YAML, or JSON? Here are a few common reasons:

• Familiarity and Comfort: Many teams are simply more comfortable writing and reviewing SQL. Using Formatted SQL means there's a lower learning curve to get started with Liquibase. DBAs, in particular, often prefer to see the exact SQL that will be run.
• Existing SQL Scripts: If you have a large number of existing SQL migration scripts, you can easily adapt them to the Liquibase format. This makes adopting Liquibase much easier for established projects.
• Database-Specific Features: Sometimes you need to use a feature that is specific to your database (like a stored procedure in Oracle or a special index type in PostgreSQL). Formatted SQL allows you to write the exact DDL you need without worrying about whether it's supported by an abstraction layer.
• Simple and Direct: For straightforward changes, writing a few lines of SQL can be quicker and more direct than creating a more verbose XML or YAML changeset.

Teams typically organize their Formatted SQL files in a dedicated directory within their source control repository. A common practice is to use a naming convention that ensures the files are executed in the correct order, such as 001_create_users_table.sql, 002_add_email_to_users.sql, and so on.

Benefits and Drawbacks

Like any technology, Liquibase Formatted SQL has its pros and cons.

Benefits:

• Control and Precision: You have complete control over the SQL that is executed. This can be critical for performance tuning or complex database operations.
• Easy to Read and Review: For those fluent in SQL, these changelogs are very easy to read and understand. This simplifies code reviews and debugging.
• Flexibility: You can mix and match Formatted SQL with other changelog formats. For example, you could use XML for database-agnostic changes and Formatted SQL for database-specific ones.
• Low Barrier to Entry: It's the quickest way to get started with Liquibase if you already know SQL.

Drawbacks:

• Database Portability: The biggest drawback is that your changelogs are tied to a specific database vendor. If you need to support multiple databases (e.g., PostgreSQL for development and Oracle for production), you will need to write and maintain separate SQL scripts.
• Manual Rollbacks: Unlike XML or YAML, where Liquibase can often auto-generate rollback statements (e.g., DROP TABLE for a CREATE TABLE change), with Formatted SQL you have to write the rollback logic yourself using a --rollback comment.
• Less Abstraction: You lose some of the power of Liquibase's more abstract change types, like addLookupTable, which can simplify complex operations into a single changeset.
• Potential for Errors: Writing raw SQL opens the door to syntax errors or other issues that might be caught by Liquibase's more structured formats.

The Best of Both Worlds

It's important to remember that you don't have to choose just one format. Many teams find a hybrid approach works best. You can have a main XML or YAML changelog that includes your Formatted SQL files. This allows you to take advantage of the strengths of each format.

For example, you might use XML for the majority of your changes to maintain database independence, but then use a Formatted SQL file for a specific, performance-critical stored procedure.

Conclusion

Liquibase Formatted SQL is a powerful and flexible way to manage your database changes. It's a great choice for teams that are comfortable with SQL, have existing scripts, or need to use database-specific features. While it has some drawbacks, particularly around database portability, its ease of use and direct control make it an excellent tool in the database developer's toolkit.

‍

Frequently Asked Questions

Q1: What is Liquibase Formatted SQL?
It’s a way to define Liquibase changelogs in raw SQL files using special comments for changeset boundaries and attributes.

Q2: How do I start a formatted SQL changelog?
Begin your file with --liquibase formatted sql and define changesets with --changeset AUTHOR:ID.

Q3: Can I use attributes in formatted SQL?
Yes. Attributes like runAlways, context, dbms, and runOnChange can be added after the changeset declaration.

Q4: Does formatted SQL support rollbacks and tagging?
Yes. Since formatted SQL builds the same internal structure as XML, all Liquibase features still apply.

Q5: When was formatted SQL introduced?
It was introduced as part of Liquibase 2.0 to give developers more flexibility in writing changelogs.

‍