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 throughout 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, 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 now reference 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 of resources that you might find useful
- One-Stop Guide to Database Migration with Liquibase and Spring Boot
- Evolving your database using Spring-boot and Liquibase
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.