Maven `diff`

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

Uses

diff 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 goal 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

Maven configuration

You can configure Liquibase Maven in multiple ways. You can pass arguments like <changeLogFile> in Maven directly in your pom.xml file, in a liquibase.properties file referenced in your POM, as environment variables, or as JVM system properties.

Tip: For more information about the Maven configuration, see Configuring Maven.

Running the `diff` Maven goal

Running the diff goal requires two URLs:

referenceURL – the source for the comparison. The referenceURL attribute represents your source database which is the starting point and the basis for the database you want to compare.
URL – the target of the comparison. The url attribute stands for your target database which you want to compare to the source database. You typically perform actions and run the commands against this database.

To compare two databases, choose one of the following options:

The first option is to specify all attributes for the diff goal in your pom.xml file under the <configuration></configuration> section:

<changeLogFile>specify your changelog file</changeLogFile>
<driver>specify the name of the driver class to use to connect to the target  database</driver>
<url>specify your JDBC connection for the target database</url>
<username>specify your username for the target database</username>
<password>specify your password for the target database</password>
<referenceDriver>specify the name of the driver class to use to connect to the reference database</referenceDriver>
<referenceUrl>specify your JDBC connection for the source database</referenceUrl>
<referenceUsername> specify your username for the source database </referenceUsername>
<referencePassword> specify your password for the source database </referencePassword>
<goal>diff</goal>

The second option is to run the diff command and pass the attributes needed for your source database and target database. You can do this by running the following:

mvn liquibase:diff
-Dliquibase.changeLogFile=myChangeLog.xml
-Dliquibase.driver=oracle.jdbc.OracleDriver
-Dliquibase.url="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
-Dliquibase.username=<USERNAME>
-Dliquibase.password=<PASSWORD>
-Dliquibase.referenceUrl="jdbc:oracle:thin:@<IP OR HOSTNAME>:<PORT>:<SERVICE NAME OR SID>"
-Dliquibase.referenceUsername=<USERNAME>
-Dliquibase.referencePassword=<PASSWORD>

The third option is to configure the Liquibase properties file to include your user authentication information for both databases. After you include the necessary information in the properties file, add <propertyFile>properties-file-name</propertyFile> to your pom.xml file under the plugin <configuration></configuration> section, where properties-file-name represents the name of the Liquibase properties file, like liquibase.properties. Next, run the following command:

mvn liquibase:diff -Dliquibase.outputFile=mydiff.txt

Note: Running the diff goal requires a Maven project to be implemented. The --outputFile=mydiff.txt attribute will save the output to a file. If you do not specify this, you will see the output in your command line.

To generate a changelog that applies changes after the comparison of databases to the target database, run the diff goal with the diffChangeLogFile attribute, as follows:

mvn liquibase:diff -Dliquibase.diffChangeLogFile=mydiff.xml

Note: Enter the name and the extension format you want to use in place of mydiff.xml. Alternatively, you can add <diffChangeLogFile>specify the changelog file name here</diffChangeLogFile> to your pom.xml file.

Output

The diff goal 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 attribute 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
Procedure
Unique Constraints
View

Liquibase Pro diff categories:

Check Constraint
Package
Package Body
Function
Trigger
Synonyms

When successful, the diff Maven goal produces the following output:

[INFO] Scanning for projects...
[INFO]
[INFO] -------------------< com.example:liquibase-project >--------------------
[INFO] Building liquibase-project 05XII19
[INFO] --------------------------------[ jar ]---------------------------------
[INFO]
[INFO] --- liquibase-maven-plugin:4.1.1:diff (default-cli) @ liquibase-project ---
[INFO] ------------------------------------------------------------------------
[INFO] Successfully installed license from Base64 string starting with 'ABwwGgQUvH' (property liquibaseProLicenseKey).
[INFO] Liquibase Pro 4.1.1 by Datical licensed to support until Wed Sep 22 23:59:59 CDT 2021
[INFO] Liquibase Pro 4.1.1 by Datical licensed to support until Wed Sep 22 23:59:59 CDT 2021
[INFO] ####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Get advanced features and support at          ##
##      liquibase.com/support                     ##
##                                                ##
####################################################
Starting Liquibase at 06:47:21 (version 4.1.1 #10 built at 2020-10-12 19:24+0000)
[INFO] Parsing Liquibase Properties File local.liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:oracle:thin:@localhost:1521/ORCL
[INFO] Performing Diff on database MVN_UAT @ jdbc:oracle:thin:@localhost:1521/ORCL (Default Schema: MVN_UAT)
Diff Results:
Reference Database: MVN_DEV @ jdbc:oracle:thin:@localhost:1521/ORCL (Default Schema: MVN_DEV)
Comparison Database: MVN_UAT @ jdbc:oracle:thin:@localhost:1521/ORCL (Default Schema: MVN_UAT)
Compared Schemas: MVN_DEV -> MVN_UAT
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Check Constraint(s): NONE
Unexpected Check Constraint(s): NONE
Changed Check Constraint(s): NONE
Missing Column(s): NONE
Unexpected Column(s):
MVN_UAT.DATABASECHANGELOG.AUTHOR
MVN_UAT.DATABASECHANGELOG.COMMENTS
MVN_UAT.DATABASECHANGELOG.CONTEXTS
MVN_UAT.DATABASECHANGELOG.DATEEXECUTED
MVN_UAT.DATABASECHANGELOG.DEPLOYMENT_ID
MVN_UAT.DATABASECHANGELOG.DESCRIPTION
MVN_UAT.DATABASECHANGELOG.EXECTYPE
MVN_UAT.DATABASECHANGELOG.FILENAME
MVN_UAT.DATABASECHANGELOG.ID
MVN_UAT.DATABASECHANGELOGLOCK.ID
MVN_UAT.DATABASECHANGELOG.LABELS
MVN_UAT.DATABASECHANGELOG.LIQUIBASE
MVN_UAT.DATABASECHANGELOGLOCK.LOCKED
MVN_UAT.DATABASECHANGELOGLOCK.LOCKEDBY
MVN_UAT.DATABASECHANGELOGLOCK.LOCKGRANTED
MVN_UAT.DATABASECHANGELOG.MD5SUM
MVN_UAT.DATABASECHANGELOG.ORDEREXECUTED
MVN_UAT.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):
PK_DATABASECHANGELOGLOCK UNIQUE  ON MVN_UAT.DATABASECHANGELOGLOCK(ID)
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s):
PK_DATABASECHANGELOGLOCK on MVN_UAT.DATABASECHANGELOGLOCK(ID)
Changed Primary Key(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):
DATABASECHANGELOG
DATABASECHANGELOGLOCK
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
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  28.102 s
[INFO] Finished at: 2020-11-25T06:47:42-06:00
[INFO] ------------------------------------------------------------------------

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: mvn liquibase:diff -Dliquibase.diffTypes=tables,indexes,views

`diff` optional Maven configuration attributes

Attribute	Definition
`liquibase.changeLogDirectory`	Specifies the path to the directory where Liquibase can find your changelog file.
`liquibase.changeLogFile`	The changelog file for Liquibase to use.
`liquibase.changelogCatalogName`	Specifies the catalog Liquibase will use to create your changelog tables.
`liquibase.changelogSchemaName`	Specifies the schema Liquibase will use to create your changelog tables.
`liquibase.clearCheckSums`	Boolean. Forces checksums to be cleared from the DATABASECHANGELOG table. Default value is: false.
`liquibase.contexts`	Specifies which contexts Liquibase will execute, which can be separated by a comma if multiple contexts are required. If a context is not specified, then all contexts will be executed.
`liquibase.databaseChangeLogLockTableName`	Specifies the table name to use for the DATABASECHANGELOGLOCK table.
`liquibase.databaseChangeLogTableName`	Specifies the table name to use for the DATABASECHANGELOG table.
`liquibase.databaseClass`	Specifies the database object class.
`liquibase.defaultCatalogName`	Specifies the default catalog name to use for the database connection.
`liquibase.defaultSchemaName`	Specifies the default schema name to use for the database connection.
`liquibase.diffChangeLogFile`	Defines whether the created changelog needs to fix differences between the two databases. If the file exists, changes are appended to that file. If this is null, a comparison report is sent to STDOUT.
`liquibase.diffExcludeObjects`	Specifies objects to be excluded from the changelog. Example filters: `"table_name"`, `"table:main_."`, `"column:._lock, table:primary.*"`.
`liquibase.diffIncludeCatalog`	Boolean. Includes the catalog in the `diff` output. If this is null, then the catalog will not be included.
`liquibase.diffIncludeObjects`	Specifies objects to be included in the changelog. Example filters: `"table_name"`, `"table:main_."`, `"column:._lock, table:primary.*"`.
`liquibase.diffIncludeSchema`	Boolean. Includes the schema in the `diff` output. If this is null, then the schema will not be included.
`liquibase.diffIncludeTablespace`	Boolean. Includes the tablespace in the `diff` output. If this is null, then the tablespace will not be included.
`liquibase.diffTypes`	Specifies the list of `diff` types to include in the changelog expressed as a comma-separated list from: `tables` `views` `columns` `indexes` `foreignkeys` `primarykeys` `uniqueconstraints` `data` If this is null, then the default types will be: `tables` `views` `columns` `indexes` `foreignkeys` `primarykeys` `uniqueconstraints`
`liquibase.driver`	Specifies the driver class name to use for the database connection.
`driverPropertiesFile`	Specifies the location of a JDBC connection properties file which contains properties the driver will use.
`liquibase.emptyPassword`	Deprecated. Boolean. Use an empty or null value for the password instead. Default value is: false.
`expressionVariables`	Specifies a map-based collection of changelog properties to apply.
`expressionVars`	Specifies a property-based collection of changelog properties to apply.
`liquibase.format`	Specifies the format in which to display the `diff` output. This format can be TXT or JSON.
`liquibase.includeArtifact`	Boolean. Includes a Maven project artifact in the class loader, which obtains the Liquibase properties file and changelog files. Default value is `true`.
`liquibase.includeTestOutputDirectory`	Boolean. Includes the Maven test output directory in the class loader which obtains the Liquibase properties file and changelog files. Default value is `true`.
`liquibase.labels`	Specifies which labels Liquibase runs, and which can be separated by a comma if multiple labels are required or you need to designate a more complex expression. If no label is specified, then all labels are run.
`liquibase.licenseKey`	Specifies your Liquibase Pro license key.
`liquibase.outputDefaultCatalog`	Boolean. Specifies whether to ignore the catalog or database name. Default value is: false.
`liquibase.outputDefaultSchema`	Boolean. Specifies whether to ignore the schema name. Default value is: false.
`liquibase.outputFile`	Writes the `diff` output to a file.
`liquibase.outputFileEncoding`	Indicates that you want to set the character encoding of the output file during the `updateSQL` phase.
`liquibase.outputSchemas`	Outputs schema names. This is a CSV list.
`liquibase.password`	Specifies the database password for the database connection.
`liquibase.promptOnNonLocalDatabase`	Deprecated. Boolean. Controls whether users are prompted before executing changesets to a non-local database. Default value is: true.
`liquibase.propertyFile`	Specifies the Liquibase properties file to use for configuration, like `liquibase.properties`.
`liquibase.propertyFileWillOverride`	Boolean. Indicates that you want the `liquibase.properties` file to override any settings provided in the Maven plugin configuration. By default, if a property is explicitly specified, it is not overridden if it also appears in the properties file. Default value is: false.
`liquibase.propertyProviderClass`	Specifies the property provider, which must be a `java.util.Properties` implementation.
`liquibase.referenceDefaultCatalogName`	Specifies the reference database catalog.
`liquibase.referenceDriver`	Specifies the fully qualified name of the driver class to use to connect to the reference database. If this is not specified, then the driver will be used instead.
`liquibase.referencePassword`	Specifies the reference database password to use to connect to the specified database. If this is null, then an empty password will be used.
`liquibase.referenceSchemas`	Specifies schemas names on a reference database to use in `diff`. This is a CSV list.
`liquibase.referenceServer`	Specifies the server id in `settings.xml` to use when authenticating.
`liquibase.referenceUrl`	Specifies the reference database URL to connect to for executing Liquibase.
`liquibase.referenceUsername`	Specifies the reference database username to use to connect to the specified database.
`liquibase.schemas`	Specifies schemas on a target database to use in `diff`. This is a CSV list.
`liquibase.server`	Specifies the server ID in the Maven `settings.xml` to use when authenticating.
`liquibase.skip`	Boolean. Specifies whether to skip running Liquibase. The use of this attribute is not recommended but can be used when needed. Default value is: false.
`systemProperties`	Specifies a list of system properties you want to pass to the database.
`liquibase.url`	Specifies the database URL you want to use to execute Liquibase.
`liquibase.username`	Specifies the database username for the database connection.
`liquibase.verbose`	Boolean. Controls the amount of output detail when you call the plugin. Default value is: false.

Maven diff

Uses

Maven configuration

Running the diff Maven goal

Filtering diff types

diff optional Maven configuration attributes

Maven `diff`

Running the `diff` Maven goal

Filtering `diff` types

`diff` optional Maven configuration attributes