How to Use Liquibase Drift Detection

Drift detection is useful in determining what is different when deploying to a single database or determining what is different between two target databases to ensure that they are in sync.

Liquibase has the ability to generate the missing changesets that can either be applied to a database or marked as run to ensure that the Liquibase project is also in sync with the database.

There are two general scenarios where drift detection is used:

  • Comparing a single database's current state against a previous state
  • Comparing the current state of two target databases

Comparing a single database's current state against a previous state

If the database can be accessed outside of the Liquibase process either via a hotfix pipeline or DBAs/developers have access to the database, it is possible for the Liquibase project to become out of sync with the target database. This option can also determine if code has been deployed to the database outside of the Liquibase process.

In order to compare a database to itself, you must utilize the snapshot command.

  • The general flow of this process is:
  • Perform a Liquibase update
  • Run the snapshot command to capture the current state of the database

This can either be run in a separate automation job, or it should be in the same job before the update command (and determine if the job should fail because of this).

Important information

  • The general solution provided below highlights the main commands to perform this workflow. You will still need a username, password, etc., set in order to connect to the database.
  • If these commands are all run at once, the changelog_file_name.sql file will not have any changesets. This file will only have changesets if there are changes in the database, not in the snapshot.

How-to guide

  1. Run the update command on the target database
    2. liquibase --url=$targetURL update
  2. Run the snapshot command on the target database
    3. liquibase --url=$targetURL --output-file=.json snapshot --snapshot-format=json
  3. There are two commands that can be used to determine the differences, diff (option A) and diff-changelog (option B):
    • Run the diff command with --format=json to get a quick glance at what is different:
      • liquibase --url="offline:?snapshot=..." --reference-url=$targetURL diff --format=JSON
    • Run the diff-changelog command on the target database while using the snapshot from step 2 as the URL.
      • liquibase --url="offline:?snapshot=..." --reference-url=$targetURL diff-changelog --changelog-file=.sql

      Note: If this is being used inside of an automation pipeline, this diff-changelog command should be run either in a separate job than the Liquibase update, or it should be run before the update in step 1 to determine if changes have occurred in the database.

Variable descriptions for the example commands listed above

  • $targetURL: This is the normal JDBC URL used to connect to the database.
  • <db_type>: This value needs to be set to the type of database (Postgres, Oracle, etc.).
    • This value is referred to as the DBMS or "shortname," which can be found for all supported databases.
  • <snapshot_file_name>: the snapshot file containing the current state of the database in a JSON format.
  • <changelog_file_name>: the generated changelog (set to SQL) containing changesets that are not in the database.

Comparing the current state of two target databases

When there are multiple databases in the pipeline, such as a PROD database and a UAT database, database drift can occur when certain changes were deployed as a hotfix to PROD that didn't get deployed to the UAT database. This is all achieved via the diff-changelog command.

How-to guide

  1. Follow the steps outlined in Liquibase's documentation on diff-changelog.
    2. liquibase diff-changelog --changelog-file=<changelog_file_name>.xml
--url="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
--username=<USERNAME>
--password=<PASSWORD>
--referenceUrl="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
--referenceUsername=<USERNAME>
--referencePassword=<PASSWORD>

Variable descriptions for the example command listed above

  • <changelog_file_name>.xml: A changelog that will have the changesets needed to get the target database to match the reference database.

What's Next: How to Use the Generated Changelog

In both scenarios outlined in this article, a changelog will be generated with changesets that are not in the current Liquibase project. The user who owns the changelog management process should determine how to incorporate these changelogs into the project.

Note: All generated SQL using Liquibase should be carefully reviewed before applying to any database.

Drift Report

In Liquibase Pro 4.25.0 and later, you can generate a Drift Report every time you run the diff command. You can run a Drift Report on the diff-changelog command in Liquibase Pro 4.25.1 and later. The Drift Report is a human-readable report of any database drift in your system. You can easily integrate the Drift Report into your CI/CD pipeline.

For more information on enabling the Drift Report and integrating it into your CI/CD pipeline, see Drift Report.