Integrate Flyway with Spring Boot to implement database versioning from scratch

The article directories

• • What is the Flyway
  • Flyway integration and usage
  • • Add the dependent
    • The configuration file
    • The script
    • test
  • The principle of
  • senior
  • • Java-based migration callback

What is the Flyway

Flyway is a database version control management tool that supports automatic database version upgrades.

• When a project is initialized, database scripts are usually executed manually first.
• During the development process, when data structures or data are updated, scripts are often manually executed to synchronize the development environment and test environment.

Now we can use flyway to help us do this automatically.

Flyway integration and usage

Add the dependent

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

Since it is a Spring Boot project integration, the version can be used directly by default

The configuration file

spring:
  flyway:
    # Whether to enable Flyway, default true
    enabled: true
    Whether to automatically perform base migration when the target schema is not empty and the table has no metadata (i.e. the project in the iteration).
    baseline-on-migrate: true
    The default value is false. You are advised to enable the development environment and disable the build environment
    out-of-order: true
    # set the SQL scripts directory, you can configure multiple, such as the classpath: db/migration, filesystem: / SQL - migrations, the default classpath: db/migration
    locations:
      - classpath:db/migration
Copy the code

The script

Create a db.migration directory under the resource directory and place the SQL files

SQL script format:

• V/R+ Version number + Double underscore + Description + End:

  • Example: V20190429.1530__t_user_update.sql (development environment: suggested date + hour/minute/second)
  • For example: V1.1__init. SQL (production environment: it is recommended to merge the above steps into version numbers)

test

The principle of

Flyway needs to create a metData table in the DB (the default table name is flyway_schemA_history), which stores the record of each migration, including the version number of the migration script and the checksum value of the SQL script. When a new SQL script is scanned, Flyway parses the version number of the SQL script and compares it to migrations that have been performed in the metadata table. If the version of the SQL script is updated, the SQL file will be executed on the specified DB. Otherwise, the SQL file will be skipped.

The comparison of the two Flyway versions is based on the left alignment principle, and the absence is replaced by 0. Examples are as follows:

• 1.2.9.4 is higher than version 1.2.9.
• 1.2.10 is later than 1.2.9.4.
• 1.2.10 is the same height as 1.2.010, and leading zeros in each version are ignored.

Flyway SQL files can be divided into two categories:

• Versioned: Used for version upgrade. Each version has a unique version number and can only be applied once
• Repeatable: refers to a repeatable migration that Flyway reapplies if the CHECKsum of the SQL script changes. It is not used for version updates. This type of migration is always executed after the versioned migration is executed

By default, the Migration SQL is named as follows:

The file name consists of the following sections, some of which can be customized in addition to the default configuration.

• Prefix: indicates the configurable prefix. The default value V indicates Versioned and R indicates Repeatable
• Version: Identifies the version number. It consists of one or more digits, which can be separated by dots. Or underscore _
• Separator: Configures to separate the version id from the description. The default value is two underscores __
• Description: Indicates the description, which can be separated by underscores or Spaces
• Suffix: configurable, followed by identifier, default is. SQL *

The Flyway metadata table results as follows:

CREATE TABLE  flyway_schema_history
    (
        installed_rank INT NOT NULL,
        version VARCHAR(50),
        description VARCHAR(200) NOT NULL,
        type VARCHAR(20) NOT NULL,
        script VARCHAR(1000) NOT NULL,
        checksum INT,
        installed_by VARCHAR(100) NOT NULL,
        installed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        execution_time INT NOT NULL,
        success TINYINT(1) NOT NULL.PRIMARY KEY (installed_rank),
        INDEX flyway_schema_history_s_idx (success)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

senior

Java-based migration callback

Flyway enables you to create callbacks using two different methods (Java or SQL). The former is the most flexible. It gives us the freedom to execute arbitrary code.

The core code is as follows:

import lombok.extern.slf4j.Slf4j;
import org.flywaydb.core.api.callback.BaseCallback;
import org.flywaydb.core.api.callback.Context;
import org.flywaydb.core.api.callback.Event;
import org.flywaydb.core.internal.jdbc.JdbcTemplate;
import org.springframework.context.annotation.Configuration;

import java.sql.SQLException;

@Configuration
@Slf4j
public class ExampleFlywayCallback extends BaseCallback {
    @Override
    public void handle(Event event, Context context) {
        switch (event) {
            // Triggered after each successful migration. This event will be triggered in the same transaction as the migration
            case AFTER_EACH_MIGRATE:
                log.info("{},", event);
                final JdbcTemplate jdbcTemplate = new JdbcTemplate(
                        context.getConnection());

                // Create 10 random users
                for (int i = 1; i <= 10; i++) {
                    try {
                        jdbcTemplate.execute(String.format("insert into test_user"
                                + " (username, first_name, last_name) values"
                                + " ('%[email protected]', 'Elvis_%d', 'Presley_%d')", i, i, i));
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
        }
    }
}
Copy the code

We can execute any logic we need in the Java migration callback, giving us the flexibility to implement more perverted requirements.

Reference:

• www.cnblogs.com/harrychines…
• Blog.csdn.net/qq_38455201…
• Docs. Spring. IO/spring – the boot…
• Reflectoring. IO/database – mi…