preface

In a project, version upgrades are often made. In a version upgrade, the database upgrade is involved. Usually, we may have a file system to save SQL scripts, which need to upgrade to the corresponding SQL script execution. This is cumbersome and error-prone.

Flyway is a database versioning tool that applies, manages, and tracks database changes independently of the database. In layman’s terms, Flyway manages different people’s SQL scripts in the same way that Git manages different people’s code, allowing for database synchronization.

The specific implementation

Maven rely on

<dependencies>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
        <version>6.0.8</version>
    </dependency>
</dependencies>
Copy the code

Plugin dependencies:

<plugins>
    <plugin>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-maven-plugin</artifactId>
        <version>6.0.8</version>
    </plugin>
</plugins>
Copy the code

File location

The default directory for storing SQL files is DB /migration

You can also set Spring.flyway. locations=classpath:db/migration.

Naming conventions

SQL file naming conventions: Prefix + version number + double underscore (_) + Description + suffix

You can also modify the naming conventions as follows:

spring:
  flyway:
    # prefix, default V
    sql-migration-prefix: V
    # File delimiter, default __
    sql-migration-separator: __
    # suffix, default. SQL
    sql-migration-suffixes: .sql
Copy the code

The version number can be structured as follows:

  • 1
  • 001
  • 5.2
  • 1.2.3.4.5.6.7.8.9
  • 205.68
  • 20200616113556
  • 2020.6.16.11.35.56
  • 2020.06.16.11.35.56

SQL file

The contents of the SQL file are the SQL scripts that need to be updated. Such as:

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`account` VARCHAR (40) NOT NULL COMMENT 'Username'.`password` VARCHAR (255) NOT NULL COMMENT 'password'.`nickname` VARCHAR (60) DEFAULT NULL COMMENT 'nickname'.`email` VARCHAR (40) DEFAULT NULL COMMENT 'email'.`phone` VARCHAR (11) DEFAULT NULL COMMENT 'phone'.`create_time` datetime DEFAULT NULL COMMENT 'Creation time'.`create_user` VARCHAR (11) DEFAULT NULL COMMENT 'Founder'.`modify_time` datetime DEFAULT NULL COMMENT 'Modification time'.`modify_user` VARCHAR (11) DEFAULT NULL COMMENT 'Modifier',
	PRIMARY KEY (`id`))ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;
Copy the code

Start the project

After adding Maven configuration and SQL scripts, you can start the project.

If the startup is successful, the following information is displayed:

Viewing the database, the Flyway-schema-history table is generated, which is used to record the SQL information executed. As follows:

The name of this table can be changed by setting spring.flyway.table=flyway_schema_history.

In addition to this table, the configured SQL script is executed to generate a service table as follows:

If the initial database is not empty, the following error occurs:

You can configure spring.flyway.baseline-on-migrate=true to set the current database structure to the baseline version on which SQL scripts will be executed.

Commonly used configuration

spring:
  flyway:
    # open flyway
    enabled: true
    Whether to perform a baseline when the database is not empty
    baseline-on-migrate: false
    Execute the baseline version number
    baseline-version: 1
    Check whether the SQL file exists
    check-location: true
    # SQL file location
    locations: classpath:db/migration
    # SQL file prefix
    sql-migration-prefix: V
    # SQL file separator
    sql-migration-separator: __
    # SQL file suffix
    sql-migration-suffixes: .sql
    Record table name
    table: flyway_schema_history
    # Target version
    target:
Copy the code

conclusion

So far, we have successfully managed the database version through Flyway.

Thanks for reading, and if it helps, just click a like!

The source code

Github.com/zhuqianchan…

Review past

  • Build backend frameworks from scratch – keep updating