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

• Option 2: Configure the Liquibase properties file to include the connection information for both databases. Then, run the following command in the CLI:

liquibase diff 

For information, see Create and Configure a liquibase.properties File.

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 

Example output

Liquibase Version: 4.9.1
Liquibase Pro 4.9.1 by Liquibase licensed to Liquibase Pro Evaluation until Tue Jun 07 18:00:00 MDT 2022
WARNING!  Your license will expire in 27 days!
To renew Liquibase Pro please contact sales@liquibase.com or go to https://www.liquibase.org/download

Diff Results:
INFO This command might not yet capture Liquibase Pro additional object types on h2
Reference Database: DBUSER @ jdbc:h2:tcp://localhost:9090/mem:integration (Default Schema: PUBLIC)
Comparison Database: DBUSER @ jdbc:h2:tcp://localhost:9090/mem:dev (Default Schema: PUBLIC)
Compared Schemas: PUBLIC
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s):
INTEGRATION
name changed from 'INTEGRATION' to 'DEV'
Missing Check Constraint(s): NONE
Unexpected Check Constraint(s): NONE
Changed Check Constraint(s): NONE
Missing Column(s): NONE
Unexpected Column(s):
PUBLIC.COMPANY.ADDRESS1
PUBLIC.PERSON.ADDRESS1
PUBLIC.COMPANY.ADDRESS2
PUBLIC.PERSON.ADDRESS2
PUBLIC.DATABASECHANGELOG.AUTHOR
PUBLIC.COMPANY.CITY
PUBLIC.PERSON.CITY
PUBLIC.DATABASECHANGELOG.COMMENTS
PUBLIC.DATABASECHANGELOG.CONTEXTS
PUBLIC.PERSON.COUNTRY
PUBLIC.DATABASECHANGELOG.DATEEXECUTED
PUBLIC.DATABASECHANGELOG.DEPLOYMENT_ID
PUBLIC.DATABASECHANGELOG.DESCRIPTION
PUBLIC.DATABASECHANGELOG.EXECTYPE
PUBLIC.DATABASECHANGELOG.FILENAME
PUBLIC.COMPANY.ID
PUBLIC.DATABASECHANGELOG.ID
PUBLIC.DATABASECHANGELOGLOCK.ID
PUBLIC.PERSON.ID
PUBLIC.DATABASECHANGELOG.LABELS
PUBLIC.DATABASECHANGELOG.LIQUIBASE
PUBLIC.DATABASECHANGELOGLOCK.LOCKED
PUBLIC.DATABASECHANGELOGLOCK.LOCKEDBY
PUBLIC.DATABASECHANGELOGLOCK.LOCKGRANTED
PUBLIC.DATABASECHANGELOG.MD5SUM
PUBLIC.COMPANY.NAME
PUBLIC.PERSON.NAME
PUBLIC.DATABASECHANGELOG.ORDEREXECUTED
PUBLIC.DATABASECHANGELOG.TAG
Changed Column(s): NONE
Missing Database Package(s): NONE
Unexpected Database Package(s): NONE
Changed Database Package(s): NONE
Missing Database Package Body(s): NONE
Unexpected Database Package Body(s): NONE
Changed Database Package Body(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Function(s): NONE
Unexpected Function(s): NONE
Changed Function(s): NONE
Missing Index(s): NONE
Unexpected Index(s):
PRIMARY_KEY_5 UNIQUE  ON PUBLIC.PERSON(ID)
PRIMARY_KEY_6 UNIQUE  ON PUBLIC.COMPANY(ID)
PRIMARY_KEY_D UNIQUE  ON PUBLIC.DATABASECHANGELOGLOCK(ID)
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s):
CONSTRAINT_6 on PUBLIC.COMPANY(ID)
CONSTRAINT_8 on PUBLIC.PERSON(ID)
PK_DATABASECHANGELOGLOCK on PUBLIC.DATABASECHANGELOGLOCK(ID)
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Synonym(s): NONE
Unexpected Synonym(s): NONE
Changed Synonym(s): NONE
Missing Table(s): NONE
Unexpected Table(s):
COMPANY
DATABASECHANGELOG
DATABASECHANGELOGLOCK
PERSON
Changed Table(s): NONE
Missing Trigger(s): NONE
Unexpected Trigger(s): NONE
Changed Trigger(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

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

Example of a filtered output

Diff Results:
INFO This command might not yet capture Liquibase Pro additional object types on h2
Reference Database: DBUSER @ jdbc:h2:tcp://localhost:9090/mem:integration (Default Schema: PUBLIC)
Comparison Database: DBUSER @ jdbc:h2:tcp://localhost:9090/mem:dev (Default Schema: PUBLIC)
Compared Schemas: PUBLIC
Product Name: EQUAL
Product Version: EQUAL
Missing Index(s): NONE
Unexpected Index(s):
PRIMARY_KEY_5 UNIQUE  ON PUBLIC.PERSON(ID)
PRIMARY_KEY_6 UNIQUE  ON PUBLIC.COMPANY(ID)
PRIMARY_KEY_D UNIQUE  ON PUBLIC.DATABASECHANGELOGLOCK(ID)
Changed Index(s): NONE
Missing Table(s): NONE
Unexpected Table(s):
COMPANY
DATABASECHANGELOG
DATABASECHANGELOGLOCK
PERSON
Changed Table(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

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), camelCase (properties file), and ENVAR_CASE (environment variable).

--reference-url
liquibase.command.referenceUrl
LIQUIBASE_COMMAND_REFERENCE_URL

--url
liquibase.command.url
LIQUIBASE_COMMAND_URL

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

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

--diff-types
liquibase.command.diffTypes
LIQUIBASE_COMMAND_DIFF_TYPES

--driver
liquibase.command.driver
LIQUIBASE_COMMAND_DRIVER

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

--exclude-objects
liquibase.command.excludeObjects
LIQUIBASE_COMMAND_EXCLUDE_OBJECTS

--format
liquibase.command.format
LIQUIBASE_COMMAND_FORMAT

--include-objects
liquibase.command.includeObjects
LIQUIBASE_COMMAND_INCLUDE_OBJECTS

--output-schemas
liquibase.command.outputSchemas
LIQUIBASE_COMMAND_OUTPUT_SCHEMAS

--password
liquibase.command.password
LIQUIBASE_COMMAND_PASSWORD

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

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

--reference-driver
liquibase.command.referenceDriver
LIQUIBASE_COMMAND_REFERENCE_DRIVER

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

--reference-password
liquibase.command.referencePassword
LIQUIBASE_COMMAND_REFERENCE_PASSWORD

--reference-schemas
liquibase.command.referenceSchemas
LIQUIBASE_COMMAND_REFERENCE_SCHEMAS

--reference-username
liquibase.command.referenceUsername
LIQUIBASE_COMMAND_REFERENCE_USERNAME

--schemas
liquibase.command.schemas
LIQUIBASE_COMMAND_SCHEMAS

--username
liquibase.command.username
LIQUIBASE_COMMAND_USERNAME

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

• Best Practices for Using Diffs
• Diffing Multiple Schemas in Liquibase
• Maven diff