Liquibase Demostration with Spring Boot Application

The following demonstration is a Spring Boot application which only exposes a GET endpoint /users/:id. To integrate Liquibase, add a dependency for it in pom.xml.

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

The changelog files are located in resources/db/changelog/.

Liquibase supports SQL, XML, JSON and YAML format for storing changelog files. The 00-db.changelog-master.yaml includes the changelog files for the releases in the correct order.

00-db.changelog-master.yaml

databaseChangeLog:
  - include:
      file: 01-db.changelog-yaml-example.yaml
      relativeToChangelogFile: true
  - include:
      file: 02-db.changelog-sql-example.sql
      relativeToChangelogFile: true
  - include:
      file: 03-db.changelog-json-example.json
      relativeToChangelogFile: true
  - include:
      file: 04-db.changelog-xml-example.xml
      relativeToChangelogFile: true

01-db.changelog-yaml-example.yaml

Here's an example of changelog in YAML format. It creates a table called user_details with four fields – id, username, first_name, last_name. A ChangeSet is identified by id and author. For best practice, keep only one change per ChangeSet. Pre-conditions can also be added here.

databaseChangeLog:
  - changeSet:
      id: create-table-user
      author: liquibase-demo-service
      preConditions:
        - onFail: MARK_RAN
          not:
            tableExists:
              tableName: user_details
      changes:
        - createTable:
            columns:
              - column:
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: user_pkey
                  name: id
                  type: BIGINT
              - column:
                  constraints:
                    nullable: false
                  name: username
                  type: TEXT
              - column:
                  constraints:
                    nullable: false
                  name: first_name
                  type: TEXT
              - column:
                  name: last_name
                  type: TEXT
            tableName: user_details

02-db.changelog-sql-example.sql

Here's an example of changelog in SQL format. It must start with the line –liquibase formatted sql to provide liquibase with metadata, followed by arbitrary SQL statements with a comment of the form : --changeset author:id attribute1:value1 attribute2:value2 [...].

--liquibase formatted sql

--changeset liquibase-demo-service:add-user-name-constraint
ALTER TABLE user_details ADD CONSTRAINT user_details_username_key UNIQUE (username);

03-db.changelog-json-example.json

Here's an example of changelog in JSON format.

{ "databaseChangeLog": [
  {
    "changeSet": {
      "id": "add-last-name-constraint",
      "author": "liquibase-demo-service",
      "changes": [
        {
          "addNotNullConstraint": {
            "columnName": "last_name",
            "constraintName": "user_last_name_key",
            "tableName": "user_details"
          }
        }]
    }
  }
 ]
}

04-db.changelog-xml-example.xml

Here's an example of changelog in XML format. It utilizes loadUpdateData to loads and update from a CSV file into an existing table.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<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">
  <changeSet author="liquibase-docs" id="loadUpdateData-example" context="!prod">
    <loadUpdateData
      encoding="UTF-8"
      relativeToChangelogFile="true"
      file="../data/users.csv"
      onlyUpdate="false"
      primaryKey="id"
      quotchar="'"
      separator=","
      tableName="user_details">
    </loadUpdateData>
  </changeSet>
</databaseChangeLog>

By default, Liquibase is enabled. Those changelog files will be executed at Spring Boot startup. To disable it, add the following property in src\main\resources\application.yaml.

spring:
  liquibase:
    enabled: false

To deploy any changes in the changelog files that have not been deployed to database yet, run the update goal.

./mvnw org.liquibase:liquibase-maven-plugin:update \
-Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \
-Dliquibase.changeLogFile=src/main/resources/db/changelog/00-db.changelog-master.yaml  \
-Dliquibase.username=demouser \
-Dliquibase.password=demopassword

After running update goal, four tables have been created.

databasechangelog tracks which changeset have been run.

databasechangeloglock ensures there is only one Liquibase is running at one time as multiple instances being executed against the same database concurrently will lead to conflicts.

The table user_details is defined in 01-db.changelog-yaml-example.yaml, with constraints defined in 02-db.changelog-sql-example.sql and 03-db.changelog-json-example.json. The data is loaded in 04-db.changelog-xml-example.xml.

user_details_id_seq is a sequence table for user_details.

To verify the result, start the Sprint Boot Application.

Get user with id 100000000

Tag can be used to mark the current database state for rollback in the future.

To create a tag, run the following goal

./mvnw org.liquibase:liquibase-maven-plugin:tag \
-Dliquibase.tag=version1 \
-Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \
-Dliquibase.username=demouser \
-Dliquibase.password=demopassword

A tag version has been added.

There are three rollback modes – rollbackByTag, rollbackToDate and rollbackCount.

· RollbackByTag reverts all changes made to the database after the specific tag.

· RollbackToDate reverts all changes made to the database from the current date to the date and time you specify.

· RollbackCount reverts the changes sequentially starting with the most recent changes.

There are another modes such as rollbackOneChangeSet and rollbackOneUpdate but it requires Liquibase Pro.

Take the first one as an example, supposing there is another changelog – adding middle_name in user_details.

05-add-middle-name.json

Here we add the rollback statement to this changeset.

{ "databaseChangeLog": [
  {
    "changeSet": {
      "id": "add-middle-name",
      "author": "liquibase-demo-service",
      "changes":[{
        "addColumn":{
          "catalogName":"cat",
          "columns":[{
                "column":{
                  "name":"middle_name",
                  "type":"TEXT"
                } 
              }
            ],
            "schemaName":"public",
            "tableName":"user_details"
            }
          }
        ],
      "rollback": [
        {
            "sql": {
                "sql": "ALTER TABLE USER_DETAILS DROP COLUMN middle_name;"
            }
        }
      ]
    }
  }
]}

Deploy this changelog

./mvnw org.liquibase:liquibase-maven-plugin:update \
-Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \
-Dliquibase.changeLogFile=src/main/resources/db/changelog/05-add-middle-name.json \
-Dliquibase.username=demouser \
-Dliquibase.password=demopassword 

The column "middle_name" has been added.

The latest changelog is after the tag version1.

If we need to revert the changes after it, update 00-db.changelog-master.yaml to include the latest changelog.

  - include:
      file: 05-add-middle-name.json
      relativeToChangelogFile: true

then run the following goal.

./mvnw org.liquibase:liquibase-maven-plugin:tag \
-Dliquibase.tag=version1 \
-Dliquibase.url=jdbc:postgresql://localhost:5432/liquibasedemo?current_schema=public \
-Dliquibase.username=demouser \
-Dliquibase.password=demopassword

The record for 05-add-middle-name.json has been dropped.

The column middle_name has been dropped.

Similarly, we can use RollbackToDate or RollbackCount to revert the changes.

RollbackToDate liquibase:rollback -Dliquibase.rollbackCount=1

RollbackCount liquibase:rollback "-Dliquibase.rollbackDate=Feb 03, 2021"

To conclude, we can define changelogs in 4 possible formats and run the Maven Goals to deploy them to database in the pipeline. We can also create a tag to mark the current state and rollback if necessary.