Database diff tool

Liquibase | Database Refactoring
November 30, 2019 – 09:53 am
Top 10 free database tools for sys admins

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 -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 With the Partitioning, OLAP and Data Mining options' Target: 'Oracle Database 10g Express Edition Release' 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

Related Posts