Applies to:
- Liquibase Pro
- Liquibase Open Source (Community)
Summary:
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 can generate the missing changesets, which 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
- Run a diff-changelog to compare the snapshot to the target 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
- Run the update command on the target database
liquibase --url=$targetURL update
- Run the snapshot command on the target database
liquibase --url=$targetURL --output-file=.json snapshot --snapshot-format=json
- There are two commands that can be used to determine the differences diff (option a) and diff-changelog (option b).
- Run the diff command 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
- Important 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.
- Run the diff command format=json to get a quick glance at what is different
- For information on how to use the generated changelog, see the section "What's Next: How to use the generated changelog"
Variable descriptions for the example commands listed above:
-
$targetURL
: This is the normal JDBC URL used to connect to the database. -
lt;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 here.
-
<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
- Follow the steps outlined in Liquibase's documentation on diff-changelog.
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>-
<changelog_file_name>.xml
: A changelog that will have the changesets needed to get the target database to match the reference database.
-
- For information on how to use the generated changelog, see the section "What's Next: How to use the generated changelog
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.
Comments
0 comments
Article is closed for comments.