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

This is another alternative that you can try out:

@BeforeEach
fun populateTestData(@Value("classpath:test-data.sql") testDataSql: Resource, @Autowired connectionFactory: ConnectionFactory) {
    val resourceDatabasePopulator = ResourceDatabasePopulator()
    resourceDatabasePopulator.addScript(testDataSql)
    resourceDatabasePopulator.populate(connectionFactory).block()
}

Answer 3

Consider following step to use @Sql:

Step 1: Add dependency

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <scope>test</scope>
        </dependency>

Note:

1. scope for dependency spring-boot-starter-jdbc is test as we only need @Sql during JUnit test case.

Step 2: Implement org.springframework.test.context.TestExecutionListener

import static org.springframework.test.context.jdbc.Sql.ExecutionPhase.AFTER_TEST_METHOD;
import static org.springframework.test.context.jdbc.Sql.ExecutionPhase.BEFORE_TEST_METHOD;

import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.core.annotation.AnnotatedElementUtils;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;
import org.springframework.test.context.TestContext;
import org.springframework.test.context.TestExecutionListener;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.Sql.ExecutionPhase;
import org.springframework.test.context.jdbc.SqlGroup;

import io.r2dbc.spi.ConnectionFactory;
import reactor.core.publisher.Mono;

public class SqlScriptsR2dbcTestExecutionListener implements TestExecutionListener {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(SqlScriptsR2dbcTestExecutionListener.class);
    
    public void beforeTestClass(TestContext testContext) throws Exception {
    }

    public void beforeTestMethod(TestContext testContext) throws Exception {
        executeSqlScripts(testContext, BEFORE_TEST_METHOD);
    }
    
    public void afterTestMethod(TestContext testContext) throws Exception {
        executeSqlScripts(testContext, AFTER_TEST_METHOD);
    }

    /**
     * Execute SQL scripts configured via {@link Sql @Sql} for the supplied
     * {@link TestContext} and {@link ExecutionPhase}.
     */
    private void executeSqlScripts(TestContext testContext, ExecutionPhase executionPhase) throws Exception {
        boolean classLevel = false;
        Set<Sql> sqlAnnotations = AnnotatedElementUtils.getMergedRepeatableAnnotations(testContext.getTestMethod(),
                Sql.class, SqlGroup.class);
        if (sqlAnnotations.isEmpty()) {
            sqlAnnotations = AnnotatedElementUtils.getMergedRepeatableAnnotations(testContext.getTestClass(), Sql.class,
                    SqlGroup.class);
            if (!sqlAnnotations.isEmpty()) {
                classLevel = true;
            }
        }

        for (Sql sql : sqlAnnotations) {
            executeSqlScripts(sql, executionPhase, testContext, classLevel);
        }
    }

    private void executeSqlScripts(Sql sql, ExecutionPhase executionPhase, TestContext testContext,
            boolean classLevel) throws IOException {
        if (sql == null || sql.scripts() == null || sql.scripts().length < 1) {
            return ;
        }
        ApplicationContext applicationContext = testContext.getApplicationContext();
        ConnectionFactory connectionFactory = applicationContext.getBean(ConnectionFactory.class);
        List<FileSystemResource> resources = Arrays.asList(sql.scripts())
                                         .stream()
                                         .peek(path -> LOGGER.info("Script path: {}", path))
                                         .map(scriptPath -> new FileSystemResource(scriptPath))
                                         .toList();
        Resource []scriptResources = resources.toArray(new Resource[resources.size()]);
        create(connectionFactory, scriptResources);
    }
    
    private void create(ConnectionFactory connectionFactory, Resource [] resources) throws IOException {
        final StringBuilder sb = new StringBuilder();
        for (Resource resource: resources) {
            sb.append(Files.readString(Paths.get(resource.getURI()), Charset.forName("UTF-8")));
        }
        Mono.from(connectionFactory.create())
            .flatMapMany(connection -> connection.createStatement(sb.toString())
                                                 .execute())
            .subscribe();
    }
}

Note:

1. Refer following snipped code for JDBC connectivity in above code snipped:

ConnectionFactory connectionFactory = ConnectionFactories
  .get("r2dbc:h2:mem:///testdb");

Mono.from(connectionFactory.create())
  .flatMapMany(connection -> connection
    .createStatement("SELECT firstname FROM PERSON WHERE age > $1")
    .bind("$1", 42)
    .execute())
  .flatMap(result -> result
    .map((row, rowMetadata) -> row.get("firstname", String.class)))
  .doOnNext(System.out::println)
  .subscribe();

Step 3: Implementation in JUnit 5 Test case

@DataR2dbcTest
@ActiveProfiles(value = "test")
@TestExecutionListeners(value = {
  DependencyInjectionTestExecutionListener.class,
  SqlScriptsR2dbcTestExecutionListener.class
})
class SampleRepositoryTest {


    @Tags(value = { 
      @Tag(value = "r2dbc"), 
      @Tag(value = "save"), 
      @Tag(value = "findAll") 
    })
    @Sql(scripts = { 
      SCHEMA_H2_SQL 
    }, executionPhase = BEFORE_TEST_METHOD)
    @Test
    void test() {}
}

Note:

1. DependencyInjectionTestExecutionListener.class is required to make sure the @Autowired should not be null during the test case run.

Step 4: Configuration in application-test.properties

spring.r2dbc.url=r2dbc:h2:mem:///testdb?options=DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.r2dbc.username=sa
spring.r2dbc.password=sa
spring.r2dbc.initialization-mode=always

Step 5: Query constant sql scripts file

public class JpaConstant {
    
    private JpaConstant() {}
    
    public static final String CONFIG_FOLDER = "src/main/resources/config/";
    
    public static final String SCHEMA_H2_SQL = CONFIG_FOLDER + "schema-h2.sql";
}

Answer 4

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.