What is Liquibase and how to start using it? Automate your database scripts deployment with this Liquibase tutorial

Many organizations have implemented DevOps in their applications, that’s true. But, at the same time, their database change process hasn’t realised any of these benefits and is still left in the dark ages. But what if you could automate that too? Yeah, you guessed right – it can be done using Liquibase. And here’s the basic Liquibase tutorial to show you how to do that.

Is this Liquibase tutorial for you?

Are you manually executing scripts to your database? Or maybe you’re wasting time validating database scripts received from your team?

After that, are you merging scripts into one file and executing them in every environment? How about deployment errors? Have you ever spent hours looking at who, why, and what was changed in the database? 

But what if you can’t have an entire CI/CD process right now or company policy doesn’t allow you to run scripts on specific environments? That’s not a problem for Liquibase.

By using Liquibase you can:

• automate your database deployment scripts,
• consistently deploy the same way in every environment,
• have rollbacks always  prepared for every database change,
• have all detailed info of deployments in one place.

What’s more, thanks to this you will have:

• fewer deployment errors,
• happy and efficient developers coding together on the same databases,
• every change audited, e.g who, when (and why) changed the column SHOES.SHOE_SIZE from a NUMBER data type to a VARCHAR2,
• more coffee time.

In a series of articles, I’ll show you how we automated our database change process at Pretius using Liquibase and GIT – examples from limited-access environments included. Let’s start with this basic Liquibase tutorial.

What is Liquibase exactly?

Liquibase (LB) is an open source tool written in Java. It makes defining database changes easy, in a format that’s familiar and comfortable to each user. Then, it automatically generates database-specific SQL for you.

Database changes (every change is called changeset) are managed in files called changelogs.

Liquibase needs two tables at your db schema(created automatically):

• DATABASECHANGELOG — a table storing information about all changes made to your database,
• DATABASECHANGELOGLOCK — used to prevent users from doing changes to the database at the same time.

My examples will be based on changesets written in SQL — it’s the easiest way to start automating your Oracle database change process.

Start with installing Liquibase

Go to https://www.liquibase.org/download and download the latest version — choose “Just the files”. In this article, I will use version 4.3.0. built 09.02.2021.

Extract the downloaded zip folder (e.g., to disk C:\). Now you must set a New Path System Variable to the liquibase-version#bin folder on your computer. For Liquibase to work properly, you must also have JAVA installed.

Go to your favourite CLI tool (I use Visual Studio Code) and type:

If everything’s ok, you will see:

If you use UTF8 encoding in your files remember to edit the liquibase.bat file by adding line:

Configure your project and Liquibase

Ok, let’s see how we can organize our files (folder HR is my GIT repository). In these folders, we will put files created during project development. If you had other types of objects (which are “create or replace” type) just create folder with it, e.g “synonyms”.

Now, we need to create Liquibase properties file with connection to our DEV database:

Now, create an update.xml file (put it into new hr/liquibase folder with ojdbc file):

<?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-4.3.xsd”></databaseChangeLog>

Use Oracle Wallet (optional)

If your Oracle database is hosted on Oracle Autonomous Database, you need to use the wallet to connect to it through Liquibase. Download your wallet and remember the password for it.

Unpack your WALLET_NAME.ZIP to previously created HR/liquibase folder. Also edit your HR/liquibase/wallet_name/ojdbc.properties file:

Your file should look like on the screen above. In the lines javax.net.ssl.trustStorePassword and javax.net.ssl.keyStorePassword, put your ATP wallet password.

Edit URL at your liquibase_local.properties file and set your connection name (from Wallet/tnsnames.ora and path to wallet):

Check your sqlnet.ora file, make sure there is “ SSL_SERVER_DN_MATCH=yes”. Don’t change anything else.

Connect Liquibase with your database

If everything is set properly we can make the first connection to our DEV database. Start your favourite CLI from the HR folder (location of liquibase properties file) – for this article purposes I use terminal directly from VS Code and connection to my local development database.

liquibase -> invocation of LB(environment path)

defaultsFile -> name and location of our properties file

(if you’d name properties file to  “liquibase.properties” then you may omit this command because it’s liquibase default. I’ll prefer to have different names for every connection)

updateSQL -> Liquibase command, only generation of SQL script (it won’t do anything on your database)

In a few second LB will generate output_file.sql

As you can see if you’d run this script to your database it would create two tables: DATABASECHANGELOG and DATABASECHANGELOGLOCK.

Ok, now let’s create those tables:

Update  command will run execute SQL to database.

Tables are created:

Now, we need to create a changelog file that will point to our folders with objects (those we can create / replace).

I created HR/master.xml file:

<?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-4.3.xsd”>
<includeAll path=”triggers” relativeToChangelogFile=”true”/>
<includeAll path=”views” relativeToChangelogFile=”true”/>
<includeAll path=”types” relativeToChangelogFile=”true”/>
<includeAll path=”package_spec” relativeToChangelogFile=”true”/>
<includeAll path=”package_bodies” relativeToChangelogFile=”true”/>
</databaseChangeLog>

It points to my objects  folders and all of it’s content.

In main changelog HR\liquibase\update.xml set path to your master.xml file, just add line:

<include file=”./master.xml”/>

Liquibase always runs from our liquibase_dev.properties file and update.xml file, so It must see all of your files from there.

Track your DML and DDL database changes

Ok, wait…but what about changes like DDL or DML? No problem. 

For that type of change we create a separate changelog file and write our changesets inside of it.

Just create changelog.sql file and mark it as Liquibase sql file by typing:

Point to our new changelog in master.xml file, by adding:

<include file=”changelog.sql” relativeToChangelogFile=”true” />

Order in which you point to your changelogs or folders is very important. It tells Liquibase in which order to run your sql. It is better to run changelogs first  ( inside of which is “create table(…)” ) and after that compile package which uses this table.

Let’s create first project table in our changeset. Just write:

Let’s ask LB to generate our SQL file (just to preview what changes are going to be made to our database).

As you may noticed, LB is going to lock our DATABASECHANGELOGLOCK table by setting LOCKED = 1 ( while you are running your script to DB, column LOCKED is set to 1. When another user runs LB in the same time, Liquibase will wait until lock is released), then it will create a SHOES table, insert log change into DATABASECHANGELOG and release lock from DATABASECHANGELOGLOCK table.

If everything is fine, execute script to our database:

Table SHOES has been created.

We can check who,why and when created this table.

Finally, nothing is anonymous! 🙂

Track other database changes (packages, views, etc.)

Now, we can do the same with some scripts. I created a package called SHOES_PKG in 2 separate files. Every file is unique changeset and  should be marked as liquibase formatted sql file.

Sql file is unique changeset with additional parameters:

runOnChange:true — it means, everytime we change our package Liquibase will run this changeset against our database (compile this package)

stripComments:false — do not cut our code comments

Now, if we check what SQL would LB run against database (updateSQL) — it would compile both package spec and package body.

Let’s compile these package in our DB (update command).

Everything is logged and packages are compiled.

Have a look at MD5SUM column value — it’s last checksum of your changeset.

For now, all pending changes are executed, LB will not generate anything in SQL (besides locking LB table) — check it by running updateSQL.

Now, let’s change our SHOES_PKG body and save the file.

Checksum of the file has changed and LB will compile this package again — let’s run an update.

Liquibase compiled package body again and updated row with these changeset in DATABASECHANGELOG table – with actual DATEEXECUTED and new MD5SUM value.

Liquibase tutorial: Summary

As you can see, by using Liquibase you can track everything during your database change release process. 

However, all developers should stick to this workflow:

• Always add your changesets to a changelog ( don’t change anything without Liquibase!) – changeset should be unique combining AUTHOR:ID(task) and filename (file with your changelog)
• Verify the SQL you will execute ( always run updateSQL before update command).
• Run database update command.
• Verify that the changeset or changesets were executed (check your DB objects and DATABASECHANGELOG table)

Here we end the basic Liquibase tutorial. However, stay tuned for the next articles! Here’s what you can expect:

• What is Liquibase and how to start using it?
• How to use Liquibase in an existing project?
• Liquibase and GIT: How can many developers work effectively and conflict-free?
• Case study: How to use Liquibase without production access?
• How to create automated rollbacks using Liquibase?
• How to compare multiple database schemas using Liquibase?