Schema version control with Liquibase - Part 2: single database project

Introduction

In Part 1, I went through the basic concepts of Liquibase. We now have a good enough understanding of how it works to start working with a database. In this Part 2, I am going to show how to manage a single database, single environment project. We are first going to set up our project. Then we'll write and execute our first changeset. We will finally see how to make more changes and use the rollback command.

Setting up your Liquibase project

First, we are going to set the changelogs where we'll save all our changes made to the database. Then, we'll write our liquibase.properties file where we can save all our settings.

Setup Changelog

In this section, I am going to set up the changelog files for PostgreSQL. As I mentioned earlier, changelog files can be written in four different formats: XML, JSON, YAML, and SQL. SQL is what we use every day when working on our databases so I'll use that format. It makes the changesets easier to write and to read. I also want to be able to have multiple changelog files so they don't grow too large. To do this, I need to create an XML master changelog that tells Liquibase where are the SQL changelog files.

In my project directory, I create a changelog-master.xml file and a changelogs folder where I'll save all my changelog files. It should look like this:

project/
    |_ changelog-master.xml
    |_ changelogs/
        |_ changelog_1.sql
        |_ changelog_2.sql
        |_ ...

In the changelog-master.xml file, I add the following lines:

<?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"  
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

    <includeAll path="changelogs/" relativeToChangelogFile="true"/>

</databaseChangeLog>

The <includeAll> tag specifies the directory that contains all our changelog files.

Setup liquibase.properties

I create the liquibase.properties file in my project's directory:

project/
    |_ liquibase.properties
    |_ master_changelog.xml
    |_ changelogs/

I introduced the most commonly used options in part 1 of this series. In our project, we are interested in: - Pointing to the master changelog file so we don't have to type the information every time we run a command. - Enter the database connection information (driver, host, username, and password) - Set up how Liquibase should log our activity. - Set the name of the DATABASECHANGELOG and DATABASECHANGELOGLOCK table to match our database naming convention.

# Specify the changelog file
classpath: /liquibase/changelog
changeLogFile: changelog-master.xml

# Database settings
driver: org.postgresql.Driver
url: jdbc:postgresql://172.18.0.2:5432/mydb
username: postgres
password: postgres


# Setup log level
# OFF, ERROR, WARN, INFO, DEBUG, TRACE, ALL
logLevel: INFO
logFile: /liquibase/changelog/logs/liquibase.log

# Set the name of the liquibase DB tables
databaseChangeLogTableName: database_changelog
databaseChangeLogLockTableName: database_changelog_lock

Writing our first changeset

We're all set. We're going to start with a single database. I'll write the first changelog that will create the database and our first table.
Changelog files are executed sequentially, we need to keep this in mind when choosing our naming convention for the changelog files. As I am working on a simple example, I will just start every changelog file name with a sequence number. You can use any other convention you want, as long as the files will remain ordered correctly.

I create the file changelogs/001-initiate-database.postgresql.sql. I then write my changesets in this file. It is good practice to one changeset for each change we make. In my case, I'll write one change case that creates the database and one changeset that creates the table.

-- liquibase formatted sql

/* 
 * This changelog creates the users table
 *
 * Author: Guillaume Martin
 * Date: 2021-11-30
 */

-- changeset guillaume:001:01
-- comment: create users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY
    , username VARCHAR(10)
    , password VARCHAR(100)
);
-- rollback DROP TABLE users;

Let's go through the elements of this script.
At the top of the file, I wrote -- liquibase formatted sql. This is just to tell Liquibase that what is following is written in SQL.
Then, I added a comment block using /* */. You don't have to do it, but it is always good to explain what this changelog is about. It will help your future self or a coworker who will have to review the history of changes.
The -- changeset guillaume:001:01 line starts a changeset. The author is guillaume and the id is 001:01. You always need to add author:id after changeset. You can use whatever works for you for the id. In this example, I use the sequence number of the changelog and the sequence number of the changeset.
The -- comment: create database line lets you add a comment that will be saved in the DATABASECHANGELOG table. Type a short description of what this changeset is doing.
After the comment line, we can type the SQL statement we want to execute. Try to have only one statement per changeset.
After the SQL statement, we add a rollback instruction that tells Liquibase what to do when we roll back this changeset. It is just a SQL statement that reverses what is done by the SQL statement we just added to our changeset. It looks like this: -- rollback DROP DATABASE mydb;.
We repeat the same thing for all the changesets we want to add to this changelog file.

Update the database

Our changeset is ready to be executed. We first run the status command to see what are the pending changes that Liquibase is going to execute.

$ liquibase status --verbose
[...]
Liquibase Community 4.6.2 by Liquibase
1 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.2:5432/mydb
     changelogs/001-create-users-table.postgresq.sql::001:01::guillaume
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'status' was executed successfully.

We can see that there's only one pending changeset. When you use the --verbose option, you get more details on what is pending. In our example, it is this line:

changelogs/001-create-users-table.postgresq.sql::001:01::guillaume

Liquibase is going to execute the changeset guillaume:001:01 from the 001-create-users-table.postgresq.sql changelog file. It is what we expect. Let's update the database

$ liquibase --defaults-file=/liquibase/changelog/liquibase.properties update
[...]
Liquibase Community 4.6.2 by Liquibase
Skipping auto-registration
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'update' was executed successfully.

We can now check what happened in our database.

mydb=# \dt
                  List of relations
 Schema |           Name           | Type  |  Owner   
--------+--------------------------+-------+----------
 public | database_changelog       | table | postgres
 public | database_changelog_lock  | table | postgres
 public | users                    | table | postgres
(3 rows)

Liquibase has created a database_changelog and a database_changelog_lock table. AS we saw earlier, those tables are used by Liquibase to manage the changes. We'll look at the database_change_log table later. There is also a new users table. This is the table that we wanted to create in our changeset. 

mydb=# \d+ users
                                                         Table "public.users"
  Column  |          Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
----------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id       | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 username | character varying(10)  |           |          |                                   | extended |              | 
 password | character varying(100) |           |          |                                   | extended |              | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Access method: heap

All the columns are here with the correct data types. The update was successful.

Let's have a look at what is in the database_changelog table.

mydb=# SELECT * FROM database_changelog;
-[ RECORD 1 ]-+----------------------------------------------------------
id            | 001:01
author        | guillaume
filename      | changelog/changelogs/001-create-users-table.postgresq.sql
dateexecuted  | 2021-12-02 09:50:40.93064
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:518ae699e8c46dc100c1af590f370738
description   | sql
comments      | create users table
tag           | 
liquibase     | 4.6.2
contexts      | 
labels        | 
deployment_id | 8438640623

In the first three columns, we can find the author, id, and file of the changeset that was executed. In the exectype column, we can see that the changeset has been executed and the dateexecuted columns give us the time it happened. The exectype column tells us how the changeset was executed. It works like a status. The possible values are EXECUTED, FAILED, SKIPPED, RERAN, and MARK_RAN.
The md5sum is a checksum of the changeset and is here to detect changes that may occur to it after it was executed.
The description is autogenerated. It is the comment that you wrote in your changeset that is going to help you and others understand what this changeset did without having to look for it in all your changelogs.
The deployment id is a unique identifier that is given to all the changesets that were deployed together.

Use a tag to mark the initial state

Now that we have executed our first changeset, we are going to tag it to mark the initial state of our database. 

liquibase --defaults-file=/liquibase/changelog/liquibase.properties tag version 0
[...]
Successfully tagged 'postgres@jdbc:postgresql://172.17.0.2:5432/mydb'
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'tag' was executed successfully.

I called that tag version 0 but you can use whatever fits with your workflow. Now, let's see what happened in the database_change_log table:

mydb=# SELECT * FROM database_changelog;
-[ RECORD 1 ]-+----------------------------------------------------------
id            | 001:01
author        | guillaume
filename      | changelog/changelogs/001-create-users-table.postgresq.sql
dateexecuted  | 2021-12-02 09:50:40.93064
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:518ae699e8c46dc100c1af590f370738
description   | sql
comments      | create users table
tag           | version 0
liquibase     | 4.6.2
contexts      | 
labels        | 
deployment_id | 8438640623

The tag column has been updated in the record of our first update.

Make a change: Add an email column

Oops! I forgot to add an email column in the users table. Let's write a changeset to correct this.
First, I create a new changelog file: 002-add-email-to-users.postgresql.sql.
Then, I add a changeset that will modify the users table to add an email column.

-- liquibase formatted sql

-- changeset guillaume:002:01
-- comment: add email column to users
ALTER TABLE users
ADD COLUMN email VARCHAR(50);
-- rollback ALTER TABLE users DROP COLUMN email;

Now, I can update my schema:

liquibase --defaults-file=/liquibase/changelog/liquibase.properties update
[...]
Skipping auto-registration
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'update' was executed successfully.

We can verify that our table has been updated:

mydb=# \d users
                                                         Table "public.users"
  Column  |          Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
----------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id       | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 username | character varying(10)  |           |          |                                   | extended |              | 
 password | character varying(100) |           |          |                                   | extended |              | 
 email    | character varying(50)  |           |          |                                   | extended |              |

The new email column is here. Let's have a look at what happened in the database_change_log table:

mydb=# SELECT * FROM database_changelog;
-[ RECORD 1 ]-+-----------------------------------------------------------
id            | 001:01
author        | guillaume
filename      | changelog/changelogs/001-create-users-table.postgresq.sql
[...]
-[ RECORD 2 ]-+-----------------------------------------------------------
id            | 002:01
author        | guillaume
filename      | changelog/changelogs/002-add-email-to-users.postgresql.sql
dateexecuted  | 2021-12-02 09:59:25.57433
orderexecuted | 2
exectype      | EXECUTED
md5sum        | 8:9007fc87ab61ac625e7c8630f6dab5b9
description   | sql
comments      | add email column to users
tag           | 
liquibase     | 4.6.2
contexts      | 
labels        | 
deployment_id | 8439165359

We have a new record that shows the execution of our last changeset.

Rollback last change

Actually, I don't need that email column in the users table. Let's cancel the last change we made. I previously added a version 0 tag to the last change I made before adding the email column. I can ask Liquibase to remove all the changes that were made after that version using the rollback command.

liquibase --defaults-file=/liquibase/changelog/liquibase.properties rollback version 0
[...]
Rolling Back Changeset:changelogs/002-add-email-to-users.postgresql.sql::002:01::guillaume
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'rollback' was executed successfully.

If we look at the users table, we can see that the email column is gone.

mydb=# \d users
                                                         Table "public.users"
  Column  |          Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
----------+------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id       | integer                |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 username | character varying(10)  |           |          |                                   | extended |              | 
 password | character varying(100) |           |          |                                   | extended |              |

The record of the second changeset execution has also been removed from the database_change_log file.

mydb=# SELECT * FROM database_changelog;
-[ RECORD 1 ]-+----------------------------------------------------------
id            | 001:01
author        | guillaume
filename      | changelog/changelogs/001-create-users-table.postgresq.sql
dateexecuted  | 2021-12-02 09:50:40.93064
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:518ae699e8c46dc100c1af590f370738
description   | sql
comments      | create users table
tag           | version 0
liquibase     | 4.6.2
contexts      | 
labels        | 
deployment_id | 8438640623

You need to be careful with the next updates. The changeset for adding the email column is still there and is not marked as executed. Liquibase will execute it again the next time you run the update command. We can see this by checking the status:

liquibase status --verbose
[...]
1 change sets have not been applied to postgres@jdbc:postgresql://172.17.0.2:5432/mydb
     changelogs/002-add-email-to-users.postgresql.sql::002:01::guillaume
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'status' was executed successfully.

You will have to either modify the changeset to make it do what you want or just delete it.

Using git to put our project under version control

All the project information is in plain text files. It is easy now to create a repository that will track all the changes we make to our changelogs and properties files. Having your changelogs in a central repository also allows everybody to access the latest version of your schema.

Starting version control in an existing project.

Even if our database has been up and running for years, it is never too late to do things right. We still can setup version control in our project.

1. Set up the project for Liquibase

The first thing to do is to add the master changelog and properties files. Then, we create the directories that will hold our changelogs.

2. Generate the baseline

The next step is to generate a changelog where we record all the changesets required to build the database as it currently is. The idea is to make our project look like we used Liquibase from the beginning. To do so, we run the generate-changelog command. Liquibase will create a new changelog file that contains all the changesets. It is wise at this point to control what those changesets to make sure that everything is ok. If we run all those changesets on a blank database, we should get a new database that is in the same state as the one we are using. Note that the community edition of Liquibase will not generate changesets for stored procedures, triggers, and functions. We would have to either write those ourselves or get a licance for the pro version.

liquibase --defaults-file=/liquibase/changelog/liquibase.properties generate-changelog --changelog-file=/liquibase/changelog/changelogs/000-baseline-changelog.sql
[...]
BEST PRACTICE: The changelog generated by diffChangeLog/generateChangeLog should be inspected for correctness and completeness before being deployed.

When generating formatted SQL changelogs, it is important to decide if batched statements
should be split or not.  For storedlogic objects, the default behavior is 'splitStatements:false'
.All other objects default to 'splitStatements:true'.  See https://docs.liquibase.org for additional information.

Generated changelog written to /liquibase/changelog/changelogs/000-baseline-changelog.sql
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'generate-changelog' was executed successfully.

The changelog that was generated looks like this:

-- liquibase formatted sql

-- changeset ?:1643243662057-1
CREATE TABLE "public"."users" ("id" INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, "username" VARCHAR(10), "password" VARCHAR(100), "email" VARCHAR(50), CONSTRAINT "users_pkey" PRIMARY KEY ("id"));

3. Populate the tracking table

We don't want Liquibase to execute the changesets we just generated the next time we run the update command. We are going to record those changesets as executed in the DATABASECHANGELOG table. We first run the changelog-sync-sql command. This will generate a SQL file with statements to insert rows into the DATABASECHANGELOG table. It should create one row per changeset. We control that the insert statements are ok. Once we confirmed that the inserts can be executed safely, we run the changelog-sync command. This will execute the actual SQL statements that populate the DATABASECHANGELOG with all the changesets required to generate the database up this point.

liquibase --defaults-file=/liquibase/changelog/liquibase.properties changelog-sync-sql --output-file=/liquibase/changelog/output/changelog-sync-sql.sql

The command should generate the following file

-- *********************************************************************
-- SQL to add all changesets to database history table
-- *********************************************************************
-- Change Log: changelog/changelog-master.xml
-- Ran at: 1/27/22, 12:49 AM
-- Against: postgres@jdbc:postgresql://172.17.0.2:5432/mydb
-- Liquibase version: 4.7.0
-- *********************************************************************

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

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

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

-- Lock Database
UPDATE public.database_changelog_lock SET LOCKED = TRUE, LOCKEDBY = '6ee28069134f (172.17.0.4)', LOCKGRANTED = '2022-01-27 00:49:53.064' WHERE ID = 1 AND LOCKED = FALSE;

-- Create Database Change Log Table
CREATE TABLE public.database_changelog (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));

INSERT INTO public.database_changelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1643243662057-1', '?', 'changelog/changelogs/000-baseline-changelog.sql', NOW(), 1, '8:d66eaafb21a0c267d2429a6594c51ba3', 'sql', '', 'EXECUTED', NULL, NULL, '4.7.0', '3244593731');

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

We can see that the script is going to create the database_changelog_lock and the database_changelog tables. It will then insert a new row in the database_changelog table to mark our baseline changlog as executed. The SQL code look ok, we can run the changelog-sync command to actually execute this script.

liquibase --defaults-file=/liquibase/changelog/liquibase.properties changelog-sync
[...]
Starting Liquibase at 00:58:36 (version 4.7.0 #1140 built at 2022-01-07 19:26+0000)
[...]
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'changelog-sync' was executed successfully.

We can see in our database that the two tables have been created:

mydb=# \dt
                  List of relations
 Schema |          Name           | Type  |  Owner
--------+-------------------------+-------+----------
 public | database_changelog      | table | postgres
 public | database_changelog_lock | table | postgres
 public | users                   | table | postgres
(3 rows)

We can also check if the baseline changelog has been added to the database_changelog table:

mydb=# SELECT * FROM database_changelog;
-[ RECORD 1 ]-+------------------------------------------------
id            | 1643243662057-1
author        | ?
filename      | changelog/changelogs/000-baseline-changelog.sql
dateexecuted  | 2022-01-27 00:58:37.668223
orderexecuted | 1
exectype      | EXECUTED
md5sum        | 8:d66eaafb21a0c267d2429a6594c51ba3
description   | sql
comments      |
tag           |
liquibase     | 4.7.0
contexts      |
labels        |
deployment_id | 3245117524

Finaly, if we check the status of Liquibase, it should tell us that everything is up to date:

liquibase --defaults-file=/liquibase/changelog/liquibase.properties status --verbose
[...]
postgres@jdbc:postgresql://172.17.0.2:5432/mydb is up to date
Logs saved to /liquibase/changelog/logs/liquibase.log
Liquibase command 'status' was executed successfully.

We now have a baseline. Any change we do from now on will be made with Liquibase.

Conclusion

We have seen in this part how to manage a project with a single database. It is quite useful to keep track of all the changes you make to your schema. Another benefit of using Liquibase is that we can keep the schema of our database synchronized between different environments so we always work on the same version. We will see how to do that in the next part of this series. In the third and final part of this series, I will explain how to use Liquibase with multiple environments.