Manage Database changes using Liquibase + Spring + CockroachDB

Working with a dev environment is kind of different from prod, and managing the changes should be too. To achieve the changes Liquibase is an option rather than Hibernate create-drop strategy.

Database tables

Why

To maintain all DB schema changes into one place, Liquibase has these reasons.

1. Version-controlled database schema changes
2. Automatically orders scripts for deployment
3. Branching and merging for teams
4. Embeds into your product or build tools, like Jenkins
5. Easily rollback changes

For this tutorial, the focus will be on item 1.

Diagram

App diagram

How

• Spring Boot application, start from scratch using this link from https://start.spring.io or this one with the required dependencies, or clone the application via GitHub 🔽

git clone https://github.com/luizgustavocosta/16-bits-liquibase.git

• CockroachDB, you can use a free tier accessing this link, no card required.
• Liquibase, is a dependency and will be configured along the way.

1. SpringBoot

Open the application using IntelliJ, recommended.

Initial project content

1.1. Create an application.yml and delete the application.properties file

This is my preference, but it’s up to you to maintain the application.properties. Check below the content. The values between {} should be replaced by you.

❗️The important point here is has the ddl-auto to none, to avoid any action by Hibernate side. If you have any questions about ddl-auto, please visit this link.

✅ The liquibase.change-log represents the configuration file to Liquibase

✅ The liquibase.contexts represents the context name for Liquibase

spring:
  output:
    ansi:
      enabled: always
  liquibase:
    change-log: classpath:db/changelog-master.xml
    default-schema:
    drop-first: false
    contexts: 16bits
    enabled: true
  datasource:
    url: ${db.url}
    driver-class-name: org.postgresql.Driver
    username: ${db.userName}
    password: ${db.password}
    hikari:
      connection-test-query: SELECT 1
  jpa:
    open-in-view: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.CockroachDB201Dialect
    show-sql: false
    hibernate:
      ddl-auto: none
server:
  port: 9090

1.2. Replace the values inside the ${}

One approach to replace those values is set as environment variables, and then avoid hard-coded. If you’re using IntelliJ, hit the Edit Configuration and then set the values, follow the steps to achieve this.

Edit Configurations…

Add Environment variables

Add variables and values

1.3. Configure the application log

To configure it, create a new file with the name logback.xml and use the following content.

Logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <include resource="org/springframework/boot/logging/logback/base.xml"/>

    <logger name="org.hibernate.SQL" level="DEBUG"/>
    <logger name="org.hibernate.type.descriptor.sql" level="TRACE"/>

    <logger name="com.costa.luiz" level="DEBUG"/>

    <root level="INFO"/>
</configuration>

2. Liquibase

The Liquibase configuration will be done inside the Spring Boot project, into the resources folder.

2.1. Required files

Create a file with the name changelog-master.xml under the resources/db folder and add the content below.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="1" author="luiz" context="16bits">
        <validCheckSum></validCheckSum>
        <sqlFile path="db/create.sql"/>
    </changeSet>
    
</databaseChangeLog>

Additionally, create also a file with the name create.sql, this file will store the DDL instructions.

CREATE TYPE financial_category AS ENUM ('Mutual Funds', 'NPS', 'Company', 'Individual');
create table financialProduct
(
    id           int          not null primary key default unique_rowid(),
    name         varchar(100) not null,
    type         financial_category,
    creation_time TIMESTAMPTZ                       DEFAULT now(),
    available    bool default false
);

Now, you should have a structure like this

Liquibase files

3. Run

First let’s check the tables. Using CockroachDB, the command show_tables; is enough. Here there are none tables related to Liquibase, but after running the application will have the tables databasechangelog, databasechangeloglock and financialproduct. The tables databasechangelog, databasechangeloglock belong to Liquibase and are used to track the changes.

Run the application through the maven, or by IntelliJ

Run by IntelliJ

If everything goes well you should see the IntelliJ logs like this

IntelliJ log

After the Liquibase runs the new tables are available.

4.1. Add data to new table

Once the table is in place, it’s time to add new data. For it, let’s open the file changelog-master.xml, and let’s add two more rows which can be identified by the bold lines.

⚠️ The context attribute refers to spring.liquibase.contexts path into application.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet id="1" author="luiz" context="16bits">
        <validCheckSum></validCheckSum>
        <sqlFile path="db/create.sql"/>
    </changeSet>

    <changeSet id="2" author="luiz" context="16bits">
        <insert tableName="financialproduct">
            <column name="id">1</column>
            <column name="name">Buy BTC</column>
            <column name="type">Individual</column>
            <column name="available" valueBoolean="true"/>
        </insert>
        <insert tableName="financialproduct">
            <column name="id">2</column>
            <column name="name">Green fund</column>
            <column name="type">Company</column>
            <column name="available" valueBoolean="true"/>
        </insert>
    </changeSet>
</databaseChangeLog>

Now, the table financialProduct has 2 rows. For it, you need to restart the application.

5. Queries

5.1. See the enums

See enum values, link to query.

SELECT
    pg_type.typname, 
     pg_enum.enumlabel
FROM
    pg_type 
JOIN
    pg_enum ON pg_enum.enumtypid = pg_type.oid;

5.1. Drop the enums

drop type financial_category;

5.2. Drop table

drop <table_name>;

6. Software versions

• Java → openJDK11
• Spring → 2.5.2
• CockroachDB → 21.1.3
• Liquibase → 4.3.5
• Maven → 3.6.3

7. Alternatives

To replace the Liquibase you can choose Flyway, for Spring you can use Micronaut or Quarkus, for CockroachDB you can use any SQL or NoSQL DB.

8. Conclusion

This is a really good option, since production data is highly important and maintaining it is controlled in order to avoid surprises.

9. References

luizgustavocosta/16-bits-liquibase

Liquibase

Liquibase | Quickstart | Get up and running in minutes

Hibernate hbm2ddl.auto schema generation - Vlad Mihalcea

Display user-defined types and their details