Streamlining Database Migrations with Spring Boot Flyway and PostgreSQL

Introduction: Why Database Migrations Matter?

In the fast-paced world of software development, change is inevitable. As applications evolve, so too must the databases that support them. Whether you’re adding new features, optimizing performance, or fixing bugs, database schema changes are a critical part of the process. However, managing these changes manually can quickly become complex and error-prone, especially as your team grows or your application scales.

This is where database migrations come into play. Migrations allow you to apply consistent, repeatable changes to your database schema across all environments—development, testing, staging, and production. By automating this process, you reduce the risk of human error, ensure consistency, and maintain a clear history of how your database has evolved over time.

What is Flyway?

Flyway is an open-source database migration tool that makes it easy to manage and track schema changes. It works by applying incremental SQL or Java-based migrations in a controlled manner, ensuring that your database schema is always in sync with your application’s needs. Flyway integrates seamlessly with popular databases like PostgreSQL and frameworks like Spring Boot, making it a powerful tool for modern application development.

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 Flyway 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=flyway,data-jpa,postgresql \
  --type=maven-project \
  --javaVersion=21 \
  flyway-migrations-demo

Line 2: flyway for Flyway 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 flyway-migrations-demo and open it with your favourite IDE.

Application Configuration

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

application.properties

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

The spring.flyway.locations property specifies the location where Flyway will look for migration files. By default, this is classpath:db/migration.

Create Migration Files

Migration files are where you define the changes to your database schema. Each migration file has a unique version number and a descriptive name. For example:

V1__Add_user_table.sql
V2__Alter_user_table_add_email.sql

• Version number: Start with "V" followed by a version number (V1, V2, etc.). This helps Flyway determine the order in which migrations should be applied.
• Separator: Use double underscores __ to separate the version number from the description.
• Description: Provide a brief description of the migration.

Here’s an example of a simple migration file that creates a user table:

V1__Add_user_table.sql

CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Running Application

When you run your Spring Boot application, Flyway 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

Best Practices for Migration Files

To ensure smooth database migrations, follow these best practices:

• Keep Migrations Small and Incremental: Break down complex changes into smaller, manageable steps. This makes it easier to troubleshoot issues and roll back changes if necessary.

• Use Descriptive Names: The name of each migration should clearly describe its purpose. This makes it easier to understand the history of changes at a glance.

• Test Migrations Thoroughly: Before applying migrations to production, test them in a staging environment that closely mirrors production. This helps catch any issues early.

• Avoid Direct Modifications in Production: Always use migrations to make changes to the database schema. Direct modifications can lead to inconsistencies and make it difficult to track changes.

• Version Control Your Migrations: Store your migration files in version control along with your application code. This ensures that schema changes are tracked and can be rolled back if needed.

Understanding the Flyway Metadata Table

Flyway maintains a metadata table in your database, typically named flyway_schema_history, to track which migrations have been applied. This table contains information such as:

Version: The version number of the migration. Description: The description of the migration. Script: The name of the migration file. Execution Time: How long the migration took to apply. Status: Whether the migration was successful. This table is crucial for managing and auditing your database schema. It ensures that migrations are only applied once and provides a clear history of changes.

Conclusion

Database migrations are a vital part of modern application development, enabling you to manage schema changes in a consistent, repeatable way. By integrating Flyway with Spring Boot and PostgreSQL, you can automate this process and reduce the risk of errors, ensuring that your database schema evolves alongside your application.