Introduce Flyway

Flyway is an open source database versioning tool that favors a protocol over configuration approach. Flyway can manage and track database changes independently of application implementation, supports automatic database version upgrade, and has a default protocol, which does not require complex configuration. Migrations can be written into SQL scripts and Java code, supporting Command Line and Java API. Also supports Build Build tools and Spring Boot, while in a distributed environment can safely and reliably upgrade the database, but also support failure recovery.

Flyway purposes

The global design of the database is usually carried out at the beginning of a project. However, during the development of new features of the product, it is inevitable that the database Schema needs to be updated, such as adding new tables, adding new fields and constraints, which often happen in actual projects. So, how can developers quickly synchronize on other developer machines once they have completed SQL scripts for the database? And how to synchronize quickly on the test server? And how do you ensure that integration tests are executed and passed?

Manual execution of SQL scripts on each test server can be laborious and time-consuming, so why not automate it? Of course, for high-level and PROD environments, manual execution is required by the DBA. Finally, it’s a good idea to write an automated program that automatically performs updates, but if there are plug-ins or libraries that can help you do that, why not take advantage of them? Of course, there’s nothing wrong with repeating the wheel for learning purposes. This can be done with Flyway, which enables automated database versioning and logging of database version updates.

Flyway command

Metadata is used to record Metadata. Each command has different functions and solves different problems. The following describes the Metadata and commands respectively, and the diagrams are from Flyway official documents.

Metadata Table

The core of Flyway is the Metadata table used to record the evolution and status of all versions. When Flyway is started for the first time, a Metadata table named flyway_schema_history will be created by default. Its table structure is as follows (take MySQL as an example) :

Migrate

Migrate refers to the migration of database schemas to the latest version and is a core function of Flyway’s workflow. When Flyway migrates, it checks Metadata tables and creates Metadata tables if they do not exist. Metadata is used to record version change history and checksums.

Migrate scans Migrations on a specified file system or Classpath and compares existing Migrations in the Metadata table one by one. If there are unapplied Migrations, Flyway takes these Migrations and applies them in order to the database, otherwise nothing needs to be done. In addition, Migrate is usually performed by default at application startup to avoid inconsistencies between the application and the database.

Clean

Delete all objects in the corresponding database Schema, including table structures, views, stored procedures, functions, and all data. The Clean operation is very useful during the development and testing phases to help update and rebuild database table structures quickly and efficiently, but it should not be used on Production databases!

Info

Info is used to print detailed and status information about all Migrations. This is also done through the Metadata table and Migrations. Info helps you quickly locate the current database version, as well as view successful and failed Migrations.

Validate

Validate refers to verifying whether Migrations that have been applied have changed. Flyway enables validation by default. The Validate principle compares the Checksum values of the Metadata table and local Migrations. If the Checksum values are the same, the verification succeeds. Otherwise, the verification fails.

Baseline

Baseline is a solution for a database that already has a Schema structure by creating a new Metadata table in a non-empty database and applying Migrations to that database. The Baseline can be applied to a specific version, making it possible to add Metadata tables to databases with existing table structures to manage new Migrations with Flyway.

Repair

The Repair operation can Repair the Metadata table, which is useful when the Metadata table is faulty. Repair Restores Metadata errors and is used in either of the following ways:

  • Remove failed Migration records. This problem is only for databases that do not support DDL transactions.
  • Adjust the Checksums of Migratons already applied. For example, a Migratinon has been applied but is modified locally and the Checksum is expected to be reapplied. Do not perform this operation because it may cause environmental failures.

Supported databases

Flyway currently supports a number of databases, including: Oracle, SQL Server, SQL Azure, DB2, DB2 z/OS, MySQL(including Amazon RDS), MariaDB, Google Cloud SQL, PostgreSQL(including Amazon RDS and Heroku), Redshift, Vertica, H2, Hsql, Derby, SQLite, SAP HANA, solidDB, Sybase ASE and Phoenix.

Flyway application

Flyway can through the command line and plug-in (such as maven) run the corresponding command, specific can refer to https://flywaydb.org/getstarted/firststeps/commandline.

This article will focus on applying Flyway in Spring Boot.

Introduction of depend on

<! -- https://mvnrepository.com/artifact/org.flywaydb/flyway-core -->
<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-core</artifactId>
	<version>5.0.7</version>
</dependency>
Copy the code

Configuration Flyway

flyway:
  locations: classpath:db/migration
  baseline-on-migrate: true
  url: jdbc:mysql://localhost:3306/test
  sql-migration-prefix: V
  sql-migration-suffix: .sql
Copy the code

We specify the above properties in the configuration file. The meanings of Flyway configuration attributes are as follows:

  • Flyway. baseline-version: used to mark the version of the existing Schema when performing the baseline (default: 1)
  • Flyway. enabled: Enables flyway (default: true)
  • Flyway. sql-migration-prefix: indicates the file name prefix of SQL migration
  • Flyway. sql-migration-suffix: indicates the file name suffix of SQL migration
  • Flyway. baseline-on-migrate: Whether a baseline is automatically invoked when a migration is performed against a non-empty Schema without metadata tables
  • Flyway. location: Location of the migration script (default: DB /migration)

Migrations are created correctly

Migrations are the version scripts Flyway uses to update its database, such as: An SQL-based Migration is named V1__init_tables. Sql, which is the Sql statement that creates all the tables, and Flyway also supports Java-based Migration. Flyway load Migrations of the default Locations for the classpath: db/migration, you can also specify the filesystem: / project/folder, load it is in the Runtime automatically performed recursively.

In addition to specifying Location, Flyway must also follow certain naming modes for Migrations. Migration is mainly divided into two types: Versioned and Repeatable.

  • The Versioned migrations type is used for version upgrade. Each version has a unique identifier and can be used only once. Loaded migrations cannot be modified because the Metadata records the Checksum value. The version identifier is the version number and consists of one or more numbers. The delimiter between the numbers can be a dot or underscore. At runtime, the underscore is actually replaced by a dot.

  • Repeatable Migrations Repeatable migrations are migrations that can be loaded repeatedly. Each update of Repeatable migrations affects the Checksum value and is then reloaded. They are not used for version updates. Useful for managing updates to volatile database objects. Repeatable Migrations are always executed in sequence after Versioned, but developers must maintain the script themselves and ensure repeatability. CREATE OR REPLACE is usually used in SQL statements to ensure repeatability.

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

Create an SQL script file

The SQL script created in the service is shown above. After the service is started, the following log information is displayed:

The current database script is up to date. The latest version in the schemA_version table is 1.4.

conclusion

This article focuses on Flyway, including the six commands it provides and how to use Flyway. Flyway can effectively improve the database version management mode, which usually simplifies many unnecessary and tedious operations in the local and DEV environment. Of course, Flyway application in the production line needs to be very careful, which may cause serious consequences to online data.

Subscribe to the latest articles, welcome to follow my official account

reference

  1. Flyway
  2. Learn and use Flyway quickly