Liquibase – Handling Database in Spring Boot

If you are building an application with Spring Boot, handling the database changes becomes a nightmare over time. The more changes you add, the more changes you have to maintain for your database. Liquibase is the best solution out there. In this post, we will show how to handle database changes using liquibase.

What is Liquibase?

Liquibase is an open-source library to track, manage, and apply database changes. Liquibase tracks the changes to database through an XML configuration where a developer will usually add changesets.

Each changeset will have an id and author attributes. Liquibase uses a changelog to track the database changes. Every changeset you add, that will get added in the changelog. Changelog is a ledger of all the changes you are doing to database.

How does Liquibase work?

To track database changes, you will write an XML file that is platform-independent. This XML file will be used on the command line to translate into scripts for your database engine.

We can also use a maven or Gradle plugin to include database changes in the build configuration.

Liquibase uses its own tables to track changes. Those tables will be part of the schema you are building for consistency purposes. It records the hash of each changeset.

How to write a changeset?

Previously, I mentioned you can write a changeset using XML. But liquibase also offers the support for JSON or YAML.

As part of this post, I will show how I add a changeset and generate scripts for the database.

Create an XML changelog file db.changelog-master.xml for our database under folder src\main\resources\db. Usually, if you start using liquibase from the start of the project, you will create an initial changelog file that will generate initial scripts. You can track every change after that through a change set.

The file without any changeset will look like below:


<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.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
</databaseChangeLog>

Now I can handle this master file in two ways. For each changeset, I can create a separate file and include that file in the master file OR I can add every changeset in the same master file.

Each changeset needs an author and unique id.

Now we will add changeset to this changelog file and it will look like below:


<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.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="Yogesh Mali" id="jira-ticket-01">
        <createTable tableName="user">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="guid" type="varchar(50)">
            </column>
            <column name="firstname" type="varchar(100)">
                <constraints nullable="false"/>
            </column>
            <column name="middlename" type="varchar(100)"></column>
            <column name="lastname" type="varchar(100)"></column>
            <column name="email" type="varchar(100)">
                <constraints nullable="false"/>
            </column>
            <column name="companyid" type="int"></column>
            <column name="roleid" type="int"></column>
        </createTable>
        <createTable tableName="company">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="guid" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="type" type="varchar(10)"></column>
        </createTable>
        <createTable tableName="role">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="role_name" type="varchar(20)">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint baseTableName="user" baseColumnNames="companyid"
                                  constraintName="company_fk" referencedTableName="company"
                                  referencedColumnNames="id" />
        <addForeignKeyConstraint baseTableName="user" baseColumnNames="roleid"
                                  constraintName="role_fk" referencedTableName="role"
                                  referencedColumnNames="id"/>
    </changeSet>
</databaseChangeLog>

Now we are ready to create liquibase Bean in our Spring Boot project. We will have to add the following property in our application.properties file.

spring.liquibase.changeLog=classpath:/db/db.changelog-master.xml.

Also, don’t forget to add database properties in application.properties file.


spring.datasource.url=jdbc:mysql://127.0.0.1/demo
spring.datasource.username = sa
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.liquibase.changeLog=classpath:/db/db.changelog-master.xml

Before we run our Spring Boot Project, add liquibase dependency in our gradle project.

compile('org.liquibase:liquibase-core:4.0.0').

Now if we run our Spring Boot project, we will see the database tables created in the log messages as follows:


2020-07-26 12:22:24.362  INFO 32412 --- [           main] liquibase.lockservice                    : Successfully acquired change log lock
2020-07-26 12:22:25.314  INFO 32412 --- [           main] liquibase.changelog                      : Creating database history table with name: blogdemo.DATABASECHANGELOG
2020-07-26 12:22:25.345  INFO 32412 --- [           main] liquibase.changelog                      : Reading from blogdemo.DATABASECHANGELOG
2020-07-26 12:22:25.427  INFO 32412 --- [           main] liquibase.changelog                      : Table user created
2020-07-26 12:22:25.443  INFO 32412 --- [           main] liquibase.changelog                      : Table company created
2020-07-26 12:22:25.458  INFO 32412 --- [           main] liquibase.changelog                      : Table role created
2020-07-26 12:22:25.520  INFO 32412 --- [           main] liquibase.changelog                      : Foreign key constraint added to user (companyid)
2020-07-26 12:22:25.588  INFO 32412 --- [           main] liquibase.changelog                      : Foreign key constraint added to user (roleid)
2020-07-26 12:22:25.588  INFO 32412 --- [           main] liquibase.changelog                      : ChangeSet db/db.changelog-master.xml::jira-ticket-01::Yogesh Mali ran successfully in 186ms
2020-07-26 12:22:25.600  INFO 32412 --- [           main] liquibase.lockservice                    : Successfully released change log lock


As part of this execution, liquibase also created the tables databasechangelog and databasechangeloglock. Liquibase uses these tables to track the changes for the database. If you add another changeset in the changelog file, liquibase will identify that changeset based on previous changes and will perform appropriate action next time you run the application.

Conclusion

In this post, I showed how to use liquibase to handle database changes in a Spring Boot project.

One thing, I didn’t discuss in this post is another database migration tool Flyway. Flyway is also an open-source database migration tool.

If you enjoyed this post, subscribe to my blog here.

References