Mysql Question posted in
The official MySQL documentation can be found here.

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"

Tags:

2

Answers


  1. 0

    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:

    • create very simple scripts not using any special feature of the database in a single directory and check that they can work on both databases
    • create two directories (one for H2 and one for MySql) and duplicate scripts adapting them for each db
    • mix the two solutions having a common directories for script that can be executed in both databases and one directory for each db with scripts that use advanced features of that db and that cannot be shared
    Login or Signup to reply.
  2. 0

    This isnt possible. As soon as the queries become complex, you will run into issues.

    You can separate them into different folders and point your tests to use the migration scripts according to the database vendor. You can have a folder for mysql migration files in /mysql and h2 files in /h2.

    Using a different kind of database for your tests is also bad practice. I recommend using testcontainers as they will allow you to spin up docker containers during your tests, meaning you can have an instance of mysql that will run on test startup and shut down on test finish, hence eliminating the issue you are experiencing at the moment with the benefit of having meaningful tests.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search