Using DBeaver & Liquibase to Easily Compare Databases in Your CI/CD Flow

Liquibase and DBeaver have teamed up to give you the most advanced database comparison functions to get ahead in the automation game making your life easier.

Recently, our friends at DBeaver updated their DBeaver Enterprise, DBeaver Ultimate, and Team Edition versions to take advantage of Liquibase Pro and support database schema comparisons so you can quickly see any difference between databases.

If you want to make those databases identical in automation, you will want to look at DBeaver Enterprise Edition. In this edition, you can use DBeaver to compare databases and automate it in your CI/CD flow.

This integration simplifies the task and brings you closer to the CI/CD party. 

Want to try it out? Let me show you how you can do it for free!

Here are the steps to install it and take it for a ride.

Install and Configure DBeaver Enterprise Edition

Download DBeaver and select DBeaver Enterprise, DBeaver Ultimate, or Team Edition. Once downloaded, install the selected software.

Get a Free Trial of Liquibase Pro

If you’re not already using Liquibase Pro, get your free 30-day trial. (No credit card necessary.)

You’ll get a license key that you’ll use in the next step. 

Configure DBeaver to use Liquibase Pro 

You can add the Liquibase Pro license inside the DBeaver user interface.

Alternatively, you can add the following line to your db.ini file. I’m on Windows 10, so my dbeaver.ini file was found here: C:\Program Files\DBeaverEE. 

Here is the line to add to dbeaver.ini:

-Dliquibase.license.key=<Your Liquibase Pro License Key>

You can use Liquibase Community (open source) with DBeaver to compare Tables. If you add a Liquibase Pro license key to your config, you’ll also get access to Functions, Packages, Synonyms, Triggers, and Check Constraints.

Set Up Your Test Databases

If you already have two databases that you would like to compare, you can skip to the next step. However, here is an easy way to set up two PostgreSQL databases to compare.

Execute the following Docker run commands to start two PostgreSQL databases:

docker run -p 5432:5432 -e POSTGRES_PASSWORD=secret -d postgres

docker run -p 5433:5432 -e POSTGRES_PASSWORD=secret -d postgres

This will start two identical databases, one listening on port 5432 and the other on port 5433. The username for connecting is postgres and the password is secret. Of course, you can always change this to meet your requirements.

Create Database Connections in DBeaver

After starting DBeaver, create a new database connection.

1. Go to File → New
2. Select Database Connection. 
3. Choose PostgreSQL and use the information in the previous step. 

I appreciate the “Test Connection” button on the new Database Connection UI. This allows me to verify I have entered my database information correctly.

Create Database Objects in One Database

If your current database already has one or more objects, you can skip to the next step. 

If you have created the two Docker PostgreSQL databases, run the following script on one of the instances.

Select the “SQL” pull down on the toolbar and select “New SQL Script” (or use Ctrl+J). This script creates a new Table along with a Function and Trigger. 

Copy the following text into your new SQL script:

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

Compare Databases

Now it’s time to compare our databases!

1. In the Database Navigator, select the first database’s public schema. You can find it under Database Connection → postgres → Schema. 
2. Navigate to the second public schema and select it while holding the Ctrl key. 
3. Right click on one of the public schemas.
4. Select Compare/Migrate → Compare/Migrate Schema. This will open a dialog window with your two public schemas selected. (See Specify input objects for schema compare screen.)
5. Select “Generate migrate / compare plan”.

The dialog window will present the artifacts needed to change the target database to be identical to the previous one. 

In the “Report type:” select box, you can select your favorite Liquibase changelog format. (“Change Log” is XML while YAML and JSON create respective changelog formats.)

Select Save to persist a local copy.

Check Your Liquibase Pro Changelog into Your Source Repository

Liquibase works best when integrated with your existing application CI/CD infrastructure. For example, if you use Octopus Deploy for your application deployments, you should have Octopus Deploy execute Liquibase to update the database.

Summing It Up

Sure, Liquibase can compare database schemas with the diff command — but if you are already using DBeaver and want to compare databases, you can have it happen automatically in your CI/CD flow. That’s when the winning combination of DBeaver Enterprise/DBeaver Ultimate/Team Edition and Liquibase Pro makes your life much easier.