Originally published at: Comparing Two States of the Same Database Schema | Liquibase Blog
Using Liquibase diff commands gives you a powerful way to compare two database schema states.
Diff commands are handy when you need to examine an unexpected change. Let’s say you created two tables in your database. When you compare the previous state (before you added the tables) to the database state after you added the tables, sometimes you’ll find that the change didn’t go the way you had planned. The diff command will let you see that. (There are some other things to take into account when using diff in general. See our blog, Diffs: The Good, the bad, and the ugly of comparing databases.)
Even better, if you know that you want to apply the schema from the database that you know has the state you want, you can create a deployable changelog with the differences between the two states using the diffChangeLog command.
Many times, developers use these commands across several different environments. But what if there is currently just one environment being worked on that needs to be examined? It feels like you would only have one schema, so what do you compare it with? I’ll walk you through the steps you can take to compare the state you have with the state you want and then apply the changes with Liquibase.
Workflow
1. Take a snapshot of the database schema prior to making the changes.
liquibase --defaultSchemaName=schema_dev --outputFile=schema_dev_before.json snapshot --snapshotFormat=json
2. Apply any schema changes like you normally would.
3. Run the diff command.
The diff command will compare the snapshot schema_dev_before.json to your current schema schema_dev.
liquibase --url=offline:oracle?snapshot=schema_dev_before.json --referenceUrl=jdbc:oracle:thin:@hostname.net:1521:myServiceName --referenceDefaultSchemaName=schema_dev diff
4. Check for differences.
You’ll see a list containing all the differences between the schema_dev_before.json snapshot file and the current state of your schema schema_dev. For example, if you had a change adding a new table named “newtable”, the diff list will display that.
Missing Table(s): newtable
5. If desired, write these differences in a deployable changelog.
Use the diffChangeLog command to generate a changelog that contains the differences as changesets.
liquibase --url=offline:oracle?snapshot=schema_dev_before.json --referenceUrl=jdbc:oracle:thin:@hostname.net:1521:myServiceName --referenceDefaultSchemaName=schema_dev --changeLogFile=schema_dev_diff.xml diffChangeLog
6. Update the original schema.
Use the liquibase update command to apply the new changelog to the original schema.
Summing it up
Liquibase has powerful comparison features that allow you to fit them into a lot of different workflows. If you have questions or run into issues, be sure to check out our forum and our chat room. If you’re looking for more dedicated support options, consider starting a free trial of Liquibase Pro and trying out our support (and more advanced features).