Flyway is an Opensource Database migration tool, well known for its simplicity and effectiveness. This can be used from command-line, in Java projects, Maven plug-in, and Gradle plug-in. It is based on only 7 commands 7 basic commands: Migrate, Clean, Info, Validate, Undo, Baseline, and Repair. For more information one can check the official documentation here Documentation – Flyway by Redgate • Database Migrations Made Easy. (flywaydb.org)
Spring Boot and Flyway
Spring Boot comes with out-of-the-box integration for Flyway. To integrate and run flyway integration in spring boot you just need to add the following dependency in your spring boot project using Gradle or you can include this using your project build platform.
implementation 'org.flywaydb:flyway-core:8.4.4'
Important note for Mysql 8 and Maria DB support you need to add an additional dependency otherwise you will get the following exception
Caused by: org.flywaydb.core.api.FlywayException: Unsupported Database: MySQL 8.0
Addition dependencies can be found here and added as given below
implementation 'org.flywaydb:flyway-mysql:8.4.4'
Spring Boot will then automatically autowire Flyway with its default DataSource and invoke it on startup. It will automatically look for migration SQL scripts on the default location using the default versioning naming convention. You can also change Flyway DB migration properties using the application.properties or application.yaml file.
The default directory of the Flyway migration script in the spring boot project is classpath:db/migration but one can change using property files or configuration using Springboot Java configuration. You can also user classpath or filesystem path as script location property as given below.
spring.flyway.locations=classpath:db/migration,filesystem:/opt/migration
The above property will tell the Flyway migration tool to lookup scripts first in classpath then system path /opt/migration.
Script files name Typically, migrations are scripts in the form V<VERSION>__<NAME>.sql (with <VERSION> an underscore-separated version, such as ‘1’ or ‘2_1’).version and name should be separated with a double underscore. eg V1__intitial.sql or V1_2__secondrev.sql.
Spring Boot Flyway Simple Example
For example, I added the following dependencies in my Spring Boot project in build.gradle file
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
runtimeOnly 'mysql:mysql-connector-java'
implementation 'org.flywaydb:flyway-core:8.4.4'
implementation 'org.flywaydb:flyway-mysql:8.4.4'
and added the following connection properties in the application.properties file
spring.datasource.url=jdbc:mysql://192.168.1.30:3306/authdb?createDatabaseIfNotExist=true
spring.datasource.username=user1
spring.datasource.password=password
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.flyway.enabled=true
spring.flyway.url = jdbc:mysql://localhost:3307/userdb
spring.flyway.schemas=userdb
spring.flyway.defaultSchema=userdb // default schema is importent
spring.flyway.user = app-user
spring.flyway.password = pwd
spring.flyway.baseline-on-migrate=true
Note: For latest version you can get following exception
Unable to determine schema for the schema history table. Set a default schema for the connection or specify one using the ‘defaultSchema’ property. In this case you need to provide defaultSchema property as given above
In Spring properties use spring.flyway.defaultSchema=userdb normal Java project use flyway.defaultSchema=userdb
Now created migration directory db/migrations inside resources directory and create V1__intial.sql file as shown below
Now run your spring boot project. you can see the following logs of spring boot in the console
2022-02-13 10:22:49.621 INFO 15180 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2022-02-13 10:22:49.654 INFO 15180 --- [ main] o.f.c.internal.license.VersionPrinter : Flyway Community Edition 8.4.4 by Redgate
2022-02-13 10:22:49.654 INFO 15180 --- [ main] o.f.c.i.database.base.BaseDatabaseType : Database: jdbc:mysql://192.168.1.30:3306/authdb (MySQL 8.0)
2022-02-13 10:22:49.693 INFO 15180 --- [ main] o.f.core.internal.command.DbValidate : Successfully validated 1 migration (execution time 00:00.015s)
2022-02-13 10:22:49.707 INFO 15180 --- [ main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table `authdb`.`flyway_schema_history` ...
2022-02-13 10:22:49.795 INFO 15180 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema `authdb`: << Empty Schema >>
2022-02-13 10:22:49.803 INFO 15180 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema `authdb` to version "1 - Initial"
2022-02-13 10:22:49.927 INFO 15180 --- [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema `authdb`, now at version v1 (execution time 00:00.140s)
2022-02-13 10:22:50.059 INFO 15180 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
It created 2 tables and one index by executing 3 scripts in the database and it will also create one schema history table name flyway_schema_history in the database for tracking of version.
It tracks changes by migration scripts name and their checksum if any file will modify after its execution it will throw an exception, this is the basic working of the flyway. for any change in schema and DB create another higher version.
The exception will look as given below
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1
-> Applied to database : -419208586
-> Resolved locally : -1090537089. Either revert the changes to the migration, or run repair to update the schema history.
For the migration test, I create another version V1_2__Intial.sql with another table creation statement.
2022-02-13 10:31:51.543 INFO 10176 --- [ main] o.f.c.internal.license.VersionPrinter : Flyway Community Edition 8.4.4 by Redgate
2022-02-13 10:31:51.543 INFO 10176 --- [ main] o.f.c.i.database.base.BaseDatabaseType : Database: jdbc:mysql://192.168.1.30:3306/authdb (MySQL 8.0)
2022-02-13 10:31:51.589 INFO 10176 --- [ main] o.f.core.internal.command.DbValidate : Successfully validated 2 migrations (execution time 00:00.023s)
2022-02-13 10:31:51.600 INFO 10176 --- [ main] o.f.core.internal.command.DbMigrate : Current version of schema `authdb`: 1
2022-02-13 10:31:51.608 INFO 10176 --- [ main] o.f.core.internal.command.DbMigrate : Migrating schema `authdb` to version "1.2 - Intial"
2022-02-13 10:31:51.680 INFO 10176 --- [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema `authdb`, now at version v1.2 (execution time 00:00.086s)
Now it created another table and the database is migrated successfully
If your sqlfile checksum is not matching and you are sure nothing is changed then update the checksum in DB with resolve locally checksum value.
One response to “Spring Boot Flyway DB Migration Integration Example”
Great post! thanks