Liquibase: physical data model creation and management tool

🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions.

1. Introduction

The Platform for deployment and maintenance of state electronic registers is a set of constructors, each of which performs a specific role.

The Platform uses Liquibase to create a physical data model of the registry for PostgreSQL DBMS.

Out of the box, Liquibase supports deploying and versioning objects in a database, that is, creating or deleting tables, establishing relationships between these tables, creating views, setting constraints, and more.

Liquibase uses its own constructs called Change Types, each defining a specific version of database changes using XML tags. For example: <createTable>, <dropTable>.

Since Liquibase is used within the Platform as a single tool for working with the physical data model in PostgreSQL, its out-of-the-box functionality can be insufficient and redundant at the same time.

For security purposes, database developers and other categories of users do not have direct access to the data, meaning they cannot run SQL queries directly to PostgreSQL.
Liquibase has a set of built-in extensions, which:

2. Starting Liquibase with extensions

2.1. What happens on the Java side?

Liquibase is an open-source program written in Java and distributed via a liquibase.jar file.

The Liquibase architecture allows developing extensions with additional functionality and compiling them into separate .jar files, typically called liquibase-ext.jar (in our case, liquibase-ddm-ext.jar).

2.1.1. Starting Liquibase and extensions locally from the command line

For Liquibase and its extensions to work, you need to start Liquibase with the extensions file from the command line.

Starting Liquibase with extensions locally from the command line for different environments

  • Windows

  • Linux

  • macOS

Java -jar liquibase.jar --driver=org.postgresql.Driver --classpath=postgresql-{version}.jar;liquibase-ddm-ext-{version}.jar --changeLogFile=changeLog.xml --url="jdbc:postgresql://{server_ip}:{server_port}/{db_name}" --username={username} --password={password} --labels="!citus" update -Dbname={db_name}
Java -jar liquibase.jar --driver=org.postgresql.Driver --classpath=postgresql-{version}.jar:liquibase-ddm-ext-{version}.jar --changeLogFile=changeLog.xml --url="jdbc:postgresql://{server_ip}:{server_port}/{db_name}" --username={username} --password={password} --labels="!citus" update -Dbname={db_name}
Java -jar liquibase.jar --driver=org.postgresql.Driver --classpath=postgresql-{version}.jar:liquibase-ddm-ext-{version}.jar --changeLogFile=changeLog.xml --url="jdbc:postgresql://{server_ip}:{server_port}/{db_name}" --username={username} --password={password} --labels="!citus" update -Dbname={db_name}

Since this is a Java application, the developer must explicitly specify the following:

  • The file with Liquibase: liquibase.jar.

  • The driver: org.postgresql.Driver (since we are connecting to PostgresSQL).

  • The file with the extensions: liquibase-ddm-ext-{version}.jar.

  • The changelog to be applied: changeLog.xml.

  • The username and password for which a session must be created to connect to the database.

Running .jar files with Liquibase and its extensions locally is convenient for testing. In a production environment, the process is automated by the Jenkins pipelines. In this case, the XML templates are uploaded to the source code repository of a specific registry, where Jenkins tracks and applies changes automatically.

2.1.2. Liquibase changelog and changesets

A changelog is a text-based file that contains all changes made to the database. An individual unit of change in a changelog is called a changeset. A changelog can be set up as a single file or several files arranged in a hierarchy. For example, a changeLog.xml file may contain "master" changesets followed by references to separate files with minor changesets. Each file may contain descriptions of several changesets.

In summary, a changelog is a collection of changes called changesets that populate the XML templates, which are then converted into SQL queries and executed on the target database (for details, see Scenarios for building the registry physical data model).

2.1.3. Changeset rollback

You can revert each changeset to one of the previous states. Rollback statements are implemented automatically for some tags, for example, createIndex. For custom rollbacks, you need to add the <rollback> tag within the <changeset> tag.

2.2. What happens on the database side?

When Liquibase applies a changelog for the first time, it verifies that the following two service tables exist in the database:

  • ddm_db_changelog

  • ddm_db_change_loglock

The ddm_db_changelog table stores the history of changesets applied in Liquibase. Each changeset is represented as a separate record.

The id, author, and filename columns store the most critical information: the change identifier, change author, and the name of the file in which the change was made.

The ddm_db_change_loglock table ensures that only one instance of Liquibase is running at a time.

Why keep track of all this information?

If the same changelog is executed repeatedly, Liquibase will apply only those changesets that have not been applied yet.

Additional protection with checksum

Liquibase computes a checksum (hash) for each changeset and stores it in the md5sum column.

If an administrator accidentally modifies an existing changeset and tries to execute it again, Liquibase will check the exectype column and its status. If the status is EXECUTED, Liquibase will determine that this changeset has already been executed. It will then generate a checksum for the version the administrator is trying to re-execute, and if the hashes don’t match, the user will receive an error.

Checksums cannot match when a changeset is modified. A changeset with an EXECUTED status should never be modified — only reverted to a previous state via a rollback.
There is an exception when a changeset contains changes that constantly evolve. In this case, changeset modification is allowed. When a changeset is applied again, it gains a REEXECUTED status in the database.