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.

user3663027 · Answer 1 · 2021-06-26T16:30:29.760

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.

this should have been the correct answer. all other answers are just reading the sql into a string and execute as sql which technically works, but using `ConnectionFactoryInitializer` is a more appropriate approach. — Dexter Legaspi, Feb 03 '23 at 14:11

score 3 · Accepted Answer · answered Jan 09 '21 at 16:23

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();
  }

Hi @kamalhm, I get this error doing that Unsatisfied dependency expressed through field 'r2dbcEntityTemplate'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.data.r2dbc.core.R2dbcEntityTemplate' available: expected at least 1 bean which qualifies as autowire candidate Could you help me — AleGallagher, Jun 18 '21 at 20:57
Hi @AleGallagher, Take a look at this answer -> https://stackoverflow.com/questions/61692151/how-to-run-query-like-sql-for-test-in-spring-data-r2dbc/68578464#68578464 — Moncef AOUDIA, Jul 29 '21 at 16:08

score 1 · Answer 3 · answered Mar 09 '21 at 04:05

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.

initialize database for testing purpose on Spring Data R2DBC

3 Answers3