Database Migration with Flyway
0 CommentsWith techniques such as continuous delivery becoming more mainstream, automated database migrations are a baseline capability for many software teams. Flyway is a tool that anyone with basic knowledge of SQL can use to set up and maintain database schemas. Database migration with Flyway is gaining high traction in the industry primarily due to its simplicity and its ability to seamlessly integrates with your application lifecycle. Using Flyway, you can:
- Create a new database
- Hold history of migrations
- Have a reproducible state of the database
- Help manage changes being applied to numerous database instances
In this post, I will explain how to use Flyway in a Spring Boot application. We will create a minimalistic Spring Boot application that uses Java Persistence API (JPA) to manage user profiles.
Database and User Creation in MySQL
As a best practice, you should provide minimal access to Spring Boot user account, which is the username and password that you use in your Spring Boot application. The SQL script to create two such service accounts and a database is this.
DROP DATABASE IF EXISTS userprofiledb; DROP USER IF EXISTS `userprofileadmin`@`%`; DROP USER IF EXISTS `userprofileuser`@`%`; CREATE DATABASE IF NOT EXISTS userprofiledbCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER IF NOT EXISTS `userprofileadmin`@`%` IDENTIFIED WITH mysql_native_passwordBY 'password'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `userprofiledb`.* TO `userprofileadmin`@`%`; CREATE USER IF NOT EXISTS `userprofileuser`@`%` IDENTIFIED WITH mysql_native_passwordBY 'password'; GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ON `userprofiledb`.* TO `userprofileuser`@`%`; FLUSH PRIVILEGES;
The preceding script creates two service accounts userprofileuser and userprofileadmin. It also creates a userprofiledb database. The userprofileadmin account has the additional privilege to update the database and its tables.
Execute the preceding SQL script using a MySQL client. I am using MySQL Workbench to run the script.
The pom.xml File
To use Flyway with MySQL, you need the flyway-mysql dependency. The pom.xml file of the application is this.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
<relativePath/><!-- lookup parent from repository -->
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>db-migration-flyway</artifactId>
<properties>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-mysql</artifactId>
<version>8.5.13</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
<scope>compile</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
The Entity Class
I have a minimal UserProfile JPA entity for the purpose of this post. I am using Lombok to generate boilerplate code. If you are new to Lombok, I suggest you go through my Lombok post series.
UserProfile.java
package guru.springframework.flyway.domain;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Data
@NoArgsConstructor
@Entity
@Table(name = "user_profile")
public class UserProfile{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String city;
public UserProfile(String name, String city) {
this.name = name;
this.city= city;
}
}
The Repository Interface
I am using the repository programming model of Spring Data JPA. The repository interface is this.
UserProfileRepository.java
package guru.springframework.flyway.repositories;
import guru.springframework.flyway.domain.UserProfile;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface UserProfileRepositoryextends CrudRepository<UserProfile,Long> {
}
The Bootstrap Class
I have created a bootstrap class to insert seed data to the database when the application class. The bootstrap class is this.
UserProfileLoader.java
package guru.springframework.flyway.bootstrap;
import guru.springframework.flyway.domain.UserProfile;
import guru.springframework.flyway.repositories.UserProfileRepository;
import org.apache.catalina.User;
import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Configuration;
@Configuration
public class UserProfileLoaderimplements CommandLineRunner{
private final UserProfileRepositoryuserProfileRepository;
public UserProfileLoader(UserProfileRepositoryuserProfileRepository) {
this.userProfileRepository= userProfileRepository;
}
@Override
public void run(String... args) throws Exception {
userProfileRepository.deleteAll();
UserProfileuserProfileEJ= new UserProfile("Eric Johnson", "Oklahoma City");
UserProfilesavedEJ= userProfileRepository.save(userProfileEJ);
UserProfileuserProfileAS= new UserProfile("Andy Smith", "New York City");
UserProfilesavedAS= userProfileRepository.save(userProfileAS);
userProfileRepository.findAll().forEach(user -> {
System.out.println("Name: " + user.getName());
System.out.println("City: " + user.getCity());
});
}
}
The Properties File
The properties file will hold the information required to the userprofiledb database in the MySQL instance. In this file, we will also provide the service account credentials.
V<Version No>_ _<Name>.sql
Note the double underscore post Version No;. You can read more about Flyway naming patterns here.
application.properties spring.datasource.username=userprofileuser spring.datasource.password=password spring.datasource.url=jdbc:mysql://127.0.0.1:3306/userprofiledb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC spring.jpa.hibernate.ddl-auto=validate spring.sql.init.mode=always spring.flyway.user=userprofileadmin spring.flyway.password=password
The spring.flyway.user and spring.flyway.password properties instruct Spring Boot to create a separate database connection with the specified credentials to perform migrations.
Migration Script
By default, Spring Boot looks for Flyway migration scripts in resources/db/migration directory. Migration scripts have naming standard and the syntax is:
Note the double underscore postV<Version No;. You can read more about Flyway naming patterns here.
V<Version No>_ _<Name>.sql
The migration script is this.
V1__init_database.sql
drop table if exists user_profile;
drop table if exists hibernate_sequence;
create table user_profile (
id bigintnot null,
name varchar(255),
city varchar(255),
primary key (id)
) engine=InnoDB;
create table hibernate_sequence (
next_valbigint
) engine=InnoDB;
insert into hibernate_sequencevalues ( 1 );
The preceding Flyway script creates a user_profile table.
Running the Application
Build, package, and run your application.
The application creates the tables in the userprofiledb database and populates the user_profile table with seed data.
In the preceding figure, note the flyway_schema_history table. Flyway exclusively uses this table to track the version of each database, recording in it every versioned migration file applied to build that version.
The structure and content of the flyway_schema_history table is something like this.
To accommodate evolving database schemas, Flyway:
- Scans the application classpath (current working directory) and locations for available migration files.
- Compares migration files against the history table. If a version number in the filename is lower or equal to a version marked as current, it is ignored
- Marks any remaining migration files as pending migrations. These are sorted based on the version number
- Executes every migration file with a version number in version order, between the current version and the version specified as a target. If no target version is specified, it executes them all.
- Updates the metadata table accordingly, as each migration is applied
Note: The flyway_schema_history table is exclusively for use by Flyway. Never edit or update it directly.
Migration Script to Alter Table
Assume that we need to alter the user_profile table to add a gender type. To do so, we will update the entity class with a gender field, like this.
UserProfile.java
package guru.springframework.flyway.domain;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Data
@NoArgsConstructor
@Entity
@Table(name = "user_profile")
public class UserProfile{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String city;
private char gender;
public UserProfile(String name, String city, char gender) {
this.name = name;
this.city= city;
this.gender=gender;
}
}
We will also need to update the UserProfileLoader class to initialize UserProfile objects additionally with genders, like this.
UserProfileuserProfileEJ= new UserProfile("Eric Johnson", "Oklahoma City", 'M');
UserProfilesavedEJ= userProfileRepository.save(userProfileEJ);
UserProfileuserProfileAS= new UserProfile("Andy Smith", "New York City",'M');
UserProfilesavedAS= userProfileRepository.save(userProfileAS);
Next is to create a migration script to alter the user_profile table. We will name the migration script as V2_ _add_gender_to_user_profile.sql
V2_ _add_gender_to_user_profile.sql
alter table user_profileADD gender CHAR;
On accessing the lyway_schema_history table, you can see the versioning information of the migration script.
Flyway with Spring Boot Java Configuration
Spring Boot provides the FlywayMigrationStrategy functional interface as the strategy to initialize Flyway migration.
You can create custom implementations of this interface as a Spring Bean to override the default migration behavior.
For example, before migration, you might want to clean up the database.
Note: This use case is purely for development purpose and not to be used in production.
The following code shows a Spring configuration class, JavaConfigDbClean that creates a FlywayMigrationStrategy bean to perform a database clean-up before migration.
JavaConfigDbClean.java
package guru.springframework.flyway.config;
import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class JavaConfigDbClean{
@Bean
public FlywayMigrationStrategyclean(){
return flyway -> {
flyway.clean();
flyway.migrate();
};
}
}
On running the application now, you can check the console log to ensure that Flyway has cleaned the database before running the two migration scripts.
Summary
An alternative to Flyway that is widely used in the industry is Liquibase. Spring Boot supports both Flyway and Liquibase. However, both tools have additional capabilities available from the command line or build tool plugins. You can use one of them in your system independent of Spring Boot. I have used both and found Liquibase is a larger and more robust product as compared to Flyway. The latter supports SQL and Java only. But Flyway seems to have more popularity. Irrespective, both tools are mature and widely used. In upcoming posts, I plan to cover both in details.
If you want to go in-depth on how to use both the tools, I recommend my Udemy course Hibernate and Spring Data JPA: Beginner to Guru
