diffChangeLog command

The diffChangeLog command allows you to receive information on differences between two databases you are comparing and creates a changelog file containing deployable changesets.

The diffChangeLog command points out the differences in general and generates changes to resolve most of them.

Uses

The diffChangeLog command is typically used when you want to create a deployable changelog to synchronize multiple databases. The diffChangeLog command also provides more information about:

  • Missing objects in your database
  • Changes made to your database
  • Unexpected items in your database

Running the diffChangeLog command

Running the diffChangeLog command requires two URLs:

  • referenceURL – the source for the comparison. The referenceURL attribute represents your source (reference) 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 (reference) database. You typically perform actions and run the commands and against this database.

To create a diff changelog:

  • The first option is to run the diffChangeLog command and pass the attributes needed for your source (reference) database and target database.

    • As an example, you can run the following:

    liquibase
--changeLogFile=dbchangelog.xml
--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>				
diffChangeLog

    Tip: If you want to create an SQL changelog file, add your database type name when specifying the changelog file: liquibase --changeLogFile=mychangelog.oracle.sql diffChangeLog. Replace .oracle.sql with your database type. When in doubt about your database type name, check Supported Databases.

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

    Note: It is best practice to put your driver .jar file in the LIQUIBASE_HOME/lib directory. If you put the driver file in any other directory, specify the path to it in the liquibase.properties file: classpath:../path_to_drivers/ojdbc<version>.jar. For more information, see Creating and configuring a liquibase.properties file. When running the command against two different databases, both drivers should be in the LIQUIBASE_HOME/lib directory. Otherwise, the classpath property should reference both .jar files. Use the path separator that is correct for your operating system (a semicolon on Windows, a colon on Mac or Linux).

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

  • Alternatively, configure the liquibase.properties file to include your connection information for both databases. Run the following command:
    • liquibase --changeLogFile=file_name.xml diffChangeLog

    Note: Replace file_name.xml with your filename and extension format. If you specify a file name that already exists, Liquibase will append your changes to the existing file.

    For information on how to configure your liquibase.properties file, see the Creating and configuring a liquibase.properties file topic.

Output

The diffChangeLog command produces a list of all Objects and creates a changelog with a list of changesets.

Liquibase Community diffChangeLog categories:

  • Catalog
  • Column
  • Foreign Key
  • Index
  • Primary Key
  • Schema
  • Sequence
  • Procedure
  • Unique Constraints
  • View
Liquibase Pro 3.8.1 by Datical licensed to Liquibase Pro Customer
Liquibase command 'diffChangeLog' was executed successfully.

Additional Functionality with Liquibase Pro

While Liquibase Community stores all changesets in a changelog, Liquibase Pro creates a directory called Objects and places the directory at the same level as your changelog. The Objects directory contains a subdirectory for each of the following stored logic types:

  • checkconstraint
  • package
  • packagebody
  • function
  • trigger
  • synonyms

Note: Not all database platforms support all stored logic types that are listed.

The diffChangeLog command structures stored logic files into timestamped directories every time you run the command.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">
	<changeSet  author="Administrator (generated)"  id="1571345362466-8">
		<pro:createTrigger  disabled="false"  path="objects/trigger/TS_T_EXEMPLAR_SEQEXEMPLAR.sql"  relativeToChangelogFile="true"  tableName="T_EXEMPLAR"  triggerName="TS_T_EXEMPLAR_SEQEXEMPLAR"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-9">
		<pro:createTrigger  disabled="false"  path="objects/trigger/ORDERS_BEFORE_INSERT4.sql"  relativeToChangelogFile="true"  tableName="orders"  triggerName="ORDERS_BEFORE_INSERT4"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-10">
		<pro:createTrigger  disabled="false"  path="objects/trigger/ORDERS_BEFORE_INSERT2.sql"  relativeToChangelogFile="true"  tableName="orders"  triggerName="ORDERS_BEFORE_INSERT2"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-11">
		<pro:createTrigger  disabled="false"  path="objects/trigger/ORDERS_BEFORE_INSERT.sql"  relativeToChangelogFile="true"  tableName="orders"  triggerName="ORDERS_BEFORE_INSERT"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-12">
		<createView  fullDefinition="true"  path="objects/view/OREDERS_VIEW.sql"  relativeToChangelogFile="true"  viewName="OREDERS_VIEW"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-13">
		<pro:createTrigger  disabled="false"  path="objects/trigger/ORDERS_BEFORE_INSERT3.sql"  relativeToChangelogFile="true"  tableName="orders"  triggerName="ORDERS_BEFORE_INSERT3"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-14">
		<createProcedure  path="objects/storedprocedure/P_CUSTOMER_HAS_NUM_FILM.sql"  procedureName="P_CUSTOMER_HAS_NUM_FILM"  relativeToChangelogFile="true"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-15">
		<createView  fullDefinition="true"  path="objects/view/V_CUSTOMER_HAS_FILM.sql"  relativeToChangelogFile="true"  viewName="V_CUSTOMER_HAS_FILM"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-16">
		<createProcedure  path="objects/storedprocedure/SP_CUSTOMER_SOCIAL_ACCTS.sql"  procedureName="SP_CUSTOMER_SOCIAL_ACCTS"  relativeToChangelogFile="true"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-17">
		<pro:createTrigger  disabled="false"  path="objects/trigger/TRI_BORROWING.sql"  relativeToChangelogFile="true"  tableName="T_BORROWING"  triggerName="TRI_BORROWING"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-18">
		<pro:createTrigger  disabled="false"  path="objects/trigger/TRU_BORROWING.sql"  relativeToChangelogFile="true"  tableName="T_BORROWING"  triggerName="TRU_BORROWING"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-19">
		<pro:createTrigger  disabled="false"  path="objects/trigger/TSU_T_EXEMPLAR_SEQEXEMPLAR.sql"  relativeToChangelogFile="true"  tableName="T_EXEMPLAR"  triggerName="TSU_T_EXEMPLAR_SEQEXEMPLAR"/>
	</changeSet>
	<changeSet  author="Administrator (generated)"  id="1571345362466-20">
		<pro:createFunction  functionName="F_CUSTOMER_HAS_NUM_FILM"  path="objects/function/F_CUSTOMER_HAS_NUM_FILM.sql"  relativeToChangelogFile="true"/>
	</changeSet>
</databaseChangeLog>

Note: Liquibase does not currently check data type length.

Starting from Liquibase 4.4, there are changes in how you can configure Liquibase or how you can type commands, including diffChangeLog. The Liquibase CLI functionality aims to be backward compatible with all previous versions. However, it is a best practice to follow the syntax rules on the Command Line Interface page and in the output of the --help command.

Running the diff-changelog command

Running the diff-changelog command requires two URLs:

  • referenceURL represents your source database which is the starting point and the basis for the database you want to compare.

  • url stands for your target database which you want to compare to the source (reference) database. You typically perform actions and run the commands against this database.

To compare two databases:

  • The first option is to run the diff-changelog command and pass the parameters needed for your source (reference) database and target database:
    • liquibase diff-changelog
--changelog-file=dbchangelog.xml
--url="jdbc:postgresql://<host>:<port>/<dbname>"
--username=<USERNAME>
--password=<PASSWORD>
--reference-url="jdbc:postgresql://<host>:<port>/<dbname>"
--reference-username=<USERNAME>
--reference-password=<PASSWORD>

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

    Tip: If you want to create an SQL changelog file, add your database type name when specifying the changelog file: liquibase diff-changelog --changelog-file=mychangelog.oracle.sql. Replace .oracle.sql with your database type. When in doubt about your database type name, check Supported Databases.

    Note: It is best practice to put your driver .jar file in the LIQUIBASE_HOME/lib directory. If you put the driver file in any other directory, specify the path to it in the liquibase.properties file: classpath:../path_to_drivers/ojdbc<version>.jar. For more information, see Creating and configuring a liquibase.properties file. When running the command against two different databases, both drivers should be in the LIQUIBASE_HOME/lib directory. Otherwise, the classpath property should reference both .jar files. Use the path separator that is correct for your operating system (a semicolon on Windows, a colon on Mac or Linux).

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

  • Alternatively, you can configure the liquibase.properties file to include the connection information for both databases:
    • liquibase.command.changelogFile: dbchangelog.xml
liquibase.command.url: jdbc:postgresql://<host>:<port>/<dbname1>
liquibase.command.username: <username>
liquibase.command.password: <password>
liquibase.command.referenceUrl: jdbc:postgresql://<host>:<port>/<dbname2>
liquibase.command.referenceUsername: <username>
liquibase.command.referencePassword: <password>

    Then run the following command:

    liquibase diff-changelog

    Note: For information on how to configure your liquibase.properties file, see the Creating and configuring a liquibase.properties file topic.

diff-changelog command attributes

Attribute Definition Requirement
--changelog-file The changelog file to write results Required
--default-catalog-name The default catalog name to use for the database connection Optional
--default-schema-name The default schema name to use for the database connection Optional
--diff-types Types of objects to compare Optional
--driver The JDBC driver class Optional
--driver-properties-file The JDBC driver properties file Optional
--exclude-objects Objects to exclude from diff Optional
--include-objects Objects to include in diff Optional
--password The target database password Required*
--reference-default-catalog-name The reference default catalog name to use for the database connection Optional
--reference-default-schema-name The reference default schema name to use for the database connection Optional
--reference-password The reference database password Required*
--reference-url The JDBC reference database connection URL Required
--reference-username The reference database username Required*
--schemas Schemas to include in diff Optional
--url The JDBC target database connection URL Required
--username The target database username Required*

Note: The username and password attributes are not required for connections and systems which use alternate means of authentication.

Tip: For a full list of global attributes, enter --help in your CLI or check the Liquibase Configuration Reference for Parameters and Environment Variables.

diff-changelog example output

When successful, the diff-changelog command produces the following output:

####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ##
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ##
##  Free schema change activity reports at        ##
##      https://hub.liquibase.com                 ##
##                                                ##
####################################################
Starting Liquibase at 13:54:22 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
Liquibase Version: 4.4.0
Liquibase Pro 4.4.0 by Datical licensed to support until Wed Sep 22 23:59:59 CDT 2021
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed.
Liquibase command 'diff-changelog' was executed successfully.