diff

The diff command in Liquibase allows you to compare two databases of the same type, or different types, to one another.

Uses

The diff command is typically used at the completion of a project to verify all expected changes are in the changelog or to detect drift between a model schema and a database's actual schema. The diff command is also useful for the following tasks:

  • Finding missing objects between one database and another
  • Seeing that a change was made to your database
  • Finding unexpected items in your database

Syntax

Running the diff  command requires two URLs:

  • referenceURL – the source for the comparison. The referenceUrl attribute represents your source (reference) database, which is the basis for the database you want to compare.
  • url – the target of the comparison. The url attribute represents your target database, which you want to compare to the source (reference) database. You typically perform actions and run commands on this database.

To compare two databases:

  • Option 1: Run the diff command with all necessary attributes in the CLI:
  • liquibase diff 
    --url="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
    --username=<USERNAME>
    --password=<PASSWORD>
    --referenceUrl="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
    --referenceUsername=<USERNAME>
    --referencePassword=<PASSWORD>
    

    Tip: For best results, specify all commands and parameters in the --kebab-case format in the CLI. If your preference is camelCase, it also works in the CLI.

    See the snapshot command topic for an example of using a snapshot file as one of the databases being used in the command.

    Tip: Liquibase recommends that you place your database's JDBC driver JAR file in the liquibase/lib directory. If you place the driver file in a different directory, specify the path in the properties file: classpath:../<path-to-drivers>/ojdbc<version>.jar. For more information, see Create and Configure a liquibase.properties File. When you run the diff command against two databases, either the drivers for both must be in the liquibase/lib directory or the classpath property must reference both JAR files. Use the appropriate path separator for your operating system: for Windows, use a semicolon; for Mac or Linux, use a colon.

    Example: classpath: ojdbc7.jar:postgresql-42.2.8.jar

By default, the result is sent to STDOUT, which provides flexibility to use the result in other tools or in a processing pipeline. You can also have your output in a file using the --output-file=<filename> attribute.

liquibase diff --output-file=myfile.json 

The diff command produces a list of categories along with one of the following descriptions:

  • Missing: there are objects on your source database (referenceURL) that are not on your target database (URL).
  • Unexpected: there are objects on your target database (URL) that are not on your source database (referenceURL).
  • Changed: the object as it exists on the source database (referenceURL) is different than as it exists in the target database (URL).

Note: The changed description will not specify the type of change applied to your database. Run the diff-changelog command to generate a changelog that will apply the changes to the target database.

Liquibase Open Source diff categories:

  • Catalog
  • Column
  • Foreign Key
  • Index
  • Primary Key
  • Schema
  • Sequence
  • Unique Constraints
  • View

Liquibase Pro diff categories:

  • Check Constraint
  • Package
  • Package Body
  • Procedure
  • Function
  • Trigger
  • Synonyms

Note: Liquibase does not currently check datatype length.

Filtering diff types

Liquibase allows you to use diffTypes attribute to filter the types of objects you want to compare. Multiple filters can be added to the attribute as a comma-separated list. If no diffTypes are specified, all objects are considered.

Example: liquibase diff --diffTypes=tables,indexes,views

Command arguments

Tip: For best results, specify all commands and parameters in the --kebab-case format in the CLI. If your preference is camelCase, it also works in the CLI.

Attribute Definition Requirement
--reference-url

The JDBC reference database connection URL

Required
--reference-username

The reference database username

Required
--reference-password

The reference database password

Required
--url

The JDBC database connection URL. See Using JDBC URL in Liquibase.

Required
--username*

Username to connect to the target database

Required*
--password*

Password to connect to the target database

Required*
--diff-types

Specifies the types of objects to compare. Includes a list of diff types in a changelog file expressed as a comma-separated list (without spaces): catalogs, tables, functions, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints, data, storedprocedures, triggers, sequences, databasepackage, databasepackagebody.

If null, default types are tables, views, columns, indexes, foreignkeys, primarykeys, uniqueconstraints.

Optional
--driver

The JDBC driver class

Optional
--driver-properties-file

The JDBC driver properties file

Optional
--exclude-objects

Objects to exclude from diff

Optional
--format

Option to create JSON output. See diff JSON.

Optional
--include-objects

Objects to include in diff

Optional
--schemas

Schemas to include in diff

Optional

Note: The username and password attributes are not required for connections and systems which use alternate means of authentication. Also, you can specify database credentials as part of the url attribute.

Related links