Simplifying Database Development with Liquibase CLI

Liquibase is an database schema management tool that helps managing and versioning of the database schema changes. It allows defining database schema changes in a declarative way using XML, YAML, JSON, or SQL formats, and then apply these changes to the database. It supports a wide range of relational databases, including Oracle, MySQL, SQL Server, DB2, and others.

Some of the benifits of using Liquibase is -

• It allows versioning of the database schema just like the versioning of the code.

• Rollback database changes to a previous version.

• Apply database changes to multiple environments, such as dev, test, and prod, in a consistent and repeatable way.

• Integrate Liquibase with your build tools and CI/CD pipelines.

• Generate a diff report between two database schemas, or between a database schema and a Liquibase change log.

Liquibase creates DATABASECHANGELOG and DATABASECHANGELOGLOCK tables automatically if it does not exist in the database. Both of these tables are important for Liquibase to function properly, and should not be modified or deleted manually.

The DATABASECHANGELOG table is used by Liquibase to keep track of which changes have been applied to the database. It contains a record for each change that has been applied, including information such as the ID of the change, the author of the change, and the date the change was applied.

The DATABASECHANGELOGLOCK table is used to prevent multiple instances of Liquibase from attempting to apply changes simultaneously. When Liquibase is running, it acquires a lock on this table to indicate that it is currently applying changes. If another instance of Liquibase attempts to run while the lock is held, it will wait until the lock is released before proceeding.

Let's talk about Liquibase and manual SQL queries.

Liquibase and manual SQL queries are two different approaches to managing database schema changes.

Manual SQL queries requires writing of the SQL statements necessary to create, modify, or delete database objects, such as tables, indexes, and constraints. These SQL statements need to be run manually, either through a database console or a script, to apply the changes to the database schema.

Liquibase, on the other hand, is a tool or library that provides a declarative way to manage database schema changes. With Liquibase, schema changes can be defined in a change log file, which can be versioned and tracked just like the application code. This makes it easy to manage database schema changes across different environments.

For more details on Liquibase, visit the official website - https://docs.liquibase.com/

There are several ways to setup Liquibase, depending on the needs and preferences. In this blog, we will use CLI method.

Let's get started with some hands-on.

Download the zip file from the liquibase release page and extract it - https://github.com/liquibase/liquibase/releases

Open the profile (in my case .zprofile) and add the path for liquibase so that it is accessible via terminal.

PATH="/Users/pranayp/Documents/Software/liquibase/liquibase-4.21.1:$PATH"

Open the terminal and type below command to verify the installation.

liquibase --version

We will use mysql instance in local, through which liquibase will interact. Let's use docker compose method for the same.

docker-compose.yaml

version: "3.9"
# https://docs.docker.com/compose/compose-file/

services:
  db-mysql:
    container_name: db-mysql
    image: mysql:8.0.29
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:
    driver: local

networks:
  default:
    name: company_default

Start the mysql instance with below command.

docker-compose up db-mysql

Note: We can connect to mysql db through MySQLWorkbench with the help of root as username and password

Liquibase will need mysql driver to work with mysql. We can download the mysql driver from the official site - https://dev.mysql.com/downloads/connector/j/

In Liquibase, a changelog is an XML, YAML, or JSON file that describes the set of changes to be applied to a database. The "changeLog-master" file is the entry point that includes or references multiple individual changelog files. We will use YAML in this blog. The purpose of the "changeLog-master" file is to provide a central place to organize and manage all the changes to the database schema. It helps in maintaining a structured and sequential order of changes and facilitates collaboration among multiple developers working on the same database.

Let's create changeLog-master.yaml file which will have reference to 3 separate changelog file.

changeLog-master.yaml

databaseChangeLog:
- includeAll:
    path: tables-setup-0_1
    relativeToChangelogFile: true

Note: 3 changelog files are housed in tables-setup-0_1 folder

add-table-books-04052023.yaml (Creates a tables with name - books)

databaseChangeLog:
  - changeSet:
      id: 2
      author: pranay.pourkar@test.com
      labels: books
      context: books
      comment: This is the table to hold books data
      changes:
        - createTable:
            tableName: books
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: name
                  type: varchar(50)
                  constraints:
                    nullable: false

# Add a version attribute here
version: 1.0.0

add-table-users-03052023.yaml (Creates a table with name - users)

databaseChangeLog:
  - changeSet:
      id:  1
      author: pranay.pourkar@test.com
      labels: users
      context: users
      comment: This is the table to hold users data
      changes:
        - createTable:
            tableName:  users
            columns:
              - column:
                  name:  id
                  type:  int
                  autoIncrement:  true
                  constraints:
                    primaryKey:  true
                    nullable:  false
              - column:
                  name:  name
                  type:  varchar(50)
                  constraints:
                    nullable:  false
              - column:
                  name:  age
                  type:  int
                  constraints:
                    nullable:  false
              - column:
                  name:  contact
                  type:  int
                  constraints:
                    nullable:  false
              - column:
                  name:  address
                  type:  varchar(100)
                  constraints:
                    nullable:  false

# Add a version attribute here
version: 1.0.0

update-table-users-05052023.yaml (Updates table - books to add new column - author)

databaseChangeLog:
  - changeSet:
      id: 3
      author: pranay.pourkar@test.com
      labels: books
      context: books
      comment: Adding author column to the books table
      changes:
        - addColumn:
            tableName: books
            columns:
              - column:
                  name: author
                  type: varchar(50)

Folder structure will look like below

Now, let's create liquibase.properties file to hold liquibase configuration data.

# Liquibase settings
driver: com.mysql.cj.jdbc.Driver
classpath: ./mysql-connector-j-8.0.33/mysql-connector-j-8.0.33.jar
url: jdbc:mysql://localhost:3306/liquibase-example-schema?createDatabaseIfNotExist=true
username: root
password: root

# Change log file location
changeLogFile: ./db/changeLog-master.yaml

# Liquibase logging
logLevel: info
logFile: ./liquibase.log

Overall the folder structure is as below

It's time to execute our changelog files.

Run the below liquibase command on the terminal from the root folder of liquibase-example

liquibase update

We can from the logs that all the changelog files were applied. Let's us verify from the database as well.

Files are attached for the reference below.

liquibase-example.zip

Download ZIP • 5.60MB

Thank you for taking the time to read this post. I hope that you found it informative and useful in your own development work.