Spring Boot integrates Flyway to realize database version control

In the iterative development of the project, it is inevitable to update the database Schema, such as adding a new table, adding fields to the table or deleting fields, etc., so when I perform a series of operations on the database, how to quickly synchronize on the computers of other colleagues? How to quickly synchronize on test / production servers?

Every time you send a version, you may have sql changes, so you will have the following pain points:

  • Forget some sql changes
  • The execution order of sql for each developer
  • Repeat update
  • Need to manually go to the database to execute the script

The above problems and pain points can be solved by Flyway tool, which can realize automatic database version management and record database version update records.

About Flyway

Flyway is a database version management tool independent of database application, management and tracking of database changes. In popular words, flyway can manage sql scripts of different people just like Git manages different people's code, so as to achieve database synchronization. More information can be read and learned on flyway's official website.

In addition, Flyway supports many relational databases as follows:

Next, we integrate Flyway in Spring Boot to achieve database version control.

Spring Boot integrated Flyway

First create a SpringBoot project, and then add the following dependency integration Flyway in pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>5.2.4</version>
</dependency>

Then write mysql configuration and Flyway related configuration in application.yml (Flyway locations read the resources/db/migration directory under the current project by default)

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123

spring.flyway.locations=classpath:/db/migration/

Next, create the SQL script to execute in the resources/db/migration directory.

The SQL script naming specification is as follows:

  • Prefix: V represents version migration, U represents undo migration, and R represents repeatable migration
  • Version version version number: the version number is usually composed of. And integer
  • Separator separator separator: fixed by two underscores
  • Description: composed of underlined words, used to describe the purpose of this migration
  • Suffix: if it is an SQL file, it is composed of. SQL. If it is a Java class based file, the suffix is not required by default

Then, we create the v1.0  db.sql SQL migration script in the resources/db/migration directory according to the naming specification. The specific content is as follows:

DROP TABLE IF EXISTS `user` ;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `name` varchar(20) NOT NULL COMMENT 'Full name',
  `age` int(11) DEFAULT NULL COMMENT 'Age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `age`) VALUES ('1', 'wupx', '18');

Finally, start the project, and the execution log is as follows:

2020-05-07 12:41:29.126  INFO 13732 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2020-05-07 12:41:29.236  INFO 13732 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/test (MySQL 5.5)
2020-05-07 12:41:29.287  INFO 13732 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.009s)
2020-05-07 12:41:29.330  INFO 13732 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: `test`.`flyway_schema_history`
2020-05-07 12:41:29.479  INFO 13732 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `test`: << Empty Schema >>
2020-05-07 12:41:29.480  INFO 13732 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `test` to version 1.0 - init db
2020-05-07 12:41:29.481  WARN 13732 --- [           main] o.f.c.i.s.DefaultSqlScriptExecutor       : DB: Unknown table 'user' (SQL State: 42S02 - Error Code: 1051)
2020-05-07 12:41:29.631  INFO 13732 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `test` (execution time 00:00.301s)

From the startup log, it can be seen that Flyway has detected that it needs to run version script to initialize the database, so it executed the v1.0 ﹣ init ﹣ db.sql script to create the user table, and automatically created the Flyway ﹣ schema ﹣ history table to record the evolution and status of all versions. The table structure is as follows (take MySQL as an example):

Field Type Null Key Default
version_rank int(11) NO MUL NULL
installed_rank int(11) NO MUL NULL
version varchar(50) NO PRI NULL
description varchar(200) NO NULL
type varchar(20) NO NULL
script varchar(1000) NO NULL
checksum int(11) YES NULL
installed_by varchar(100) NO NULL
installed_on timestamp NO CURRENT_TIMESTAMP
execution_time int(11) NO NULL
success tinyint(1) NO MUL NULL

Query the flyway schema history table and find that a record with version number 1.0 is added, which uses v1.0 init db.sql to migrate the script.

mysql> SELECT * FROM flyway_schema_history;
+----------------+---------+-------------+------+-------------------+------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script            | checksum   | installed_by | installed_on        | execution_time | success |
+----------------+---------+-------------+------+-------------------+------------+--------------+---------------------+----------------+---------+
|              1 | 1.0     | init db     | SQL  | V1.0__init_db.sql | 1317299633 | root         | 2020-05-07 12:41:29 |             97 |       1 |
+----------------+---------+-------------+------+-------------------+------------+--------------+---------------------+----------------+---------+

Query the user table again and find that the data in the sql script has been inserted successfully.

mysql> SELECT * FROM user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | wupx |  18 |
+----+------+-----+

Next, run the project again, and the results are as follows:

2020-05-07 15:34:49.843  INFO 41880 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2020-05-07 15:34:49.981  INFO 41880 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/test (MySQL 5.5)
2020-05-07 15:34:50.036  INFO 41880 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.013s)
2020-05-07 15:34:50.043  INFO 41880 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `test`: 1.0
2020-05-07 15:34:50.043  INFO 41880 --- [           main] o.f.core.internal.command.DbMigrate      : Schema `test` is up to date. No migration necessary.

It can be seen from the log that Flyway found a migration script, that is, v1.0  init  db.sql. After judgment, it has reached the latest version 1.0, so there is no need to perform migration.

Next, we add an INSERT operation in the v1.0 ﹣ init ﹣ db.sql migration script: INSERT into user (ID, name, age) values ('2 ','hu XY','18 '); start the project again, and the following error will be reported:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1.0
-> Applied to database : 1317299633
-> Resolved locally    : -1582367361

The reason for this error is that Flyway will calculate a check sum for the script and save it in the database. It is used to compare whether there is any change in the sql file during the later running process. If there is any change, an error will be reported, which prevents the script from being modified by mistake.

summary

Flyway can effectively improve the database version management mode, and is a Java open-source database migration management tool, which has the characteristics of light and compact, and can be quickly integrated into the project without barriers. If the project has not been used, you can go to the official website to check the documents and learn more.

The complete code of this article is in https://github.com/wupeixuan/SpringBoot-Learn Under the database version control directory of.

The best relationship is mutual achievement. Everyone's watching, forwarding and leaving messages are the biggest driving force of my creation.

Reference resources

https://flywaydb.org/

https://github.com/wupeixuan/SpringBoot-Learn

Tags: Database SQL MySQL Spring

Posted on Thu, 07 May 2020 23:15:56 -0400 by furma