How to substitute @Sql in tests with Spring data r2dbc?

Question

In Spring data JPA there is an @Sql annotation which is very handy for setting up integration tests for persistence layer. It can roll out test data before each test and perform a clean up after it.

However, I could not find it in spring-data-r2dbc module. Is there anything similar to easily handle this task in spring-data-r2dbc?

Answer 1

For now I haven't found anything better than using org.springframework.data.r2dbc.connectionfactory.init.ScriptUtils#executeSqlScript(io.r2dbc.spi.Connection, org.springframework.core.io.Resource) together with JUnit @BeforeEach and @AfterEach test callbacks:

    @Autowired
    private ConnectionFactory connectionFactory;

    private void executeScriptBlocking(final Resource sqlScript) {
        Mono.from(connectionFactory.create())
                .flatMap(connection -> ScriptUtils.executeSqlScript(connection, sqlScript))
                .block();

    @BeforeEach
    private void rollOutTestData(@Value("classpath:/db/insert_test_data.sql") Resource script) {
        executeScriptBlocking(script);
    }

    @AfterEach
    private void cleanUpTestData(@Value("classpath:/db/delete_test_data.sql") Resource script) {
        executeScriptBlocking(script);
    }
    

NB: here I am using JUnit5 with jupiter API

Answer 2

If you want you can use @Sql in your integration Tests in a simple way, so you can use something you are familiar with. Even if you are using r2dbc you can add this dependencies in you pom.xml/gradle file:

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
    </dependency>

so you have both driver (blocking and non blocking version). This dependency can be useful in other situation (if you use Flyway for example - it doesn't support reactive drivers yet). Then you can create a @Configuration class that you can use in your integration tests. In case you are using postgresql in your integration test you will have something like:

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class TestConfig {

    /**
     * Necessary to run sql scripts with @Sql
     */
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl("jdbc:postgresql://localhost:5432/postgres");
        dataSource.setUsername("postgres");
        dataSource.setPassword("1234");
        return dataSource;
    }

}

To test your database your test will start with something like:

@DataR2dbcTest
@ActiveProfiles("test")
@Sql(value = "classpath:sql/MovieInfoRepositoryITest.sql")
@Import({TestConfig.class})
class MovieInfoRepositoryITest {
    ...
}

Don't be afraid to use the JDBC drive where is not "painful" for your reactive application, like executing your sql scripts during tests.