Contents

Many organizations have implemented DevOps in their applications, that’s true. But, at the same time, their database change process hasn’t realized 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 a Liquibase tutorial to show you how to do that.

You can jump to various parts of this Liquibase tutorial, according to your needs and previous knowledge:

Or just keep on reading!

Also, here’s a GitHub repository where you can find the files which I’m using in the examples below.

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.
A screenshot showing how you can oversee database changes us ing Liquibase
Wanna know who, when and why changed your database column? Keep on reading this Liquibase tutorial

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:). After that, 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. Therefore, 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 a 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 the purpose of this article, 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 HRliquibaseupdate.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:

The table SHOES has been created.

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

Finally, nothing is anonymous! 🙂

 

Are you looking for Liquibase experts?
Schedule a call

 

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.

How to install Liquibase in an existing software project?

In this part of the Liquibase tutorial, you will learn how to implement database automation in an existing software project.

Is it possible without hours of additional work? Yes!

There are a few ways to automate your existing database using Liquibase. I will show you two which I found most useful – and you can choose the one that suits your needs best.

In the examples below, I’ll be using the project created in the previous steps of this Liquibase tutorial.

How to install Liquibase when there are lots of objects in your existing project

Configure Liquibase in your project repository and leave all files as they are – just remember to add a path to them in your master.xml file.

So, I have created 2 procedures and 2 triggers before implementing Liquibase:

You DON’T need to add “changeset” or “–liquibase formatted sql” to your file right now.

I also added a path to a PROCEDURES folder to my master.xml.

Now, let’s run Liquibase updateSQL to see what SQL Liquibase would like to execute:

OK, bro. But this is not what we wanted! We already have these procedures and triggers in our database. We don’t want to create these objects again.

That’s where ChangelogSync and ChangelogSyncSQL commands come in!

Let’s run ChangelogSyncSQL to see what’s gonna happen:

The output SQL file is:

This is exactly what we wanted – just an SQL file with inserts in a DATABASECHANGELOG table. It will “tell” Liquibase that those objects were already created in the past, and there’s no need to run them again.

Now, let’s insert it to our Oracle database:

And we have 4 new changesets in the DATABASECHANGELOG table:

But what are these strange “raw” IDs? And why is the author called “includeAll”? Because this is the easiest and fastest way to move your existing project to Liquibase! And these changesets were created automatically.

If you’d like to do some changes, e.g. in P_ADD_JOB_HISTORY, just add a changeset – as you’d normally do when creating a new database object:

Then run the Liquibase Update command:

Changeset looks better now, right? With a proper author, ID, etc.

In the examples above, I showed you the easy way to add existing objects (which could be created or replaced) without creating changesets manually. In my opinion, it’s the best way to install Liquibase if you have hundreds of objects in your existing database.

When it comes to objects which cannot be replaced,  such as tables, we need to use a way described in the second scenario.

How to install Liquibase if you don’t have lots of objects in your existing project

This option requires you to create changesets for objects and changes that were already executed to your database.

Objects which you create or replace

Add objects and remember to have paths to folders in your master.xml file – just like described in the first scenario.

Run ChangelogSync and have Liquibase automatically create changesets raw / includeAll / filename.

Or, better way, create a changeset for every file like this:

That’s more work, sure, but you get better info in your logs:

Objects that cannot be created and/or replaced

What can you do with other types of objects like tables, indexes, etc.?

Once again, there are two ways:

  1. Don’t do anything with these objects but remember to always create changesets for every change in them, and add it to your changelog.sql file (alter table, drop column, etc.) – I described how to do it in a previous part of this tutorial.
  2. Create changesets and mark them as executed in the past.

Let’s have a closer look at the second way.

I have a few tables that were created before implementing Liquibase:

I create two changelog files in a new folder HR/scripts_before_liquibase.

Also, I create an additional scripts_before_liquibase.xml file which will point to our changelogs.

The priority of “include file” is very important, as it tells Liquibase in which order to run scripts – first create tables, then, create constraints and indexes.

It’s a good practice to have two files: one for creating tables, second for constraints. It will help you avoid conflicts when trying to create ref_constraint in a table which is gonna be created a few seconds later.

Remember to add a path to the master.xml file to your newly created XML file (HR/script_before_liquibase/scripts_before_liquibase.xml).

Now, create changesets for tables, constraints, etc.

OK, after we added all of our changesets, we will mark them as executed in the past.

Let’s run ChangelogSyncSQL to preview, and then ChangelogSync to run SQL against database.

And voila! All done!

Now, choose the way you prefer and implement database automation using Liquibase right now.

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:

  • 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?

Do you need database experts?

Pretius developers know how to use Liquibase to great effect. We have a great deal of experience with different industries and know a lot about designing system architecture. Do you need software experts? Drop us a line at hello@pretius.com (or use the contact form below). We’ll get back to you in 48 hours and tell you what we can do for your company.

Share