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

This article introduces the knowledge of "how to automate the management of database scripts by Flyway". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Today, discuss an interesting topic: we can achieve project version control through Git; through continuous integration through Jenkins, then for the database level, we still rely on running SQL scripts by hand. How can we ensure the latest and correct SQL scripts in multiple environments (development environment, test environment, pre-release environment, production environment)?

As we all know, manual operations are very prone to problems, and we should let programs help manage and migrate automatically. Today, the author recommends an open source database migration tool Flyway.

Flyway can not only support MySQL, it can also support a lot of other databases.

In fact, Spring Boot has perfectly integrated Flyway. In this regard, we can use it very easily. First, we introduce Maven dependencies. (note that we also need spring-boot-starter-jdbc and mysql-connector-java dependencies in our project.)

Org.flywaydb flyway-core

In addition, we configure the relevant options in application.yml. Spring Boot scans Migration under the classpath://db/migration directory by default. Here, the author adjusts it to db/sql through spring.flyway.locations.

Spring: flyway: enabled: true baseline-on-migrate: true locations: [classpath:db/sql]

When the system program starts, it automatically creates the flyway_schema_history file. (of course, you can also create it manually.) This table is the metadata table for Flyway, which holds a record of each migration, which contains 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 with 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.

CREATE TABLE `flyway_schema_ ranky` (`installed_ rank`int (11) NOT NULL, `version`varchar (50) DEFAULT NULL, `tyption`varchar (20) NOT NULL, `script` varchar (1000) NOT NULL, `checksum` int (11) DEFAULT NULL, `installed_ by`varchar (1000) NOT NULL, `installed_ on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `execution_ time` int (11) NOT NULL, `version`tinyint (1) NOT NULL, PRIMARY KEY (`installed_ rank`), KEY `flyway_schema_history_s_ idx` (`checksum`)

Then, we manually create an initialization SQL script under db/sql: V1.1__INIT_DB.sql.

DROP TABLE IF EXISTS `tag`; CREATE TABLE `tag` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `gmtCreate` date DEFAULT NULL, `gmtModified` date DEFAULT NULL, `title` varchar (32) DEFAULT NULL, `parentId` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then, after the program starts again, it will automatically publish it to the database.

Here, one additional point of knowledge is needed: the version of Flyway is relatively regular. It adopts the principle of left alignment, and the vacancy is replaced by 0. For example, 1.1 is higher than 1.0, 1.1.1 is higher than 1.1, and 1.1.01 and 1.1.1 are the same. And they are sorted by version and executed sequentially.

In addition, Flyway supports not only DDL, but also DML (insert, update, delete), and so on. Therefore, we can create another V1.2__INSERT_TAG_DATA.sql file to verify it.

INSERT tag (title, parentId) values ('java', 0); INSERT tag (title, parentId) values (' spring', 0)

Finally, let's discuss that Flyway supports common types of migration:

Versioned migrations: database upgrade script

Repeatable migrations: can be executed repeatedly and will be re-executed when the script checksums changes.

Prefix: prefix identification. The default is V for Versioned and R for Repeatable.

Version: identifies the version number, consisting of one or more digits, and the point at which separators are available. Or underscore _

Separator: used to separate version identification and description information. Default is two underscores _ _

Description: description information. Text can be separated by underscores or spaces.

Suffix: subsequent identification. Default is .sql

To sum up, Flyway helps us automate the maintenance and management of database version migration through metadata (flyway_schema_history).

This is the end of the content of "how to automate the management of database scripts by Flyway". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.