StackTips
 15 minutes

Using Flyway for Database Migration in Spring Boot

By Nilanchala @nilan, On Mar 29, 2024 Spring Boot 380 Views

Flyway is a popular open-source tool for managing database migrations. It makes it easier 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 database you can check the official documentation here.

How Flyway Migrations Works

All changes to the database are called migrations and can be either of type versioned or repeatable migrations. The migration schemas can be written in either Sql or Java.

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, dropping tables, indexes or foreign keys. Versioned migration files uses naming convention as V1__add_user_table.sql and V2__alter_user_table.sql e.g.

Repeatable migration on other hand are (re-)applied every time their checksum changes. Repeatable migration are always applied last, after all pending versioned migrations have been executed. Repeatable migration files uses naming convention such as R__add_new_table.sql

When we start the application to an empty database, Flyway will first create a schema history table (flyway_schema_history) table. This table will be 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.

Using Flyway for Database Migration 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.x. For the database operation we will use Spring boot JPA.

Install Flyway Dependencies

First, add the following dependencies to your pom.xml or to 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 optimizations 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.

spring.datasource.url=jdbc:mysql://localhost:3306/test_db  
spring.datasource.username=root  
spring.datasource.password=password  
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver  

spring.jpa.generate-ddl=false  
spring.jpa.hibernate.ddl-auto=none  
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect  
spring.jpa.show-sql=true

Create Database Changelog Files

Le us now create two database migration schema files inside resources/db/migration directory. For this we will use the Versioned migration and the file name for the script file will be V1__added_user_table.sql and V2__added_user_profile_table.sql.

V1__added_user_table.sql

create table user (  
    is_active bit not null,  
    id bigint not null auto_increment,  
    about_me varchar(255),  
    email varchar(255),  
    first_name varchar(255),  
    last_name varchar(255),  
    location varchar(255),  
    password varchar(255),  
    phone varchar(255),  
    user_role enum ('ADMIN','MENTOR','STUDENT'),  
    primary key (id)  
) engine=InnoDB;

V2__added_user_profile_table.sql

create table user_profile (  
    id bigint not null auto_increment,  
    post_count bigint,  
    user_id bigint,  
    email varchar(255),  
    first_name varchar(255),  
    last_name varchar(255),  
    phone varchar(255),  
    username varchar(255),  
    website varchar(255),  
    primary key (id)  
) engine=InnoDB;  

alter table user_profile  
   add constraint UK_ebc21hy5j7scdvcjt0jy6xxrv unique (user_id);  

alter table user_profile  
   add constraint FK6kwj5lk78pnhwor4pgosvb51r  
   foreign key (user_id)  
   references user (id);

These tables are mapped to the following entity classes.

User.class

@Entity 
@Getter 
@Setter
@AllArgsConstructor  
@NoArgsConstructor  
public class User {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  

    private String firstName;  
    private String lastName;  
    private String email;  
    private String password;  
    private boolean isActive;  

    @Enumerated(EnumType.STRING)  
    private UserRole userRole;  

    private String location;  
    private String aboutMe;  
    private String phone;  
}

@Getter  
@RequiredArgsConstructor  
public enum UserRole {  
    ADMIN("admin"),  
    MENTOR("mentor"),  
    STUDENT("student");  

    private final String roleDisplayName;  

}

UserProfile.java

@Entity  
@Data
@AllArgsConstructor  
@NoArgsConstructor  
public class UserProfile {  

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  
    private String username;  
    private String firstName;  
    private String lastName;  
    private String email;  

    private String phone;  
    private String website;  
    private Long postCount;  

    @OneToOne  
    @JoinColumn(name = "user_id", referencedColumnName = "id")  
    private User user;  
}

Configure Flyway

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

application.properties

spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true
spring.flyway.validate-on-migrate=true
spring.flyway.locations=classpath:db/migration
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 baseline and starting to manage subsequent migrations.
spring.flyway.locationsSpecifies the locations of migration scripts within your project.


Run the Migrations

Now you can apply the migrations using following command

./mvnw flyway:migrate

Alternatively, when you start your Spring Boot application, Flyway will automatically check the db/migration 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 is only available in the Flyway Teams (Commercial) edition.

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

For example V3__remove_user_table.sql

DROP TABLE user;

Now apply migration to reverse/undo user table creation.

./mvnw flyway:migrate
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.