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

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 
    xmlns_xsi="https://www.w3.org/2001/XMLSchema-instance"
    xsi_schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog
    https://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.5.xsd">

  <include file="<path to SQL files>/<changelog file name>.sql>" relativeToChangelogFile="true"/>
  <!-- more <include> tags go here -->
</databaseChangeLog>

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 <author name>:<a unique identifier for the SQL changeset>
<SQL statements go here>
<SQL statements go here>
--rollback <rollback SQL statements>
--rollback <rollback SQL statements>
--changeset <author name>:<another unique identifier>
<SQL statements go here>
<SQL statements go here>
--rollback <rollback SQL statements>
--rollback <rollback SQL statements>

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:

--liquibase formatted sql
--changeset stevedonie:create-test-table
CREATE TABLE testTable(
  columnName1 VARCHAR (355)
);
--rollback DROP TABLE
--rollback testTable

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:

--liquibase formatted sql
--changeset stevedonie:create-multiple-tables splitStatements:true endDelimiter:;
create table TEST_1 ( ID int not null, FNAME varchar(100) not null);
create table TEST_2 ( ID int not null, FNAME varchar(100) not null);
create table TEST_3 ( ID int not null, FNAME varchar(100) not null);
create table TEST_4 ( ID int not null, FNAME varchar(100) not null);

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:

  <include file="<path to SQL files>/<changelog file name>.sql" 
           relativeToChangelogFile="true" />

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:

00000_initial_database_setup.sql
00010_add_some_tables.sql
00020_create_some_procedures.sql
00030_you_can_also_have_xml_changelogs.xml

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:

liquibase --changeLogFile=<path to>/<liquibase changelog file name>.xml 
  --username=<database username> 
  --password=<database password> 
  --classpath=<path to the liquibase installation>/postgresql-42.2.5.jar 
  --url=jdbc:postgresql://<database url>/<database name> 
  update

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