In this article I will be explaining how to setup Liquibase to work with plain SQL files, using an existing database. You’ll find at the end some useful commands as well.
A sample project with this configuration can be downloaded here: https://github.com/remborg/liquibase-sql-config
Solution using plain SQL
This solution is based on this post https://www.liquibase.org/blog/plain-sql. As the article say and as I’ve experienced:
[…] most of our users prefer using SQL scripts over the other Liquibase changelog options, like XML, JSON, and YAML.
Steve Donie
Using SQL over XML can be an issue if changing DB but is way quicker and simpler to understand and has a low learning curve… And let’s be honest, how often do you need to change database in a project?
Setup
Install and configure Liquibase (steps 1 and 2 https://www.liquibase.org/get-started/first-steps).
Note: You might have to download the database connector .jar file (for MariaDB: https://mariadb.com/downloads/#connectors).
File structure
The project is following this file structure:
.
├── changelog.xml
├── liquibase-test.properties
├── liquibase-prod.properties
├── README.md
├── mariadb-java-client-2.6.2.jar
├── baseline
| └── 0010-init-db.sql
└── changesets
├── 1.0.0
| ├── 0010-adding-customer-phone-column.sql
| └── 0020-adding-customer-phone-index.sql
└── 1.1.0
└── 0010-changing-customer-phone-length.sql
changelog.xml
will contain the liquibase configuration to make our plain SQL approach work.
liquibase-test.properties
and liquibase-prod.properties
contain the variables liquibase needs to connect to the DB. You’ll need one properties
file per environment and will need another one for your local database if you have one.
I like to have a README.md
file at the root level of my projects, even though nobody reads them. I usually put all the commands and useful notes for the project.
I like to work with open source technologies, for this example I will use MariaDB and have the mariadb-java-client-2.6.2.jar
connector.
Then there is 2 folders:
baseline
contains the SQL commands to run to initialise the database (tables, indexes, static data, etc.). Really useful when I need to create a new DB.changesets
contains a subfolder per feature release, named after its version number. Each of these folders will then contain all the SQL changes for the new version.
changelog.xml
All the magic happens here. There is 2 <includeAll>
elements, one for the baseline folder and one for the changesets folder. It’s then possible to apply one or the other by passing the context as an argument in the command line.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd ">
<includeAll path="baseline" context="init"/>
<includeAll path="changesets" context="default"/>
</databaseChangeLog>
.properties files
After installing Liquibase you will get a default properties file that you can use as a template. From it you will need the following:
changeLogFile: changelog.xml
contexts: default
url: jdbc:mariadb://localhost:3306/bifrost-local
username: root
classpath: mariadb-java-client-2.6.2.jar
I never put the password in this file because it will be under version control in Git. I pass the password directly as an argument in the command line (usually using a CI tool and a secret variable so it doesn’t show up in the logs).
Generate the baseline
Assuming you already have a database, let’s create a ‘schema dump’ of this current test DB and put it in the changesets folder:liquibase --password=<db password> --defaultsFile=<.properties file> --changeLogFile=baseline/0010-baseline.h2.sql generateChangeLog
This generated script will allow us to create new database instances if we need to (adding environments, or duplicating a DB for testing, etc.).
Note: Documentation of the commands can be found here https://docs.liquibase.com/commands/community/home.html
Work with the database
Add a change to the database
For each new feature in your project that requires a change in the database:
- Create a new folder for all new the changes in changesets/ (eg. changesets/1.1.0), folders order is important.
- In this folder, create a new SQL file per command using this template:
--liquibase formatted sql
--changeset <your id here>:
<id of the change>
<SQL command to run here>
--rollback <SQL rollback command here>
So for example:
--liquibase formatted sql
--changeset remborg:0010-create-test-table
create table test_table (
id INT PRIMARY KEY,
label VARCHAR(255)
);
--rollback DROP TABLE test_table;
- Files are run one after the other in alphabetical order. To make things easy name them following a convention. I like to work with 10 increments and name the file with the ‘id of the change’ from the file itself: eg. for the example above, the file name would be 0010-create-test-table.sql
Note: It is good practice to have one change (one SQL command) and its associated rollback command per file.
Create a tag
To simplify rollbacks, create a tag after each update
of a changeset:liquibase --password=<db password> --defaultsFile=<.properties file> tag <tag name>
I always use the version number as a tag (eg. “1.1.0”).
Update the DB with latest changes
You can update the database and apply the changes using the command update
:
liquibase --password=<db password> --defaultsFile=liquibase-test.properties update
Note: To do a dry-run use updateSQL
instead of update
Some useful commands
Setup a new database
If you have to setup a new database, for example to develop locally:
- Create a new
.properties
file with your new database info - Run
liquibase --password=<db password> --defaultsFile=<new .properties file> --contexts=init update
to create schemas, indexes and statics from your baseline. - Create the ‘init’ tag so you can rollback to a blank slate if needed
liquibase --password=<db password> --defaultsFile=<.properties file> tag init
- Apply all the changesets
liquibase --password=<db password> --defaultsFile=<your .properties file> update
Rollback to a tag
If you need to revert some changes you can rollback the DB to a tag by running:liquibase --password=<db password> --defaultsFile=<.properties file> rollback <tag name>
To rollback everything and go back to the baseline
If you’ve created your DB as described above you can run this command to go back to the baseline:liquibase --password=<db password> --defaultsFile=<.properties file> rollback init
Conclusion
As you can see, thanks to Liquibase you only need a few command to get your SQL versioning working and using this SQL configuration is much simpler than learning the XML elements.
When coupled to a CI tool, before merging a feature branch it’s good to have a ‘check job’ running an update with dry-run to test that the scripts are valid, and you can automatise tags creation when deploying too.