javamysqlspring-booth2flyway

How to use common flyway migration files for both mysql and h2

I am using h2 database for testcase and mysql database as main DB. Do I able to maintain single flyway migration for both.

I am using springboot framework.

application-mysql.properties:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://localhost:3306/shipment-planning?useSsl=true
spring.datasource.username=root

spring.flyway.default-schema=shipment-planning
spring.flyway.enabled=true

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.flyway.locations = h2/db/migration

application-h2.properties

spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=sa
spring.flyway.enabled=true
spring.flyway.locations = h2/db/migration

one of syntax error I got when I run with Mysql.properties

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATA TYPE TEXT' at line 2"

Solution

  • It is not guaranteed that using different databases you can use exactly the same scripts.

    If your scripts are complex because they have strong use of advanced functionalities of a database, like special kind of indexes, tablespaces, data types, it is sure that your scripts could not be executed both in H2 and MySql.

    Considering that you have three ways to handle it: