TIL: There is a beautiful way to set up Database Version Control

And it’s called Liquibase!

Hey you!

Yes, you!

Do you want some control over your database schemas?
Do you want to have a history of the changes applied?
And some accountability?
And why not, the ability to easily test new changes?
And maybe even rollback them?

Then LiquiBase is all you need.

What is LQB?

LiquiBase is an Open Source project started in 2006¹. It allows users to plan, develop, and store database changes in textfiles, allowing to easily maintain and document what happens when. LQB supports lots of databases.

Those textfiles I mentioned are called ChangeLogs. Since it’s nice to have some sort of version control you would want to store these in your preferred source control.

The ChangeLog files contain ChangeSets. Each ChangeSet contains a number of statements that, when applied to a database, do something like:

Creating a table
Creating a view
Creating a primary key
Setting some constraints in a column
Dropping an index

And so on.

The beauty is in its simplicity, and while there are lots of other features for now let’s stick with the basics.

Let’s begin by creating a table.

Look mom! No SQL!

Creating a table is quite simple.

First, create a file. Let’s call it lqb-changelog-1.xml.

Files can be written in 4 different languages: XML, YAML, JSON, and of course SQL. I’ll use XML right now, but you can find the other variants in the GitLab repo (whenever I upload them).

The XML changelog files are defined with the tag databaseChangeLog. On the documentation, the tag is defined this way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


<?xml version="1.0" encoding="UTF-8"?>  

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"  
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

</databaseChangeLog>

Ugly? Yes. But it’s just a copy-paste, so no biggie.

Then, define an empty changeset. It needs to have an ID and author.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


<?xml version="1.0" encoding="UTF-8"?>  

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"  
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">    
    
    <changeSet id="1" author="manucastro">

    </changeSet>
</databaseChangeLog>

Within the changeset, define a table. We will name it lqb_test

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


<?xml version="1.0" encoding="UTF-8"?>  

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"  
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    
    <changeSet id="1" author="manucastro">
        <createTable tableName="lqb_test">
            
        </createTable>
    </changeSet>
</databaseChangeLog>

But since tables without columns are useless, let’s add 2: id and value

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


<?xml version="1.0" encoding="UTF-8"?>  

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"  
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">    
    
    <changeSet id="1" author="manucastro">
        <createTable tableName="lqb_test">
            <column name="id" type="integer">
            
            </column>
            <column name="value" type="varchar(32)">
            
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

Liquibase also allows us to add constraints. We will define the “NOT NULL” constraint on each column.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


<?xml version="1.0" encoding="UTF-8"?>  

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"  
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet id="1" author="manucastro">
        <createTable tableName="lqb_test">
            <column name="id" type="integer">
                <constraints nullable="false" />
            </column>
            <column name="value" type="varchar(32)">
                <constraints nullable="false" />
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

This right here is a basic example of what you can define in a changeset, read the docs for more good stuff.

Docker images

Now we can perform some basic testing. Tests will run over docker containers². I like postgres so I will be using that, but feel free to use your DB engine of choice. LQB doesn’t care. ³

And also we will need LiquiBase, obviously. I will be using Liquibase’s Docker Image

To be clear, I will be using the following docker images:

postgres:12.1-alpine
liquibase/liquibase

Setting up the database

Go to your favourite terminal and start a db right now.

1

~# docker run --rm --net host --name db -d postgres:12.1-alpine

You can check if the DB is up and running with a simple query.

1
2
3
4
5


~# docker exec --user postgres -i db psql -c "select now();"
              now              
-------------------------------
 2020-05-23 23:45:59.784746+00
(1 row)

Let’s create a database, testdb.

1

~# docker exec --user postgres -i db createdb -T template0 testdb

(And a user, just for the sake of it)

1

~# docker exec --user postgres -i db psql -c "create user testuser with superuser password 'testuser';"

Having a usable database, let’s run Liquibase.

Using Liquibase

We need to specify some required parameters:

–changeLogFile=<path and filename> - Migration File
–username=<value> - Database username
–password=<value> - Database password. If values is PROMPT, Liquibase will prompt for a password
–url=<value> - Database URL

In this case we will be mounting our changelog files to /liquibase/changelog, so our args will be:

1
2
3
4


--changeLogFile /liquibase/changelog/lqb-changelog-1.xml
--username testuser
--password testuser
--url "jdbc:postgresql://localhost:5432/testdb"

With everything set up, now we can start the liquibase docker container:

Commands

We will be using a couple of commands:

validate: validates if there are any issues with the changelog
updateSQL: prints the raw SQL that Liquibase will run on the database
update: applies changes to the database

Notes:

We run both containers on the host network
We mount the contents of the folder that contains the changelogs on /liquibase/changelog
Then, we tell liquibase to look for changelog files on that path
You already know what the other parameters do.

Validate

1
2
3
4


~# docker run --rm --net host -v $PWD:/liquibase/changelog --name lqb -i liquibase/liquibase --changeLogFile /liquibase/changelog/lqb-changelog-1.xml --username testuser --password testuser --url "jdbc:postgresql://localhost:5432/testdb" validate
Liquibase Community 3.8.9 by Datical
No validation errors found.
Liquibase command 'validate' was executed successfully.

So it validates! This means that the changelog doesn’t have any errors.

UpdateSQL

Let’s check what will Liquibase do on our precious production (not yet) database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


~# docker run --rm --net host -v $PWD:/liquibase/changelog --name lqb -i liquibase/liquibase --changeLogFile /liquibase/changelog/lqb-changelog-1.xml --username testuser --password testuser --url "jdbc:postgresql://localhost:5432/testdb" updateSQL
Liquibase Community 3.8.9 by Datical
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: /liquibase/changelog/lqb-changelog-1.xml
-- Ran at: 5/24/20, 3:07 AM
-- Against: testuser@jdbc:postgresql://localhost:5432/testdb
-- Liquibase version: 3.8.9
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT DATABASECHANGELOGLOCK_PKEY PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM public.databasechangeloglock;

INSERT INTO public.databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '192.168.0.13 (192.168.0.13)', LOCKGRANTED = '2020-05-24 03:07:34.454' WHERE ID = 1 AND LOCKED = FALSE;

-- Create Database Change Log Table
CREATE TABLE public.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10));

-- Changeset /liquibase/changelog/lqb-changelog-1.xml::1::manucastro
CREATE TABLE public.lqb_test (id INTEGER NOT NULL, value VARCHAR(32) NOT NULL);

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'manucastro', '/liquibase/changelog/lqb-changelog-1.xml', NOW(), 1, '8:71bf69f8a204a5959d96fbb8bf870354', 'createTable tableName=lqb_test', '', 'EXECUTED', NULL, NULL, '3.8.9', '0289656372');

-- Release Database Lock
UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Now, HOLD ON A MINUTE MANU, what is all that nonsense about?

In summary, what does liquibase do?

Creates a table
Performs Liquibase magic tasks

DatabaseChangeLog tables are the way that Liquibase uses to track the version of each table, and which changes it has applied on them. READ THE DOCS ABOUT THAT HERE

Now that we have calmed our paranoia and we know that there is no nasty DROP DATABASE hidden anywhere around here, we can proceed and update the database.

Update

1
2
3


~# docker run --rm --net host -v $PWD:/liquibase/changelog --name lqb -i liquibase/liquibase --changeLogFile /liquibase/changelog/lqb-changelog-1.xml --username testuser --password testuser --url "jdbc:postgresql://localhost:5432/testdb" update
Liquibase Community 3.8.9 by Datical
Liquibase: Update has been successful.

We can see now that there is an empty table called lqb_test, with 2 columns, that can’t be null.

1
2
3
4
5
6


~# docker exec --user postgres -i db psql testdb testuser -c "\d lqb_test"
                     Table "public.lqb_test"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 value  | character varying(32) |           | not null | 

1
2
3
4


[manu-T480 changelog]# docker exec --user postgres -i db psql testdb testuser -c "select * from lqb_test"
 id | value 
----+-------
(0 rows)

Good. It works.

Fin.

BUT WHY DO I NEED THIS WHEN I CAN JUST WRITE SQL!!?!?!11

Well, yeah. Why do we need containers? Why do you want an orchestrator? Metrics? Monitoring?

Because now it’s a pain in the ass to implement, and a “waste of time”, but IN THE LONG RUN they will make our life SO MUCH FUCKING EASIER.

I’m not saying you absolutely need Liquibase or any other tool to have a working & performant pipeline, but sometimes it’s nice to take a step back, see the whole process from one end to the other, and realize there might be some places that need some WD-40.

There it is. I did it. Another post after a couple of months.

“Yay”, to overcoming procrastination.

Apparently I lived under a rock for the past 14 years. ^[return]
docker is good, will write about it someday ^[return]
Well it kinda does. More on that HERE ^[return]

Table of Contents