background

In project development, program code can use SVN or Git to manage version. However, versioning has always been a headache in database development, and spring’s official support for flyway and Liquibase database versioning tools has been revealed through research

flyway

Flyway is an agile tool for porting databases. Java development, support for all JDBC compatible databases.

It is mainly used to upgrade your database structure and data as your application version is constantly updated.

Website flywaydb.org/

Supported Databases

Oracle, SQL Server, DB2, MySQL, Aurora MySQL, MariaDB, Percona XtraDB Cluster, PostgreSQL, Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, SQLite, Firebird

How Flyway works

In short: Flyway keeps track of version history through a history sheet (flyway_schema_history). Each time the project starts, files under resources/db/migration will be automatically scanned and flyway_schemA_history will be queried to determine whether the files are new. If the file is a new file, perform the migration. If not, ignore it

Flyway_schema_history table structure

CREATE TABLE `flyway_schema_history` (
  `installed_rank` int(11) NOT NULL,
  `version` varchar(50) DEFAULT NULL,
  `description` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `script` varchar(1000) NOT NULL,
  `checksum` int(11) DEFAULT NULL,
  `installed_by` varchar(100) NOT NULL,
  `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `execution_time` int(11) NOT NULL,
  `success` tinyint(1) NOT NULL,
  PRIMARY KEY (`installed_rank`),
  KEY `flyway_schema_history_s_idx` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

The principle of analytic

First execution: when executed on an empty database. A flyway_schema_history is created. This table is used to record and track the status of the database version. Flyway then scans the file system or database files in the classpath path of the project

Repeat: Flyway scans the migration file again and compares the version number of the migration with the version number in the history sheet. Flyway will ignore migrating files whose version number is smaller than or equal to the current largest version in the table. The remaining files will be migrated in ascending order of version. (This does not actually ignore, but checks the checksum value to ensure that the historical version file has not been tampered with.)

Springboot integration flyway

Rely on

You don’t need to specify the flyway version number here, because it is already specified in the SpringBoot parent dependency, which might cause package version conflicts if executed

<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> < version > 2.2.4. RELEASE < / version > < / parent >Copy the code

< the dependency > < groupId > mysql < / groupId > < artifactId > mysql connector - Java < / artifactId > < version > 8.0.17 < / version > </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>Copy the code

Increase the configuration

Spring.flyway.clean -disabled=true # Spring.flyway.clean -disabled=true Default flyway_schema_history spring.flyway.table= flyway_schemA_history # Specifies whether to allow spring.flyway.out-of-order=false # Spring. Flyway. Baseline on-migrate=true # migrate baseline on-migrate=true if the database is not empty. Spring.flyway. Baseline -version=0 # # Database user name #spring.flyway.user= # database password # flyway.flyway.validate-on-migrate =trueCopy the code

The actual configuration

spring: datasource: type: com.alibaba.druid.pool.DruidDataSource druid: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/renren-fast? useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai username: password: flyway: baseline-on-migrate: true enabled: true locations: classpath:db/migration baseline-version: 1Copy the code

Adding script files

Create the DB/Migration folder under the Resources directory

Create the version migration file v1__initial. SQL in the DB /migration directory

Naming rules

The db/migration directory is created under SRC /resources and the corresponding SQL files are created in the directory. The files are divided into different file types based on the convention and configuration principles. The file names are used to distinguish between them

Version migration starts with V and is performed only once. The rollback migration starts with U and is not used. Repeatable Migration starts with R and is reexecuted after each change

Beginning of demo V

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for initial_monitor -- ---------------------------- CREATE TABLE `users` ( id bigint not null auto_increment, name varchar(50) not null, Primary key (ID)) ENGINE = InnoDB CHARACTER SET = utF8 COLLATE = UTF8_general_ci COMMENT = 'user table' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; )Copy the code

Start the project and check that the users table is added to the database and that flyway_schemA_history has data

Demonstration begins with R

Create the version migration file r__adduser. SQL in the DB /migration directory

insert into users (name) values ("test");
Copy the code

After execution, it is found that there is one more record in the user table, and the second execution will only have one record. Only after we modify the content will the execution be repeated

insert into users (name) values ("test123");
Copy the code

This adds one more record to the table

Demo U beginning

This version is not supported, the Pro version is supported, and it is not recommended

conclusion

We use Flyway to manage the database version is relatively simple, just according to the convention of the corresponding version number, can automatically generate, can quickly and effectively manage the database version