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 so you can stay ahead of the automation game making your life easier.

Recently, our friends at DBeaver updated their Enterprise Edition version to take advantage of Liquibase Pro. Their Community Edition of DBeaver helps with “Simple Structure Comparison.” This is valuable to tell you the difference between databases if any. However, if you want to make those databases identical and utilize your CI/CD flow to do so, you will absolutely want to look at DBeaver Enterprise Edition. Now, you will be able to use DBeaver to compare databases and have it happen automagically in your CI/CD flow.

Now, it’s totally possible that you are already doing this via the command line. But the integration makes it easier and brings you closer to the CI/CD party. 

Want to give it a shot? Let me show you how you can do it for free!

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

Install and Configure DBeaver Enterprise Edition

Download DBeaver and select DBeaver EE. Once downloaded, install DBeaver EE.

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 EE to use Liquibase Pro 

Soon, you will be able to configure Liquibase Pro inside the DBeaver EE UI. For now, you will need to 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 EE to compare just the Table. If you just add a Liquibase Pro license key to your config, you’ll get the Table, Function, and Trigger.

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’s 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 EE

After starting DBeaver EE, 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 very much 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 you are using existing databases that have 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. 

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 are using Octopus Deploy for your application deployments, you should have Octopus Deploy execute Liquibase to update the database.

Summing It Up

Sure, Liquibase has the ability to compare database schemas with the diff command — but if you are already using DBeaver and want to compare databases and have it happen automagically in your CI/CD flow? That’s when the winning combination for DBeaver EE and Liquibase Pro makes your life much easier.