R2DBC - Reactive Relational Database Connectivity

R2DBC brings a reactive programming API to relational data stores. The Introduction contains more details about its origins and explains its goals.

This document describes the first and initial generation of R2DBC.

This document is organized into the following parts:

The R2DBC SPI provides reactive programmatic access to relational databases from Java and other JVM-based programming languages.

R2DBC specifies a service-provider interface (SPI) that is intended to be implemented by driver vendors and used by client libraries. By using the R2DBC SPI, applications written in a JVM programming language can run SQL statements and retrieve results by using an underlying data source. You can also use the R2DBC SPI to interact with multiple data sources in a distributed, heterogeneous environment. R2DBC targets primarily, but is not limited to, relational databases. It aims for a range of data sources whose query and statement interface is based on SQL (or an SQL-like dialect) and that represent their data in a tabular form.

A key difference between R2DBC and imperative data access SPIs is the deferred nature of execution. R2DBC is, therefore, based on Reactive Streams and uses the concepts of Publisher and Subscriber to allow non-blocking backpressure-aware data access.

This specification is targeted primarily towards:

This specification is also intended to offer:

The R2DBC specification work is being conducted as an effort of individuals that recognized the demand for a reactive, standardized API for relational database access. We want to thank all contributing members for their countless hours of work and discussion.

Thanks also go to Ollie Drotbohm, without whom this initiative would not even exist.

For information on R2DBC source code repositories, nightly builds, and snapshot artifacts, see the R2DBC homepage. You can help make R2DBC best serve the needs of the community by interacting with developers through the community. To follow developer activity, look for the mailing list information on the R2DBC homepage. If you encounter a bug or want to suggest an improvement, please create a ticket on the R2DBC issue tracker. R2DBC has an open-source organization on GitHub that bundles the various projects (SPI and drivers) under R2DBC.

To stay up to date with the latest news and announcements in the R2DBC eco system, you can subscribe to the mailing list. You can also follow the project team on Twitter (@R2DBC).

The R2DBC specification aims for establishing an interface that has a minimal API surface to integrate with relational databases by using a reactive programming model. The most significant goals are honoring and embracing the properties of reactive programming, including the following:

R2DBC aims for seamless integration of reactive JVM platforms, targeting Java as its primary platform. R2DBC should also be usable from other platforms (such as Kotlin or Scala) without scarifying its SPI for the sake of idiomatic use on a different platform.

R2DBC SPI strives to provide access to features that are commonly found across different vendor implementations. The goal here is to provide a balance between features that are implemented in a driver and these that are better implemented in a client library.

Each database comes with its own feature set and how these are implemented. R2DBC’s goal is to define a minimal standard over commonly used functionality and allow for vendor-specific deviation. Drivers can implement additional functionality or make these transparent through the R2DBC SPI.

The focus of R2DBC is on accessing relational data from the Java programming language by using databases that provide an SQL interface with which to interact.

The goal here is not to limit implementations to relational-only databases. Instead, the goal is to provide guidance for uniform reactive data access by using tabular data consumption patterns.

R2DBC does not aim to be a general-purpose data-access API.

R2DBC specializes in reactive data access and common usage patterns that result from relational data interaction. R2DBC does not aim to abstract common functionality that needs to be re-implemented by driver vendors in a similar manner. It aims to leave this functionality to client libraries, of which there are typically fewer implementations than drivers.

The R2DBC SPI aims for being primarily consumed though client library implementations.

It does not aim to be an end-user or application developer programming interface.

Having a uniform reactive relational data access SPI makes R2DBC a valuable target platform for tool vendors and application developers who want to create portable tools and applications.

The requirements for R2DBC compliance should be unambiguous and easy to identify. The R2DBC specification and the API documentation (Javadoc) clarify which features are required and which are optional.

To avoid ambiguity, we will use the following terms in the compliance section and across this specification:

The following guidelines apply to R2DBC compliance:

A driver that is compliant with the R2DBC specification must do the following:

A driver can implement optional Extensions if it is able to provide extension functionality specified by R2DBC.

R2DBC uses the Connection interface to define a logical connection API to the underlying data source. The structure of a connection depends on the actual requirements of a data source and how the driver implements these.

In a typical scenario, an application that uses R2DBC connects to a target data source byusing one of two mechanisms:

Once a connection has been established, an application using the R2DBC SPI can execute queries and updates against the connected database. The R2DBC SPI provides a text-based command interface to the most commonly used features of SQL databases. R2DBC driver implementations may expose additional functionality in a non-standard way.

Applications use methods in the Connection interface to specify transaction attributes and create Statement or Batch objects. These statements are used to execute SQL and retrieve results and allow for binding values to parameter bind markers. The Result interface encapsulates the results of an SQL query. Statements may also be batched, allowing an application to submit multiple commands to a database as a single unit of execution.

R2DBC drivers must implement the ConnectionFactory interface as a mandatory part of the SPI. Drivers can provide multiple ConnectionFactory implementations, depending on the protocol in use or aspects that require the use of a different ConnectionFactory implementation. The following listing shows the ConnectionFactory interface:

The following rules apply:

As part of its usage, the ConnectionFactories class tries to load any R2DBC driver classes referenced by the ConnectionFactoryProvider interface listed in the Java Service Provider manifests that are available on the classpath.

Drivers must include a file called META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider. This file contains the name of the R2DBC driver’s implementation (or implementations) of io.r2dbc.spi.ConnectionFactoryProvider. To ensure that drivers can be loaded by using this mechanism, io.r2dbc.spi.ConnectionFactoryProvider implementations are required to provide a no-argument constructor. The following listing shows a typical META-INF/services/io.r2dbc.spi.ConnectionFactoryProvider file:

The following listing shows the ConnectionFactoryProvider interface:

ConnectionFactories uses a ConnectionFactoryOptions object to look up a matching driver by using a two-step model:

ConnectionFactoryProvider implementations are required to return a boolean indicator whether or not they support a specific configuration represented by ConnectionFactoryOptions. Drivers must expect any plurality of Option instances to be configured. Drivers must report that they support a configuration only if the ConnectionFactoryProvider can provide a ConnectionFactory based on the given ConnectionFactoryOptions. A typical task handled by supports is checking driver and protocol options. Drivers should gracefully fail if a ConnectionFactory creation through ConnectionFactoryProvider.create(…) is not possible (i.e. when required options were left unconfigured). The getDriver() method reports the driver identifier that is associated with the ConnectionFactoryProvider implementation to provide diagnostic information to users in misconfiguration cases.

See the R2DBC SPI Specification and ConnectionFactory Discovery for more details.

The ConnectionFactoryOptions class represents a configuration for a request a ConnectionFactory from a ConnectionFactoryProvider. It enables the programmatic connection creation approach without using driver-specific classes. ConnectionFactoryOptions instances are created by using the builder pattern, and properties are configured through Option<T> identifiers. A ConnectionFactoryOptions is immutable once created. Option objects are reused as part of the built-in constant pool. Options are identified by a literal.

ConnectionFactoryOptions defines a set of well-known options:

The following rules apply:

The following example shows how to set options for a ConnectionFactoryOptions:

See the R2DBC SPI Specification for more details.

Once a ConnectionFactory is bootstrapped, connections are obtained from the create() method. The following example shows how to obtain a connection:

The connection is active once it has been emitted by the Publisher and must be released (“closed”) once it is no longer in use.

Connections are required to expose metadata about the database they are connected to. Connection Metadata is typically discovered dynamically based from information obtained during Connection initialization.

See the R2DBC SPI Specification for more details.

The Connection.validate(…) method indicates whether the Connection is still valid. The ValidationDepth argument passed to this method indicates the depth to which a connection should be validated: LOCAL or REMOTE.

If Connection.validate(…) emits true, the Connection is still valid. If Connection.validate(…) emits false, the Connection is not valid, and any attempt to perform database interaction fails. Callers of this method do not expect error signals.

Calling Connection.close() prepares a close handle to release the connection and its associated resources. Connections must be closed to ensure proper resource management. You can use Connection.validate(…) to determine whether a Connection has been closed or is still valid. The following example shows how to close a connection:

See the R2DBC SPI Specification for more details.

You can implicitly or explicitly start transactions. You can implicitly start a transaction by starting SQL execution when a Connection is in auto-commit mode (which is the default for newly created connections). When auto-commit mode is disabled, you can explicitly start a transaction by invoking the beginTransaction() method. Transactions are started by either an R2DBC driver or by the underlying database.

The Connection attribute auto-commit mode specifies when to end transactions. Enabling auto-commit mode causes a transaction commit after each SQL statement as soon as that statement is completely executed.

[[transactions.auto-commit] == Auto-commit Mode

A ConnectionFactory creates new Connection objects with auto-commit mode enabled. The Connection interface provides two methods to interact with auto-commit mode:

R2DBC applications should change auto-commit mode by invoking the setAutoCommit method instead of executing SQL commands to change the connection configuration. If the value of auto-commit is changed during an active transaction, the current transaction is committed. If setAutoCommit is called and the value for auto-commit is not changed from its current value, this is treated as a no-op.

Changing auto-commit mode typically engages database activity. Therefore, the method returns a Publisher. Querying auto-commit mode is typically a local operation that involves driver state without database communication.

When auto-commit is disabled, you must explicitly start and clean up each transaction by calling the Connection methods beginTransaction and commitTransaction (or rollbackTransaction), respectively.

This is appropriate for cases where transaction management is being done in a layer above the driver, such as:

[[transactions.isolation] == Transaction Isolation

Transaction isolation levels define the level of visibility (“isolation”) for statements that are run within a transaction. They impact concurrent access while multiple transactions are active.

The default transaction level for a Connection object is vendor-specific and determined by the driver that supplied the connection. Typically, it defaults to the transaction level supported by the underlying data source.

The Connection interface provides two methods to interact with transaction isolation levels:

R2DBC applications should change transaction isolation levels by invoking the setTransactionIsolationLevel method instead of running SQL commands to change the connection configuration.

Changing transaction isolation levels typically involves database activity. Therefore, the method returns a Publisher. Changing an isolation level during an active transaction results in implementation-specific behavior. Querying transaction isolation levels is typically a local operation that involves driver state without database communication. The return value of the getTransactionIsolationLevel method should reflect the current isolation level when it actually occurs. IsolationLevel is an extensible runtime constant, so drivers can define their own isolation levels. A driver may not support transaction levels. Calling getTransactionIsolationLevel results in returning the vendor-specific IsolationLevel object.

Savepoints provide a fine-grained control mechanism by marking intermediate points within a transaction. Once a savepoint has been created, a transaction can be rolled back to that savepoint without affecting preceding work.

The Statement interface defines methods for running SQL statements. SQL statements may contain parameter bind markers for input parameters.

The SQL that is used to create a statement can be parameterized by using vendor-specific bind markers. The portability of SQL statements across R2DBC implementations is not a goal.

Parameterized Statement objects are created by Connection objects in the same manner as non-parameterized Statements. See the the following example:

Parameter bind markers are identified by the Statement object. Parameterized statements may be cached by R2DBC implementations for reuse (for example, for prepared statement execution).

Many database systems provide a mechanism that automatically generates a value when a row is inserted. The value that is generated may or may not be unique or represent a key value, depending on the SQL and the table definition. You can call the returnGeneratedValues method to retrieve the generated value. It tells the Statement object to retrieve generated values. The method accepts a variable-argument parameter to specify the column names for which to return generated keys. The emitted Result exposes a column for each automatically generated value (taking the column name hint into account). The following example shows how to retrieve auto-generated values:

When column names are not specified, the R2DBC driver implementation determines the columns or value to return.

See the R2DBC SPI Specification for more details.

The Statement interface provides a method that you can use to provide hints to a R2DBC driver. Calling fetchSize applies a fetch-size hint to each query produced by the statement. Hints provided to the driver through this interface may be ignored by the driver if they are not appropriate or supported. Typically, fetch size can be derived from back-pressure hints. To optimize for performance, it can be useful to provide hints to the driver on a per-statement basis.

The Batch interface defines methods for running groups of SQL statements. SQL statements may not contain parameter bind markers for input parameters. A batch is created to run multiple SQL statements for performance reasons.

Result objects are forward-only and read-only objects that allow consumption of two result types:

Results move forward from the first Row to the last one. After emitting the last row, a Result object gets invalidated and rows from the same Result object can no longer be consumed. Rows contained in the result depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is run or as the rows are retrieved. An R2DBC driver can obtain a Result either directly or by using cursors.

Result reports the number of rows affected for SQL statements, such as updates for SQL Data Manipulation Language (DML) statements. The update count can be empty for statements that do not modify rows. After emitting the update count, a Result object gets invalidated and rows from the same Result object can no longer be consumed. The following example shows how to get a count of updated rows:

The streaming nature of a result allows consumption of either tabular results or an update count. Depending on how the underlying database materializes results, an R2DBC driver can lift this limitation.

A Result object is emitted for each statement result in a forward-only direction.

A Result object is most often created as the result of running a Statement object. The Statement.execute() method returns a Publisher that emits a Result objects as the result of running the statement. The following example shows how to create a Result object:

The Result object emits a Row object for each row in the books table (which contains two columns: title and author). The following sections detail how these rows and columns can be consumed.

A Row object represents a single row of tabular results.

The following methods are available on the Row interface:

get(int[, Class]) methods accept column indexes starting at 0, get(String[, Class]) methods accept column name aliases as they are represented in the result. Column names used as input to the get methods are case insensitive. Column names do not necessarily reflect the column names as they are in the underlying tables but, rather, how columns are represented (for example, aliased) in the result. The following example shows how to create and consume a Row by using its index:

The following example shows how to create and consume a Row by using its column name:

Calling get without specifying a target type returns a suitable value representation according to Mapping of Data Types. When you specify a target type, the R2DBC driver tries to convert the value to the target type. The following example shows how to creat and consume a Row with type conversion:

You can also consume multiple columns from a Row, as the following example shows:

When the column value in the database is SQL NULL, it can be returned to the Java application as null.

A RowMetadata object is created during tabular results consumption through Result.map(…). It is created for each row. The following example illustrates retrieval and usage by using an anonymous inner class:

RowMetadata methods are used to retrieve metadata for a single column or all columns.

ColumnMetadata declares methods to access column metadata on a best-effort basis. Column metadata that is available as a by-product of running a statement must be made available through ColumnMetadata. Metadata exposure requiring interaction with the database (for example, issuing queries to information schema entities to resolve type properties) should not be exposed, because methods on ColumnMetadata are expected to be non-blocking.

The following example shows how to consume ColumnMetadata by using lambdas:

See the API specification for more details.

R2DBC defines the following general exceptions:

Categorized exceptions provide a standard mapping to common error states. An R2DBC driver should provide specific subclasses to indicate affinity with the driver. Categorized exceptions provide a standardized approach for R2DBC clients and R2DBC users to translate common exceptions into an application-specific state without the need to implement SQLState-based exception translation, resulting in more portable error-handling code.

R2DBC categorizes exceptions into two top-level categories:

This section explains how SQL-specific types are mapped to Java types. The list is not exhaustive and should be received as a guideline for drivers. R2DBC drivers should use modern types and type descriptors to exchange data for consumption by applications and consumption by the driver. Driver implementations should implement the following type mapping and can support additional type mappings:

The following table describes the SQL type mapping for character types:

The following table describes the SQL type mapping for boolean types:

The following table describes the SQL type mapping for binary types:

The following table describes the SQL type mapping for numeric types:

The following table describes the SQL type mapping for datetime types:

The following table describes the SQL type mapping for collection types:

Vendor-specific types (such as spatial data types, structured JSON or XML data, and user-defined types) are subject to vendor-specific mapping.

The R2DBC SPI declares default mappings for advanced data types. The following list describes data types and the interfaces to which they map:

The Wrapped interface provides a way to access an instance of a resource which has been wrapped and for implementors to expose wrapped resources. This mechanism helps to eliminate the need to use non-standard means to access vendor-specific resources.

The io.r2dbc.spi.Closeable interface provides a mechanism for objects associated with resources to release these resources once the object is no longer in use. The associated resources are released without blocking the caller.