R2DBC v7.2.0

On this page

R2DBC

1 Introduction

This module provides integration between Micronaut and R2DBC.

2 Release History

For this project, you can find a list of releases (with release notes) here:

https://github.com/micronaut-projects/micronaut-r2dbc/releases

3 Quick Start

The quickest way to get started is to create a new Micronaut application with Micronaut Launch and choose the data-r2dbc, mysql and flyway features. This can also be done via the Micronaut 2.2 and above CLI:

Creating an application with the CLI

# For Maven add: --build maven
$ mn create-app --lang java example --features data-r2dbc,flyway,mysql

Or via curl:

Creating an application with curl

# For Maven add to the URL: &build=maven
$ curl https://launch.micronaut.io/demo.zip?lang=java&features=data-r2dbc,flyway,mysql -o demo.zip && unzip demo.zip -d demo && cd demo

The generated application will use MySQL since we passed the mysql feature adding dependency on the R2DBC driver for MySQL:

runtimeOnly("dev.miku:r2dbc-mysql")

<dependency>
    <groupId>dev.miku</groupId>
    <artifactId>r2dbc-mysql</artifactId>
    <scope>runtime</scope>
</dependency>

And for flyway the JDBC driver:

runtimeOnly("mysql:mysql-connector-java")

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

Tip	To create configurations for other drivers you can select the appropriate feature: `oracle`, `postgres`, `sqlserver`, `h2` or `mariadb`.

Now define a SQL script that creates your initial schema in src/main/resources/db/migration. For example:

Example V1__create-schema.sql

CREATE TABLE book(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), pages INT, author_id BIGINT NOT NULL);
CREATE TABLE author(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));

You can now configure your application to connect to the database using src/main/resources/application.yml which contains the application configuration:

Example application.yml

flyway: # 
  datasources:
    default:
      enabled: true
datasources:
  default: # 
    url: ${JDBC_URL:`jdbc:mysql://localhost:3306/mydatabase`}
r2dbc:
  datasources:
    default: # 
      url: r2dbc:mysql:///mydatabase
      host: ${R2DBC_HOST:localhost} # 
      port: ${R2DBC_PORT:3306}

The Flyway configuration ensures the schema migration is applied. See Micronaut Flyway for more information.
The Flyway configuration needs a JDBC datasource configured, this setting configures one and lets you override it with JDBC_URL for container deployments. See Micronaut JDBC for more information.
The property r2dbc.datasources.default.url is used to configure the default R2DBC ConnectionFactory.
Override R2DBC_HOST and R2DBC_PORT when the application runs in Docker or another container so the app connects to the database service instead of the container’s own localhost.

Tip	The R2DBC `ConnectionFactory` object can be injected anywhere in your code with dependency injection.

Now define a @MappedEntity that maps to the author table defined in the schema:

imports

package example;

import io.micronaut.data.annotation.*;
import io.micronaut.serde.annotation.Serdeable;

@Serdeable
@MappedEntity
public class Author {
    @GeneratedValue
    @Id
    private Long id;
    private final String name;

    public Author(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

imports

from dataclasses import dataclass
from typing import Annotated

from micronaut.data.annotation import GeneratedValue, Id, MappedEntity
from micronaut.serde.annotation import Serdeable

@Serdeable
@MappedEntity
@dataclass
class Author:
    name: str
    id: Annotated[int | None, Id, GeneratedValue] = None

imports

package example

import io.micronaut.data.annotation.GeneratedValue
import io.micronaut.data.annotation.Id
import io.micronaut.data.annotation.MappedEntity
import io.micronaut.serde.annotation.Serdeable

@Serdeable
@MappedEntity
data class Author(val name: String) {
    @GeneratedValue
    @Id
    var id: Long? = null
}

imports

package example

import io.micronaut.data.annotation.*
import io.micronaut.serde.annotation.Serdeable

@Serdeable
@MappedEntity
class Author {
    @GeneratedValue
    @Id
    Long id
    final String name

    Author(String name) {
        this.name = name
    }
}

And a repository interface to access the database that extends from ReactiveStreamsRepository:

imports

package example;

import org.jspecify.annotations.NonNull;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.r2dbc.annotation.R2dbcRepository;
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import jakarta.validation.constraints.NotNull;

@R2dbcRepository(dialect = Dialect.MYSQL) // 
public interface AuthorRepository extends ReactiveStreamsCrudRepository<Author, Long> {
    @NonNull
    @Override
    Mono<Author> findById(@NonNull @NotNull Long aLong); // 

    @NonNull
    @Override
    Flux<Author> findAll();
}

imports

from micronaut.data.model.query.builder.sql import Dialect
from micronaut.data.r2dbc.annotation import R2dbcRepository
from micronaut.data.repository.reactive import ReactiveStreamsCrudRepository
from reactor.core.publisher import Flux, Mono

from example.Author import Author

@R2dbcRepository(dialect=Dialect.MYSQL)  # 
class AuthorRepository(ReactiveStreamsCrudRepository[Author, int]):

    def findById(self, id: int) -> Mono[Author]: ...  # 

    def findAll(self) -> Flux[Author]: ...

imports

package example

import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.r2dbc.annotation.R2dbcRepository
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono
import jakarta.validation.constraints.NotNull

@R2dbcRepository(dialect = Dialect.MYSQL) // 
interface AuthorRepository : ReactiveStreamsCrudRepository<Author, Long> {
    override fun findById(id: @NotNull Long): Mono<Author> // 
    override fun findAll(): Flux<Author>
}

imports

package example

import org.jspecify.annotations.NonNull
import io.micronaut.data.model.query.builder.sql.Dialect
import io.micronaut.data.r2dbc.annotation.R2dbcRepository
import io.micronaut.data.repository.reactive.ReactiveStreamsCrudRepository
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono

import jakarta.validation.constraints.NotNull

@R2dbcRepository(dialect = Dialect.MYSQL) // 
interface AuthorRepository extends ReactiveStreamsCrudRepository<Author, Long> {
    @NonNull
    @Override
    Mono<Author> findById(@NonNull @NotNull Long aLong) // 

    @NonNull
    @Override
    Flux<Author> findAll()
}

The @R2dbcRepository annotation can be used to specify the datasource and dialect
You can override methods from the super interface to specialize the default Publisher return type with a concrete implementation

You can now inject this interface into controllers and use it to perform R2DBC queries:

imports

package example;

import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@Controller("/authors")
public class AuthorController {
    private final AuthorRepository repository;

    public AuthorController(AuthorRepository repository) {
        this.repository = repository;
    }

    @Get
    Flux<Author> all() { // 
        return repository.findAll();
    }

    @Get("/id")
    Mono<Author> get(Long id) { // 
        return repository.findById(id);
    }
}

imports

from micronaut.http.annotation import Controller, Get
from reactor.core.publisher import Flux, Mono

from example.Author import Author
from example.AuthorRepository import AuthorRepository

@Controller("/authors")
class AuthorController:

    def __init__(self, repository: AuthorRepository):
        self.repository = repository

    @Get
    def all(self) -> Flux[Author]:  # 
        return self.repository.findAll()

    @Get("/id")
    def get(self, id: int) -> Mono[Author]:  # 
        return self.repository.findById(id)

imports

package example

import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono

@Controller("/authors")
class AuthorController(private val repository: AuthorRepository) {
    @Get
    fun all(): Flux<Author> { // 
        return repository.findAll()
    }

    @Get("/id")
    fun get(id: Long): Mono<Author> { // 
        return repository.findById(id)
    }
}

imports

package example

import io.micronaut.http.annotation.Controller
import io.micronaut.http.annotation.Get
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono

@Controller("/authors")
class AuthorController {
    private final AuthorRepository repository

    AuthorController(AuthorRepository repository) {
        this.repository = repository
    }

    @Get
    Flux<Author> all() { // 
        return repository.findAll()
    }

    @Get("/id")
    Mono<Author> get(Long id) { // 
        return repository.findById(id)
    }
}

By returning a reactive type that emits many items you can stream data (either Flowable or Flux)
By returning a reactive type that emits a single item you return the entire response (either Single or Mono)

4 Available Drivers

The following drivers are available as of this writing.

H2

R2DBC Driver:

runtimeOnly("io.r2dbc:r2dbc-h2")

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

And for Flyway migrations the JDBC driver:

runtimeOnly("com.h2database:h2")

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

MySQL

R2DBC Driver:

runtimeOnly("dev.miku:r2dbc-mysql")

<dependency>
    <groupId>dev.miku</groupId>
    <artifactId>r2dbc-mysql</artifactId>
    <scope>runtime</scope>
</dependency>

And for Flyway migrations the JDBC driver:

runtimeOnly("mysql:mysql-connector-java")

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

MariaDB

R2DBC Driver:

runtimeOnly("org.mariadb:r2dbc-mariadb")

<dependency>
    <groupId>org.mariadb</groupId>
    <artifactId>r2dbc-mariadb</artifactId>
    <scope>runtime</scope>
</dependency>

And for Flyway migrations the JDBC driver:

runtimeOnly("org.mariadb.jdbc:mariadb-java-client")

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <scope>runtime</scope>
</dependency>

Postgresql

R2DBC Driver:

runtimeOnly("org.postgresql:r2dbc-postgresql")

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>r2dbc-postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Note

For GraalVM native images, use a recent org.postgresql:r2dbc-postgresql version. Older 0.9.x releases do not include the native-image reflection metadata required by the PostgreSQL codec layer and can fail at runtime with errors about array types such as Instant[]. The Micronaut R2DBC BOM manages a compatible version for you, but if you override the driver version manually, use 1.0.2.RELEASE or newer.

And for Flyway migrations the JDBC driver:

runtimeOnly("org.postgresql:postgresql")

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Google Cloud SQL

To use PostgreSQL through the Google Cloud SQL R2DBC connector, add the Cloud SQL connector dependency alongside the PostgreSQL R2DBC driver:

runtimeOnly("com.google.cloud.sql:cloud-sql-connector-r2dbc-postgres")

<dependency>
    <groupId>com.google.cloud.sql</groupId>
    <artifactId>cloud-sql-connector-r2dbc-postgres</artifactId>
    <scope>runtime</scope>
</dependency>

Use a full Cloud SQL URL for the datasource and provide credentials separately (for example via environment variables or secret injection):

r2dbc:
  datasources:
    default:
      url: r2dbc:gcp:postgres://<CLOUD_SQL_CONNECTION_NAME>/<DATABASE_NAME>
      username: ${DB_USER}
      password: ${DB_PASS}

If you also want R2DBC pooling, compose the delegated driver layers in the URL instead of trying to split gcp and pool across separate driver and options.driver properties:

r2dbc:
  datasources:
    default:
      url: r2dbc:pool:gcp:postgres://<CLOUD_SQL_CONNECTION_NAME>/<DATABASE_NAME>
      username: ${DB_USER}
      password: ${DB_PASS}

The protocol segment accepts both postgres and postgresql.

SQL Server

R2DBC Driver:

runtimeOnly("io.r2dbc:r2dbc-mssql")

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

And for Flyway migrations the JDBC driver:

runtimeOnly("com.microsoft.sqlserver:mssql-jdbc")

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

5 Micronaut Data R2DBC

Micronaut Data builds on this module and provides support for Reactive repositories. See the documentation on Micronaut Data R2DBC for more information and the Quick Start section for a demonstration on how to create an application.

6 Repository

You can find the source code of this project in this repository:

https://github.com/micronaut-projects/micronaut-r2dbc