initialize database for testing purpose on Spring Data R2DBC

Question

In a non-test environment, I can set up the bean to initialize the database with the schema.sql like this

    @Bean
    ConnectionFactoryInitializer initializer(@Qualifier("connectionFactory") ConnectionFactory connectionFactory) {

        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);
        initializer.setDatabasePopulator(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));

        return initializer;
    }

the problem is, I want to do something like this but for testing purpose. I'm trying to do some kind of integration testing with the database so I want the schema to be inserted to a dummy database too.

I've tried using this annotation but it still won't execute

@Sql(scripts = "classpath:schema.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)

I've created a separate application-test.properties on my test resources folder containg this properties values

spring.r2dbc.url=r2dbc:postgresql://localhost:5432/test
spring.r2dbc.username=postgres

logging.level.org.springframework.r2dbc=DEBUG

and have added @ActiveProfiles(profiles = "test") annotation too.

Answer 1

I had the same problem and ended up using flyway like so:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <scope>test</scope>
</dependency>

And then adding the appropriate configuration

@DynamicPropertySource
private static void configureProperties(DynamicPropertyRegistry registry){
    registry.add("spring.r2dbc.url",() -> String.format("r2dbc:pool:postgresql://%s:%d/%s",postgres.getHost(),postgres.getFirstMappedPort(),postgres.getDatabaseName()));
    registry.add("spring.r2dbc.username",postgres::getUsername);
    registry.add("spring.r2dbc.password",postgres::getPassword);
    registry.add("spring.flyway.url",postgres::getJdbcUrl);
    registry.add("spring.flyway.user",postgres::getUsername);
    registry.add("spring.flyway.password",postgres::getPassword);
}

That allowed me to define my scripts inside src/test/resources/db/migration

Hope it helps.

Answer 2

I came here looking for an answer for this exactly problem. Created a TestDBInitializer bean loading a test-data.sql file but it's not working even using @Profile annotation. A cleaner, althought not ideal, solution I found is using @Import({ MyTestDbInitializer.class }) like so:

@DataR2dbcTest
@Import({ TestDBInitializerConfig.class })
class R2dbcTest {
    
    @Autowired
    private MyRepository repository;

    @Test
    void testSomething() {
        Flux<MyEntity> fluxResult = this.repository.findAll();
        
        StepVerifier.create(fluxResult)
            // do things...
            .thenConsumeWhile(x -> true)
            .expectComplete()
            .log()
            .verify();
    }
}

@Configuration
@Profile("test")
public class MyTestDbInitializer {

    @Bean
    public ConnectionFactoryInitializer testProfileInitializer(ConnectionFactory connectionFactory) {

        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);

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

        return initializer;
    }
}

Still looking for an answer that doesn't need any manual initialization nor the usage of @Import annotation.

Answer 3

In case you want to reuse the existing src/main/resources/schema.sql, you can get it this way (in Kotlin):

@TestInstance(TestInstance.Lifecycle.PER_CLASS)
@DataR2dbcTest
class YourRepositoryTest @Autowired constructor(
    private val yourRepository: YourRepository,
    private val r2dbcEntityTemplate: R2dbcEntityTemplate
) {

    @BeforeAll
    fun setup() {
        val schema = StreamUtils.copyToString(
            ClassPathResource("schema.sql").inputStream,
            Charset.defaultCharset()
        )
        r2dbcEntityTemplate.databaseClient.sql(schema).fetch().rowsUpdated().block()
    }

Also, using @DataR2dbcTest already provides an R2dbcEntityTemplate bean, so you can inject it in your tests. No need to instantiate it yourself, especially on each test.

Answer 4

I've found this to be the easiest way to prepare your database test environment

  private void initializeDatabase() {
    ConnectionFactory connectionFactory = ConnectionFactories.get(dbUrl);
    R2dbcEntityTemplate template = new R2dbcEntityTemplate(connectionFactory);
    String query = "CREATE TABLE IF NOT EXISTS member (id SERIAL PRIMARY KEY, name TEXT NOT NULL);";
    template.getDatabaseClient().sql(query).fetch().rowsUpdated().block();
  }

and call this method on a @Before annotation to be run on the tests

for example, this is what I end up with

  @BeforeEach
  public void setup() {
    initializeDatabase();
    insertData();
  }

  private void initializeDatabase() {
    ConnectionFactory connectionFactory = ConnectionFactories.get(dbUrl);
    R2dbcEntityTemplate template = new R2dbcEntityTemplate(connectionFactory);
    String query = "CREATE TABLE IF NOT EXISTS member (id SERIAL PRIMARY KEY, name TEXT NOT NULL);";
    template.getDatabaseClient().sql(query).fetch().rowsUpdated().block();
  }

  private void insertData() {
    Flux<Member> memberFlux = Flux.just(
        Member.builder().name("Andrew").build(),
        Member.builder().name("Bob").build(),
        Member.builder().name("Charlie").build(),
        Member.builder().name("Dave").build()
    );
    memberRepository.deleteAll()
        .thenMany(memberFlux)
        .flatMap(memberRepository::save)
        .doOnNext(member -> log.info("inserted {}", member))
        .blockLast();
  }