Manage Database changes using Liquibase + Spring + CockroachDB

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

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.

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>
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
);
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 by IntelliJ
IntelliJ log

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.

<?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>

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Luiz Gustavo De O. Costa

Luiz Gustavo De O. Costa

Hey friend!! I’m Luiz Gustavo, a Java developer and I’m here to learn and write about Java, tests and good practices