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 your driver, classpath, and URL for the database 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:
liquibase --outputFile=mySnapshot.json snapshot --snapshotFormat=json --schemas=lookup,public
When running the
snapshot command on multiple schemas, enter the
--schemas flag after the
liquibase.properties file sample:
changeLogFile: myChangelog.xml url: jdbc:postgresql://localhost:5432/MYDATABASE username: postgres password: password classpath: ../../Drivers/postgresql-42.2.8.jar liquibaseProLicenseKey: <ProKey> includeSchema: true
- 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:
liquibase --url=offline:postgresql?snapshot=mySnapshot.json --referenceUrl="jdbc:postgresql://localhost:5432/MYDATABASE" --referenceUsername=<USERNAME> --referencePassword=<PASSWORD> diff
Note: The format for the URL is the following:
offline:<db_type>?snapshot=<path/to/snapshot.json>. Use the name of your database type from the list of the supported databases in place of
<db_type> 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
- Run the
diffChangeLogcommand specifying the snapshot (an offline mode) and the database with new changes:
liquibase --referenceUrl=jdbc:postgresql://localhost:5432/MYDATABASE --url=offline:postgresql?snapshot=mySnapshot.json --changeLogFile=mydiffchangelog.xml diffChangeLog
The generated changelog will contain new changes. Now, you can compare and confirm those changes.
- Check the changes manually or by running automation tests, and then deploy them to the production.
- Use the
generateChangeLogcommand when you start using Liquibase with an existing project and want to sync your changes for different schemas. The command will create a changelog file with a sequence of changesets that describe 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
diffChangeLogcommand, 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.