In this post, we’ll learn how to implement Flyway as Database Migrations in a Spring Boot application.

· Prerequisites
· Overview
∘ What is Flyway?
∘ Key Features of Flyway
∘ How Flyway Works
· Getting Started
∘ Configure Flyway
∘ Create Migration Files
∘ Run the Application
· Conclusion
· References

Prerequisites

This is the list of all the prerequisites:

• Spring Boot 3+
• Maven 3.6.3
• Java 21
• PostgreSQL

Overview

What is Flyway?

Flyway is an open-source database migration tool that helps manage and version control database schemas. It uses SQL scripts or Java-based migrations to track changes, making it easier to synchronize databases across different environments (development, testing, production). You can check the official documentation for the full list of supported databases.

Key Features of Flyway

• Version Control for Database Schemas: Flyway allows you to apply incremental changes to your database schema using versioned migration scripts, ensuring that every change is tracked.
• SQL-Based Migrations: Migrations are typically written in SQL, making it easy for developers familiar with SQL to create and manage schema changes.
• Automatic Migration Management: Flyway automatically detects and applies new migrations when the application starts, simplifying the deployment process.
• Rollback Support: Flyway supports undo migrations, enabling you to roll back changes if needed (though this requires you to create explicit undo scripts).
• Database Compatibility: Flyway supports a wide range of databases, including PostgreSQL, MySQL, Oracle, SQL Server, and more.
• Integration with Build Tools: Flyway can be easily integrated into build tools like Maven and Gradle, as well as CI/CD pipelines, facilitating automated database migrations during deployment.

How Flyway Works

In Flyway, all changes to the database are called Migrations. Migrations can be versioned or repeatable.

1. Versioned migrations have a version, a description, and a checksum. The version must be unique. The description is purely informative so that you can remember what each migration does. The checksum is there to detect accidental changes. Versioned migrations are the most common type of migration. They are applied in order exactly once. Optionally their effect can be undone by supplying an undo migration with the same version.
2. Repeatable migrations have a description and a checksum, but no version. Instead of being run just once, they are (re-)applied every time their checksum changes. Within a single migration run, repeatable migrations are always applied last, after all pending versioned migrations have been executed. Repeatable migrations are applied in the alphanumeric order of their description

To be picked up by Flyway, SQL migrations must comply with the following naming pattern:

The file name consists of the following parts:

• Prefix: V for versioned (configurable), U for undo (configurable) and R for repeatable migrations (configurable)
• Version: Version with dots or underscores separate as many parts as you like (Not for repeatable migrations)
• Separator: __ (two underscores) (configurable)
• Description: Underscores or spaces separate the words
• Suffix: .sql (configurable)

Getting Started

We will start by creating a simple Spring Boot project from start.spring.io.

Configure Flyway

We need to configure Flyway in the application.yaml file.

spring:
  application:
    name: spring-boot-flyway
  jpa:
    open-in-view: false
    show-sql: true
    properties:
      hibernate:
        format_sql: true
    hibernate:
      ddl-auto: none
  datasource:
    driver-class-name: org.postgresql.Driver
    username: ${SPRING_DATASOURCE_USERNAME}
    password: ${SPRING_DATASOURCE_PASSWORD}
    url:  ${SPRING_DATASOURCE_URL}
    hikari:
      poolName: Hikari
      auto-commit: false
  flyway:
    enabled: true #Enables or disables Flyway's migration functionality for your application
    locations: classpath:db/migration #Specifies the locations of migration scripts within your project.
    validate-on-migrate: true #Flyway will validate the applied migrations against the migration scripts every time it runs a migration.

Flyway uses SQL migration files to apply changes to the database. By default, these files are placed in a directory called classpath:db/migration, but the location can be modified by setting spring.flyway.locations. This is a comma-separated list of one or more classpath: or filesystem: locations.

Create Migration Files

In this story we will use the following book database schema:

We create the files based on the Flyway convention we mentioned in the first part.

• V0.0.1_1__add_author_table.sql

create table public.author
(
    id        serial primary key,
    firstname character varying(255) COLLATE pg_catalog."default",
    lastname  character varying(255) COLLATE pg_catalog."default"
);

• V0.0.1_2__add_book_table.sql

create table public.book
(
    id          serial primary key,
    description text,
    isbn        character varying(255) COLLATE pg_catalog."default",
    page        integer          NOT NULL,
    price       double precision NOT NULL,
    title       character varying(100) COLLATE pg_catalog."default",
    author_id   integer          not null references public.author (id)
);

• V0.0.1_3__insert_author_data.sql

-- init data
INSERT INTO public.author (id, firstname, lastname)
VALUES (1, 'Bree', 'Nasim'),
       (2, 'Kessie', 'Brenden'),
       (3, 'Willow', 'Kirby'),
       (4, 'Lareina', 'Lunea'),
       (5, 'Flavia', 'Zane'),
       (6, 'Noah', 'Maxwell'),
       (7, 'Kelsey', 'Clinton'),
       (8, 'Gage', 'Marsden'),
       (9, 'Perry', 'Elijah'),
       (10, 'Kennedy', 'Clementine');

ALTER SEQUENCE author_id_seq RESTART WITH 11;

• V0.0.1_4__insert_book_data.sql

INSERT INTO public.book (id, description, isbn, page, price, title, author_id)
VALUES (1, 'netus et malesuada', 'X4J 5H8', 62, 529, 'arcu. Vestibulum ut', 9),
       (2, 'mollis non,', 'M3Q 4G1', 15, 668, 'Nullam ut', 2),
       (3, 'Maecenas mi felis, adipiscing fringilla, porttitor', 'B5W 1Y8', 16, 708, 'et ipsum cursus', 5),
       (4, 'eros turpis non enim. Mauris quis turpis', 'Q1O 7Y6', 46, 642, 'Nulla tincidunt,', 4),
       (5, 'tellus non magna. Nam ligula elit, pretium', 'Q0V 7Q9', 86, 656, 'purus, in', 1),
       (6, 'a, facilisis non, bibendum sed, est.', 'V6Q 8T2', 57, 299, 'sagittis', 3),
       (7, 'suscipit nonummy. Fusce fermentum fermentum arcu. Vestibulum ante ipsum', 'Q2T 8C5', 68, 891,
        'ligula. Donec', 8),
       (8, 'arcu. Vivamus sit amet risus. Donec egestas.', 'R5E 3I4', 14, 455, 'vel', 6),
       (9, 'pede, nonummy ut, molestie in, tempus', 'I0W 6N9', 33, 874, 'lorem semper', 8),
       (10, 'sed consequat auctor, nunc nulla vulputate dui, nec', 'U4E 5V8', 7, 185, 'vel arcu.', 4);

ALTER SEQUENCE book_id_seq RESTART WITH 11;

Run the Application

When the Spring Boot application runs, Flyway automatically checks for migration files in the specified directory and applies those that still need to be used in the flyway_schema_history table.

The flyway_schema_history table serves as a record of all migrations that have been applied to the database. It keeps track of the version, description, and checksum of each migration, as well as the timestamp when it was applied.

The flyway_schema_history table typically has the following columns:

• installed_rank: The order in which the migration was applied.
• version: The version number of the migration.
• description: A brief description of the migration.
• type: The type of migration (e.g., SQL, Java).
• script: The name of the migration script.
• checksum: A checksum of the migration script.
• installed_by: The username of the user who applied the migration.
• installed_on: The timestamp when the migration was applied.
• execution_time: The time it took to execute the migration.
• success: A boolean indicating whether the migration was successful.

Now let’s run the project.

The flyway_schema_history table

Conclusion

Well done !!. This post taught us how Flyway works and how to implement it in a Spring Boot application.

The complete source code is available on GitHub.

You can reach out to me and follow me on Medium, Twitter, GitHub, Linkedln

Support me through GitHub Sponsors.

Thank you for reading!! See you in the next story.

References

• https://www.red-gate.com/products/flyway/community/
• https://docs.spring.io/spring-boot/how-to/data-initialization.html#howto.data-initialization.migration-tool.flyway
• https://stackoverflow.com/questions/tagged/flyway

👉 Link to Medium blog