Managing Database Changes with Liquibase

What is Liquibase?

Liquibase is an open-source tool for database schema version control and database change management. It allows developers to define, track, and manage database schema changes across different environments in a systematic and organized manner. Liquibase operates on the principle of changesets, which represent incremental changes to the database schema.

Resource for Learning Liquibase: Liquibase Official Documentation

How and Why We Use Liquibase

How We Use Liquibase:

  1. Defining Changesets:

    • Developers define changesets in Liquibase using XML, YAML, or SQL files. In our case we use SQL files. These changesets specify what modifications should be made to the database schema.

  2. Applying Changes:

    • Liquibase automatically executes the changesets, ensuring that the database schema is consistent with the defined changes.

  3. Tracking Changes:

    • Liquibase maintains a changelog, a historical record of all changes applied to the database. This changelog serves as a version control mechanism for the database schema.

Why We Use Liquibase:

  1. Consistency Across Environments:

    • Liquibase helps maintain consistent database schemas across different environments (development, testing, production). This ensures that everyone is working with the same version of the database structure.

  2. Change Tracking and Rollbacks:

    • By using Liquibase, we can track all changes made to the database and easily roll back to a previous state if needed. This is crucial for maintaining data integrity during development.

  3. Collaboration and Transparency:

    • Liquibase facilitates collaboration among developers by providing a clear, version-controlled history of all database changes. This promotes transparency and understanding of the database structure.

Finding Liquibase Files in IntelliJ IDEA

At its main core the liquibase is just an sql file as mentioned earlier containing Data Definition Language (DDL) commands in SQL that define the database schema of the entire akaun platform. These liquibase files are available in the backend repository (blg-akaun-platform-java) which you must have already cloned and opened in your Intellij. Use CTRL + SHIFT + N (Project File Lookup) in Intellij to look for the files with name "liquibase".

Currently there are 2 liquibase files which are liquibase-changelog.sql and liquibase-changelog_V20221019.sql where the latter has the latest changes. There were too many DDL commands to place in just one file so it has been separated out into two files. In the future there might be more files.

Changeset and Examples

Each new change in the database schema is added at the end of the latest liquibase file. Each change is called a changeset. So for example if the new change that is to be made is to add a new column to a table you would add the DDl for this at the end of the liquibase-changelog_V20221019.sql file since it is the latest. Before the DDL command that you write you should add a single lined comment.

The format of the comment should be:

'--changeset YourName:YearMonthDayTime'

There should be no extra spaces in it. Here is an example changeset comment:

Over here Jack made a changeset on July 17th 2023 at 10:03.

'--changeset Jack:202307171003'

Example changeset from liquibase SQL file:

In this example a new database change is being made where a new column named "doc_source_channel" us being added to the bl_ocr_scanned_doc_hdr table.

--changeset Anonymous:202307171003
ALTER TABLE bl_ocr_scanned_doc_hdr
    ADD COLUMN IF NOT EXISTS doc_source_channel VARCHAR(255);

ALTER TABLE bl_ocr_generic_doc_hdr
    ADD COLUMN IF NOT EXISTS doc_source_channel VARCHAR(255);