How to perform database schema versioning with Flyway and Spring?
Flyway is a tool used for database migration control. Migrations are executed automatically, and we also have a history of database modifications.
Flyway supports many databases, but in this example, we will use MySQL.
Requirements
- JDK 17;
- Spring 3;
- SpringDataJpa;
- Mysql;
- Flyway;
Project
<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>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-mysql</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>With this site, you can create your Spring project: https://start.spring.io
To establish a connection with the database, we need to specify some variables in the properties file:
spring.datasource.url=jdbc:mysql://localhost/company?createDatabaseIfNotExist=true&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=I am using MySQL through a Docker container, with the root user and no password.
I created two entities that will represent two tables in the database, and the mapping was done using annotations.
package natan.code.company.domain.model;
import jakarta.persistence.*;
import lombok.Data;
import lombok.EqualsAndHashCode;
@Data
@Entity
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
public class Branch {
@EqualsAndHashCode.Include
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false)
private String name;
}
package natan.code.company.domain.model;
import jakarta.persistence.*;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.time.OffsetDateTime;
@Data
@Entity
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@EqualsAndHashCode.Include
private Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private String email;
@Column(nullable = false, columnDefinition = "datetime", name = "birth_datetime")
private OffsetDateTime dateOfBirth;
@ManyToOne
@JoinColumn(name = "branch_id", nullable = false)
private Branch branch;
}
Let’s observe what happens when the project starts.
There are no migrations, and a table called “flyway_schema_history” was created in our database. What does it do?
Its purpose is to store the version history. When a migration is executed, it is stored in this table. This is how Flyway keeps track of the history to manage it.
And where is the migration?
Inside the resources folder, we need to create two more folders: db/migration.
Inside the migration folder, we can create the migration file. We need to follow this structure: “V001__create_initial_tables.sql”.
We can also have version variations, such as 1.1, 1.2, 2.1.
We will use this pattern: 001, 002, 003, and so on.
In the migration, we will specify the following script:
CREATE TABLE `branch` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `employee` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`birth_datetime` datetime(6) DEFAULT NULL,
`branch_id` bigint NOT NULL,
PRIMARY KEY (`id`),
KEY `FK1ppr8greedyrey8nchpr0v4dn` (`branch_id`),
CONSTRAINT `FK1ppr8greedyrey8nchpr0v4dn` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;When running the application, we get the following result:
The migration was executed, and we can see in the “flyway_schema_history” table the history of migrations.
Now we need to add the cellphone field to the “employee” table.
I created the migration “V002__adding_cellphone_in_employee.sql” with the following script.
alter table `employee` add cellphone varchar(15);Upon execution, we get the following result:
