Liquibase CLI

About

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.

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/

Setup and Run Liquibase using CLI method

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

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

Setup Mysql Instance

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

Mysql Driver

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/

Changelog Files Setup

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: [email protected]
      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: [email protected]
      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: [email protected]
      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

Liquibase Properties

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