Setup h2 in spring boot application with r2dbc and flyway

Question

I'm playing around with Spring Boot and the reactive jdbc driver called r2dbc. In my main application I'm using Postgres as a database and now I want to the use h2 for the tests. And the Flyway migration is working with the setup but when the Spring application is able to insert records.

Here is my setup and code

@SpringBootTest
class CustomerRepositoryTest {

    @Autowired
    CustomerRepository repository;

    @Test
    void insertToDatabase() {
        repository.saveAll(List.of(new Customer("Jack", "Bauer"),
                new Customer("Chloe", "O'Brian"),
                new Customer("Kim", "Bauer"),
                new Customer("David", "Palmer"),
                new Customer("Michelle", "Dessler")))
                .blockLast(Duration.ofSeconds(10));
    }
}

Here is the error that I'm getting

 :: Spring Boot ::        (v2.3.4.RELEASE)

2020-10-14 15:59:18.538  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : Starting CustomerRepositoryTest on imalik8088.fritz.box with PID 25279 (started by imalik in /Users/imalik/code/private/explore-java/spring-example)
2020-10-14 15:59:18.540  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : No active profile set, falling back to default profiles: default
2020-10-14 15:59:19.108  INFO 25279 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
2020-10-14 15:59:19.273  INFO 25279 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 160ms. Found 1 R2DBC repository interfaces.
2020-10-14 15:59:19.894  INFO 25279 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 6.5.0 by Redgate
2020-10-14 15:59:20.052  INFO 25279 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:h2:mem:///DBNAME (H2 1.4)
2020-10-14 15:59:20.118  INFO 25279 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.022s)
2020-10-14 15:59:20.131  INFO 25279 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "PUBLIC"."flyway_schema_history" ...
2020-10-14 15:59:20.175  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2020-10-14 15:59:20.178  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.0.0 - schma
2020-10-14 15:59:20.204  INFO 25279 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.036s)
2020-10-14 15:59:20.689  INFO 25279 --- [           main] i.g.i.repository.CustomerRepositoryTest  : Started CustomerRepositoryTest in 2.466 seconds (JVM running for 3.326)

2020-10-14 15:59:21.115 DEBUG 25279 --- [           main] o.s.d.r2dbc.core.DefaultDatabaseClient   : Executing SQL statement [INSERT INTO customer (first_name, last_name) VALUES ($1, $2)]


org.springframework.data.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [INSERT INTO customer (first_name, last_name) VALUES ($1, $2)]; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [42102] [42S02] Tabelle "CUSTOMER" nicht gefunden
Table "CUSTOMER" not found; SQL statement:
INSERT INTO customer (first_name, last_name) VALUES ($1, $2) [42102-200]

My src/test/resources/application.yaml is looking like this:

spring:
  r2dbc:
    url: r2dbc:h2:mem:///DBNAME?options=DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    username: sa
    password:

  flyway:
    url: jdbc:h2:mem:///DBNAME
    baseline-on-migrate: true
    user: sa
    password:

Any ideas whats missing missing or whats wrong with the setup? If further information is needed please let me know.

Addition/Solution:

The url pattern is different between jdbc and r2dbc. The working solution for me is as follows:

url: r2dbc:h2:file:///./tmp/test-database
url: jdbc:h2:file:./tmp/test-database

And In order to setup Flyway you have to Configure Flyway:

// Flyway is not compatible with r2dbc yet, therefore this config class is created
@Configuration
public class FlywayConfig {

    private final Environment env;

    public FlywayConfig(final Environment env) {
        this.env = env;
    }

    @Bean(initMethod = "migrate")
    public Flyway flyway() {
        return new Flyway(Flyway.configure()
                .baselineOnMigrate(true)
                .dataSource(
                        env.getRequiredProperty("spring.flyway.url"),
                        env.getRequiredProperty("spring.flyway.user"),
                        env.getRequiredProperty("spring.flyway.password"))
        );
    }
}

Hereinbefore

I followed the Getting Started R2DBC Tutorial with Spring Boot 2.4.3 but noticed that the ConnectionFactoryInitializer Bean is deprecated. I'm afraid the Spring Boot support for schema.sql/data.sql with R2DBC is going to be removed as indicated here. I'd suggest removing its Bean configuration from the Application class and use some 3rd party tool such as Flyway or Liquibase, as you did. – Pettitoes

Answer 1

I am currently having the same problem using r2dbc with liquibase. I am suspecting that the JDBC url points to a different database due to a slightly different syntax between R2DB and JDBC. I can manage to get h2 running from the file system though...

    url: r2dbc:h2:file:///~/db/testdb
...
    url: jdbc:h2:file:~/db/testdb

EDIT:

In non-reactive Spring Data I'd usually populate the Schema into the H2 memory database using a schema.sql/data.sql pair. This is also possible with R2DBC, but you have to configure the populator yourself.

It's also in the Getting Started R2DBC Tutorial. Basically you have to register a ConnectionFactoryInitializer bean.

  @Bean
  public ConnectionFactoryInitializer initializer(@Qualifier("connectionFactory") ConnectionFactory connectionFactory) {
    var initializer = new ConnectionFactoryInitializer();
    initializer.setConnectionFactory(connectionFactory);

    var populator = new CompositeDatabasePopulator();
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));
    populator.addPopulators(new ResourceDatabasePopulator(new ClassPathResource("data.sql")));
    initializer.setDatabasePopulator(populator);

    return initializer;
  }

Assagai

yeahh it worked, thanks. The issue was in the different syntax of url patterns and I was not able to configure it for in-memory there the file database in the same root folder instead of in the homedir. I'll expand my question with the solution. – Hereinbefore

I followed the Getting Started R2DBC Tutorial with Spring Boot 2.4.3 but noticed that the ConnectionFactoryInitializer Bean is deprecated. I'm afraid the Spring Boot support for schema.sql/data.sql with R2DBC is going to be removed as indicated here. – Pettitoes

Answer 2

I've faced the same issue to setup and access to h2 database in memory for tests:

• Liquibase for database migration using JDBC driver
• Tests Reactive Crud Repository using R2DBC driver

Error encoutred:

org.springframework.data.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [INSERT INTO MY_TABLE... Table "MY_TABLE" not found ...

Inspired by Chris's solution, i configured my src/testresources/application.properties file as follow:

spring.r2dbc.url=r2dbc:h2:mem:///~/db/testdb
spring.r2dbc.username=sa
spring.r2dbc.password=

spring.liquibase.url=jdbc:h2:mem:~/db/testdb;DB_CLOSE_DELAY=-1
spring.liquibase.user=sa
spring.liquibase.password=
spring.liquibase.enabled=true

Heatstroke

Answer 3

I was able to get it working.

First of all I created following test configuration class (because I want to execute tests only agains H2, on production mode I am using PostgreSQL):

@TestConfiguration
public class TestConfig {
    @Bean
    @Profile("test")
    public ConnectionFactory connectionFactory() {
        System.out.println(">>>>>>>>>> Using H2 in mem R2DBC connection factory");
        return H2ConnectionFactory.inMemory("testdb");
    }

    @Bean(initMethod = "migrate")
    @Profile("test")
    public Flyway flyway() {
        System.out.println("####### Using H2 in mem Flyway connection");
        return new Flyway(Flyway.configure()
                .baselineOnMigrate(true)
                .dataSource(
                        "jdbc:h2:mem:testdb",
                        "sa",
                        "")
        );
    }
}

As you can see in the code above, both beans are scoped to the "test" profile only. As you can imagine I have pretty much the same beans in a regular ApplicationConfiguration class but annotated as a @Profile("default") and configured to use a PostgreSQL.

Second thing is that I created annotation which combines several other annotations to not repeat myself and to easily pickup beans declared in the TestConfig class:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@SpringBootTest
@ActiveProfiles("test")
@Import(TestConfig.class)
public @interface IntegrationTest {
}

Now the test itself:

@IntegrationTest
class CartsIntegrationTest {
 // test methods here ....
}

I believe the main hint is to use H2ConnectionFactory.inMemory("testdb");

Oscilloscope

Indeed, key is to use the inMemory connection. My solution did not include 2 different configs. But the general idea is right: define Flyway bean in main configuration and override ConnectionFactory in a test context. – Stretcher

Answer 4

There were 2 issues I was experiencing in my project.

1. I needed to include the dependency:

   <dependency>
       <groupId>io.r2dbc</groupId>
       <artifactId>r2dbc-h2</artifactId>
       <scope>test</scope>
   </dependency>
   

2. I needed to change the value for spring.r2dbc.url to r2dbc:h2:mem:///test_db

With these changes, rd2bc worked with an in memory h2 database for testing. See also:

https://github.com/r2dbc/r2dbc-h2

Vin

Answer 5

Flyway currently only supports the blocking JDBC APIs, and it is not compatible with the reactive r2dbc if possbile do not mix them in the same application.

1. Try to register a ConnectionFactoryInitializer to initiate the database schema and data as @Chris posted, my working example can be found here.

2. Try nkonev/r2dbc-migrate which is trying to migrate the flyway to the R2dbc world.

Uraeus