Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

preface

Recently, someone in the project recommended to use Flayway to do database version management, so that the script can be automatically executed in normal project development, without manual database synchronization operation. In our normal development process, the development environment configuration synchronization is ok, but the production and test environment is still not recommended to use, production account permission management, the system database account generally only DML permission, not DDL operation permission.

Basic working principle of FlayWay

FlayWay records version history through a history table (flyway_schema_history). The files under resources/ DB /migration are automatically scanned each time the project starts and flyway_schemA_history is queried to determine whether the files are new. If it is a new file, perform the migration. If not, ignore it.

There are two main steps:

  1. When Flyway executes on an empty database, it creates a history table with an empty data record by default named Flyway_schemA_history, which is used to track or record the state of the database.
  2. Flyway will start scanning for migrated files in the documentation system or project classpath path.

SpringBoot integration Flyway

Introduce FlyWay dependencies

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

FlyWay configuration

flyway:
  baseline-on-migrate: true
  locations: classpath:db/migration
  check-location: true
  enabled: true
Copy the code

Start the project

We can see that a history table, flyway_schemA_history, is initialized

When v1.1.sql is created, restarting the application will add a modified version record, and the table fields in the database will be added automatically.

Alter table smp_down_link_record add column 'create_by' int(10) NOT NULL comment 'founder ';Copy the code

FAQ

  1. An error message was reported after SpringBoot integration
java.sql.SQLSyntaxErrorException: SELECT command denied to user 'test'@'127.0.0.1' for table 'user_variables_by_thread'\
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
Copy the code

Solution to lower version

  1. In fact, there are some problems encountered in the access process of FlyWay. In fact, FlayWay requires relatively large permissions, and there are also some potential pits. Some pits to be avoided are listed below. The best way to access personal tasks is for the middleware team to encapsulate some of the configuration, otherwise the database will be deleted, which is not worth the loss. Generally, it is recommended to do synchronization only in the development environment, test and production environment or follow the upgrade script, relatively complete.

Reference documentation

Website making: github.com/flyway