R2DBC Reactive Database Example in Spring

Published
Updated

This article will guide you on how to configure and use a relational database reactively using Spring Framework 5.3 and the R2DBC library. For your convenience, I have also included details on how to configure R2DBC for MySQL and PostgreSQL.

Introduction

Reactive design patterns have rapidly increased in popularity due to the increased scalability, elasticity, and responsiveness they provide. Until recently, relational database interaction has largely remained unchanged and reliant on synchronous queries. With the establishment of the R2DBC specification, database drivers can be developed in a unified standard (as seen with JDBC) and built on a non-blocking network layer, providing all of the expected benefits of a reactive data source.

With the release of Spring Framework 5.3, R2DBC core has been migrated out of the Spring Data package and up a level into the r2dbc sub-package. If you try to use any components in the *org.springframework.data.r2dbc.**, you will notice the deprecations.

With this 5.3 milestone comes a large number of features and improved integration with other parts of the Spring ecosystem. Most notably, ConnectionFactory implementations, schema initializations, and integration with transaction management constructs such as the @Transactional annotation.

Driver Support

As of December 2020, the following databases have implemented R2DBC drivers:

Maven Configuration

These demos rely on Spring Boot 2.4 and the the R2DBC starter.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.codetinkering.example</groupId>
    <artifactId>r2dbc-example</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>

        <!-- Comment the following if using a db other than h2 -->
        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Uncomment the following to use PostgreSQL: -->
        <!--
        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-postgresql</artifactId>
            <version>0.8.6.RELEASE</version>
        </dependency>
        -->

        <!-- Uncomment the following to use MySQL: -->
        <!--
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
            <version>0.8.2.RELEASE</version>
        </dependency>
        -->

    </dependencies>

    <properties>
        <java.version>15</java.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Creating a ReactiveCrudRepository

import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;

public interface VehicleRepository extends ReactiveCrudRepository<Vehicle, Integer> {
    
    @Query("SELECT * FROM vehicle WHERE make = :make")
    Flux<Vehicle> findByMake(String make);

    @Query("SELECT * FROM vehicle WHERE model = :model")
    Flux<Vehicle> findByModel(String model);
}

Reactive Repository Examples

package com.codetinkering.example;

import io.r2dbc.spi.ConnectionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.ClassPathResource;
import org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer;
import org.springframework.r2dbc.connection.init.ResourceDatabasePopulator;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.time.Duration;
import java.util.Arrays;
import java.util.List;

@SpringBootApplication
public class R2dbcExampleApplication {

    private static final Logger logger = LoggerFactory.getLogger(R2dbcExampleApplication.class);

    @Bean()
    ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {

        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);
        // This will create our database table and schema
        initializer.setDatabasePopulator(new ResourceDatabasePopulator(new ClassPathResource("dbsetup.sql")));
        // This will drop our table after we are done so we can have a fresh start next run
        initializer.setDatabaseCleaner(new ResourceDatabasePopulator(new ClassPathResource("cleanup.sql")));
        return initializer;
    }

    @Bean
    public CommandLineRunner reactiveDatabaseExample(VehicleRepository vehicleRepository) {
        return args -> {

            List<Vehicle> vehicles = Arrays.asList(
                new Vehicle("Ford", "Mustang", "Red"),
                new Vehicle("Ford", "Bronco", "Orange"),
                new Vehicle("Chevy", "Silverado", "Blue"),
                new Vehicle("Chevy", "Tahoe", "Black"),
                new Vehicle("Toyota", "Supra", "Green")
            );
            // Create some vehicles and insert them into the database, blocking for up to 5 seconds
            vehicleRepository.saveAll(vehicles).blockLast(Duration.ofSeconds(5));

            // Find one or more vehicles from the repository using a query
            vehicleRepository.findByMake("Chevy").doOnNext(vehicle -> {
                logger.info(vehicle.toString());
            }).blockLast(Duration.ofSeconds(5));

            // Find a single vehicle by an ID, returns a Mono
            vehicleRepository.findById(1).doOnNext(vehicle -> {
                logger.info(vehicle.toString());
            }).block(Duration.ofSeconds(5));

            // Print all vehicles from the repository
            vehicleRepository.findAll().doOnNext(vehicle -> {
                logger.info(vehicle.toString());
            }).blockLast(Duration.ofSeconds(5));

            // Print all vehicles that match the provided make "Ford"
            // block the thread until the mono is completed or the request times out (5 seconds)
            Mono<List<Vehicle>> vehicleListMono = vehicleRepository.findByMake("Ford").collectList();
            List<Vehicle> vehicleList = vehicleListMono.block(Duration.ofSeconds(5));

            for (Vehicle v : vehicleList) {
                logger.info(v.toString());
            }
        };
    }

    public static void main(String[] args) {
        SpringApplication.run(R2dbcExampleApplication.class, args);
    }
}

Running the Example

Start your project via Maven using the following:

mvn spring-boot:run
 Starting R2dbcExampleApplication using Java 15.0.1 on ... with ...
 No active profile set, falling back to default profiles: default
 Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
 Finished Spring Data repository scanning in 32 ms. Found 1 R2DBC repository interfaces.
 Started R2dbcExampleApplication in 0.888 seconds (JVM running for 1.266)
 ...

R2DBC MySQL Connection

Here’s how to configure your MySQL Database as a datasource for Spring R2DBC (instead of H2):

Step 1: Remove H2 Dependencies

Remove or comment out the h2 dependency:

<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-h2</artifactId>
    <scope>runtime</scope>
</dependency>

Step 2: Add MySQL R2DBC Maven dependencies

Include the Spring MySQL database connector and the Miku r2dbc MySQL connector within your pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>dev.miku</groupId>
    <artifactId>r2dbc-mysql</artifactId>
    <version>0.8.2.RELEASE</version>
</dependency>

Step 3: Configure your application.properties yml file

The following is configured for a local MySQL instance which we will start in the next step via Docker Compose.

# MySQL
spring.r2dbc.url=r2dbc:mysql://localhost:3306/db
spring.r2dbc.username=user
spring.r2dbc.password=pass
spring.r2dbc.initialization-mode=always

Step 4:

Start the MySQL docker compose image by navigating to the project directory and running the following in your terminal:

cd docker-compose-files/mysql
docker-compose up

R2DBC PostgreSQL Connection

Here’s how to configure your PostgreSQL as a datasource for Spring R2DBC (instead of H2):

Step 1: Remove H2 Dependencies

Remove or comment out the h2 dependency:

<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-h2</artifactId>
    <scope>runtime</scope>
</dependency>

Step 2: Add PostgreSQL R2DBC Maven dependencies

Include the r2dbc PostgreSQL database connector within your pom.xml file:

<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-postgresql</artifactId>
    <version>0.8.6.RELEASE</version>
</dependency>

Step 3: Configure your application.properties yml file

The following is configured for a local PostgreSQL instance which we will start in the next step via Docker Compose.

# PostgreSQL
spring.r2dbc.url=r2dbc:postgresql://localhost:5432/postgres
spring.r2dbc.username=postgres
spring.r2dbc.password=example
spring.r2dbc.initialization-mode=always

Step 4:

Start the PostgreSQL docker compose image by navigating to the project directory and running the following in your terminal:

cd docker-compose-files/postgresql
docker-compose up

Common Issues

 Caused By: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42P07] relation already exists

or

org.springframework.r2dbc.connection.init.ScriptStatementFailedException: Failed to execute SQL script statement
...
io.r2dbc.spi.R2dbcBadGrammarException: [1050] [42S01] Table already exists

Your table already exists. Try deleting the table from your Postgres database or removing the ConnectionFactoryInitializer bean from your application code so that the table isn’t created each time upon startup. I have included a database cleaner in the ResourceDatabasePopulator for your convenience so that the table is automatically dropped after each run.

Checkout this project from Github

git clone https://github.com/code-tinkering/r2dbc-example
Download Zip