Article From:https://www.cnblogs.com/jpfss/p/9967691.html

What is Flyway?

Flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration.

FlywayIt is an open source version management tool for databases, and it tends to be more conventionally superior to configuration. Flyway can manage and track database changes independently of applications, support automatic upgrade of database versions, and have a set of default rules without complex configuration. Migrations canIt can be written as SQL script or in Java code. It not only supports Command Line and Java API, but also supports Build build tools and Spring Boot, etc. It can upgrade database safely and reliably in distributed environment, and it also supports Java API.Failure recovery, etc.

FlywayMainly based on six basic commands:Migrate, Clean, Info, Validate, Baseline and Repair,I’ll explain them one by one later. The main databases currently supported are Oracle, SQL Server, SQL Azure, DB2, DB2 z/OS, MySQL (including Amazon RDS), Mar.IaDB, Google Cloud SQL, PostgreSQL (including Amazon RDS and Heroku), Redshift, Vertica, H2, Hsql, DeRby, SQLite, SAP HANA, solidDB, Sybase ASE and Phoenix.

For Flyway’s strengths, supported databases and comparison with other database version tools, you can read Flyway’s official website.

Why Flyway?

Generally, the project starts with a global design for the database, but in the process of developing new features of the product, it is inevitable to encounter the need to update the database Schema, such as adding new tables, adding new fields and constraints, which often occurs in actual projects. So, when the developer finishesHow to synchronize quickly with other developer machines after a more SQL script for the database? And how to synchronize quickly on the test server? And how to ensure that the integration test can be successfully executed and passed?

Suppose you take the Spring Book technology stack project as an example, someone might say that Hibernate is used locally to update the database Schema schema automatically, and then QA or DEV are sent to the test server to execute the SQL script manually, while writing a Gradle task fromExecute update.

Personally, I think that Hibernate automatically updates the database, feeling unreliable, opaque, control freedom is not high, and sometimes it is easy to make mistakes, such as: a field created with SQL is VARCHAR type, and the configuration in Entity is CHAR type, thatWhen running the integration test, the fields in the automatically created database table are CHAR type, while the actual SQL script expects VARCHAR type. Although the test passes, it is not the expected behavior, and will lose when running Service on the local bootRun or server.Defeat. In addition, it is time-consuming and laborious to execute SQL scripts manually on various test servers. Why not automate them? Of course, for high-level and PROD environments, DBA still needs to execute them manually. Finally, it’s a good idea to write an automated program to automatically execute updates, if you already have one.Some plug-ins or libraries can help you better achieve such functions. Why not make good use of them? Of course, if it’s for learning purposes, it’s no blame to build wheels repeatedly.

In fact, the above problems can be solved by Flyway tools, Flyway can achieve automated database version management, and can record database version update records. Flyway official website has carried out a combination of Why database migrations examples.Detailed elaboration, you can refer to it if you are interested.

Metadata Table

FlywayAt its core is the Metadata table for recording all versions of evolution and status, which creates the default name when Flyway first startsSCHEMA_VERSIONThe metadata table of MySQL is structured as follows:

FieldTypeNullKeyDefault
version_rankint(11)NOMULNULL
installed_rankint(11)NOMULNULL
versionvarchar(50)NOPRINULL
descriptionvarchar(200)NONULL
typevarchar(20)NONULL
scriptvarchar(1000)NONULL
checksumint(11)YESNULL
installed_byvarchar(100)NONULL
installed_ontimestampNOCURRENT_TIMESTAMP
execution_timeint(11)NONULL
successtinyint(1)NOMULNULL

FlywayA clear example of How Flyway works is provided on the official website, which you can refer to.

Migrate

MigrateIt refers to the migration of database Schema to the latest version, which is the core function of Flyway workflow. Flyway checks Metadata (metadata) tables when it is in Migration. If it does not exist, it will create Metadata tables. Metadata tables are mainly used in Flyway workflow.Record version change history and Checksum and so on.

MigrateMigrations under the specified file system or Classpath (which can be understood as version scripts for the database) are scanned, and existing version records in the Metadata table are compared one by one. Flyway will do so if Migrations are not applied.Get these Migrations and Apply them in order to the database, otherwise you don’t need to do anything. In addition, the Migrate operation should be executed by default when the application starts, thus avoiding inconsistency between the program and the database.

Clean

CleanIt is relatively easy to understand that all objects in the corresponding database Schema, including table structures, views, stored procedures, functions, and all data, will be cleared.

CleanOperations are very useful in the development and testing stages. They can help to update and regenerate database table structures quickly and effectively. However, special attention should be paid to the fact that they should not be used in Production’s database.

Info

InfoUsed to print all the details and status information of Migrations, in fact, through Metadata table and Migration, the following figure is a good illustration of the information printed by Info.

InfoIt can help quickly locate the current database version and view Migrations that succeed or fail in execution.

Validate

ValidateFlyway is the default to open validation when it verifies that there has been a change in Apply’s Migrations.

ValidateThe principle is to compare the Checksum value of the Metadata table with that of the local Migrations. If the value is the same, the validation passes, otherwise the validation fails, thus preventing unintentional modification of the local Migrations that have been Apply to the database.

Baseline

BaselineA solution to the existing Schema database is to create a new Metadata table in a non-empty database and apply Migrations to the database.

BaselineIt can be applied to specific versions, so that Metadata tables can be added to existing table structure databases, so that new Migrations can be managed by Flyway.

Repair

RepairOperations can repair Metadata tables, which are very useful when there are errors in Metadata tables.

RepairIt fixes errors in Metadata tables, usually for two purposes:

  • Remove failed Migration records, only for databases that do not support DDL transactions.
  • Re-adjust the Checksums value of Migrations that have been applied, such as: a Migratinon has been applied, but it has been modified locally, and it is expected to re-apply and adjust the Checksum value, but try not to do so, otherwise it may cause other rings.Circumstances fail.

How to use Flyway?

The main focus here is to integrate and use Flyway in Gradle and Spring Boot. Databases usually use MySQL, PostgreSQL, H2 or Hsql.

In addition to specifying Locations, Flyway’s scanning of Migrations must follow certain naming patterns. Migration can be divided into two categories: Versioned and Repeatable.

  • Versioned migrations
    The commonly used version type is Versioned, which is used for version upgrades. Each version has a unique identifier and can only be applied once. Migrations that have been loaded can no longer be modified because the Metadata table records its Checksum value. amongThe version identification version number is composed of one or more digits. The separator between digits can adopt dots or underscores. In fact, the underscores are replaced by dots at run time, and the leading zeros of each part are automatically ignored.

  • Repeatable migrations
    RepeatableRepeatable Migrations, whose updates affect the Checksum value, are then reloaded and are not used for version upgrades. It is very useful for managing updates of unstable database objects. Migration of RepeatableS is always executed sequentially after Versioned, but developers must maintain scripts themselves and ensure that they can be executed repeatedly, usually in SQL statementsCREATE OR REPLACETo ensure repeatable execution.

By default, command rules based on Sql’s Migration file are shown in the following figure:

The file name is composed of the following parts. In addition to using the default configuration, some parts can also customize the rules.

  • prefix: Configurable, prefix identifier, default valueVIt means Versioned.RRepresenting Repeatable
  • version: Identify version number, consisting of one or more digits, the usable points of separators between digits.Or underline_
  • separator: Configurable for separating version identification and description information, defaulting to two underscores__
  • description: Description information. Texts can be separated by underscores or spaces
  • suffix: Configurable, subsequent identification, default.sql

In addition, you can refer to Java-based migrations for interesting information on how to use Java-based migrations.

Supported database

There are still many databases supported by Flyway, including Oracle, SQL Server, SQL Azure, DB2, DB2 z/OS, MySQL (including Amazon RDS), Ma.RiaDB, Google Cloud SQL, PostgreSQL (including Amazon RDS and Heroku), Redshift, Vertica, H2, Hsql, DErby, SQLite, SAP HANA, solidDB, Sybase ASE and Phoenix.
At present, the most widely used databases for individuals are PostgreSQL, MySQL, H2 and Hsql, for each database.flyway.urlThe example is configured as:

1
2
3
4
5
6
7
8
9
10
11
# PostgreSQL
flyway.url = jdbc:postgresql://localhost:5432/postgres?currentSchema=myschema
# MySQL
flyway.url = jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC&useSSL=true
# H2
flyway.url = jdbc:h2:./.tmp/testdb
# Hsql
flyway.url = jdbc:hsqldb:hsql//localhost:1476/testdb

Flyway Command Line”

FlywayThe command line tool supports running directly from the command lineMigrate, Clean, Info, Validate, BaselineandRepair6This command does not need other Build tools, does not need the application to run in JVM, only needs a simple command line, but needs to be downloaded and installed according to different operating systems. Flyway will search the following configuration files in turn, and the later the configuration will override the earlier one.To configure:

A typical Flyway project example directory structure is as follows:

For more information on Flyway command line usage, refer to Flyway Command-line.

First, Flyway plug-ins need to be introduced into Gradle, usually in two ways:

  • Mode 1: Builscript dependency.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    buildscript {
    repositories {
    mavenCentral()
    }
    dependencies {
    classpath("org.flywaydb:flyway-gradle-plugin:4.0.3")
    }
    }
    apply plugin: 'org.flywaydb.flyway'
  • Mode 2 (Recommendation): Use DSL to refer to Plugins.

    1
    2
    3
    plugins {
    id "org.flywaydb.flyway" version "4.0.3"
    }

There are two ways to configure Flyway Properties in Gradle:

  • Method 1: inbuild.gradleConfigure Flyway Properties in.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    flyway {
    url = jdbc:h2:./.tmp/testdb
    user = sa
    password =
    }
    # Or written:
    project.ext['flyway.url'] = 'jdbc:h2:./.tmp/testdb'
    project.ext['flyway.user'] = 'sa'
    project.ext['flyway.password'] = ''
  • Mode two: ingradle.propertiesConfigure Flyway Properties in.

    1
    2
    3
    flyway.url = jdbc:h2:./.tmp/testdb
    flyway.user = sa
    flyway.password =

If you expect to automatically perform some Flyway tasks while running Gradle Clean/Build Tasks, you can setdependsOn,If you do not expect to perform Flyway tasks implicitly, you can not configure them.

1
2
clean.dependsOn flywayRepair # To repair the Flyway metadata table
build.dependsOn flywayMigrate # To migrate the schema to the latest version

In addition, other Tasks:flywayInfo, flywayValidate, flywayBaselineThey correspond to Flyway commands. When using Spring Boot, run./gradlew bootRunThe latest db. migration script is automatically checked and loaded.

Special attention:Should not be executed in a Production environment./gradlew flywayClean,Unless you know your behavior and purpose, this command will clear all database objects, which is quite dangerous.

For more information on Flyway’s use in Gradle, see Flyway Gradle Plugin.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
flyway.baseline-description= # The description to tag an existing schema with when executing baseline.
flyway.baseline-version=1 # Version to start migration.
flyway.baseline-on-migrate=false # Whether to execute migration against a non-empty schema with no metadata table
flyway.check-location=false # Check that migration scripts location exists.
flyway.clean-on-validation-error=false # will clean all objects. Warning! Do NOT enable in production!
flyway.enabled=true # Enable flyway.
flyway.encoding=UTF-8 # The encoding of migrations.
flyway.ignore-failed-future-migration=true # Ignore future migrations when reading the metadata table.
flyway.init-sqls= # SQL statements to execute to initialize a connection immediately after obtaining it.
flyway.locations=classpath:db/migration # locations of migrations scripts.
flyway.out-of-order=false # Allows migrations to be run "out of order".
flyway.placeholder-prefix= # The prefix of every placeholder.
flyway.placeholder-replacement=true # Whether placeholders should be replaced.
flyway.placeholder-suffix=} # The suffix of every placeholder.
flyway.placeholders.*= # Placeholders to replace in Sql migrations.
flyway.schemas= # Default schema of the connection and updating
flyway.sql-migration-prefix=V # The file name prefix for Sql migrations
flyway.sql-migration-separator=__ # The file name separator for Sql migrations
flyway.sql-migration-suffix=.sql # The file name suffix for Sql migrations
flyway.table=schema_version # The name of Flyway's metadata table.
flyway.url= # JDBC url of the database to migrate. If not set, the primary configured data source is used.
flyway.user= # Login user of the database to migrate. If not set, use spring.datasource.username value.
flyway.password= # JDBC password if you want Flyway to create its own DataSource.
flyway.validate-on-migrate=true # Validate sql migration CRC32 checksum in classpath.

If Gradle is used, it is usually usedbuild.gradleIntroduceorg.flywaydb:flyway-core:4.0.3It can be used after dependency. There may be the following requirements:

  • Memory databases are used locally in Run and Tests, wherespring.jpa.hibernate.ddl-autoAre set tovalidate,SchemaIt does not need Hibernate automatic generation and expects to use Flyway, while online environments will use real databases instead of Flyway. How can we achieve this?
    Solution:Can be incommon.propertiesMiddle configurationflyway.enabled=false,Then enable Flyway in the configuration of local or dev. Usually it is recommended to use this mode. After all, it can control different environments. In addition, the local Run does not depend on the real database, and can ensure that the database Schema is created by script.

  • When running Tests, you will use a memory database, a separate configuration file, not Flyway, but a real database when running local bootRun, using Flyway. After all, you don’t want to manually execute scripts locally after every Schema change. How to implement this?
    Solution:Set upbootRun.dependsOnAdd Flyway dependencies dynamically:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    addFlywayDenpendency {
    doLast {
    dependencies {
    compile('org.flywaydb:flyway-core:4.0.3')
    }
    }
    }
    bootRun.dependsOn=addFlywayDenpendency
  • If the project has multiple teams developing different functions at the same time, many branches need to be built, and database Schema changes will be involved. When later Merge, how can Migration versions be controlled and there will be no conflict between database changes?
    Solution:If the database changes of the two branches conflict, either the original database design is unreasonable or the current database changes are unreasonable, so the team needs to consider and coordinate the whole situation. However, when the database is modified at the same time, it will not cause conflicts. Usually, this is the main situation in the actual project.Well, that can be set up.flyway.out-of-order=true,This allows V1 and V3 to be applied when V2 appears. In fact, this problem does not exist in the local use of memory databases, because all objects in the database will be automatically cleaned up, while in the local or dev use of real databases, such problems are encountered, and therefore need to be addressed.Pay attention to that.
    In addition, it is worth mentioning the parameters of Flyway.ignore-failed-future-migrationDefault istrue,When the Rollback database changes to the old version and a new version already exists in the metadata table, Flyway ignores this error and only displays warning information.

Post Views: 3

Link of this Article: Quick mastery and use of Flyway

Leave a Reply

Your email address will not be published. Required fields are marked *