SpringBoot e-commerce project mall (40K + STAR) address: github.com/macrozheng/…

Abstract

When the application is upgraded, the database table structure is often upgraded, and the database table structure needs to be migrated. Generally we will use tools or scripts to implement, manual operation after all has a certain risk, if the application can automatically upgrade the database table structure! Flyway is a tool that automatically updates the database table structure when an application is launched. This tool is used in conjunction with SpringBoot.

Flyway profile

Flyway is a database migration tool that makes database migration much easier. It versions databases like Git and supports command-line tools, Maven plug-ins, and third-party tools (such as SpringBoot).

Flyway has the following features:

  • Simple: Easy to use and learn, database migration through different versions of SQL scripts.
  • Professional: Focus on database migration functions, you don’t have to worry about any problems.
  • Powerful: support a variety of databases, a large number of third-party tools, support CI/DI.

Relevant concepts

The working principle of

With Flyway, we need to write SQL scripts for database migration. For example, v1__initial_setup. SQL initializes three tables, and V2__First_Changes. Flyway creates the Flyway_schemA_history table to store the execution of these SQL scripts for database versioning. When we use Flyway for database migration, Flyway decides which SQL scripts need to be executed based on the records in the Flyway_schemA_HISTORY table to implement the database migration.

Script naming conventions

When creating Flyway SQL scripts, there are naming conventions that determine the order and manner in which Flyway executes the scripts, as illustrated in the diagram below.

To be executed correctly by Flyway, SQL migration scripts need to follow the following specification:

  • Prefix:VRepresents a database migration with version numbers,URepresents a rollback of some database version,RRepresents repeatable database migration;
  • Version: Flyway executes database migration scripts in the order of the Version number;
  • Separator: Uses a double underscore Separator when naming.
  • Description: Describes the operation Description of the migration script.
  • Suffix: indicates.sqlFile.

Relevant command

  • Migrate: Database migration command. You can run the SQL script to upgrade database tables to the latest version.
  • Clean: Delete all tables in the database. Do not use these tables in the production environment.
  • Info: Displays all details and status information about database migration.
  • Validate: verifies whether the database migration is available.
  • Undo: Rollback the database migration.
  • Baseline: Created based on the existing databaseflyway_schema_historyTable, database migrations greater than the base version will be applied.
  • Repair: repairflyway_schema_historyTable.

Command line tool

There are many ways to use Flyway to achieve data migration. Let’s first experience the use of Flyway through the command line tool.

  • To get started, you need to download Flyway’s command-line tool, the Community edition, from flywaydb.org/download

  • After the download is complete, decompress the directory structure as follows.

  • Modify the Flyway configuration file/conf/flyway.conf, modify the database configuration.
flyway.url=jdbc:mysql://localhost:3306/flyway? useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai flyway.user=root flyway.password=rootCopy the code
  • in/sqlAdd SQL execution script in directory, add create hereums_adminTable execution scriptV1.0.1 __Create_ums_admin_table. SQL;
CREATE TABLE `ums_admin`
(
  `id`          bigint(20) NOT NULL AUTO_INCREMENT,
  `username`    varchar(64)  DEFAULT NULL,
  `password`    varchar(64)  DEFAULT NULL,
  `icon`        varchar(500) DEFAULT NULL COMMENT 'avatar',
  `email`       varchar(100) DEFAULT NULL COMMENT 'email',
  `nick_name`   varchar(200) DEFAULT NULL COMMENT 'nickname',
  `note`        varchar(500) DEFAULT NULL COMMENT 'Remarks',
  `create_time` datetime     DEFAULT NULL COMMENT 'Creation time',
  `login_time`  datetime     DEFAULT NULL COMMENT 'Last Login Time',
  `status`      int(1)       DEFAULT '1' COMMENT 'Account enabled status: 0-> Disabled; 1 - > enable '.PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 8
  DEFAULT CHARSET = utf8 COMMENT ='Background User table';
Copy the code
  • useflyway migrateCommand to perform data migrationflyway baselineCommand to create a table that holds migration recordsflyway_schema_historyTo just go,

  • Use the firstflyway baselineCommand, and then use it againflyway migrateCommand. The command line displays information indicating that the command is successfully executed.

  • in\sqlDirectory to add SQL execution script, toums_adminAdd some data to the table, execute the script asV1.0.2 __Add_ums_admin. SQL;
INSERT INTO ums_admin (username, PASSWORD, email, nick_name, STATUS)
VALUES ('test'.'123456'.'[email protected]'.'Test account'.1);
INSERT INTO ums_admin (username, PASSWORD, email, nick_name, STATUS)
VALUES ('macro'.'123456'.'[email protected]'.'Regular Account'.1);
INSERT INTO ums_admin (username, PASSWORD, email, nick_name, STATUS)
VALUES ('andy'.'123456'.'[email protected]'.'Regular Account'.1);
Copy the code
  • We can useflyway infoCommand to seeflyway_schema_historyThe data migration record in the table can be found1.0.2The release update is still in progressPendingState, usingflyway migrateChanged toSuccess;

  • We can create repeatable SQL scripts, typically used to create views, stored procedures, functions, etc., based onums_adminTable creates a view and executes the script asR__Ums_admin_view.sql;
CREATE
  OR REPLACE VIEW ums_admin_view AS
SELECT username,
       PASSWORD,
       email
FROM ums_admin;
Copy the code
  • useflyway migrateThe command can be executed repeatedly (when the script starting with R is changed), and the script will be executed at all timesVExecute the initial script after it finishes executing;

  • Flyway’s rollback mechanism relies on the SQL script created hereU1.0.1 __Create_ums_admin_table. SQLandU1.0.2 __Add_ums_admin. SQLTwo rollback scripts;
# U1. 01.__Create_ums_admin_table.sql
DROP TABLE ums_admin
Copy the code
# U1. 02.__Add_ums_admin.sql
DELETE FROM ums_admin;
Copy the code
  • useflyway undoThe rollback command can be used to perform a rollback. Unfortunately, the community version does not support rollback.

Maven plug-in

Flyway also provides Maven plugins that support much the same functionality as command-line tools.

  • To use Flyway in Maven projects through plug-ins, you need to add Flyway plug-ins to POM.xml and configure the database connection information.
<! --Flyway Maven plugin -->
<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>7.3.2</version>
    <configuration>
        <url>jdbc:mysql://localhost:3306/flyway? serverTimezone=Asia/Shanghai</url>
        <user>root</user>
        <password>root</password>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>
</plugin>
Copy the code
  • Create in the resouce directorydb\migrationDirectory to put SQL scripts used for database upgrade;

  • Flyway’s Maven plugin supports the following commands;

  • Double click on theflyway:infoCommand, the following output, this method is basically the same as the command line tool.
[INFO] -- Flyway-maven-plugin :7.3.2: INFO (default-cli) @mall-tiny-flyway -- [INFO] Flyway Community Edition 7.3.2 by Redgate [INFO] Database: JDBC: mysql: / / localhost: 3306 / flyway (mysql 5.7) [INFO] Schema version: 1.0.2 [INFO] [INFO] + -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | Category | Version | Description | Type | Installed On | State | +------------+---------+------------------------+----------+---------------------+----------+ | | 1 | << Flyway Baseline > > | BASELINE 11:17:35 | 2020-12-24 | BASELINE | | Versioned | | 1.0.1 Create ums admin table SQL | | 2020-12-24 11:17:42 | Success | | Versioned | 1.0.2 | Add ums admin 11:33:40 SQL | | 2020-12-24 | Success | | the Repeatable | | ums admin view | SQL | 2020-12-24 11:33:40 | Success | +------------+---------+------------------------+----------+---------------------+----------+ [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- [INFO] Total time: 9.076 s [INFO] Finished at: 2020-12-24T14:28:16+08:00 [INFO] Final Memory: 28M/286M [INFO] ------------------------------------------------------------------------ Process finished with exit code  0Copy the code

Used with SpringBoot

Flyway is easy to use with SpringBoot since SpringBoot officially supports Flyway!

  • First add flyway-related dependencies to POM.xml without adding Flyway version numbers:
<! --Flyway dependencies -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
Copy the code
  • Modifying a Configuration Fileapplication.yml, configure the data source and Flyway;
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/flyway? useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: root
  flyway:
    # Enable Flyway
    enabled: true
    # disable the clean command for Flyway. Using the clean command will delete all tables in the schema
    clean-disabled: true
    Set Flyway SQL script path
    locations: classpath:db/migration
    # flyway_schemA_history = flyway_schemA_history
    table: flyway_schema_history
    The flyway_schemA_history table can be generated by using the baseline command
    baseline-on-migrate: true
    SQL scripts below the baseline version will not be executed in Migrate
    baseline-version: 1
    # set the character encoding
    encoding: UTF-8
    Out-of-order migration is not allowed
    out-of-order: false
    Set the Flyway schema. If not, set it to the schema specified in datasourcel.url
    schemas: flyway
    Enable migrate when executing migrate
    validate-on-migrate: true
Copy the code
  • Finally, run the SpringBoot application directly to automatically create the corresponding database, and the console will output the following information.
The 2020-12-24 14:38:15. 10716-659 the INFO [main] O.F.C.I nternal. License. VersionPrinter: Flyway Community Edition 6.4.1 by Redgate 2020-12-24 14:38:15.898 INFO 10716 -- [main] o.f.c.internal.database.DatabaseFactory : Database: JDBC: mysql: / / localhost: 3306 / flyway (mysql 5.7) 14:38:15 2020-12-24. 10716-972 the INFO [main] o.f.core.internal.command.DbValidate : Do you have any information? Successfully validated 3 Migrations (Execution time 00:00.047s) 2020-12-24 14:38:15.988 INFO 10716 -- [main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table `flyway`.`flyway_schema_history` with baseline ... The 14:38:16 2020-12-24. 10716-106 the INFO [main] o.f.core.internal.com mand. DbBaseline: Successfully baselined schema with version: 1 2020-12-24 14:38:16. 10716-122 the INFO [main] o.f.core.internal.com mand. DbMigrate: Current version of schema `flyway`: 1 2020-12-24 14:38:16. 10716-134 the INFO [main] o.f.core.internal.com mand. DbMigrate: Migrating Schema 'flyway' to version 1.0.1 - Create UMS admin table 2020-12-24 14:38:16.248 INFO 10716 -- [main] o.f.core.internal.command.DbMigrate : Migrating Schema 'flyway' to version 1.0.2 - Add UMS admin 2020-12-24 14:38:16.281 INFO 10716 -- [main] o.f.core.internal.command.DbMigrate : Migrating schema 'flyway' with REPEATable migration Ums admin View 2020-12-24 14:38:16.314 INFO 10716 -- [main] O.f.core.internal.com mand. DbMigrate: Successfully applied 3 migrations to schema ` flyway ` (execution time 00:00, 206 s)Copy the code

conclusion

Using Flyway to automatically upgrade database table structures has advantages over manually upgrading database table structures. With Flyway, we can upgrade the database while we are upgrading the application. Since the community version does not currently support database rollback, it is necessary to make a backup before upgrading.

The resources

Official documentation: flywaydb.org/documentati…

Project source code address

Github.com/macrozheng/…

In this paper, making github.com/macrozheng/… Already included, welcome everyone Star!