SpringBoot database management - database management and migration with flyway

Liquibase and its integration with SpringBoot were introduced above. In addition to Liquibase, there is another component, Flyway, which is a similar database version management middleware that is often used. This article mainly introduces Flyway, and SpringBoot integrates Flyway. @pdai

• SpringBoot database management - database management and migration with flyway

  • Knowledge preparation

    • What is Flyway? What problem does it solve?

      • Migrations in Flyway
      • Common commands in Flyway

  • Simple example

    • POM dependencies
    • yml configuration
    • Migrate configuration
    • test

  • further understanding

    • MySQL support issues
  • Sample source code

Knowledge preparation

Need to understand Flyway and the problem to be solved, as well as some basic concepts, such as transitions (migrations), common commands (commands) and so on.

What is Flyway? What problem does it solve?

Flyway is a database migration tool. Simply put, it is a tool that helps you execute database scripts when you deploy your application. Flyway supports two types of scripts, SQL and Java. You can package scripts into applications. When the application starts, Flyway manages the execution of these scripts. These scripts are called migrations by Flyway.

PS: Essentially the same as the liquibase mechanism .

According to the order of verion (compared with the update records in the database to find the unupdated ones), the update is as follows

The update records are as follows

Migrations in Flyway

For Flyway, all changes to the database are called migrations, which are equivalent to changesets in liquibase.

The definition of migrations in Flyway is more detailed, including the following three:

1. Versioned Migrations : Executed once per version, including version, description and checksum; often used to create, modify, delete tables; insert, modify data, etc.
2. Undo Migrations : The inverse of Versioned Migrations.
3. Repeatable Migrations : can be executed multiple times, including description and checksum (no version); mainly used for views, stored procedures, functions, etc.

The formats corresponding to these three types are as follows:

1. Prefix: V for Versioned Migrations, U for Undo Migrations, R for Repeatable Migrations
2. Version number: Unique version number, such as V1.0.1
3. Separator: __ (two underscores)
4. descriptive information: descriptive information
5. Suffix: .sql

(PS: Undo Migrations is in the paid version)

Common commands in Flyway

What are the common commands in Flyway? What does it mean?

Migrate : is the core of Flyway workflow. It will scan the filesystem or classpath for available Migrates. It will compare them to the Migrate that has been applied to the database. Migrate data if any discrepancies are found.

Clean : Clear all objects in the corresponding database schema, including table structures, views, stored procedures, etc. The clean operation is very useful in the dev and test phases; (PS: cannot be used in the product environment)

Info : It is used to print the detailed and status information of all Migrations, which is also done through MetaData and Migrations, which can quickly locate the current database version;

Validate : Verify and apply whether the Migrations have changed. It is enabled by default. The principle is to compare the checkNum value of the MetaData table and the local Migrations. If the value is the same, the verification passes, otherwise it fails.

Undo : The inverse operation of Migrate, that is, the rollback operation, which is a charging function

BaseLine : A solution for databases that already have a database schema structure. Create a new MetaData table in a non-empty database, and apply Migrations to the database; it can also be applied to a database with an existing table structure or add a Metadata table.

Repair : The repair operation can repair the metaData table, which is useful when there is an error in the metadata

Simple example

This article mainly introduces the management of database changes based on SpringBoot's integration of flyway.

POM dependencies

The dependencies of the Maven package mainly include the mysql driver, JDBC (here spring-boot-starter-data-jpa includes the jdbc package, of course, the jdbc package can also be imported directly), and the flyway package.

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
    <version>8.5.7</version>
</dependency>

yml configuration

SpringBoot AutoConfig already includes the configuration of flyway by default, under spring.flyway configuration

 spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db_flyway?useSSL=false&autoReconnect=true&characterEncoding=utf8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: bfXa4Pt2lUUScy8jakXf
  flyway:
    enabled: true
    encoding: UTF-8
    # 可以支持多个location, 用','隔开
    locations: classpath:db/migration
    # migrate是否校验
    validate-on-migrate: true

During development, more configuration can be found from the following SpringBoot AutoConfig.

Migrate configuration

Here we prepare two Versioned Migrations

• V1.0__Init_DB.sql

 DROP TABLE IF EXISTS `tb_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone_number` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

• V1.1__Init_Data.sql

 LOCK TABLES `tb_user` WRITE;
/*!40000 ALTER TABLE `tb_user` DISABLE KEYS */;
INSERT INTO `tb_user` VALUES (1,'pdai','dfasdf','[e-mail]',1212121213,'afsdfsaf','2021-09-08 17:09:15','2021-09-08 17:09:15');
/*!40000 ALTER TABLE `tb_user` ENABLE KEYS */;
UNLOCK TABLES;

test

Start springBootApplication, we can see the following log

 2022-04-13 07:56:56.122  INFO 86030 --- [           main] o.f.c.i.database.base.DatabaseType       : Database: jdbc:mysql://localhost:3306/test_db_flyway (MySQL 8.0)
2022-04-13 07:56:56.220  INFO 86030 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.074s)
2022-04-13 07:56:56.245  INFO 86030 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table `test_db_flyway`.`flyway_schema_history` ...
2022-04-13 07:56:56.270  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.282  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.292  INFO 86030 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `test_db_flyway`: << Empty Schema >>
2022-04-13 07:56:56.297  INFO 86030 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `test_db_flyway` to version "1.0 - Init DB"
2022-04-13 07:56:56.309  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Unknown table 'test_db_flyway.tb_user' (SQL State: 42S02 - Error Code: 1051)
2022-04-13 07:56:56.309  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.309  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.310  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. (SQL State: HY000 - Error Code: 3719)
2022-04-13 07:56:56.310  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.317  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Integer display width is deprecated and will be removed in a future release. (SQL State: HY000 - Error Code: 1681)
2022-04-13 07:56:56.317  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Integer display width is deprecated and will be removed in a future release. (SQL State: HY000 - Error Code: 1681)
2022-04-13 07:56:56.317  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. (SQL State: HY000 - Error Code: 3719)
2022-04-13 07:56:56.317  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.318  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.333  INFO 86030 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `test_db_flyway` to version "1.1 - Init Data"
2022-04-13 07:56:56.334  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.335  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Table storage engine for 'tb_user' doesn't have this option (SQL State: HY000 - Error Code: 1031)
2022-04-13 07:56:56.335  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.335  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 1 rows affected
2022-04-13 07:56:56.336  WARN 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Table storage engine for 'tb_user' doesn't have this option (SQL State: HY000 - Error Code: 1031)
2022-04-13 07:56:56.337  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.337  INFO 86030 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : 0 rows affected
2022-04-13 07:56:56.346  INFO 86030 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema `test_db_flyway`, now at version v1.1 (execution time 00:00.058s)

Generated flyway updated record, test_db_flyway . flyway_schema_history

User table structure and data

further understanding

Further understanding through a few questions.

MySQL support issues

From Flyway's support for MySQL, it can be seen that the official expects to acquire more paying users through the large base used by MySQL.

First of all, if you just import the package of flyway-core:8.5.7, the following error will be reported

 Caused by: org.flywaydb.core.api.FlywayException: Unsupported Database: MySQL 8.0
    at org.flywaydb.core.internal.database.DatabaseTypeRegister.getDatabaseTypeForConnection(DatabaseTypeRegister.java:106) ~[flyway-core-8.5.7.jar:na]
    at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:76) ~[flyway-core-8.5.7.jar:na]
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:147) ~[flyway-core-8.5.7.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:124) ~[flyway-core-8.5.7.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-2.5.3.jar:2.5.3]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) ~[spring-beans-5.3.9.jar:5.3.9]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) ~[spring-beans-5.3.9.jar:5.3.9]
    ... 18 common frames omitted

because the built-in Datebase type could not be found

So the package you should import is flyway-mysql:8.5.7, and the interesting thing is that the flyway-core version contained in this package is 7.7.3

Then let's take a look at the support of MySQL on the official website . For this operation, the 5.7 version also needs to use the enterprise version. Just to charge, toss....

Sample source code

https://github.com/realpdai/tech-pdai-spring-demos

more content

Say goodbye to fragmented learning, one-stop systematic learning without routines Back-end development: Java full-stack knowledge system (https://pdai.tech)