What is Flyway

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

Flyway is a simple open-source database version controller (agreed is larger than the configuration), mainly provides commands such as migrate, clean, info, validate, baseline, and repair. It supports SQL (PL/SQL, T-SQL) and Java, supports command line clients, etc. It also provides a series of plugin support (Maven, Gradle, SBT, ANT, etc.).

Official website: https://flywaydb.org/

For more information about Flyway, please go to google.

Why use Flyway?

In daily development, we use git to manage the version of the code, then what about the version of the database? Use flyway.

Personally think that you can roughly understand the flyway as a database git, which is convenient for multi-person collaboration and recording.

Git: It’s easier for you and your colleagues to maintain the same project, and you can easily get the latest changes.

Flyway: Lets you know your colleagues’ changes to the database in a timely manner and can automatically perform these changes locally.

The problem I encountered

Most of the projects I have experienced are already configured with flyway, and I am used to this convenience.

I recently started a new project and maintained it with another colleague. This project is not equipped with a flyway, so I experienced the following two situations:

1. Colleagues made changes to the database and merged the code into the line (I didn’t participate in the review). After that, I pulled the online code and connected the local database to start the project. I reported the error because there was no newly added data table in the local project. Start depends on him.
2. After completing a requirement, I plan to put the code online. I need to manually go to the line (go to the server or through PHPMyAdmin and other tools) to create the data table before the code is merged.

What is this anti-human operation??? Not afraid that I will write create as a drop?

So I couldn’t bear it. I worked overtime for half an hour after work last night, adding the flyway’s reliance on the project.

Springboot integrates Flyway

1. Add Project Dependency

Maven：

<dependency>
 <groupId>org.flywaydb</groupId>
 <artifactId>flyway-core</artifactId>
 <version>5.0.3</version>
</dependency>

Bazel:

//add this in deps
"//third_party/java:org_flywaydb_flyway_core",

2.Use Flyway for database version control

#flyway
#open flyway
flyway.enabled=true
flyway.encoding=utf-8
#sql file location
flyway.locations=classpath:db/migration
#version record table
flyway.table=schemas_version
flyway.baseline-on-migrate=true
flyway.validate-on-migrate=false

3. Add the SQL file under the path classpath:db/migration configured above. The naming format is:

The number after V represents the current version, generally in incremental form, and cannot be repeated.

But you can choose from many forms, such as V1, V2 or V1.1, V1.2 and so on.

1. Create the database in the database, pay attention to the new library, start the project, the flyway will automatically execute all the sql files when the project starts, and create a new data table schemas_version to save the version data.

Is Flyway easy to use?

I feel very good, it successfully solved my following pain points.

While maintaining a project with my colleagues, I made some changes to the database. After I pulled the latest code using git, the operation always reports an error. I need to re-execute the creation statement of the table to create it locally. After using the flyway, the latest SQL file will be pulled while pulling the latest code, and the data table will be created automatically when the service starts. There is no need to care about some data tables that are not related to you.
 
Newly take over a project, develop and debug locally. After creating a new database locally, you need to execute the build statement. You can use the flyway to automatically create all the tables of the project.
 
Change the computer, I develop and use a desktop computer and a notebook every day. In an emergency, I use some laptops in my home to carry out some development. Every time I change the computer, I need to re-create the database table again, which is very troublesome. The traversal using the flyway is the same as the second point (it can be understood that changing the computer is equivalent to running a new project on the new computer).

Precautions

The above tutorial does not describe the specific configuration of the flyway in great detail. The daily configuration changes on the copy above have three annotations. There are other needs that can be Baidu.
The above tutorial is to use flyway in a new project, can you add flyway dependencies to existing projects? of course can.
Add a dependency method to an existing project (this method is for personal use, there should be other more convenient and reliable methods, welcome to discuss):
What is the version of the flyway that depends on it? After using it once, you will know that it is based on the records in the schema_version table in the database.

Every time you execute a SQL file, you will add a similar

When the project starts, the flyway scans the SQL file and finds that there is currently a file at the beginning of V14. At the same time, there is no record of V14 execution in the database, then the file will be executed.

V13 success

You can export all the table creation statements of the current database to the SQL file, then create a new V1__init_database.sql file, copy all the creation statements into the file, then create a new library, connect the new library startup project, and then copy the schema_version data in the new library. The structure and data of the table are added to the existing database so that the project does not affect the existing database when it starts.

The essence of this wave of operations is: imitate a record, let flyway think that the current database is an already executed version, do not make any changes, and subsequent changes can continue to be added.

Sometimes the wrong SQL statement will be written. If it is executed again after modification, it will report the record of the version already in the database. At this time, enter the database and delete the corresponding record in the schemas_version. (In any case, the development environment does not matter, we will not go to the production environment to execute the wrong SQL).
Summary: flyway is a good thing, it is strongly recommended that the spring boot project be configured, which can save a lot of time and avoid many troubles caused by joint development.