January 28, 2020

Liquibase Works with Plain Old SQL

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.

Create an XML changelog that will house your SQL changesets




  
  

Notice the 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.

Create formatted SQL changelogs

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:

--liquibase formatted sql
--changeset :


--rollback 
--rollback 
--changeset :


--rollback 
--rollback 

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 --changset 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”.

Real example:

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 splitStatements:true addition to the –changset 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 single .sql changelog file, and you can also include multiple SQL formatted changelogs in a single XML changelog file.

Make sure your SQL changelogs are in the 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.

Processing an entire directory with <includeAll>

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 <includeAll> 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.

Trigger Liquibase to update the database

Our example database is Postgres, but you can use any Liquibase-compatible database. Run the following command to execute Liquibase:

Wrapping up

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

Share on: