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
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>
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
Initialize database environment
New myflyway Library
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
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.