StackTips

Using Flyway for Database Migration in Spring Boot

nilan avtar

Written by

Nilanchala,  21 min read,  4.78K views, updated on Oct. 02, 2024

Flyway is a popular open-source tool for managing database migrations. It makes it easy to manage and version control the database schema for your application.

Flyway supports almost all popular databases including Oracle, SQL Server, DB2, MySQL, Amazon RDS, Aurora MySQL, MariaDB, PostgreSQL and more. For the full list of supported databases, you can check the official documentation here.

How Flyway Migrations Works

Any changes to the database are called migrations. Flyway supports two types of migrations; versioned or repeatable migrations.

  • Versioned migrations Versioned migrations are the most common type of migration, they are applied once in the order they appear. Versioned migrations are used for creating, altering, and dropping tables, indexes or foreign keys.

    Versioned migration files uses naming convention using [Prefix][Separator][Migration Description][Suffix] for example, V1__add_user_table.sql and V2__alter_user_table.sql

  • Repeatable migration Repeatable migrations on the other hand are (re-)applied every time they change. Repeatable migrations are useful for managing views, stored procedures, or bulk reference data updates where the latest version should replace the previous one without considering versioning.

    Repeatable migrations are always applied last after all pending versioned migrations are been executed. Repeatable migration files use naming convention such as R__add_new_table.sql

The migration schemas can be written in either SQL or Java.

When we start the application to an empty database, Flyway will first create a schema history table (flyway_schema_history) table. This table IS used to track the state of the database.

After the flyway_schema_history table is created, it will scan the classpath for the migration files. The migrations are then sorted based on their version number and applied in order.

As each migration gets applied, the schema history table is updated accordingly.

Integrating Flyway in Spring Boot

In this tutorial, we will create a Spring Boot application to deal with MySQL8 database migration using Flyway. This example uses Java 17, Spring Boot 3.2.4 and MySQL 8.0.26. For the database operation, we will use Spring boot JPA.

Install Flyway Dependencies

First, add the following dependencies to your pom.xml or your build.gradle file.

  • The spring-boot-starter-data-jpa dependency is used for using Spring Data Java Persistence API (JPA) with Hibernate.
  • The mysql-connector-j is the official JDBC driver for MySQL databases. It allows your Java application to connect to a MySQL database for operations such as creating, reading, updating, and deleting records.
  • The flyway-core dependency is essential for integrating Flyway into your project, enabling migrations and version control for your database schema.
  • The flyway-mysql dependency adds the Flyway support for MySQL databases. It provides MySQL-specific functionality and optimisations for Flyway operations. It's necessary when your application uses Flyway for managing database migrations on a MySQL database.

pom.xml

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>  
        <groupId>org.flywaydb</groupId>  
        <artifactId>flyway-core</artifactId>  
    </dependency>  
    <dependency>  
        <groupId>org.flywaydb</groupId>  
        <artifactId>flyway-mysql</artifactId>  
    </dependency>
    <!-- Other dependencies-->
</dependencies>       

Configure the Database Connection

Now let us provide the database connection properties in your application.properties file.

# DB properties
spring.datasource.url=jdbc:mysql://localhost:3306/flyway_demo
spring.datasource.username=root
spring.datasource.password=Passw0rd
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#JPA
spring.jpa.show-sql=true

Create Database Changelog Files

Let us now create a couple of database migration schema files inside the resources/db/migrations directory.

V1__add_movies_table

CREATE TABLE movie
(
    id        bigint NOT NULL AUTO_INCREMENT,
    title     varchar(255) DEFAULT NULL,
    headline  varchar(255) DEFAULT NULL,
    language  varchar(255) DEFAULT NULL,
    region    varchar(255) DEFAULT NULL,
    thumbnail varchar(255) DEFAULT NULL,
    rating    enum('G','PG','PG13','R','NC17') DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

V2__add_actor_table.sql

CREATE TABLE actor  
(  
    id         bigint NOT NULL AUTO_INCREMENT,  
    first_name varchar(255) DEFAULT NULL,  
    last_name  varchar(255) DEFAULT NULL,  
    PRIMARY KEY (id)  
) ENGINE=InnoDB;

V3__add_movie_actor_relations.sql

CREATE TABLE movie_actors
(
    actors_id bigint NOT NULL,
    movie_id  bigint NOT NULL,
    PRIMARY KEY (actors_id, movie_id),
    KEY       fk_ref_movie (movie_id),
    CONSTRAINT fk_ref_movie FOREIGN KEY (movie_id) REFERENCES movie (id),
    CONSTRAINT fl_ref_actor FOREIGN KEY (actors_id) REFERENCES actor (id)
) ENGINE=InnoDB;

R__create_or_replace_movie_view.sql

CREATE OR REPLACE VIEW movie_view AS  
SELECT id, title  
FROM movie;

V4__insert_test_data.sql

INSERT INTO movie (title, headline, language, region, thumbnail, rating)
VALUES ('Inception', 'A thief who steals corporate secrets through the use of dream-sharing technology.', 'English',
        'USA', 'inception.jpg', 'PG13'),
       ('The Godfather',
        'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son.',
        'English', 'USA', 'godfather.jpg', 'R'),
       ('Parasite',
        'A poor family, the Kims, con their way into becoming the servants of a rich family, the Parks. But their easy life gets complicated when their deception is threatened with exposure.',
        'Korean', 'South Korea', 'parasite.jpg', 'R'),
       ('Amélie',
        'Amélie is an innocent and naive girl in Paris with her own sense of justice. She decides to help those around her and, along the way, discovers love.',
        'French', 'France', 'amelie.jpg', 'R');

-- Inserting data into the 'actor' table
INSERT INTO actor (first_name, last_name)
VALUES ('Leonardo', 'DiCaprio'),
       ('Al', 'Pacino'),
       ('Song', 'Kang-ho'),
       ('Audrey', 'Tautou');

-- Leonardo DiCaprio in Inception
INSERT INTO movie_actors (actors_id, movie_id)
VALUES (1, 1);

-- Al Pacino in The Godfather
INSERT INTO movie_actors (actors_id, movie_id)
VALUES (2, 2);

-- Song Kang-ho in Parasite
INSERT INTO movie_actors (actors_id, movie_id)
VALUES (3, 3);

-- Audrey Tautou in Amélie
INSERT INTO movie_actors (actors_id, movie_id)
VALUES (4, 4);

These tables are mapped to the following entity classes.

Movie.java

@Entity
@Data
public class Movie {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String headline;
    private String thumbnail;
    private String language;
    private String region;
    @Enumerated(EnumType.STRING)
    private ContentRating rating;

    @ManyToMany
    Set<Actor> actors;
}

public enum ContentRating {
    G,
    PG,
    PG13,
    R,
    NC17
}

Actor.java

@Entity  
@Data  
public class Actor {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;
    String firstName;
    String lastName;
}

Configure Flyway

We can control the migration process using the following properties in application.properties file:

application.properties

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migrations
spring.flyway.baseline-on-migrate=true
spring.flyway.validate-on-migrate=true
PropertyUse
spring.flyway.enabled=trueEnables or disables Flyway's migration functionality for your application
spring.flyway.validate-on-migrate=trueWhen this property is set to true, Flyway will validate the applied migrations against the migration scripts every time it runs a migration. This ensures that the migrations applied to the database match the ones available in the project.

If validation fails, Flyway will prevent the migration from running, which helps catch potential problems early.
spring.flyway.baseline-on-migrate=trueUsed when you have an existing database that wasn't managed by Flyway and you want to start using Flyway to manage it. Setting this to true allows Flyway to baseline an existing database, marking it as a baseline and starting to manage subsequent migrations.
spring.flyway.locationsSpecifies the locations of migration scripts within your project.

Run the Migrations

When you start your Spring Boot application, Flyway will automatically check the db/migrations directory for any new migrations that have not yet been applied to the database and will apply them in version order.

./mvnw spring-boot:run

Reverse/Undo Migrations in Flyway

Flyway allows you to revert migrations that was applied to the database. However, this feature requires you to have Flyway Teams (Commercial) license.

If you're using the community/free version of Flyway, the workaround is to create a new migration changelog file to undo the changes made by the previous migration and apply them.

For example, V5__delete_movie_actors_table.sql

DROP TABLE movie_actors;

Now run the application to apply the V5 migration changelog to your database.

Using Flyway Maven Plugin

Flyway provides a maven plugin to manage the migrations from command line. It provides 7 goals.

GoalDescription
flyway:baselineBaselines an existing database, excluding all migrations up to and including baselineVersion.
flyway:cleanDrops all database objects (tables, views, procedures, triggers, ...) in the configured schemas. The schemas are cleaned in the order specified by the schemas property..
flyway:infoRetrieves the complete information about the migrations including applied, pending and current migrations with details and status
flyway:migrateTriggers the migration of the configured database to the latest version.
flyway:repairRepairs the Flyway schema history table. This will remove any failed migrations on databases without DDL transactions
flyway:undoUndoes the most recently applied versioned migration. Flyway teams only
flyway:validateValidate applied migrations against resolved ones on the classpath. This detect accidental changes that may prevent the schema(s) from being recreated exactly.

To integrate the flyway maven plugin in your maven project, we need to add flyway-maven-plugin plugin to your pom.xml file.

<properties>
    <database.url>jdbc:mysql://localhost:3306/flyway_demo</database.url>
    <database.username>YOUR_DB_USER</database.username>
    <database.password>YOUR_DB_PASSWORD</database.password>
</properties>

<build>
    <plugins>
        <plugin>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <version>10.10.0</version>
            <configuration>
                <url>${database.url}</url>
                <user>${database.username}</user>
                <password>${database.password}</password>
            </configuration>
        </plugin>  

        <!-- other plugins -->
    </plugins>  
</build>

Now you can use the Maven goals

./mvnw flyway:migrate

Maven allows you to define properties in the project's POM and pass the value from the command line.

./mvnw -Ddatabase.username=root -Ddatabase.password=Passw0rd flyway:migrate

Getting Started with Spring Boot- Beginner's Guide

This course covers the fundamentals of Spring Boot an you will gain the necessary skills to develop various types of applications using the powerful features of the Spring Boot framework.

>> CHECK OUT THE COURSE
nilan avtar

Nilanchala

I'm a blogger, educator and a full stack developer. Mainly focused on Java, Spring and Micro-service architecture. I love to learn, code, make and break things.

Related posts

Let’s be friends!

🙌 Stay connected with us on social media for the latest updates, exclusive content, and more. Follow us now and be part of the conversation!