Shulou(Shulou.com)06/02 Report--

This article is about how to use Flyway to manage version changes of a database in java. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Preface

With the continuous iteration of the project, the database table structure and data are changing. Some businesses even run in parallel in multi-environment versions. In the era when data is king, it is urgent to manage the version of the database. How can version control tools like Git manage databases? Flyway and Liquibase are commonly used in Java projects to manage database versions. Flyway is relatively popular.

2. Characteristics of Flyway

Flyway is popular because it has the following advantages:

Simple is very easy to install and learn, and the way of migration is also easy to be accepted by developers.

Dedicated Flyway focuses on database migration and version control without other side effects.

Powerful is designed for continuous delivery. Have Flyway migrate the database when the application starts.

3. The working mechanism of Flyway

Flyway needs to first create a metadata table in DB (default table name is flyway_schema_history), which holds a record of each migration (migration) containing the version number of the migration script and the checksum value of the SQL script. The following figure shows multiple database versions.

The corresponding metadata records:

Installed_rankversiondescriptiontypescriptchecksuminstalled_byinstalled_onexecution_timesuccess11Initial SetupSQLV1__Initial_Setup.sql1996767037axel2016-02-04 22:23:00.0546true22First ChangesSQLV2__First_Changes.sql1279644856axel2016-02-06 09:18:00.0127true

Flyway scans the classpath of a file system or application to read DDL and DML for migration. Check the migration against the metadata table. If the version number declared by the script is less than or equal to one of the version numbers marked as the current version, they are ignored. The rest of the migration is pending: available, but not applied. Finally, they are sorted by version number and executed sequentially, and the execution results are written to the metadata table.

The corresponding metadata records:

Installed_rankversiondescriptiontypescriptchecksuminstalled_byinstalled_onexecution_timesuccess11Initial SetupSQLV1__Initial_Setup.sql1996767037axel2016-02-04 22:23:00.0546true22First ChangesSQLV2__First_Changes.sql1279644856axel2016-02-06 09:18:00.0127true

Flyway supports the command line (command-line tools need to be downloaded) and Java Api, as well as the build tools Maven and Gradle. Here we focus on Java Api.

3. Rules of Flyway

How does Flyway compare the order of two SQL files? It adopts the principle of left alignment, and the vacancy is replaced by 0. To give a few examples:

1.0.1.1 is higher than version 1.0.1.

1.0.10 is higher than version 1.0.9.4.

The 1.0.10 version number is the same as the 1.0.010 version number, and the leading 0 of each version number section is ignored.

Flyway divides SQL files into Versioned, Repeatable, and Undo:

Versioned is used for version upgrades. Each version has a unique version number and can only be executed once.

Repeatable can be executed repeatedly, and when Flyway detects a change in the checksum of a SQL script of type Repeatable, Flyway reapplies the script. It is not used for version updates, and this type of migration is always executed after the Versioned is executed.

Undo is used to undo the impact of versioned migrations with the same version. However, the rollback is too rough and mechanized and is generally not recommended. It is generally recommended to use Versioned mode to solve the problem.

The naming rules for these three types are shown below:

Prefix is configurable and prefixed. The default value V is Versioned, R is Repeatable, and U is Undo.

Version identifies the version number, which consists of one or more digits, and the separator between numbers is available. Or underscore _

Separator is configurable to separate version identification and description information, which defaults to two underscores _ _

Description description information, text can be separated by an underscore _ or a space

Suffix is configurable with subsequent identification. Default is .sql

4. Spring Boot integrates Flyway

Spring Boot provides automatic configuration of Flyway. So that we can use Flyway to version control the database right out of the box.

4.1 Flyway dependence

All you need to do is introduce dependency:

Org.flywaydb flyway-core

Of course, you need to integrate your relevant database environment. Here we use H2 database to demonstrate, other databases are the same but different dialects. For those who are not familiar with H2 database, please see my feature article Spring Boot 2: H2 database integration and use.

4.2 Flyway configuration

In order to explain the configuration intuitively, we first configure the H2 database as follows in the Spring Boot configuration file application.yml:

Spring: datasource: # H3 driver driver-class-name: org.h3.Driver # H3 database persistence to disk D:/h3 library name: flyway mysql mode url: jdbc:h3:file:D:/h3/flyway;MODE=MySQL DATABASE_TO_LOWER=TRUE H3: # enable console access default false console: enabled: true settings: # enable h3 console tracking to facilitate debugging default false trace: true # allow console remote access default false web-allow-others: true # H3 access path The following path: / h3-console

The configuration of the corresponding Flyway is:

# flyway configuration spring: flyway: # enable or disable the clean command of flyway enabled: true # flyway will delete all table under the specified schema, and production must be banned. This default value is theoretically unscientific for false as the default configuration. Clean-disabled: directory of true # SQL script, multiple paths are separated by comma default classpath:db/migration locations: classpath:db/migration # metadata version control information table default flyway_schema_history table: flyway_schema_history # if there is no flyway_schema_history this metadata table Before you can execute the flyway migrate command, you must first execute the flyway baseline command # set to true and flyway will automatically execute baseline once when baseline is needed. Baseline-on-migrate: true # specifies the version number of baseline. The default value is 1. SQL files lower than this version number will be ignored when migrate. Baseline-version: 1 # character encoding default UTF-8 encoding: UTF-8 # whether to allow developers to migrate out of order false out-of-order production recommendations false out-of-order: false # schema list controlled by flyway If we configure flyway as the default, the schema configured with spring.datasource.url, # can specify multiple schema, but only the metadata table will be created under the first schema, and the migration sql script will only be applied on the first schema. # but the flyway Clean command will be executed one by one under these schema. So make sure that the production spring.flyway.clean-disabled is true schemas: flyway # whether to automatically call verification when performing the migration. If your version is illogical, for example, if you execute DML first and there is no corresponding DDL, an exception validate-on-migrate: true will be thrown.

Be sure to read the Flyway configuration instructions carefully.

4.3 write SQL initialization script

Let's first write an initialization SQL file to add an sys_user table to the schema flyway that the H2 database has initialized automatically. Please pay attention to the naming rules. The script name is V1.0.1__Add_table_user.sql. The location of the SQL script is under the configured spring.flyway.locations. The content is:

Use `flyway`; CREATE TABLE `flyway` (`user_ id` int (10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (1024) NOT NULL unique, `encode_ password` varchar (1024) NOT NULL, `age` int (3) NOT NULL, PRIMARY KEY (`user_ id`) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 Insert into flyway.sys_user values (1) {noop} 12345)

Start the Spring Boot application. Open the H2 database console http://localhost:8080/h3-console, paste jdbc:h3:file:D:/h3/flyway;MODE=MySQL;DATABASE_TO_LOWER=TRUE in the JDBC URL column and click the Connect button to enter the following interface:

Here-1 is because we default to the flyway_schema_history table that Flyway requires. 0 is because the H2 database initializes the Schema flyway automatically, and other databases may need to be set up manually.

4.4 Writing SQL change scripts

We write a V1.0.0__Delete_sysuser_felordcn.sql to delete the initialized users in the V1.0.1__Add_table_user.sql. You will find that the startup error occurred, because we turned on the check, so an exception will be thrown for the version of the logic error. We change the version number to V1.0.2__Delete_sysuser_felordcn.sql and start it again. Through the H2 database console, we will find that there is one more change record:

At the same time, there is no data in the sys_user table, as expected.

5. Flyway best practices

From the above introduction, I believe that you will soon use Flyway for database version control. Here is a summary of some practical experience in development:

Spring.flyway.cleanDisabled=false must be banned in production.

Try to avoid using Undo mode.

The development version number should be named at multiple levels according to the team as far as possible to avoid confusion. For example, V1.0.1 naming ProjectName _ {Feature | fix} _ Developer_Description.sql, this naming can also get more information about script developers and related features.

The value of spring.flyway.outOfOrder uses true in production and false in development.

When multiple systems share a database schema, configure spring.flyway.table to set different metadata table names for different systems instead of using the default flyway_schema_history.

Thank you for reading! On "how to use Flyway in java to manage database version changes" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.