1, Flyway introduction
Flyway Is an open source database version management tool. It can be easily used on the command line or introduced in Java applications to manage our database version.
In a project or product, it is difficult to clarify the business and design the database table at the beginning, so the data table will continue to iterate in the iteration cycle. Using Flyway in Java applications can be used to iterate the database table structure quickly and effectively, and ensure that the data tables are consistent when deployed to the test environment or production environment.
Flyway supports many databases. Excerpts from the official website are as follows:
Supported databases are Oracle, SQL Server (including Amazon RDS and Azure SQL Database), Azure Synapse (Formerly Data Warehouse), DB2, MySQL (including Amazon RDS, Azure Database & Google Cloud SQL), Aurora MySQL, MariaDB, Percona XtraDB Cluster, TestContainers, PostgreSQL (including Amazon RDS, Azure Database, Google Cloud SQL & Heroku), Aurora PostgreSQL, Redshift, CockroachDB, SAP HANA, Sybase ASE, Informix, H2, HSQLDB, Derby, Snowflake, SQLite and Firebird.
2, How does Flyway work
Flyway workflow is as follows:
1. After the project is started and the application completes the establishment of database connection pool, Flyway runs automatically.
2. When first used, Flyway creates a Flyway_ schema_ The history table is used to record sql execution records.
3. Flyway will scan all sql scripts under the specified path of the project (classpath: dB / migration by default), which is the same as flyway_ schema_ The history table compares the script records. If the database records the executed script, which is inconsistent with the sql script in the project, flyway will report an error and stop the project execution.
4. If the verification passes, all scripts whose version number is not greater than the maximum version number of the sql record in the table will be ignored. Then execute other scripts one by one according to the version number from small to large.
3, Using Flyway in a SpringBoot project
1. Initialize a SpringBoot project
Introduce MySQL database driver dependency and Flyway dependency:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.example.flyway.FlywayApplication</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
2. Add Flyway configuration
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/flywaydemo?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useInformationSchema=true
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
flyway:
enabled: true # Open flyway
# The clean command of flyway will delete all table s under the specified schema. Production must be disabled. The default value is false. Theoretically, it is unscientific to use it as the default configuration.
clean-disabled: true
locations: classpath:db/migration #flyway file directory
table: flyway_schema_history #History table name
baseline-on-migrate: true
validate-on-migrate: true #Continuity check
baseline-version: 1 #Specify default version
encoding: UTF-8 #code
out-of-order: false # Allow non sequential migration of development suggestions true production suggestions false
sql-migration-prefix: V #Prefix default v
sql-migration-separator: __ #Delimiter, default__
sql-migration-suffixes: .sql #Migrate files, default sql
Spring boot imports flyway when creating a project. You can use the default build without configuring yml
3. Build database
The naming of SQL statements must comply with certain specifications, otherwise flyway will report an error when running. There are two main naming rules:
- The SQL name that needs to be executed only once starts with an uppercase "V" followed by a combination of "0 ~ 9" numbers. You can use "." between numbers Or underline "" Split, then split with two underscores, followed by the file name, and finally End of SQL. For example, v2 1.5__ create_ user_ ddl. sql,V4.1_2__add_user_dml.sql.
- Repeatable SQL starts with a capital "R", followed by two underscores, followed by the file name, and finally End of SQL. For example, R__truncate_user_dml.sql.
Among them, the SQL execution priority starting with V is higher than that starting with R.
The database name is flyway
For the first execution, you need to create a table flyway in the database instance flyway_schema_history``flyway_schema_history is a flyway version control record table, which must be created.
CREATE TABLE `flyway_schema_history` ( `installed_rank` int(11) NOT NULL, `version` varchar(50) DEFAULT NULL, `description` varchar(200) NOT NULL, `type` varchar(20) NOT NULL, `script` varchar(1000) NOT NULL, `checksum` int(11) DEFAULT NULL, `installed_by` varchar(100) NOT NULL, `installed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `execution_time` int(11) NOT NULL, `success` tinyint(1) NOT NULL, PRIMARY KEY (`installed_rank`), KEY `flyway_schema_history_s_idx` (`success`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Next, three scripts are prepared as follows:
1.V1__create_user.sql, where the code is as follows. The purpose is to create a user table and execute it only once.
CREATE TABLE IF NOT EXISTS `USER`( `USER_ID` INT(11) NOT NULL AUTO_INCREMENT, `USER_NAME` VARCHAR(100) NOT NULL COMMENT 'User name', `AGE` INT(3) NOT NULL COMMENT 'Age', `CREATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `CREATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN', `UPDATED_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `UPDATED_BY` varchar(100) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`USER_ID`) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.V2__add_user.sql, where the code is as follows. The purpose is to insert a piece of data into the user table and execute it only once.
insert into `user`(user_name,age) values('lisi',33);
3.R__add_unknown_user.sql, in which the code is as follows. The purpose is to insert a piece of data into the user table if there is any change during each startup.
insert into `user`(user_name,age) values('unknown',33);
The corresponding directory screenshot is as follows:
Note: the file location here should correspond to the path in the yaml file, otherwise the sql file cannot be executed
The file locations in the following figure correspond to those in yaml one by one
Or:
You can also execute sql in the specified folder
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/flywaydemo?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useInformationSchema=true
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
flyway:
enabled: true # Open flyway
# The clean command of flyway will delete all table s under the specified schema. Production must be disabled. The default value is false. Theoretically, it is unscientific to use it as the default configuration.
clean-disabled: true
locations: classpath:db/migration/create #flyway file directory
table: flyway_schema_history #History table name
baseline-on-migrate: true
validate-on-migrate: true #Continuity check
baseline-version: 1 #Specify default version
encoding: UTF-8 #code
out-of-order: false # Allow non sequential migration of development suggestions true production suggestions false
sql-migration-prefix: V #Prefix default v
sql-migration-separator: __ #Delimiter, default__
sql-migration-suffixes: .sql #Migrate files, default sql
Next, there will be the user table in the database
But the other two sql files were not executed.
4. Start the main program of the SpringBoot program
At this point, the default configuration of flyway is enough for us to start running. At this point, we start the main program of SpringBoot. If there is no configuration error in the above steps, the database script file will be executed automatically at startup. The operation screenshot is as follows:
4, flyway, pay attention
1. The migration of flyway must be performed on a blank database, otherwise an error will be reported;
2. For databases that already have data, you must first baseline and then migrate;
3. The clean operation is to delete all contents of the database, including those before baseline;
4. Try not to modify the executed SQL, even the re executable SQL starting with R, which will be unfavorable to data migration;
5, Configuration list of flyway
flyway.baseline-description Description of the base version when the migration is performed.
flyway.baseline-on-migrate Target found during migration schema Whether to automatically perform benchmark migration when a table is not empty and has no metadata. The default value is false.
flyway.baseline-version When starting baseline migration schema The default value is 1.
flyway.check-location Check whether the location of the migration script exists. By default false.
flyway.clean-on-validation-error Whether to call automatically when a verification error is found clean，default false.
flyway.enabled Open flywary，default true.
flyway.encoding Set the code during migration. The default is UTF-8.
flyway.ignore-failed-future-migration Whether to ignore the wrong migration when reading the metadata table. The default is false.
flyway.init-sqls To be executed when the connection is initialized SQL.
flyway.locations Location of migration script, default db/migration.
flyway.out-of-order Allow unordered migration, default false.
flyway.password Password for target database.
flyway.placeholder-prefix Set each placeholder Prefix of, default ${.
flyway.placeholder-replacementplaceholders Whether to be replaced, default true.
flyway.placeholder-suffix Set each placeholder Suffix, default}.
flyway.placeholders.[placeholder name]set up placeholder of value
flyway.schemas Setting needs flywary Migrating schema，Case sensitive, default to the default connection schema.
flyway.sql-migration-prefix The prefix of the migration file. The default is V.
flyway.sql-migration-separator File name separator for migration script, default__
flyway.sql-migration-suffix The suffix of the migration script. The default is.sql
flyway.tableflyway The metadata table name used. The default is schema_version
flyway.target The target version used during migration. The default is latest version
flyway.url Used during migration JDBC URL，If not specified, the configured master data source will be used
flyway.user User name of the migration database
flyway.validate-on-migrate Whether to verify during migration. The default value is true
thank you for reading. I like it without error. If there is any error, please correct it in the comment area.