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

In Liquibase Pro 4.25.0 and later, you can use diff to seamlessly generate a Drift Report for your databases.

Syntax

Running the diff  command requires two URLs:

  • reference-url – the source for the comparison. The --reference-url 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>
    --reference-url="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
    --reference-username=<USERNAME>
    --reference-password=<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 diff types, 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 types:

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

Liquibase Pro diff types:

  • 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 diff-types 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 diff-types are specified, all objects are considered.

Example: liquibase diff --diff-types=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.

Note: Syntax for each parameter is specified in kebab-case (CLI and flow file), camelCase (properties file and JAVA_OPTS), and ENVAR_CASE (environment variable).

Attribute Definition Requirement
--reference-url
liquibase.command.referenceUrl
LIQUIBASE_COMMAND_REFERENCE_URL

The JDBC reference database connection URL

Required
--url
liquibase.command.url
LIQUIBASE_COMMAND_URL

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

Required
--default-catalog-name
liquibase.command.defaultCatalogName
LIQUIBASE_COMMAND_DEFAULT_CATALOG_NAME

Name of the default catalog to use for the database connection

Optional
--default-schema-name
liquibase.command.defaultSchemaName
LIQUIBASE_COMMAND_DEFAULT_SCHEMA_NAME

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

Optional
--diff-types
liquibase.command.diffTypes
LIQUIBASE_COMMAND_DIFF_TYPES

Specifies the types of objects to compare. Specify multiple values as a comma-separated list (without spaces). Valid values are: catalogs, checkconstraints, columns, data, databasepackage, databasepackagebody, foreignkeys, functions, indexes, primarykeys, sequences, storedprocedures, triggers, uniqueconstraints, views.

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

Note: The diff types checkconstraints, databasepackage, databasepackagebody, functions, storedprocedures, and triggers require a valid Liquibase Pro license key to use.

Optional
--drift-severity
cmdArgs: {drift-severity: "val"}
liquibase.command.<cmdName>.driftSeverity
LIQUIBASE_COMMAND_<CMDNAME>_DRIFT_SEVERITY

Specifies the severity level of all error types in the drift report (changed, missing, or unexpected content). Valid values are INFO (0), MINOR (1), MAJOR (2), CRITICAL (3), and BLOCKER (4), from least severe to most severe. If both --drift-severity and one of the individual error type arguments are set, the individual error type arguments take priority. Default: INFO.

Optional
--drift-severity-changed
cmdArgs: {drift-severity-changed: "val"}
liquibase.command.<cmdName>.driftSeverityChanged
LIQUIBASE_COMMAND_<CMDNAME>_DRIFT_SEVERITY_CHANGED

Specifies the severity level of changed content in the drift report. Valid values are INFO (0), MINOR (1), MAJOR (2), CRITICAL (3), and BLOCKER (4), from least severe to most severe. Overrides the value of --drift-severity for changed content only. Default: INFO.

Optional
--drift-severity-missing
cmdArgs: {drift-severity-missing: "val"}
liquibase.command.<cmdName>.driftSeverityMissing
LIQUIBASE_COMMAND_<CMDNAME>_DRIFT_SEVERITY_MISSING

Specifies the severity level of missing content in the drift report. Valid values are INFO (0), MINOR (1), MAJOR (2), CRITICAL (3), and BLOCKER (4), from least severe to most severe. Overrides the value of --drift-severity for missing content only. Default: INFO.

Optional
--drift-severity-unexpected
cmdArgs: {drift-severity-unexpected: "val"}
liquibase.command.<cmdName>.driftSeverityUnexpected
LIQUIBASE_COMMAND_<CMDNAME>_DRIFT_SEVERITY_UNEXPECTED

Specifies the severity level of unexpected content in the drift report. Valid values are INFO (0), MINOR (1), MAJOR (2), CRITICAL (3), and BLOCKER (4), from least severe to most severe. Overrides the value of --drift-severity for unexpected content only. Default: INFO.

Optional
--driver
liquibase.command.driver
LIQUIBASE_COMMAND_DRIVER

The JDBC driver class

Optional
--driver-properties-file
liquibase.command.driverPropertiesFile
LIQUIBASE_COMMAND_DRIVER_PROPERTIES_FILE

The JDBC driver properties file

Optional
--exclude-objects
liquibase.command.excludeObjects
LIQUIBASE_COMMAND_EXCLUDE_OBJECTS

Objects to exclude from diff

Optional
--format
liquibase.command.format
LIQUIBASE_COMMAND_FORMAT

Option to create JSON output. See diff JSON.

Optional
--include-objects
liquibase.command.includeObjects
LIQUIBASE_COMMAND_INCLUDE_OBJECTS

Objects to include in diff

Optional
--open-report
cmdArgs: {open-report: "val"}
liquibase.command.<cmdName>.openReport
LIQUIBASE_COMMAND_<CMDNAME>_OPEN_REPORT

If true, automatically opens the report in your default browser. Default: false.

Optional
--output-schemas
liquibase.command.outputSchemas
LIQUIBASE_COMMAND_OUTPUT_SCHEMAS

Lets you replace the schemas in the output changelog. This is a CSV list. The parameter size must match --schemas. If you have three items names in --schemas, you must also have three items in --output-schemas.

Example: liquibase generate-changelog --schemas=a,b,c --output-schemas=d,e,f

Optional
--password
liquibase.command.password
LIQUIBASE_COMMAND_PASSWORD

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

Optional
--reference-default-catalog-name
liquibase.command.referenceDefaultCatalogName
LIQUIBASE_COMMAND_REFERENCE_DEFAULT_CATALOG_NAME

The reference default catalog name to use for the database connection

Optional
--reference-default-schema-name
liquibase.command.referenceDefaultSchemaName
LIQUIBASE_COMMAND_REFERENCE_DEFAULT_SCHEMA_NAME

The reference default schema name to use for the database connection

Optional
--reference-driver
liquibase.command.referenceDriver
LIQUIBASE_COMMAND_REFERENCE_DRIVER

The JDBC driver class for the reference database

Optional
--reference-driver-properties-file
liquibase.command.referenceDriverPropertiesFile
LIQUIBASE_COMMAND_REFERENCE_DRIVER_PROPERTIES_FILE

The JDBC driver properties file for the reference database

Optional
--reference-liquibase-catalog-name
liquibase.command.referenceLiquibaseCatalogName
LIQUIBASE_COMMAND_REFERENCE_LIQUIBASE_CATALOG_NAME

Reference database catalog to use for Liquibase objects. Liquibase 4.24.0+.

Optional
--reference-liquibase-schema-name
liquibase.command.referenceLiquibaseSchemaName
LIQUIBASE_COMMAND_REFERENCE_LIQUIBASE_SCHEMA_NAME

Reference database schema to use for Liquibase objects. Liquibase 4.24.0+.

Optional
--reference-password
liquibase.command.referencePassword
LIQUIBASE_COMMAND_REFERENCE_PASSWORD

The reference database password.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

Optional
--reference-schemas
liquibase.command.referenceSchemas
LIQUIBASE_COMMAND_REFERENCE_SCHEMAS

Schemas names on reference database to use in diff. This is a CSV list.

Optional
--reference-username
liquibase.command.referenceUsername
LIQUIBASE_COMMAND_REFERENCE_USERNAME

The reference database username.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

Optional
--report-enabled
cmdArgs: {report-enabled: "val"}
liquibase.command.<cmdName>.reportEnabled
LIQUIBASE_COMMAND_<CMDNAME>_REPORT_ENABLED

Enables a report at the command level. Overrides the global argument --reports-enabled. Default: false.

Optional
--report-name
cmdArgs: {report-name: "val"}
liquibase.command.<cmdName>.reportName
LIQUIBASE_COMMAND_<CMDNAME>_REPORT_NAME

Specifies the name of the report file at the command level. Overrides the global argument --reports-name. By default, Liquibase generates a new report file labeled with a timestamp (user's local time). If you set a custom name, Liquibase overwrites the existing file every time you generate a new report. Default: report-<day-month-year-timecode>.html.

Optional
--report-path
cmdArgs: {report-path: "val"}
liquibase.command.<cmdName>.reportPath
LIQUIBASE_COMMAND_<CMDNAME>_REPORT_PATH

Specifies the file path to the report file at the command level. Overrides the global argument --reports-path. Default: ./.

Optional
--schemas
liquibase.command.schemas
LIQUIBASE_COMMAND_SCHEMAS

Schemas to include in diff

Optional
--username
liquibase.command.username
LIQUIBASE_COMMAND_USERNAME

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

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.

Drift report

In Liquibase 4.25.0 and later, you can automatically generate a Drift Report using the diff command. The drift report requires a Liquibase Pro license key.

Related links