initialize database for testing purpose on Spring Data R2DBC

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.

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