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.
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"))
);
}
}
Spring Boot & H2 - Overview H2 database is an open source, embedded and in memory relational database management system. It is written in Java and provides a client/server application. It stores data in system memory instead of disk. Once program is closed, data is also lost.
I've faced the same issue to setup and access to h2 database in memory for tests:
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
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;
}
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");
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With