SpringBoot integrates Flyway to upgrade the database version

1. Why use Flyway

In daily development, the following problems are often encountered:

1. Forget to execute SQL written by yourself in all environments;
2. The SQL written by others cannot be sure whether it has been executed in all environments;
3. Someone has modified the executed SQL and expects to execute it again;
4. It is necessary to add new environment for data migration;
5. Each release requires manual control to release the DB version first and then the application version;
6. Other scenarios

It should be a common thing that the database needs to be modified in the later stage due to changes in project requirements or defects in the early design. If the project is not online, the table may be deleted and re created. However, if the project is online, it cannot be so simple and rough. Each time the operation and maintenance deployment project is deployed, the SQL file must be manually executed. You need to upgrade the existing data table through SQL script.

With flyway, these problems can be well solved.

After using Flyway, if you want to upgrade the database version, you don't need the previous database script. You can directly create a new database script. When the project is started, it will be automatically executed after detecting a new and higher version of the script. In this way, it will be much more convenient to work with other colleagues. Because normally, the code is pulled from Git instead of database scripts, so if someone updates the database, other colleagues may not receive the latest notification. Using Flyway can effectively avoid this problem.

All scripts, once executed, will be in flyway_ schema_ There are records in the history table. If you accidentally make a mistake, you can manually start from flyway_ schema_ Delete the record in the history table, and then modify the SQL script before restarting (not recommended for production environment).

2. How does Flyway work

Flyway workflow is as follows:

1. After the project is started and the application completes the establishment of database connection pool, Flyway runs automatically.
2. Flyway will create a flyway for the first time_ schema_ The history table is used to record sql execution records.
3.Flyway will scan all sql scripts under the specified path of the project (classpath: dB / migration by default), which is the same as flyway_ schema_ The history table compares the script records. If the database records the executed script, which is inconsistent with the sql script in the project, flyway will report an error and stop the project execution.
4. If the verification passes, the maximum version number is recorded according to the sql in the table, and all scripts whose version number is not greater than this version are ignored. Then execute other scripts one by one according to the version number from small to large.

3. Use Flyway in the project

First, the core dependency package of flyway is introduced into the pom file:

3.1. Introducing core dependency packages

<dependency>
     <groupId>org.flywaydb</groupId>
     <artifactId>flyway-core</artifactId>
     <version>5.2.4</version>
</dependency>

Version 5.2.4 is used here. After testing, version 7.0.0 conflicts with the currently used SpringBoot version, which will cause flyway not to execute. Therefore, try not to use a higher version of flyway.

3.2 configuration file

Simply configure a property to use

# flyway configuration
spring:
flyway:
# Enable or disable flyway
enabled: true
# The clean command of flyway will delete all table s under the specified schema. Production must be disabled. The default value is false. Theoretically, it is unscientific to use it as the default configuration.
clean-disabled: true
# The directory of SQL scripts. Multiple paths are separated by commas. The default value is classpath:db/migration
locations: classpath:db/migration
# metadata version control information table default flyway_schema_history
table: flyway_schema_history
# Without flyway_ schema_ For the metadata table history, the flyway baseline command must be executed before the flyway migrate command is executed
# When set to true, flyway will automatically execute baseline when baseline is required.
baseline-on-migrate: true
# Specify the version number of the baseline. The default value is 1. SQL files lower than this version number will be ignored when migrating
baseline-version: 1
# Character encoding default UTF-8
encoding: UTF-8
# Allow non sequential migration of development suggestions true production suggestions false
out-of-order: false
# The schema list controlled by flyway is required. Here, we configure flyway. By default, the schema configured by spring.datasource.url is used,
# Multiple schemas can be specified, but the metadata table will only be created under the first schema, and the migration sql script will only be applied to the first schema
# However, the flyway Clean command will be executed under these schema s in turn, so ensure that the production spring.flyway.clean-disabled is true
schemas: flyway
# Whether to automatically call verification during migration. When your version is illogical, for example, if you execute DML without corresponding DDL, an exception will be thrown
validate-on-migrate: true

flyway's properties configuration list (properties not tested):

# Description of the base version at the time of migration
flyway.baseline-description
#Whether to perform benchmark migration automatically when the target schema is found to be non empty and has a table without metadata during migration. The default is false
flyway.baseline-on-migrate =false
#When starting benchmark migration, label the version of the existing schema. The default value is 1
flyway.baseline-version=1
#Check whether the location of the migration script exists. The default is false
flyway.check-location=false
#Whether to automatically call clean when a verification error is found. The default is false
flyway.clean-on-validation-error=false
#Whether to enable flyware. The default value is true
flyway.enabled=true
#Set the code during migration. The default is UTF-8
flyway.encoding
#Whether to ignore the wrong migration when reading the metadata table. The default is false
flyway.ignore-failed-future-migration
#SQL to execute when the connection is initialized
flyway.init-sqls
#The location of the migration script. The default is db/migration
flyway.locations
#Whether to allow disordered migration. The default is false
flyway.out-of-order
#Password for the target database
flyway.password
#Set the prefix of each placeholder. The default is ${
flyway.placeholder-prefix
#Whether to be replaced. The default value is true
flyway.placeholder-replacementplaceholders
#Set the suffix of each placeholder, default}
flyway.placeholder-suffix
#Set the value of placeholder
flyway.placeholders.[placeholder name]
#Set the schema that needs to be migrated by flyware. It is case sensitive. The default is the connection default schema
flyway.schemas
#The prefix of the migration file. The default is V
flyway.sql-migration-prefix
#File name separator for migration script, default__
flyway.sql-migration-separator
#The suffix of the migration script. The default is. sql
flyway.sql-migration-suffix
#The metadata table name used. The default is schema_version
flyway.tableflyway
#The target version used during migration. The default is latest version
flyway.target
#The JDBC URL used during migration. If it is not specified, the configured master data source will be used
flyway.url
#User name of the migration database
flyway.user
#Whether to verify during migration. The default value is true
flyway.validate-on-migrate

yml configuration list of flyway (tested, no problem, the configuration file in yml format is recommended)

spring.flyway.clean-disabled: this attribute is very critical. It indicates whether to clear the tables under the existing library. If the executed script is V1__xxx.sql, the tables under the existing library will be cleared first, and then the script will be executed. This is very convenient in the development environment, but it is fatal in the production environment, and it is cleared by default. The production environment must be configured and set to true.

3.3. Create db/migration

By default, flyway reads the folder under resources/db/migration. If you need to modify this path, you can implement it in the configuration file

3.4. Writing sql files

The naming of the SQL statement here needs to comply with certain specifications, otherwise the flyway will report an error when running. There are two main naming rules:

1. The SQL name that needs to be executed only once starts with a capital "V" followed by a combination of "0 ~ 9" numbers. The numbers can be separated by a "." or an underscore "", and then two underscores__ Split, followed by the file name, and finally. SQL. For example, V20210707__create_user.sql,V20210707__add_user.sql.
2. Repeatable SQL starts with a capital "R", followed by two underscores, followed by the file name, and ends with. SQL.. For example, R__truncate_user_dml.sql.
Among them, the SQL execution priority starting with V is higher than that starting with R.

5: Fixed capital

20210707.01: 20210707 is the date, followed by. 01 for serial number

Because flyway is executed in a sequence, for example, V2021 is executed__ create_ User, execute V2020 again_ update_ user. An error will be reported because 2020 < 2021. Therefore, ensure that the serial number increases in turn.

How does Flyway compare the order of two SQL files? It adopts the principle of left alignment, and the absence is replaced by 0. For example:

1.0.1.1 is higher than 1.0.1.
1.0.10 is higher than 1.0.9.4.
1.0.10 is as high as 1.0.010, and the leading 0 of each version number part will be ignored.

__: This is two_

create_user is a simple sql description

. sql: File suffixes ending in. sql are conventions

As long as the flyway database is created in the database and the project is started, flyway will execute the sql file, create the user table, and automatically generate a flyway_schema_history table

From this startup log, you can see the execution information of Flyway and the execution of database scripts. At the same time, Flyway also created a flyway_schema_history table, which is used to record the update history of the database.

flyway_ schema_ The history will record the execution records of sql files. Every time you start a project, you will go to flyway_schema_history checks whether the sql has been executed. If it has not been executed, it indicates that the sql is a new sql, which will be automatically executed and recorded in the table.

With this record, the next time you start the project, the three script files V20210707.01, V20210707.02 and V20210708.01 will not be executed, because the system knows that the script has been executed. If you want these scripts to be executed again, you need to manually delete flyway_ schema_ According to the corresponding records in the history table, the script will be executed when the project is started.

The files beginning with R are slightly different from those beginning with V. as long as the files beginning with R are sent for modification, they will be executed again. If the file beginning with V has been executed normally, an error will be reported when sending modifications. In order to control the version, try to use the files starting with V, so that you can clearly see the sql files in each version.

4. Frequently asked questions

4.1. Question 1

Problems encountered by flyway Caused by: java.lang.ClassNotFoundException: org.flywaydb.core.api.callback.FlywayCallbac

Reason: the SpringBoot version is inconsistent with the flyway version. Generally, the flyway version is too high.

Solution: just reduce the version of SpringBoot to 5.2.4

4.2. Question 2

SpringBoot integrates flyway, but it does not take effect. Flyway will not automatically execute sql

Reason: above

Reason 2: the database is not configured in the project, and sq dependency or configuration is not introduced

Solution: as above

Solution 2: introduce sql dependency and configure sql information in yml file

4.3. Question 3

FlywayException: Validate failed: Detected failed migration to version

Cause: there is a conflict between the sql script and the database. You need to check the sql script for errors. Simply put, the sql file starting with V has been executed in flyway_ schema_ There is this data in the history table, but the sql file is changed, resulting in an error when executing again.

Solution: create a new sql file. Do not modify the original file starting with V or in flyway_ schema_ Find the file related execution records in the history table, delete and re execute.

Posted by mtwildcard on Sun, 31 Oct 2021 22:04:50 -0700

Programmer Group