Database version management: flyway

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:

Tags: Java Database MySQL

Posted on Sun, 05 Dec 2021 16:53:47 -0500 by ivi