Posted 28 Jan 2020 by Steve Donie
It’s time for some myth-busting! Yes, Liquibase really can work with SQL scripts. In fact, our recent Liquibase survey revealed that most of our users prefer using SQL scripts over the other Liquibase changelog options, like XML, JSON, and YAML.
So if you love SQL, here’s how you can keep using your SQL scripts AND use Liquibase to automate your database deployments. Yes, the first step involves creating an XML changelog, but I promise that you’ll be using SQL for all actual changes.
<include> tag in the XML example above. Each SQL changelog file that needs to be tracked by Liquibase has to be registered in this XML changelog file.
Each change will be applied to the database in the order listed in the changelog.
Later on in this post you’ll learn about another technique that will use all the files in a given directory.
For each change to the database schema, you will need to add the SQL file. While you can use completely plain SQL, there are some additional benefits if you add a few select comments to your SQL files. This is referred to as “liquibase formatted SQL”. Formatted SQL changelogs look like this:
The first line must always be the comment
--liquibase formatted sql. This allows typical SQL tools like Oracle sqldeveloper to treat those lines as comments,
but gives Liquibase a clue that this file is special. The second line starting with
--changeset is required for each changeset. As with changesets in other
formats, each changeset must have an author, a unique identifier, and should be an “atomic unit of change”.
In this example, we purposely made the rollback statement cover multiple lines. When that is the case, you will need to have the
--rollback comment at the beginning of each line.
Here’s an example that demonstrates the ability to have multiple related SQL statements in a single changeset:
In this example, notice the
spliStatements:true addition to the
--changeset line. This tells Liquibase to split the block of SQL into multiple statements, each of which gets
executed separately. The
endDelimiter parameter specifies that the statements should be split on the semicolon character.
Piece of cake!
Now you can rinse and repeat for as many SQL changesets as you’d like. You have a couple of options here for organizing the changesets - you can have multiple changesets in a
.sql changelog file, and you can also include multiple SQL formatted changelogs in a single XML changelog file.
Include the SQL changelog files in the changelog file we created earlier with the following XML tags:
Add as many SQL changelogs as you’d like and register them in the changelog file.
If it seems tedious to both create the .sql file and then add a reference to the file in the XML changelog, there is a way to simplify that. It does require some initial planning though.
Rather than manually adding each sql file you create to the XML changelog, you can instead organize all your SQL into directories, and use the
tag in the changelog to process all of them. When doing this, you will want to have a well-defined naming scheme for the files, because the order that they
are applied is just alphabetical. One reasonable scheme is to use something like this:
By using an initial five-digit number, the files will get sorted in a predictable order. As people add more changesets, leave some ‘space’ in the numbering sequence in case you ever need to add a new changeset in between two existing changesets. Notice that you can also include changelogs in other formats.
Our example database is Postgres, but you can use any Liquibase-compatible database. Run the following command to execute Liquibase:
So there you go — an easy-to-understand way of using SQL that’s easy to use in your standard SQL development environment, with the added benefits of tracking them with Liquibase. If you’re new to Liquibase, try our tutorial: Your First Migration with SQL.
For all the details, see the formatted sql documentation