Liquibase Command diff

All the Database's new features or Objects such as schema, tables, etc, are first tested in a lower environment and once verified then only moved to the production database. After migration, it's required to actually verify that all changes are committed successfully and that both the lower environment and production environment are in sync.

Liquibase provides a diff command to actually detect the difference between two databases of the same type or different types. diff command can also be used for the below purposes

1. Comparing and finding missing objects between the source database and the target database
2. validating the changes made to a database compared to a backup database
3. finding any 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 produces diff types as follows

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 compare both the source and target databases, run the command

liquibase diff

The diff commands produce three major types of diff outputs

• Missing It will list all the objects that are present in the source database but missing in the target database.
• Unexpected it will list all the objects present in the target database that are not in the source database
• Changed It will list all the objects that are present in the source database as well as in the target but with some changes

 

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 diff