flyway is a database migration tool that supports automatic backup and execution of SQL scripts. It is very convenient for us to deploy applications to multiple environments to initialize databases or upgrade database tables.

1, Why use flyway

Let's make an analogy with the Git version control tool, which is often used now. It can well control the version of our code base and realize the functions of version backtracking, version fallback, version submission and so on.

flyway is actually a version control tool for the database level. It can also well support the functions of database version backtracking, version fallback, version submission and so on.

During the initial construction of the project, we may need to transfer the project code to multiple environments, such as development environment and test environment, and the database is divided into development library and test library. At the same time, we often need to constantly change the table structure in the development process. If flyway is not introduced, we can only manually synchronize the development library with the test library. However, if flyway is used, we can automatically synchronize the database table structure as long as the project is successfully deployed.

2, How to use flyway

1. Firstly, pom dependency is introduced

   <!-- mysql Take version 8 as an example -->
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>8.0.22</version>
   </dependency>
   <!--Database version control-->
   <dependency>
       <groupId>org.flywaydb</groupId>
       <artifactId>flyway-core</artifactId>
       <version>5.2.1</version>
   </dependency>
   <!--mybatis-->
   <dependency>
       <groupId>org.mybatis.spring.boot</groupId>
       <artifactId>mybatis-spring-boot-starter</artifactId>
       <version>2.1.0</version>
   </dependency>
   

2. configuration file

   server:
     port: 8000
   
   spring:
     application:
       name: myflyway
     datasource:
       driver-class-name: com.mysql.cj.jdbc.Driver
       url: jdbc:mysql://127.0.0.1:3306/myflyway?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
       username: root
       password: root
     flyway:
       # Enable flyway
       enabled: true
       # Encoding format, default UTF-8
       encoding: UTF-8
       # The storage path of the migration sql script file is db/migration by default
       locations: classpath:db/migration
       # Prefix of migration sql script file name. The default value is V
       sql-migration-prefix: V
       # Separator of migration sql script file name, with 2 underscores by default__
       sql-migration-separator: __
       # Suffix of migration sql script file name
       sql-migration-suffixes: .sql
       # Whether to verify during migration. The default value is true
       validate-on-migrate: true
       # When the migration finds that the database is not empty and there are tables without metadata, the benchmark migration is automatically performed to create a new schema_version table
       baseline-on-migrate: true
   

3. Initialize database environment

   New myflyway Library

4. Create DDL script

   Create script name specification:

   

For example, I create the first version of the SQL script file for creating tables, and the table name is test, and the name is V1.0.0_001_ddl_test.sql

UNLOCK TABLES;

CREATE TABLE IF NOT EXISTS `test` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Self increasing id Primary key',
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT 'full name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

UNLOCK TABLES;

Put the file under resource/db/migration

1. Start project

   

You can see that our ddl script statement is successfully executed, and a flyway will be automatically created_ schema_ History table, which records the execution history.

Reference project address: https://gitee.com/dearvainycos/myflyway

If we use mysql as the database, we must introduce the corresponding mybatis dependency into the pom file when using flyway, otherwise it will not take effect.

reference material:

• Flyway introduction

• Flyway configuration

• SpringBoot: managing database versions using flyway