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.
- Run the
snapshotcommand to capture the state of the database containing different schemas:
- Manually make some changes to the target database on the different schemas.
- Run the
diffcommand specifying the snapshot (an offline mode) and the database with new changes:
- Run the
diff-changelogcommand specifying the snapshot (an offline mode) and the database with new changes:
- Check the changes manually or by running automation tests, and then deploy them to the production.
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
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
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
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.
- Use the
generate-changelogcommand 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-changelogcommand, you can identify the change and generate a changelog update.
- Document your changes. Run the
diffcommand 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.
Diffcommands 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.