Do you have a way to compare two databases to check whether they are the same or they differ? And if they differ, would you like to know what is the difference? What would you say if I told you that Liquibase can do it and even generate an update script to make them the same? Oh, yes, it can. I am going to show you how to use it.
Two databases
I have two databases on my SQL Server instance: MyDatabase and StockExchange.
When manually looking at them I see some similarities but I suppose they are not exactly the same. There might be some objects in one of them that do not exist in the other.
Database comparison
I could manually go through table by table, compare the lists of columns, their properties etc. but it would take a while. Databases with a few hundred tables are not uncommon. What is more, such task is error prone.
A tool that could do it for me would be ideal for such purpose. I checked - Liquibase can do it! The command is called diff and is described on the Liquibase official website.
In my case I have to run this command:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver \
--classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" \
--url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" \
--username=sa --password=pass \
diff
--referenceUrl="jdbc:sqlserver://localhost\DEVSQL01;databaseName=StockExchange" \
--referenceUsername=sa --referencePassword=pass
It means that I want to check the state of MyDatabase by comparing it to the StockExchange database.
The output is this:
d:\Liquibase\Diff>liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=saPass diff --referenceUrl="jdbc:sqlserver://localhost\DEVSQL01;databaseName=StockExchange" --referenceUsername=sa --referencePassword=saPass
Diff Results:
Reference Database: sa @ jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=StockExchange;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Default Schema: dbo)
Comparison Database: sa @ jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDatabase;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Default Schema: dbo)
Product Name: EQUAL Product Version: EQUAL Missing Catalog(s): NONE Unexpected Catalog(s): NONE Changed Catalog(s): StockExchange name changed from 'StockExchange' to 'MyDatabase' Missing Column(s): wallets.comment wallets.creationDate quotes.date walletTickers.investedMoney quotes.priceClose quotes.priceHigh quotes.priceLow quotes.priceOpen quotes.quoteId settings.settingKey wallets.startDate quotes.ticker walletTickers.ticker settings.value quotes.volume walletTickers.volume walletTickers.walletId wallets.walletId walletTickers.walletTickerId Unexpected Column(s): DATABASECHANGELOG.AUTHOR USERS.BIRTH_DATE DATABASECHANGELOG.COMMENTS DATABASECHANGELOG.DATEEXECUTED DATABASECHANGELOG.DESCRIPTION DATABASECHANGELOG.EXECTYPE DATABASECHANGELOG.FILENAME DATABASECHANGELOG.ID DATABASECHANGELOGLOCK.ID USERS.ID DATABASECHANGELOG.LIQUIBASE DATABASECHANGELOGLOCK.LOCKED DATABASECHANGELOGLOCK.LOCKEDBY DATABASECHANGELOGLOCK.LOCKGRANTED DATABASECHANGELOG.MD5SUM USERS.NAME DATABASECHANGELOG.ORDEREXECUTED DATABASECHANGELOG.TAG Changed Column(s): NONE Missing Foreign Key(s): FK_walletTickers_walletId(walletTickers.walletId -> wallets.walletId) Unexpected Foreign Key(s): NONE Changed Foreign Key(s): NONE Missing Index(s): IX_quotes_date_ticker unique on quotes(date, ticker) IX_walletTickers_walletId unique on walletTickers(walletId) PK_quotes on quotes(quoteId) PK_walletTickers on walletTickers(walletTickerId) PK_wallets on wallets(walletId) Unexpected Index(s): PK_DATABASECHANGELOGLOCK on DATABASECHANGELOGLOCK(ID) Changed Index(s): NONE Missing Primary Key(s): PK_quotes on quotes(quoteId) PK_walletTickers on walletTickers(walletTickerId) PK_wallets on wallets(walletId) Unexpected Primary Key(s): PK_DATABASECHANGELOGLOCK on DATABASECHANGELOGLOCK(ID) Changed Primary Key(s): NONE Missing Schema(s): NONE Unexpected Schema(s): NONE Changed Schema(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 Table(s): quotes settings walletTickers wallets Unexpected Table(s): DATABASECHANGELOG DATABASECHANGELOGLOCK USERS Changed Table(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 Liquibase 'diff' Successful
As you can see the report is pretty detailed. It produces a list of objects that are different or missing in one of the databases. Missing objects (e.g. tables) are the ones that exist in StockExchange but are missing in MyDatabase. If any object exists in the database that is being compared (MyDatabase) but is missing in the referenced database (StockExchange), it is reported as an unexpected object.
Database upgrade
I set expectations high in the foreword. Now, it is time to fulfill my promise - I will generate an upgrade script from MyDatabase to StockExchange. Liquibase has a special command for it - diffChangeLog.
All parameters stay the same.
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver \
--classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" \
--url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" \
--username=sa --password=pass \
diffChangeLog
--referenceUrl="jdbc:sqlserver://localhost\DEVSQL01;databaseName=StockExchange" \
--referenceUsername=sa --referencePassword=pass
The command prints an upgrade script to the standard output. Of course, it can be easily streamed to a file if needed by using a proper OS syntax (> on Windows, | on Linux).
d:\Liquibase\Diff>liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=pass diffChangeLog --referenceUrl="jdbc:sqlserver://localhost\DEVSQL01;databaseName=StockExchange" --referenceUsername=sa --referencePassword=pass
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.2.xsd">
<changeSet author="DBA presents (generated)" id="1475415730288-1">
<createTable tableName="quotes">
<column autoIncrement="true" name="quoteId" type="INT">
<constraints primaryKey="true" primaryKeyName="PK_quotes"/>
</column>
<column name="ticker" type="VARCHAR(20)">
<constraints nullable="false"/>
</column>
<column name="date" type="date">
<constraints nullable="false"/>
</column>
<column name="priceOpen" type="MONEY">
<constraints nullable="false"/>
</column>
<column name="priceHigh" type="MONEY">
<constraints nullable="false"/>
</column>
<column name="priceLow" type="MONEY">
<constraints nullable="false"/>
</column>
<column name="priceClose" type="MONEY">
<constraints nullable="false"/>
</column>
<column name="volume" type="INT"/>
</createTable>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-2">
<createTable tableName="settings">
<column name="settingKey" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="value" type="VARCHAR(50)"/>
</createTable>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-3">
<createTable tableName="walletTickers">
<column autoIncrement="true" name="walletTickerId" type="INT">
<constraints primaryKey="true" primaryKeyName="PK_walletTickers"/>
</column>
<column name="walletId" type="INT">
<constraints nullable="false"/>
</column>
<column name="ticker" type="VARCHAR(20)">
<constraints nullable="false"/>
</column>
<column name="volume" type="INT">
<constraints nullable="false"/>
</column>
<column name="investedMoney" type="MONEY">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-4">
<createTable tableName="wallets">
<column autoIncrement="true" name="walletId" type="INT">
<constraints primaryKey="true" primaryKeyName="PK_wallets"/>
</column>
<column defaultValueComputed="getdate()" name="creationDate" type="datetime">
<constraints nullable="false"/>
</column>
<column name="comment" type="VARCHAR(200)"/>
<column name="startDate" type="date"/>
</createTable>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-5">
<addForeignKeyConstraint baseColumnNames="walletId" baseTableName="walletTickers" constraintName="FK_walletTickers_walletId" deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="walletId" referencedTableName="wallets"/>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-6">
<createIndex indexName="IX_quotes_date_ticker" tableName="quotes" unique="false">
<column name="date"/>
<column name="ticker"/>
</createIndex>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-7">
<createIndex indexName="IX_walletTickers_walletId" tableName="walletTickers" unique="false">
<column name="walletId"/>
</createIndex>
</changeSet>
<changeSet author="DBA presents (generated)" id="1475415730288-8">
<dropTable tableName="USERS"/>
</changeSet>
</databaseChangeLog>
Liquibase 'diffChangeLog' Successful
The output can be executed by Liquibase to upgrade MyDatabase to StockExchange. The command that can do it is update and it is well described in my introduction to Liquibase article and video.
For more information about Liquibase check:
