Diffing Multiple Schemas in Liquibase

You can diff multiple schemas to capture the changes made by a few people in their database environments and add those changes into a release artifact so they can be deployed in the existing release automation.

Liquibase allows handling multiple schemas with the following commands:

Using Liquibase with Multiple Schemas

There are a lot of workflows you can apply and use with multiple schemas. One of the typical workflows to diff multiple schemas is the following:

Note: Before running commands, ensure that you have configured the Liquibase properties file to include the driver, classpath, and URL for the database that you want to capture. Another way is to pass all attributes on the command line.

  1. Run the snapshot command to capture the state of the database containing different schemas:
    2. liquibase --output-file=mySnapshot.json snapshot --snapshot-format=json --schemas=lookup,public

    When running the snapshot command on multiple schemas, enter the --schemas flag after the snapshot command.

    Example properties file:

    changelog-file: myChangelog.xml
url: offline:postgresql=//localhost:5432/MYDATABASE
username: postgres
password: password
classpath: ../../Drivers/postgresql-42.2.8.jar
licenseKey: <ProKey>
includeSchema: true
  2. Manually make some changes to the target database on the different schemas.
  3. Run the diff command specifying the snapshot (an offline mode) and the database with new changes:
    4. liquibase diff
--url=offline:postgresql?snapshot=mySnapshot.json
--referenceUrl="jdbc:postgresql://localhost:5432/MYDATABASE"
--referenceUsername=<USERNAME>
--referencePassword=<PASSWORD>

    Note: The format for the URL is the following: offline:<db>?snapshot=<path/to/snapshot.json>. Use the name of your database type from the list of the supported databases in place of <db> and the path relative to where the command is running in place of <path/to/snapshot.json>. If you are using another database, like H2, you may need to wrap your URL in ": "offline:<db_type>?snapshot=<path/to/snapshot.json>".

  4. Run the diff-changelog command specifying the snapshot (an offline mode) and the database with new changes:
    5. liquibase diff-changelog
--url=offline:postgresql?snapshot=mySnapshot.json
--referenceUrl=jdbc:postgresql://localhost:5432/MYDATABASE
--changelog-file=mydiffchangelog.xml

    The generated changelog contains changes that you can compare and confirm.

  5. Check the changes manually or by running automation tests, and then deploy them to the production.

Best practices

  • Use the generate-changelog command when you start using Liquibase with an existing project and want to sync your changes for different schemas. The command creates a changelog file with a sequence of changesets that describes how to recreate the current state of the database.
  • Identify a change over which you do not have control or the change that someone applies directly to the database. Using the diff-changelog command, you can identify the change and generate a changelog update.
  • Document your changes. Run the diff command to see the output of all changes.

Nevertheless, there are times when you cannot use diffing capabilities to handle database schema changes. See the following examples:

  • Performance. As your data set grows, the amount of information to compare grows until it becomes unmanageable.
  • Data changes. During development, the test data is often added to the development database and should not be copied into other databases. Also, the new data may be added to testing and production databases and should not be deleted because it does not exist in the development database.
  • Diff commands do not handle all expected differences. For example, you can use 50 states, which are inserted in different orders or with different sequence start values. Their id will be different but they will work as expected.

Related links