diff JSON

Note: This is a Liquibase Pro command, so you need a Liquibase Pro License Key to use it.

Starting with Liquibase 3.9.0, you can automate drift detection at scale in your database schemas with the Liquibase Pro machine-readable JSON diff output. The diff --format=json command is a Liquibase Pro extension to the Liquibase Community diff command.

Uses

The diff command in a JSON format is typically used to detect drift between a model schema and a database's actual schema. You can use the output of the diff command in a JSON format as an input to automation processes. For example, the results in a JSON diff can be parsed in your build system to trigger alerts, generate reports, or run the diff-changelog command.

After you run the command, a JSON-structured object lists the differences between the databases, as well as the values that are configured in your Liquibase properties or Maven POM file, or are passed as command-line arguments under the url and referenceUrl keys.

Running the diff JSON command

Running the diff JSON 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 against this database.

To compare two databases or schemas:

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

liquibase diff 
--format=json
--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: Liquibase recommends placing the driver .jar file in the LIQUIBASE_HOME/lib directory. If you place the driver file in a different directory, specify the path in the properties file, as follows: classpath:../<path-to-drivers>/ojdbc<version>.jar. For more information, see Specifying Properties in a Connection Profile.
When running the diff command against two databases, either the drivers for both must reside in the LIQUIBASE_HOME/lib directory or the classpath property must reference both .jar files. Use the appropriate path separator for your operating system, as follows: for Windows, use a semicolon; for Mac or Linux, use a colon.

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

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

Alternatively, you can configure the Liquibase properties file to include the connection information for both databases, and then run the following command: 

liquibase diff --format=json

Tip: All commands and parameters use the --kebab-case format in the CLI environment. This is the format Liquibase recommends for best results. If your preference is camelCase, it will still work in the CLI.

Note: For more information, see Specifying Properties in a Connection Profile.

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 --output-file=myfile.json diff --format=json

Using the diff JSON command

The diff JSON 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 Pro diff JSON categories:

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

Also, Liquibase Pro includes Liquibase Community diff categories when running --format=json:

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

Note: Liquibase does not currently check datatype length.

Filtering diff types

Liquibase allows you to use diffType 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 --diffTypes=tables,indexes,views diff 

Starting Liquibase at 13:54:07 (version 4.4.0 #11 built at 2021-06-09 16:36+0000)
Liquibase Version: 4.4.0
Liquibase Pro 4.4.0 by Liquibase Enterprise licensed to support until Wed Sep 22 23:59:59 CDT 2021
{
  "diff": {
	"diffFormat": 1,
	"created": "Mon Jul 19 13:54:08 CDT 2021",
	"databases": {
	  "reference": {
	  "majorVersion": "13",
	  "minorVersion": "2 (Debian 13",
	  "name": "PostgreSQL",
	  "url": "jdbc:postgresql://example.net:5432/dev"
	},
	"target": {
	  "majorVersion": "13",
	  "minorVersion": "2 (Debian 13",
	  "name": "PostgreSQL",
	  "url": "jdbc:postgresql://example.net:5432/test"
	}
  },
  "missingObjects": [
	{
	  "missingObject": {
		"name": "contract_bank_accounts",
		"type": "table",
		"schemaName": "public",
		"catalogName": "dev"
	   }
	 },
	 {
	   "missingObject": {
		 "name": "firstname",
		 "type": "column",
		 "relationName": "contract_bank_accounts",
		 "schemaName": "public",
		 "catalogName": "dev"
	    }
	  },
	  {
		"missingObject": {
		  "name": "pay_by_quarter",
		  "type": "column",
		  "relationName": "sal_emp",
		  "schemaName": "public",
	      "catalogName": "dev"
	     }
	   },
	   {
		 "missingObject": {
		   "name": "schedule",
		   "type": "column",
		   "relationName": "sal_emp",
	       "schemaName": "public",
		   "catalogName": "dev"
		  }
	    },
		{
		  "missingObject": {
		    "name": "lastnames",
		    "type": "column",
		    "relationName": "contract_bank_accounts",
		    "schemaName": "public",
		    "catalogName": "dev"
		   }
		 },
		 {
		   "missingObject": {
			 "name": "id",
			 "type": "column",
			 "relationName": "contract_bank_accounts",
			 "schemaName": "public",
			 "catalogName": "dev"
		    }
		  },
		  {
			"missingObject": {
			  "name": "name",
			  "type": "column",
			  "relationName": "sal_emp",
			  "schemaName": "public",
			  "catalogName": "dev"
			 }
		   },
		   {
			 "missingObject": {
			   "name": "contract_bank_accounts_pkey",
			   "type": "primaryKey",
			   "schemaName": "public",
			   "catalogName": "dev"
			 }
		   },
		   {
			 "missingObject": {
			   "name": "sal_emp",
			   "type": "table",
			   "schemaName": "public",
			   "catalogName": "dev"
			 }
		    },
			{
			  "missingObject": {
				"name": "contract_bank_accounts_pkey",
				"type": "index",
				"schemaName": "public",
				"catalogName": "dev"
			  }
			}]
		  ,
		  "unexpectedObjects": [
			{
			  "unexpectedObject": {
				"name": "id",
				"type": "column",
				"relationName": "actor",
				"schemaName": "public",
				"catalogName": "test"
			  }
			},
			{
		      "unexpectedObject": {
				"name": "firstname",
				"type": "column",
				"relationName": "actor",
				"schemaName": "public",
				"catalogName": "test"
			   }
			 },
			 {
			   "unexpectedObject": {
				 "name": "contacts",
				 "type": "table",
				 "schemaName": "public",
				 "catalogName": "test"
			    }
			  },
			  {
				"unexpectedObject": {
				  "name": "bcolor",
				  "type": "column",
				  "relationName": "colors",
				  "schemaName": "public",
				  "catalogName": "test"
			     }
			   },
			   {
				 "unexpectedObject": {
				   "name": "bcolor",
				   "type": "column",
				   "relationName": "colors1",
				   "schemaName": "public",
				   "catalogName": "test"
				  }
				},
				{
				  "unexpectedObject": {
					"name": "id",
					"type": "column",
					"relationName": "contacts",
					"schemaName": "public",
					"catalogName": "test"
			      }
				},
				{
				  "unexpectedObject": {
					"name": "lastname",
					"type": "column",
					"relationName": "contacts",
					"schemaName": "public",
					"catalogName": "test"
				   }
				 },
				 {
				   "unexpectedObject": {
					 "name": "colors_pkey1",
					 "type": "index",
					 "schemaName": "public",
					 "catalogName": "test"
					}
				  },
				  {
					"unexpectedObject": {
					  "name": "twitter",
					  "type": "column",
					  "relationName": "actor",
					  "schemaName": "public",
					  "catalogName": "test"
					 }
				   },
				   {
					 "unexpectedObject": {
					   "name": "colors",
					   "type": "table",
					   "schemaName": "public",
					   "catalogName": "test"
					  }
					},
				    {
					  "unexpectedObject": {
						"name": "actor_pkey",
						"type": "index",
						"schemaName": "public",
						"catalogName": "test"
					   }
					 },
					 {
					   "unexpectedObject": {
						 "name": "id",
						 "type": "column",
						 "relationName": "colors",
						 "schemaName": "public",
						 "catalogName": "test"
						}
					  },
					  {
						"unexpectedObject": {
						  "name": "colors1",
						  "type": "table",
						  "schemaName": "public",
						  "catalogName": "test"
						 }
					   },
					   {
						 "unexpectedObject": {
						   "name": "actor",
						   "type": "table",
						   "schemaName": "public",
						   "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "contacts_pkey",
						    "type": "primaryKey",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "firstname",
						    "type": "column",
						    "relationName": "contacts",
						    "schemaName": "public",
					 	    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "colors_pkey",
						    "type": "index",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "lastname",
							"type": "column",
							"relationName": "actor",
							"schemaName": "public",
							"catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
							"name": "actor_pkey",
							"type": "primaryKey",
							"schemaName": "public",
							"catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
							"name": "colors_pkey",
							"type": "primaryKey",
							"schemaName": "public",
							"catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "fcolor",
						    "type": "column",
						    "relationName": "colors",
						    "schemaName": "public",
					    	"catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "colors_pkey1",
						    "type": "primaryKey",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "contacts_pkey",
						    "type": "index",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
						    "name": "id",
						    "type": "column",
						    "relationName": "colors1",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						},
						{
						  "unexpectedObject": {
					 	    "name": "fcolor",
						    "type": "column",
						    "relationName": "colors1",
						    "schemaName": "public",
						    "catalogName": "test"
						  }
						}]
					   ,
					   "changedObjects": [
						 {
						   "changedObject": {
						   "name": "test",
						   "type": "catalog",
						   "differences": [
						     {
						       "difference": {
						         "comparedValue": "test",
							     "field": "name",
								 "message": "name changed from 'dev' to 'test'",
								 "referenceValue": "dev"
							}
						 }]

				}
	 		 }]

   }
}
Liquibase command 'diff' was executed successfully.

Related Links