JDBC for Spring WebFlux: Spring Data R2DBC

Disclaimer: The project is still in experiment mode, do not use it in production.

Sample project on GitHub:
https://github.com/vxavictor513/kubenote/tree/master/r2dbc-and-flyway

Spring Data R2DBC 1.0 is the reactive/non-blocking alternative for JDBC in Spring WebFlux world, and it has recently hit milestone 2, with several nice improvements, such as:

1. MySQL support using jasync-sql
2. Url-based configuration
3. Connection pool support

However, the announcement and documentation available in the internet is rather scarce, thus I am writing this to share some tips I learnt when playing with Spring Data R2DBC.

Dependencies

spring-boot-starter-data-r2dbc

This is the primary dependency to enable Spring Data R2DBC. Don’t be confused with spring-boot-starter-r2dbc that only enables R2DBC client without Spring Data implementation.

r2bdc-pool and spring-jdbc are required for connection pooling.

<dependency>
   <groupId>org.springframework.boot.experimental</groupId>
   <artifactId>spring-boot-starter-data-r2dbc</artifactId>
   <version>0.1.0.M1</version>
</dependency>
<dependency>
   <groupId>io.r2dbc</groupId>
   <artifactId>r2dbc-pool</artifactId>
</dependency>
<dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
</dependency>

jasync-r2dbc-mysql

Add this when you’re using MySQL.

<dependency>
   <groupId>com.github.jasync-sql</groupId>
   <artifactId>jasync-r2dbc-mysql</artifactId>
   <version>0.9.41</version>
</dependency>

r2dbc-postgresql

Add this when you’re using PostgreSQL.

<dependency>
   <groupId>io.r2dbc</groupId>
   <artifactId>r2dbc-postgresql</artifactId>
</dependency>

Repository

As the dependencies are still in experiment mode, we need to add the following Maven repositories to download the dependencies.

<repositories>
   <repository>
      <id>spring-libs-milestone</id>
      <url>https://repo.spring.io/libs-milestone</url>
   </repository>
   <repository>
      <id>spring-libs-snapshot</id>
      <url>https://repo.spring.io/libs-snapshot</url>
   </repository>
</repositories>

Configuration

Spring Data R2DBC has auto configuration that does the magic to initialize all necessary beans. All we need to do is just configuring properties at application.properties.

Some documentations show examples withrdbc driver, e.g.rdbc:postgresql://127.0.0.1:5432/demo, but that is outdated.

# MySQL
spring.r2dbc.url=r2dbc:pool:mysql://127.0.0.1:3306/demo
spring.r2dbc.username=user
spring.r2dbc.password=password
spring.r2dbc.pool.initial-size=100
spring.r2dbc.pool.max-size=500
spring.r2dbc.pool.max-idle-time=30m
spring.r2dbc.pool.validation-query=SELECT 1

For PostgreSQL, use postgresql protocol.

# PostgreSQL
spring.r2dbc.url=r2dbc:pool:postgresql://127.0.0.1:5432/demo

To omit connection pooling, remove :pool.

spring.r2dbc.url=r2dbc:mysql://127.0.0.1:3306/demo

Into the Code

1. Add @EnableR2dbcRepositories in your application class.

@SpringBootApplication
@EnableR2dbcRepositories
public class R2dbcApplication {

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

}

2. Auto-wire DatabaseClient in your repository class. It is just like JdbcTemplate.

@Repository
public class PetRepository {

   @Autowired
   private DatabaseClient databaseClient;

   public Mono<Pet> getOne(String name) {
      return databaseClient
            .execute().sql("SELECT name, sound FROM pets WHERE name = :name")
            .bind("name", name)
            .map((row, rowMetadata) -> new Pet(
                  row.get("name", String.class),
                  row.get("sound", String.class)
            )).one()
            .switchIfEmpty(Mono.error(new RuntimeException("Pet not found!")));
   }
}

Or using fluent API provided by Spring Data.

@Repository
public class PetRepository {

   @Autowired
   private DatabaseClient databaseClient;

   public Mono<Pet> getOne(String name) {
      return databaseClient
            .select().from("pets")
            .matching(Criteria.where("pets").is(name))
            .as(Pet.class).fetch().one()
            .switchIfEmpty(Mono.error(new RuntimeException("Pet not found!")));
   }
}

3. You can also implement ReactiveCrudRepository interface if you prefer ORM style.

That’s it!

Do check out my sample project on GitHub where you can see the Spring Data R2DBC in action.

https://github.com/vxavictor513/kubenote/tree/master/r2dbc-and-flyway

Thanks for reading!

Extended Reading

1. https://spring.io/blog/2019/03/20/spring-tips-reactive-mysql-support-with-jasync-sql-and-r2dbc