Database diff tool
While the best way to track database changes is by adding change sets during development (see the problem with database diffs), there are times when being able to perform database diffs is valuable, particularly near the end of a project as a double-check that all required changes are included in the change log.
Running Diff
Diff command support is available through the command_line and ant tools. When diff-ing databases, you specify the target database like you normally do in Liquibase (–url, –username, etc. flags) and you specify the base database with additional flags after the command name.
Example
liquibase.sh -driver=oracle.jdbc.OracleDriver \ -url=jdbc:oracle:thin:@testdb:1521:test \ -username=bob \ -password=bob \ diff \ -referenceUrl=jdbc:oracle:thin:@localhost/XE \ -referenceUsername=bob \ -referencePassword=bob
Database Comparisons
Currently, Liquibase runs the following comparisons:
- Version Differences
- Missing/unexpected tables
- Missing/unexpected views
- Missing/unexpected columns
- Missing/unexpected primary keys
- Missing/unexpected unique constraints
- Missing/unexpected foreign Keys
- Missing/unexpected sequences
- Missing/unexpected indexes
- Column definition differences (data type, auto-increment, etc.)
- View definition differences
- Data differences (limited), not checked by default
It does not (currently) check
- Non-foreign key constraints (check, etc)
- Stored Procedures
- Data type length
Liquibase can diff different database types, but the results may be skewed due to differences in case and data types.
Controlling Checks (since 1.8)
What changes are checked for can be controlled with the diffTypes parameter to the diff commands. The following options are available and can be passed as a comma-separated list:
- tables [DEFAULT]
- columns [DEFAULT]
- views [DEFAULT]
- primaryKeys [DEFAULT]
- indexes [DEFAULT]
- foreignKeys [DEFAULT]
- sequences [DEFAULT]
- data
If no diffTypes are specified, the checks marked DEFAULT will be run.
Note: This only works with the “generateChangeLog” command, not the “diff” or “diffChangeLog” commands.
Output Modes
Liquibase supports two output modes: report mode (“diff”) and change log mode (“diffChangeLog”). In both modes, diff progress is reported to standard error during execution.
Report Mode
In report mode, a description of the differences between two databases is reported to standard out.
Base Database: BOB jdbc:oracle:thin:@testdb:1521:latest Target Database: BOB jdbc:oracle:thin:@localhost/XE Product Name: EQUAL Product Version: Base: 'Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 With the Partitioning, OLAP and Data Mining options' Target: 'Oracle Database 10g Express Edition Release 10.2.0.1.0' Missing Tables: NONE Unexpected Tables: NONE Missing Views: NONE Unexpected Views: NONE Missing Columns: CREDIT.MONTH CREDIT.COMPANY CMS_TEMPLATE.CLASSTYPE CONTENTITEM.SORTORDER Unexpected Columns: CATEGORY.SORTORDER Missing Foreign Keys: NONE Unexpected Foreign Keys: FK_NAME (ID_VC -> STATUS_ID_VC) Missing Primary Keys: NONE Unexpected Primary Keys: NONE Missing Indexes: NONE Unexpected Indexes: NONE Missing Sequences: NONE Unexpected Sequences: NONE