The Dev World – Sergio Lema

In this article, I show the usage of Liquibase to manage the database migrations with SQL files. I also show how to configure Liquibase on Maven.

Content:

• What is Liquibase?
• Liquibase with Maven
• Liquibase SQL changesets
• Run the Liquibase Maven Plugin
• Rollbacks with Liquibase

You can find more details in the following video.

All the code is available at my repository.

In a previous article, I’ve configured my Spring Boot application to be connected to a database. But the database creation and schema modification were hardly managed. And today I will handle the modifications in the database with Liquibase.

What is Liquibase?

Liquibase is a library that allows me to perform modifications on the database without the need to run the queries manually. It will help me to ensure the database is correctly adapted to the application. If a newer version of the application I add a new column, Liquibase will ensure that before starting the newer version, it will add the new column.

Liquibase with Maven

Let’s start by adding the Maven dependency. The difference now is that Liquibase will run before the application. I must run it to ensure you have the database in the correct version. I must say: “Hey Liquibase, update my database”. For this, I will use a Maven plugin.

     <properties>        <liquibase.propertyFile>${project.basedir}/src/main/resources/liquibase.properties</liquibase.propertyFile>
     </properties>

     ...

     <build>
        <pluginManagement>
            <plugins>
                <plugin>
                    <groupId>org.liquibase</groupId>
                    <artifactId>liquibase-maven-plugin</artifactId>
                    <version>3.10.0</version>
                    <configuration>
                        <propertyFile>${liquibase.propertyFile}</propertyFile>
                    </configuration>
                    <dependencies>
                        <dependency>
                            <groupId>org.hibernate</groupId>
                            <artifactId>hibernate-core</artifactId>
                            <version>5.4.12.Final</version>
                        </dependency>
                        <dependency>
                            <groupId>javax.xml.bind</groupId>
                            <artifactId>jaxb-api</artifactId>
                            <version>2.3.1</version>
                        </dependency>
                    </dependencies>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>

I will need a property file where I configure the database connection. I will need some additional dependencies: the driver of the database; and some XML parser. This is because some part of the configuration is done in XML. Let’s see now the property file.

driver: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/socialnetworkdb
username: socialnetwork
password: socialnetwork
changeLogFile: src/main/resources/sql/db.changelog-master.xml

The additional configuration file, the changelog file, contains the modifications to be performed to the database. But I will use it to list all the changelog files per version.

Liquibase SQL changesets

<?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-3.8.xsd">
    <include file="src/main/resources/sql/db.changelog-1.0.sql"/>
</databaseChangeLog>

The versioned changelog files name must follow a versioning naming, to know easily the order to run the files. And here, I will specify all the files to run the changeset, to run the modifications of the database. This way, each file will be associated with the version of the application. Each file I add must be included here.

--liquibase formatted sql

--changeset sergio:1
create table if not exists social_network_user (
               id bigserial primary key,
               first_name varchar(100),
               last_name varchar(100),
               login varchar(100),
               password varchar(100),
               created_date timestamp
);
create sequence if not exists social_network_user_sequence start 1000 increment 1;

--changeset sergio:2
create table if not exists message (
               id bigserial,
               content text,
               user_id bigint references social_network_user(id),
               created_date timestamp
);

create sequence if not exists message_sequence start 1000 increment 1;

--changeset sergio:3
create table if not exists friends (
               user_id bigint not null references social_network_user(id),
               friend_id bigint not null references social_network_user(id)
)

I can have all the modifications in a single changeset, or each modification in a changeset. As I want. A changeset is just a registered action by Liquibase. If a problem occurs during the modification of the database, the current changeset will be rollbacked. So, I try to maintain the modifications which go together in the same changeset.

I indicate the name of the user who made modifications and a sequential number. This information must be unique per file. In the next version file, the changelog 2.0, I can have again the changeset sergio:1. Liquibase identifies a single changeset by the name of the file, its author and the sequence value.

But as with Git, the changesets already run in the database can’t be modified. Each time a changeset is run against the database, a checksum is generated and stored. If I modify a previous changeset, Liquibase will see that the checksum changed and will raise an error. So, never modify an older changeset, create a new one with the modifications.

Run the Liquibase Maven Plugin

Running a plugin requires that the project was previously built. I need a compiled project. So, from the terminal let’s just compile the application. Then, tell Liquibase to update the database. I will tell the plugin Liquibase the scope “update” which is “update the database with the given modifications”.

mvn clean package
mvn liquibase:update

If I take a look at the database structure now, I will see the tables that I wanted to create from the SQL file, and I also have those two additional tables from Liquibase: liquibasechangelog and liquibasechangeloglock.

liquibasechangeloglock is to handle the concurrent updates with a lock flag. And liquibasechangelog indicates what was done.

Rollbacks with Liquibase

What more can I do with Liquibase? Imagine I have an application already with the first version of the database. And I have a new version of the application ready to be deployed. But with some changes in the database: new columns. Before deploying the application, before starting the new version of the application, I have to run Liquibase update to ensure that the database has the structure. And only then I can start the new version of the application. But then something goes wrong. Something bad happens. The new version has a lot of errors. The new version doesn’t even start. What can we do? Go back to the previous version. But wait, what about the changes in the database? The old version doesn’t accept them. Don’t worry. I don’t need to rollback the queries I made manually. I don’t need to revert the Liquibase changesets manually. For that, Liquibase has a rollback command which performs the rollback I want. I just need to configure them as following.

--liquibase formatted sql

--changeset sergio:1
create table if not exists social_network_user (
                id bigserial primary key,
                first_name varchar(100),
                last_name varchar(100),
                login varchar(100),
                password varchar(100),
                created_date timestamp
);
create sequence if not exists social_network_user_sequence start 1000 increment 1;
<strong>--rollback drop table social_network_user;
--rollback drop sequence social_network_user_sequence;</strong>

--changeset sergio:2
create table if not exists message (
                id bigserial,
                content text,
                user_id bigint references social_network_user(id),
                created_date timestamp
);

create sequence if not exists message_sequence start 1000 increment 1;
<strong>--rollback drop table message;
--rollback drop sequence message_sequence;
</strong>
--changeset sergio:3
create table if not exists friends (
                user_id bigint not null references social_network_user(id),
                friend_id bigint not null references social_network_user(id)
)
<strong>--rollback drop table friends;</strong>

I have to add the rollback command for each changeset. This way while writing the modifications for the database, I also write the adequate rollbacks. To ensure the correctness rollback action and avoid a lot of headache if a problem occurs.

Now, how do I rollback a modification? I have to specify a parameter which is how many rollbacks, how many changesets I want to rollback.

mvn liquibase:rollback -Dliquibase.rollbackCount=1

Conclusion

Liquibase allows me to manage the history of the database. Git manages the history of my code as Liquibase manages the history of my database.

I can use Liquibase not only to write any SQL command, as schema modifications but also data migrations or deletions. So please, never modify the schema of your database manually. Please, never!

References

• https://www.liquibase.org/

Check my repository for the code of the article.