Introduction

Welcome to part 1 of "A whirlwind tour of Database Schema Changes Tracking Tools"

In this article, I will give you a general idea and test the basic functionalities of Open-Source Liquibase. It supports many databases, so no matter what your database stack is, Liquibase might be a good choice for you.

Read more about test prerequisites in my "Introduction" blog post here.

What is Liquibase?

Liquibase is a database schema change management solution written in Java, first released in 2006. It enables faster and safer database change revisions from development to production. Liquibase's three main elements are changelogs, changesets, and a tracking table.

The Liquibase Open Source version covers my tests. However, the paid PRO version of Liquibase offers extra features.

The official website of Liquibase can be found here.

General idea of Liquibase

In Liquibase, each change in the database is called a changeset.

"Create table (...)", "Create package (...)", etc. - all of these are changesets.

To define a change to the database, we need to create a new changeset.

Changesets can be defined in SQL, JSON, YAML, or XML format (we can use all of them together), but I will mainly use SQL.

A changelog is a file that lists all changes made to the database.

The most important rule of Liquibase is: you SHOULD NOT MAKE CHANGES directly in your database. Define changesets in your files to do this (SQL format in my examples)

Liquibase tracks changes in the automatically created DATABASECHANGELOG table

Supported databases

Liquibase works with the greatest number and variety of databases. Some of the supported databases are:

• Oracle Database

• Microsoft SQl Server

• AWS RDS

• Snowflake

• PostgreSQL

• Yugabyte

• MySQL

• and many more..read more about compatibility here

Test

1. Installation, Configuration & Connection to the database

It's very easy - download the proper files and add the path to your system environment variables.

Open your terminal and type this command to check if Liquibase is installed.

liquibase --version

As you can see, many drivers, such as connectors to Oracle Database, Microsoft SQL Server, or MariaDB, are already installed with Liquibase.

Now, create a new file called liquibase.properties and configure it to connect to the Oracle database(hosted in the cloud OCI).

Configuration with other database vendors will be similar. You can read more about it here.

# Enter the path for your changelog file.
changeLogFile=controller.xml

#### Enter the Target database 'url' information  ####
### Oracle database
url: jdbc:oracle:thin:@dev_low?TNS_ADMIN=/Users/rg/apps/oracle/wallets/priv/Wallet_DEV

# Enter the username for your Target database.
liquibase.command.username: HR

# Enter the password for your Target database.
liquibase.command.password: <enter_password>

# Enter a name of preview file for all [command]-[sql]
outputfile=dev_update.sql

Now, create the first changelog file. We can call it controller.xml

I have put those two new files in a newly created folder /open_source_liquibase/

Now, let's check if a connection to the Oracle Database in OCI is working (DEV environment by running "liquibase -status" command:

Everything is set.

2. Tracking database schema changes using Open-Source Liquibase

As I mentioned in my introduction blog post, I will make some changes in my database schema in DEV environment:

1. Insert a new row into the table COUNTRIES

2. Create a new table PARAMETERS

3. Add a new column to the existing table EMPLOYEES

4. Change existing procedure SECURE_DML

5. Insert new rows into the newly created table PARAMETERS

My main goal is to find a way to track it and, later, easily deploy it to the production environment.

Making changes

I've created new folders to organize changes. Now, I want to make those changes in my HR schema:

• /pre_scripts/ - for everything that should be executed before I will make changes to my database schema objects

  • /dml/ - for my inserts before object changes

• /database/ for object changes with subfolders:

  • /table/

  • /procedure/

• /post_scripts/- for everything that should be executed after I made changes to my database schema objects

  • /post_script/dml/ - for my inserts after everything else

1. Insert a new row into the table COUNTRIES

Table COUNTRIES already exists, so I've created a new file, pre_scripts/dml/dml_countries.sql

I will use it to store all inserts that should be done in this table.

--liquibase formatted sql
--changeset RAFAL:added_new_country_Poland 
--comment Added new country Poland
INSERT INTO HR.COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('PL', 'Poland', '10');

I've also added the Liquibase syntax necessary at the beginning of every SQL file

--liquibase formatted sql

And syntax defining that this is my changeset (a unit of change) in a format:

--changeset LOGIN:CHANGE_ID

--changeset RAFAL:added_new_country_Poland

Login and change_id, together with a filename, must be unique.

I've also added an optional "comment" syntax:

--comment Added new country Poland

The same steps are for other database schema changes

2. Create a new table PARAMETERS

I've created a new database/table/parameters.sql file

--liquibase formatted sql
--changeset RAFAL:created_PARAMETERS_table 
--comment Created new table PARAMETERS
CREATE TABLE HR.PARAMETERS 
(
  NAME VARCHAR2(50),
  VALUE VARCHAR2(50) 
);

3. Add a new column to the existing EMPLOYEES table in the file database/table/employees.sql

--liquibase formatted sql
--changeset RAFAL:add_new_column 
--comment Added new column for storing employees address
ALTER TABLE HR.EMPLOYEES 
ADD (ADDRESS VARCHAR2(100) );

4. Change existing procedure SECURE_DML

I copied the existing procedure into my SQL file and edited it (changed error message)

I've also added a parameter runOnChange:true.

runOnChange:true

With this setting, a changeset with my procedure can be modified many times in the same file. This parameter is recommended for all replaceable types of database objects (packages, views, procedures etc.)

--liquibase formatted sql
--changeset RAFAL:secure_dml_procedure  runOnChange:true
--comment Changed error message
CREATE OR REPLACE PROCEDURE HR.SECURE_DML
IS
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR (-20205, 
        'You may only make changes during normal office hours (this bracket is a change)');
  END IF;
END SECURE_DML;
/

5. Insert new rows into the newly created table PARAMETERS

This one goes at the end because I've just written a changeset that will create the PARMATERS table.

--liquibase formatted sql
--changeset RAFAL:parameters_dml1  r
--comment Added MY_BLOG_URL parameter value
INSERT INTO HR.PARAMETERS (name, value) VALUES ('MY_BLOG_URL','rafal.hashnode.dev');

Deploy changes to DEV

As a reminder:

The most important rule of Liquibase is: you SHOULD NOT MAKE CHANGES directly in your databas

All changes 1 to 5 are in my files (changesets). Now, I need to deploy it to DEV.

As you remember, I created my changelog file "controller.xml"

A changelog is a file that lists all changes made to the database.

But it's empty now, so Liquibase wouldn't know what files should be executed.

I will add my changesets to it (the order of changesets is an order of execution):

<?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" 
        xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

      <include file="pre_scripts/dml/countries_dml.sql"/>
      <include file="database/table/parameters.sql"/>
      <include file="database/table/employees.sql"/>
      <!--
      Or I could use this:
      <includeAll path="database/table"/> 
      But then, all .SQL files from this folder will be executed alphabetically:
      1. Employees.sql
      2. Parameters.sql
      -->
      <include file="database/procedure/secure_dml.sql"/>
      <include file="post_scripts/dml/parameters_dml.sql"/>

      </databaseChangeLog>

Now, I can deploy changes to my DEV database.

But, first, I will preview what exactly will be executed by running liquibase update-sql command

liquibase update-sql

The file looks good. My changes will be executed properly, and new rows will be added to the Liquibase tracking table.

Now, I can deploy my changes to DEV.

liquibase update

That's it. All my changes are in DEV.

3. Deployment to another environment (PROD)

To deploy to another environment, we need to configure another "properties" file.

# Enter the path for your changelog file.
changeLogFile=controller.xml

#### Enter the Target database 'url' information  ####
url: jdbc:oracle:thin:@prod_low?TNS_ADMIN=/Users/rg/apps/oracle/wallets/priv/Wallet_DEV

# Enter the username for your Target database.
liquibase.command.username: HR

# Enter the password for your Target database.
liquibase.command.password: <enter_password>

# Enter a name of preview file for all [command]-[sql]
outputfile=prod_update.sql 

#OJDBC driver localization
# its included in Liquibase installation foldr, but If you want to make sure all developers use same file you can put it in your repository and specify classpath 
#classpath: ojdbc8.jar

Liquibase uses the liquibase.properties file by default. To deploy to PROD, we need to explicitly point to the PROD properties file.

liquibase update --defaults-file=liquibase_prod.properties

All changes were deployed. It's also clearly communicated in the terminal message.

Let's run the update command again, and we will get the message "Database is up to date, no changesets to execute"

This happens because Liquibase already knows what was deployed to my PROD database. The history of changes is stored in the DATABASECHANGELOG table.

4. Visualise (retrieve) a list of changes made to the database schema.

To check what was deployed, you can simply query the DATABASECHANGELOG table:

If you use APEX, you can create simple, fancy reports to show changes to users.

Or you can use a terminal to retrieve a list of changes:

liquibase history --defaults-file=liquibase_prod.properties

Summary

I hope that this blog post showed you a general idea of what working with standalone Liquibase looks like. If you want to install this tool and get to know it better, read my detailed tutorial here.

Drop me a comment if you have any questions!

Rafal

Tested tools