Spring Boot Flyway DB Migration Integration Example

Spring Boot Flyway DB Migration Integration Example

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

Now created migration directory db/migrations inside resources directory and create V1__intial.sql file as shown below

Flyway migration script

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.

Flyway table

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.

Flyway migration script
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”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.