Liquibase Command diffChangeLog

The diff command provided by Liquibase lets DBA inspect the difference between the source database and the target database. Liquibase provides a diffChangeLog command, which along with listing down the differences between the two databases, creates a ChangeLog file as well with all ChangeSets required to resolve those changes and keep both databases synced.

The diffChangeLog command will create ChangeSet for the below

1. missing objects between the source database and the target database
2. changes made to one database
3. unexpected database object

The diff command compares databases on URL and reference Url

• referenceURL it is the URL for the source database, it will be considered as the basis of comparison
• url contains the URL for the target database, it is the target database for comparisons

Liquibase Open Source and Liquibase Pro command diffChangelog produces ChangeSet for

Liquibase Open Source	Liquibase Pro
Catalog	Check Constraint
Column	Package
Foreign Key	Package Body
Index	Procedure
Primary Key	Function
Schema	Trigger
Sequence	Synonyms
Unique Constraints
View

execute the below script into the database

create database appleDbSource;
use appleDbSource;
CREATE TABLE apple ( apple_id BIGINT NOT NULL AUTO_INCREMENT, apple_name VARCHAR(255), available CHAR(1), current_date_time DATETIME DEFAULT NOW (), PRIMARY KEY (apple_id), CONSTRAINT uqniue_apple_name UNIQUE (apple_name))  ENGINE=INNODB;
insert into apple (apple_name, available, current_date_time) values ("Macintosh", 'Y',  now());
commit;

create database appleDbTarget;
use appleDbTarget;
CREATE TABLE apple ( apple_id BIGINT NOT NULL AUTO_INCREMENT, apple_name VARCHAR(255), current_date_time DATETIME DEFAULT NOW (), PRIMARY KEY (apple_id))  ENGINE=INNODB;
insert into  apple (apple_name, current_date_time) values ("Macintosh",  now());
 commit;

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
# target db
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDbTarget?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot
# source db
liquibase.command.referenceUrl :jdbc:mysql://localhost:3306/appleDbSource?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.referenceUsername: root
liquibase.command.referencePassword: rootroot

To create ChangeLogfor missing objects between the source and target databases, run the command

liquibase diffChangeLog

create database appleDbSource;
use appleDbSource;
CREATE TABLE apple ( apple_id BIGINT NOT NULL AUTO_INCREMENT, apple_name VARCHAR(255), available CHAR(1), current_date_time DATETIME DEFAULT NOW (), PRIMARY KEY (apple_id), CONSTRAINT uqniue_apple_name UNIQUE (apple_name))  ENGINE=INNODB;
insert into apple (apple_name, available, current_date_time) values ("Macintosh", 'Y',  now());
commit;

create database appleDbTarget;
use appleDbTarget;
CREATE TABLE apple ( apple_id BIGINT NOT NULL AUTO_INCREMENT, apple_name VARCHAR(255), current_date_time DATETIME DEFAULT NOW (), PRIMARY KEY (apple_id))  ENGINE=INNODB;
insert into  apple (apple_name, current_date_time) values ("Macintosh",  now());
 commit;

changeLogFile=changelog.mysql.xml
# target db
liquibase.command.url:jdbc:mysql://localhost:3306/AppleDbTarget?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username: root
liquibase.command.password: rootroot
# source db
liquibase.command.referenceUrl :jdbc:mysql://localhost:3306/appleDbSource?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.referenceUsername: root
liquibase.command.referencePassword: rootroot
diffTypes:catalogs,tables,functions,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints,data,storedprocedures,triggers,sequences,databasepackage,databasepackagebody

liquibase diffChangeLog