Fundamentals of Liquibase

Database versions

Liquibase is one of the database version control tools that will stand numerous coders in good stead to rapidly manage database schema changes.

The basic thought of Liquibase is to provide an automated way of generating database migration scripts to tell your DB Engine how to go from “Schema A” to “Schema B”, where “Schema A” can be any state ( including an empty database ),“Schema B” can be any other database schema and to keep a version log of each of the changes in those scripts.

The feature that is probably most attractive in Liquibase is its ability to roll changes back and forward from a specific point — saving you from the need to know what was the last change/script you ran on a specific DB instance.

Basic Concepts:

Changeset :

The changeset is a unit of change that Liquibase executes on a database. A changeset is uniquely tagged by an author, id attributes, and changelog file path.

Example of change set XML format:

<changeSet id="202010211812" author="Author_name">
  <createTable tableName="Book">
    <column name="id" type="bigint">
      <constraints primaryKey="true" primaryKeyName="book_id_pk" />
     </column>
    <column name="name" type="varchar(250)" />
    <column name="price" type="decimal" />
  </createTable>
</changeSet>

Changelog :

Changelog file is the source of all Liquibase changes. Changelog contains a sequentially list all data base changes (changesets). Liquibase uses this changelog record to audit the database and execute any changes that are not yet applied to the database. Changelog can be in XML or JSON or YAML or SQL format and even mix and match different types of changelogs is also allowed.

Please check out Examples on change log of different formats for your reference.

DATABASECHANGELOGLOCK table :

This table is used by liquibase to ensure only one instance of Liquibase is running at one time.

DATABASECHANGELOG table

This table is used by liquibase to track which changesets have been run. The table tracks each changeset as a row, identified by a combination of the “id”, “author”, and “filename” columns.

There is no primary key on the table. This is to avoid any database-specific restrictions on key lengths. The composite of “id”, “author”, and “filename” is unique across all rows of the table.

How to use Liquibase ?

Liquibase can be used by embedding in application or by installing tool in machine explicitly. The below explanation is about installing the liquibase independently in machine and running from command line or terminal.

• Install liquibase on your machine.
• Use init project command to create project folder with changelog and liquibase.properties file.

liquibase init project --project-dir=<path/to/some/directory> --changelog-file=<filename> --format=<sql|xml|json|yaml|yml> --project-defaults-file=<liquibase.properties> --url=<JDBC URL> --username=<username> --password=<password>

Or directly create a folder with changelog and liquibase.properties file manually with all the required details.

liquibase.properties file

changeLogFile: ../db.changelog-version1
url: jdbc:postgresql://localhost:5432/liquibase_demo?
username: username
password: password
driver: org.postgresql.Driver
classpath: path/to/file/postgresql-42.2.19.jar

Note : Traverse to the Project folder in commandline or terminal.

Commands used in Liquibase

1. Update commands

• update: On execution of update command Liquibase look through changesets successively in a changelog file, compares unique identifiers (id + author+path to file name) to the value stored in DATABASECHANGELOG table if value not present in table then the changes in that corresponding change set will be deployed on the data base.

liquibase --changeLogFile=<name of change log file> update

• updateSQL: This will examine the raw SQL before updating it.

liquibase --changeLogFile=<name of change log file> updateSQL

similarly we can use other update commands (updatecount, updateCountSQL, updateToTag, updateToTagSQL, updateTestingRollback)

2. Rollback Commands:

Liquibase supports roll back feature which allows to undo the changes made to data base. This process can be achieved either automatically or with a custom rollback command.

• rollback: This command will rolls back all the changes made to the data base after a specified tag.

liquibase --changeLogFile=<change log file> rollback <tag>

We can revert the changes of database to the date and time we need or specified number of change sets using rollbacktodate, rollbackcount respectively.

There are few helper commands (rollbacksqltag, rollbacktodatesql, rollbackcountsql) that produces raw sql liquibase which can be run while executing the above rollback commands.

3. Diff Commands

Diff commands are basically used to find the differences between two different Data bases of same type or different types even the differences between previous version of database and current state of data base by using the snapshot of previous version data base.

• diff: diff command writes differences between databases in to standard out.

Add the below mentioned fields in liqibase.properties file

changeLogFile: <../path/changelogFile>
driver: org.postgresql.Driver url:jdbc:postgresql://localhost:5432/liquibaseDB?
username: <username>
password: <password>
referenceDriver: org.postgresql.Driver referenceUrl://localhost:5432/referenceDB?
referenceUsername: <username>
referencePassword: <password>
classpath: path/to/file/postgresql-42.2.19.jar

Here ulr, username, password are our target database url or snapshot and credentials to access the target database where referenceUrl, referenceUsername, referencePassword are source or reference database url or snapshot and credentials .

If required we can avoid the liqibase.properties file and can add all these properties in command itself. The sample command is given below.

liquibase diff --driver=<driver> --url="<url>" --username=<username> --password=<password> --referenceDriver=<referenceDriver> --referenceUrl="<referenceUrl>" --referenceUsername=<Username> --referencePassword=<password>

• diffChangeLog: Instead of writing differences to standard out this command will append changes to the changelog file in any changelog supported format.

Command sample command is given below.

liquibase --changeLogFile=</path/dbchangelog> --driver=<driver> --url="<url>" --username=<username> --password=<password> --referenceDriver=<referenceDriver> --referenceUrl="<referenceUrl>" --referenceUsername=<Username> --referencePassword=<password> diffChangeLog

If required all properties can be stored in liquibase.properties file and can execute just liquibase diffChangeLog command.

liquibase diffChangeLog

Pros and Cons of liquibase:

Pros:

• Liquibase helps in tracking, managing and applying data base changes.
• Liquibase tracks changes independently rather than using a single version to support multiple developers and/or code branches.
• Database history documentation can be generated using liquibase.
• Liquibase has ability to have the same change description on multiple database types (EX :- If application supports both MySQL and Postgres, we can have only one changelog that can be executed against both database types).
• We can use if/then logic while applying changes.
• Liquibase can run in 3-ways (by embed in to the application, by embed Liquibase into build tools, Run Liquibase to generate SQL for review)

Cons:

• Path to the changelog file and name of changelog file which is already applied to database using liquibase should be not be changed because liquibase track the changes based on unique identifiers (id + author+path to file name) if filename or path changes then the files are considered new and executed again which may cause issue and through exceptions.

Conclusion:

Schema versioning is really a great advantage. The automation of schema updates is surely an efficiency gain. Implementation effort and time of liquibase is relatively low and also the technology stack is minimally increased by Liquibase so it will be easy to implement and manage liquibase but it serves great benefits to easily develop, evolve and ship the application which is less fallible.

This Article illustrates the basic concepts of liquibase and basic commands can use in liquibase for full story of liquibase please refer docs.