Table of Contents
1 Introduction
2. What is Flyway?
3. Why use Flyway?
4. Simple example
4.1. Create Spring Boot project
4.2. Add Flyway dependency
4.3. Springboot adds Flyway configuration
4.4. Create and execute SQL script
4.5. Start testing
4.6. Flyway version management
5. SQL script file naming rules
6. Precautions for use
1. Foreword
In modern application development, database changes are an inevitable process. To manage database versions, maintain migration history, and ensure consistency among development teams, Flyway is a powerful database migration tool, and integration with Spring Boot makes database version management easier.
2. What is Flyway?
Flyway is an open source database migration tool that allows developers to easily manage structural changes to the database. By using Flyway, you can track versions of your database schema and automate upgrades, rollbacks, and migrations. The core idea of Flyway is to separate database migration scripts from application code, ensuring that database evolution can be managed separately from code evolution.
Official website address: Homepage – Flyway
3. Why use Flyway?
To better understand why you should use Flyway, let’s look at some of the main advantages of using Flyway:
- Version Control: Flyway allows you to incorporate database schema changes into a version control system, just like you do with your application code. This way you can easily track the history of database changes and roll back if needed.
- Collaboration: When multiple developers work together on a project, Flyway ensures everyone is using the same database schema. This helps avoid problems caused by database inconsistencies.
- Automation: Flyway provides the ability to automatically execute database migration scripts. This means you don’t need to manually run SQL scripts to update the database, Flyway automatically detects and performs the necessary migrations when the application starts.
- Rollback: If something goes wrong, Flyway allows you to roll back to a previous database version, avoiding possible data loss or corruption.
4. Simple example
4.1. Create Spring Boot project
Create the demo project springboot-flyway and integrate the basic springboot environment.
4.2, add Flyway dependency
You need to pay attention to the MySQL version. I installed MySQL5.7 here, so the highest version supported by flyway is 7.15.0. The corresponding relationships for other versions are:
Flyway 8.x and MySQL versions supported:
- Flyway 8.x releases generally support MySQL 8 and higher.
Flyway 7.x and MySQL versions supported:
- Flyway 7.x releases generally support MySQL 5.7 and higher.
- Flyway 7.0.0 introduces support for the MySQL 8.0 JSON data type.
Flyway 6.x and MySQL versions supported:
- Flyway version 6.x supports MySQL 5.7 and higher.
Flyway 5.x and MySQL versions supported:
- Flyway 5.x version supports MySQL 5.1 and above.
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>7.15.0</version>
</dependency>
4.3, Springboot adds Flyway configuration
spring:
datasource:
dynamic:
primary:primary
# mysql data source configuration
datasource:
primary:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/flyway-demo?useUnicode=true &characterEncoding=utf8 &useSSL=false
username: root
password: root
flyway:
# Whether to enable flyway
enabled: true
encoding: utf-8
# The default storage directory for sql scripts is db/migration.
locations: classpath:db/migration
# sql script file name prefix, default capital V
sql-migration-prefix: V
#The delimiter of sql script file name, default 2 underscores__
sql-migration-separator: __
# Migrate the suffix of the sql script file name
sql-migration-suffixes: .sql
# metadata version control information table default flyway_schema_history
table: flyway_schema_history
# Specify the version number of baseline. The default value is 1. SQL files lower than this version number will be ignored when migrating.
baseline-version: 1
4.4. Create and execute SQL script
Create the db/migration directory under resources. And create and execute MySQL script file:
Create V1.0.0__create_user.sql:
CREATE TABLE IF NOT EXISTS `user`
(
`USER_ID` INT NOT NULL AUTO_INCREMENT,
`USER_NAME` VARCHAR(100) NOT NULL COMMENT 'User name',
`AGE` INT NOT NULL COMMENT 'age',
`CREATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`CREATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
`UPDATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UPDATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
PRIMARY KEY (`USER_ID`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
Create V1.0.0__create_role.sql:
CREATE TABLE IF NOT EXISTS `role`
(
`ROLE_ID` INT NOT NULL AUTO_INCREMENT,
`ROLE_NAME` VARCHAR(100) NOT NULL COMMENT 'Role name',
`CREATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`CREATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
`UPDATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UPDATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN',
PRIMARY KEY (`ROLE_ID`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4;
4.5, Start Test
You can see that the flyway-demo database has successfully executed the script file.
Check MySQL, the table has been successfully created.
4.6, Flyway version management
In addition to the role and user tables we created, there is also the flyway_schema_history table. This is used to record the history and status information of database migration. This table exists to track and manage database version control to ensure that database migrations and structural changes are manageable and traceable. Open it and you can see the transition script record we executed:
By maintaining the flyway_schema_history table, Flyway can check the current state of the database when the application starts and determine which new migration scripts need to be applied. If new scripts need to be applied, Flyway will automatically execute these scripts to ensure that the structure of the database remains consistent with the version of the application.
5. SQL script file naming rules
Execution method + version number__sql name.sql script suffix ---//example: V1.0.0__create_table.sql
- Those starting with V are executed only once. Usually used for DDL table structure operations. V + version (the numbers between version numbers are separated by “.” or “_”, “_” will be automatically compiled into “.”) + “__” + file description + suffix name. For example: V1.0.0__create_user.sql.
- Those starting with R can be executed multiple times. Such as R__optimazed_user.sql.
- Script files starting with V must have a higher execution priority than R.
- The version number is aligned to the far left, such as:
- 1.0.1 is higher than version 1.0.0.
- 1.0.1.1 is a higher version than 1.0.1.
- 1.10.0 is a higher version than 1.9.9.
6. Precautions for use
- Migration script naming convention: Flyway relies on a naming convention for migration scripts, which usually starts with a version number, followed by a double underscore and a descriptive name, such as V1__create_table.sql. The version number is used to determine the order in which the script is executed.
- Do not modify applied migration scripts: Once migration scripts have been applied, they should not be modified. If the database structure needs to be changed, a new migration script should be created to handle the changes.
- Backup and rollback strategy: Before proceeding with database migration, be sure to back up the database to prevent unexpected situations. Flyway supports rollback to a previous version, but caution is still recommended in production environments.
- Multi-environment configuration: Use Spring Boot configuration files to provide different database connection information and Flyway configurations for different environments (development, testing, production).
- Monitoring and logging: Configure appropriate monitoring and logging to track migration execution and handle errors that may arise.
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 132039 people are learning the system