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.
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.
Currently, Liquibase runs the following comparisons:
Data differences (limited), not checked by default
It does not (currently) check
Non-foreign key constraints (check, etc)
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:
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.
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.
In report mode, a description of the differences between two databases is reported to standard out.
In change log mode, the an XML change log of what is necessary to upgrade the base database to the target database is sent to standard out. This change log can be included as is, or copied into an existing change log. If the diff command is passed an existing change log file, the new change sets will be appended to the end of the file.
Database objects to include in the changelog can be controlled with the includeObjects or excludeObjects parameters. (since 3.3.2)
The format supported is:
- An object name (actually a regexp) will match any object whose name matches the regexp.
- A type:name syntax that matches the regexp name for objects of the given type
- If you want multiple expressions, comma separate them
- The type:name logic will be applied to the tables containing columns, indexes, etc.
NOTE: name comparison is case sensitive. If you want insensitive logic, use the (?i) regexp flag.
- “table_name” will match a table called “table_name” but not “other_table” or “TABLE_NAME”
- “(i?)table_name” will match a table called “table_name” and “TABLE_NAME”
- “table_name” will match all columns in the table table_name
- “table:table_name” will match a table called table_name but not a column named table_name
- “table:table_name, column:*._lock” will match a table called table_name and all columns that end with “_lock”