How to load initial data using H2 and Flyway in Spring Boot

Question

I have a specific requirement to load initial data using H2 database as embedded Postgres and Flyway for integration test.

build.gradle

implementation 'org.flywaydb:flyway-core:9.19.1'
implementation 'org.postgresql:postgresql:42.6.0'
...
runtimeOnly 'com.h2database:h2:2.1.214'

application-dev.properties

#1st approach 
#spring.datasource.url=jdbc:postgresql://localhost:5432/configdb
#spring.datasource.driverClassName=org.postgresql.Driver
#spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL8Dialect

#2nd approach
spring.datasource.url=jdbc:h2:mem:configdb;DB_CLOSE_DELAY=-1;MODE=PostgreSQL
spring.datasource.driverClassName=org.h2.Driver

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=none

spring.flyway.schemas=config
spring.flyway.locations=classpath:db

flyway.conf in $PROJECT_ROOT

flyway.user=postgres
flyway.password=postgres
flyway.schemas=config
#flyway.url=jdbc:postgresql://localhost:5432/configdb
flyway.url=jdbc:h2:mem:configdb
flyway.locations=classpath:db

V1__CreateConfigTable.sql in resources/db

CREATE TABLE configdb.config
(
    id   bigint PRIMARY KEY,
    ...
);

-- INSERT query

but it fails with the following error:

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to obtain connection from database: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Answer 1

You can just ovverride property e.g.:

spring.flyway.locations=classpath:/db/migration,classpath:/db/testdata

and add your sql file with initial data in second path. Make sure your script can be executed multiple times (small hint: ON CONFLICT DO NOTHING; at the end of each insert)