Nelida Velazquez
Managing Database migrations with Liquibase and Spring Boot

When I worked on my first Backend project, I realized how useful it was to have a process that allows you to track and manage changes in the Database and to be able to do it in a way that was easy to apply or rollback these changes to your different environments. I also noticed that implementing a process like this, early in the project, made us feel much more confident in our first release and since then every release has been much more simple and easy to handle.

In this tutorial I will walk you through the steps needed to implement a tracking/management process like this that is integrated with your Spring Boot Application. Liquibase is the main tool that we will use in this tutorial.

Other tools that you will need are:

  • Docker, to host a local Database and be able to test schema changes and make sure the process works correctly.
  • A database client of your preference. You will need this to connect to your local database and verify that the changes were applied correctly. In my case I’m using Datagrip, but you could also use other free options like pgAdmin.

Before we start these are some of the assumptions I’m making through out the tutorial:

  • You are more or less familiar with the core concepts of Liquibase, such as changelog and changesets. If you need a refresher or reading more about those concepts I’m adding some resources at the end.
  • You are using a Relational Database, I’m using PostgreSQL for all my examples but everything should apply for other Relational Databases.
  • You are familiar with Gradle, we are using them in this example to manage dependencies.

Install dependencies

Add the Liquibase core library to your gradle file. You will also need your Database Driver (in my case is Postgres) and Spring Boot Starter JDBC, if you are using JPA you don’t need to add this dependency but if you are using something like R2DBC like me you will need it.

dependencies {
  implementation("org.liquibase:liquibase-core")
  implementation("org.postgresql:postgresql")
  implementation("org.springframework.boot:spring-boot-starter-jdbc")
}

Configure your database

For Liquibase to be able to connect to the database you will need to specify the URL, driver type and username and password . You can add these details in your application.yml file like this:

spring:
 liquibase:
    url: jdbc:postgresql://localhost/postgres
    driverClassName: org.postgresql.Driver
    user: postgres
    password: liquibase

Note: If you have a Spring Datasource already configured, you don't need to do this.

Create a changelog file

Next step is to create your change log file, which you can add into the resources folder.

Your changelog can be  XML, YAML, JSON or SQL. I like using the SQL format because it doesn’t require additional cognitive load to learn how to read each changeset.

When using the SQL format make sure the file starts with the following comment:

--liquibase formatted sql

For each changeset you add, make sure to add a comment on top of it to identify it:

--changeset author:changeset_id

In the end your changelog should  look something like this:

--liquibase formatted sql

--changeset tolkiana:58283
CREATE TABLE sizes (
   id SERIAL PRIMARY KEY,
   code VARCHAR,
   sort_order INTEGER
);


--changeset tolkiana:58284
CREATE TABLE colors (
   id SERIAL PRIMARY KEY,
   code VARCHAR,
   name VARCHAR
);

Reference your changelog file

Finally you can reference now the changelog file in your application.yml file like this:

spring:
 liquibase:
   url: jdbc:postgresql://localhost/postgres
   driverClassName: org.postgresql.Driver
   user: postgres
   password: liquibase
   changeLog: "classpath:changelog.sql"
   dropFirst: false

With this configuration every time you run/deploy your application. Liquibase will know where to look for any changes that need to be applied to the database. To learn more about how Liquibase knows what changes it needs to apply you can refer to the following documentation.

Generate changelog

Maybe you are thinking, great Nelida, that will only work if I’m starting from scratch, my database it’s already in production. That’s ok, you can generate your initial changelog from your current database with the generateChangeLog command and then use the changeLogSync command to mark all these generated changesets as executed, this will be the baseline for your Liquibase configuration.

1. First  you will need liquibase command line tool which you can install with homebrew like this:

$ brew install liquibase

2. Download the appropriate jdbc driver jar file to connect to your database. In my case I’m using the PostgreSQL driver.

3. Save your driver in the same directory where you are planning to run liquibase.

4. Use the command generateChangeLog to create the initial changelog with the following parameters:

$ liquibase \                
--driver=[driver name] \
--classpath=[path to where the driver is located] \
--url=[database URL] \  
--changeLogFile=[path to the output file] \    
--username=[DB username] \   
--password=[DB password] \                             
generateChangeLog

An example of how running the command with the postgres driver would be something like this:

$ liquibase \
--driver=org.postgresql.Driver \
--classpath=postgresql-42.2.9.jar \
--url="jdbc:postgresql://localhost/postgres" \
--changeLogFile=main-changelog.postgresql.sql \
--username=postgres \
--password=liquibase \
generateChangeLog

5. You can also specify specific schemas by adding the --schema parameter

$ liquibase \
--driver=org.postgresql.Driver \
--classpath=postgresql-42.2.9.jar \
--url="jdbc:postgresql://localhost:5432/postgres" \
--changeLogFile=main-changelog.postgresql.sql \
--schemas=mySchema
--username=postgres \
--password=liquibase \
generateChangeLog

6. Now you can drop this file in the resources folder of your project and reference it in your yml file as described in the previous section. Use this changelog as your base configuration, from now on any new changes in the database must be added as changesets in this file.

7. But before adding any new changesets make sure to synchronize the changelog file so liquibase knows it doesn’t need to apply all the changes in it because they already exist. You can do this with the changelogSync command and the parameters are very similar to the previous command.

$ liquibase \                       
--driver=org.postgresql.Driver \
--classpath=postgresql-42.2.9.jar \
--url="jdbc:postgresql://localhost:5432/postgres" \           
--changeLogFile=main-changelog.sql \                     
--username=postgres \  
--password=liquibase \                   
changelogSync

Testing in a local instance

When working with Liquibase it is important to have a way to test any new changes in a local database instance before trying to roll them into a different environment. For example after generating your changelog file you might want to test that the synchronization works properly and that any new changes will apply correctly.

One way to accomplish this is with a docker image. The following article describes in detail how to do this but the TL;DR is that you can pull the postgres image and run it by passing some parameters to specify what username and password you want to assign to the postgres database like this:

$ docker pull postgres:11

$ docker run --rm --name pg-docker -e POSTGRES_PASSWORD=liquibase -d -p 5432:5432 postgres

You can additionally put this into a script so you can spin up and tear down the instance as needed.

#!/usr/bin/env bash

echo "Stopping postgres image..."
docker stop pg-docker
set -e
echo "Pulling postgres 11 image..."
docker pull postgres:11
echo "Starting postgres image..."
docker run --rm   --name pg-docker -e POSTGRES_PASSWORD=liquibase -d -p 5432:5432 postgres

Finally you can create a dev or local profile on your project and have the datasource point to this local instance and when you run your application with this profile liquibase will apply all the changes to the local database because they don’t exist on it yet.

Having a process like this makes development and testing much easier because each developer can have their own instance and test any changes independently, furthermore they can do it without affecting each other or any testing environments.

Organize changelog files

As mentioned earlier your changelogs can be written in different formats so you can take advantage of this to organize them by type and environment.

Organize by type

Liquibase allows you to track schema changes but you can also create changesets that add, update or delete information from the database, this is very useful when you want to pre-populate your tables with some test or default data. I recommend keeping these changes in different files. For example if you use SQL format you can have the following files:

  • changelog.sql ➡️  For all schema changes
--liquibase formatted sql

--changeset tolkiana:2332
CREATE TABLE sizes (
   id SERIAL PRIMARY KEY,
   code VARCHAR,
   sort_order INTEGER
);
  • dataset.dev.sql ➡️ For data that I only want in the dev database
--liquibase formatted sql

--changeset tolkiana:3833
INSERT INTO sizes (code, sort_order) VALUES ('FAKE-1', 1);
INSERT INTO sizes (code, sort_order) VALUES ('FAKE-2', 2);
INSERT INTO sizes (code, sort_order) VALUES ('FAKE-3', 3);
INSERT INTO sizes (code, sort_order) VALUES ('FAKE-4', 4);
  • dataset.prod.sql ➡️ For data that I only want in the prod database
--liquibase formatted sql

--changeset tolkiana:5868
INSERT INTO sizes (code, sort_order) VALUES ('X-SMALL', 1);
INSERT INTO sizes (code, sort_order) VALUES ('SMALL', 2);
INSERT INTO sizes (code, sort_order) VALUES ('MEDIUM', 3);
INSERT INTO sizes (code, sort_order) VALUES ('LARGE', 4);

Organize by environment

Use a yaml format to reference specific files for each environment. In the example below we are telling Liquibase that we want to apply the schema changes (changelog.sql) to all environments and that we want to apply each dataset (dataset.dev.sql and dataset.prod.sql) to the corresponding environment.

# main-changelog.yml
databaseChangeLog:
 - include:
     file: db/changelog/changelog.sql
     context: dev, prod
 - include:
     file: db/changelog/dataset.dev.sql
     context: dev
 - include:
     file: db/changelog/dataset.prod.sql
     context: prod

Finally you can reference this file in your application.yml file. Keep in mind that if you are using different profiles, you have to specify the appropriate context in each one so Liquibase knows what context is running in each profile, for example:

# application-prod.yml
liquibase:
 changeLog: "classpath:db/changelog/main-changelog.yml"
 contexts: prod
 dropFirst: false

# application-dev.yml
liquibase:
 changeLog: "classpath:db/changelog/main-changelog.yml"
 contexts: dev
 dropFirst: false

Conclusion

If you want to learn more about Liquibase core concepts and how it works, here are a couple resources that you might find useful

Finally, you can download my code example here, it contains all the configurations and tips described in this document including the script to spin up a local database with docker. You can follow the instructions in the README file to spin up the database and run the project to see how Liquibase creates and populates the tables when you run the project for the first time. Happy coding! 👋

Photo by Ula Kuźma on Unsplash
liquibase springboot postgres kotlin