Managing Database Migrations with Spring Boot Liquibase and PostgreSQL

Introduction: Why Database Migrations Matter?

Database migrations are critical for maintaining a consistent and reliable schema across environments in modern application development. If you’re working with Spring Boot and PostgreSQL, Liquibase offers a powerful, flexible solution for managing database changes. In this guide, we’ll explore setting up Liquibase with Spring Boot and PostgreSQL to simplify schema updates, version control, and rollbacks.

Why Use Liquibase?

Liquibase is a widely used open-source tool that provides a structured way to track, manage, and apply database schema changes. It offers a variety of features to support developers in complex environments:

• Changelog Files: Liquibase organizes migrations into changelog files that support XML, YAML, JSON, or SQL formats.
• Rollback Support: Liquibase allows you to define rollback logic, making it easy to revert changes if needed.
• Database Independence: With support for over 30 databases, Liquibase offers flexibility across projects. By integrating Liquibase with Spring Boot, you can automate migrations, manage schema versioning, and improve collaboration across teams.

In this article, we will be using PostgreSQL as our database. You can maintain your database in any database management system. For a convenient deployment option, consider cloud-based solutions like Rapidapp, which offers managed PostgreSQL databases, simplifying setup and maintenance.

tip

Create a free database in Rapidapp in seconds here

Step-by-Step Guide to Use Liquibase Migrations

Project Initialization and Dependencies

We will be using Spring Boot and PostgreSQL to build a todo application. You can initialize a spring boot project by using Spring Boot CLI. Once installed, you can use following command to initialize a project with required dependencies.

spring init \
  --dependencies=liquibase,data-jpa,postgresql \
  --type=maven-project \
  --javaVersion=21 \
  liquibase-migrations-demo

Line 2: liquibase for Liquibase integration, data-jpa for database persistence, and postgresql for PostgreSQL driver.

Line 3: --type=maven-project for creating a Maven project.

Line 4: --javaVersion=21 we will use Java 21 in Google Cloud Run environment.

Now that we initialized the project, go to the folder liquibase-migrations-demo and open it with your favourite IDE.

Application Configuration

Next, configure Liquibase in your application.properties file. At a minimum, you need to specify the database connection details:

application.properties showLineNumbers

spring.datasource.url=jdbc:postgresql://<host>:<port>/<db>
spring.datasource.username=<user>
spring.datasource.password=<password>

spring.liquibase.enabled=true
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.yaml

The spring.liquibase.change-logs property specifies the location where Liquibase will look for changelogs which contains actual schema changes.

Create Your First Changelog File

Liquibase migrations are structured into changelog files that record each schema change as an individual "changeSet."

1. In src/main/resources/db/changelog/, create a new file named db.changelog-master.yaml. This file will be the main changelog file that references individual changes.

databaseChangeLog:
  - include:
      file: classpath:/db/changelog/changes/01-create-accounts-table.sql

2. Now, create the referenced file 01-create-accounts-table.sql to define your first changeSet:

01-create-accounts-table.sql

CREATE TABLE accounts (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  email VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  last_login TIMESTAMP
);

When it comes to naming the migration files, it's a good practice to follow a consistent naming convention. A common approach is to prefix the file with a version number and a brief description, like 01-create-accounts-table.sql.

• Version number: There is no convention for the version number, but it helps on ordering the migration files.
• Description: Provide a brief description of the migration.

SQL File Features in Liquibase

When using SQL, Liquibase offers some additional features within comments, such as:

• Changesets: Define each migration step using --changeset author:id in comments. This keeps Liquibase’s tracking in place, similar to other formats.
• Rollback Commands: Optionally add rollback commands with --rollback <SQL command>, allowing automatic rollbacks when needed. You can see a simple example below;

--liquibase formatted sql
--changeset huseyinbabal:1
CREATE TABLE accounts (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  email VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  last_login TIMESTAMP
);

--rollback DROP TABLE accounts;

Running Application

When you run your Spring Boot application, Liquibase will automatically detect and apply any pending migrations to your PostgreSQL database. You’ll see output in the console indicating that the migrations have been successfully applied. You can run application as follows.

./mvnw spring-boot:run

Rollback

If you need to rollback a migration, you can use the rollback command. For example, to rollback the last changeSet, you can run:

./mvnw liquibase:rollback \
    -Dliquibase.rollbackCount=1 \
    -Dliquibase.url="jdbc:postgresql://<host>:<port>/<db>" \
    -Dliquibase.username="<username>" \
    -Dliquibase.password="<password>" \
    -Dliquibase.changeLogFile=src/main/resources/db/changelog/db.changelog-master.yaml \
    -Dliquibase.driver=org.postgresql.Driver

This time we used liquibase:rollback command to rollback the last changeSet. You can also rollback to a specific changeSet by providing liquibase.rollbackTag parameter. For more information, you can check the official documentation.

Best Practices for Migration Files

To ensure smooth database migrations, follow these best practices:

• Organize Changelogs: Use a master changelog file to keep a well-organized list of all changes.

• Use Descriptive Names: Name changelog files descriptively to make it clear what each migration does.

• Version Control Changelogs: Commit changelog files to version control to ensure that schema changes are applied consistently across environments.

Understanding the Liquibase Metadata Table

When you run migrations with Liquibase, it automatically creates two metadata tables in your database: DATABASECHANGELOG and DATABASECHANGELOGLOCK. These tables keep track of migration status and control, ensuring changes are applied in a consistent and non-conflicting manner. Let’s look at each table in detail.

DATABASECHANGELOG Table

The DATABASECHANGELOG table records every changeSet that has been successfully applied to the database, allowing Liquibase to know which changes have already been executed. Here’s an overview of its key columns:

• ID: A unique identifier for each changeSet, as specified in the changelog file. This ID is combined with the AUTHOR and FILENAME columns to uniquely identify a changeSet.
• AUTHOR: The author of the changeSet, which is specified in the changelog file.
• FILENAME: The file path of the changelog file containing the changeSet. This helps differentiate between changeSets if multiple changelog files are used.
• DATEEXECUTED: The timestamp indicating when the changeSet was applied. This helps you track when each change was made.
• ORDEREXECUTED: A number indicating the order in which the changeSets were applied, starting from 1. This can help troubleshoot issues by showing the order of migrations.
• EXECTYPE: Specifies how the changeSet was executed. Common values include EXECUTED (successful), RERAN, and FAILED, which indicate the status of each migration.
• MD5SUM: A hash value representing the changeSet’s content. Liquibase uses this hash to detect if a changeSet has been modified after it was initially applied.
• DESCRIPTION: A brief description of the change, based on the change type (e.g., createTable, addColumn).
• COMMENTS: Any comments associated with the changeSet, as specified in the changelog.
• TAG: Optional; a tag that can be assigned to a particular changeSet, often used to mark significant points in the schema history (e.g., a major release).
• LIQUIBASE: The version of Liquibase used to apply the changeSet. This can be useful for tracking compatibility.
• CONTEXT: An optional field that can be used to specify a context for the changeSet. This can help control which changeSets are applied based on the context.
• LABELS: Optional; a comma-separated list of labels that can be assigned to a changeSet. Labels can be used to group changeSets based on common characteristics.
• DEPLOYMENT_ID: An identifier that can be used to track deployments. This can be useful for auditing and tracking changes across environments. The DATABASECHANGELOG table essentially serves as a ledger for Liquibase, ensuring it applies only new changeSets, avoiding duplications, and maintaining the history of changes.

DATABASECHANGELOGLOCK Table

The DATABASECHANGELOGLOCK table controls the concurrency of migrations to prevent multiple processes from applying changes at the same time, which can lead to conflicts or corruption. It has fewer columns but plays a critical role in database integrity.

• ID: A unique identifier for the lock. This is generally 1 because only one lock record is required.
• LOCKED: A boolean value indicating if a Liquibase process currently holds the lock. When set to TRUE, it prevents other instances from running migrations simultaneously.
• LOCKGRANTED: The timestamp when the lock was acquired. This can be useful for diagnosing if a lock has been held too long (e.g., if a migration process was interrupted).
• LOCKEDBY: Information on the host or process that acquired the lock, often containing the hostname and IP address. This can help identify which process holds the lock if issues arise.

The DATABASECHANGELOGLOCK table ensures that only one Liquibase instance modifies the schema at a time, which is particularly useful in distributed systems or CI/CD environments where migrations might be triggered concurrently.

How These Tables Support Safe and Consistent Migrations

• The DATABASECHANGELOG table ensures migrations run in a consistent sequence and that changes are not re-applied or skipped.
• The DATABASECHANGELOGLOCK table prevents race conditions, helping avoid potential conflicts from simultaneous migrations. Understanding these tables can help you troubleshoot migration issues and ensure Liquibase runs smoothly. Both tables are essential for Liquibase’s reliability and ability to manage complex database schemas effectively.

Conclusion

By adopting Liquibase in your Spring Boot project, you can efficiently manage database changes, reduce deployment friction, and allow your team to focus more on innovation. Whether you’re deploying new features or managing complex database versions, Liquibase streamlines the process, making database management simpler, safer, and more collaborative.