Reactive Relational Database Connectivity - YugabyteDB YSQL Implementation
This project contains the PostgreSQL implementation of the R2DBC SPI. This implementation is not intended to be used directly, but rather to be used as the backing implementation for a humane client library to delegate to.
This driver provides the following features:
- Login with username/password (MD5, SASL/SCRAM) or implicit trust
- SCRAM authentication
- TLS
- Explicit transactions
- Notifications
- Binary data transfer
- Execution of prepared statements with bindings
- Execution of batch statements without bindings
- Binary data transfer
- Read and write support for all data types except LOB types (e.g.
BLOB
,CLOB
) - Extension points to register
Codec
s to handle additional Postgres data types
Next steps:
- Multi-dimensional arrays
Code of Conduct
This project is governed by the Spring Code of Conduct. By participating, you are expected to uphold this code of conduct. Please report unacceptable behavior to spring-code-of-conduct@pivotal.io.
Getting Started
Here is a quick teaser of how to use R2DBC PostgreSQL in Java:
URL Connection Factory Discovery
ConnectionFactory connectionFactory = ConnectionFactories.get("r2dbc:postgres://<host>:5432/<database>");
Publisher<? extends Connection> connectionPublisher = connectionFactory.create();
Programmatic Connection Factory Discovery
ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
.option(DRIVER, "postgresql")
.option(HOST, "...")
.option(PORT, "...") // optional, defaults to 5432
.option(USER, "...")
.option(PASSWORD, "...")
.option(DATABASE, "...") // optional
.build());
Publisher<? extends Connection> connectionPublisher = connectionFactory.create();
// Alternative: Creating a Mono using Project Reactor
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
Supported ConnectionFactory Discovery Options
Option | Description |
---|---|
ssl |
Enables SSL usage (SSLMode.VERIFY_FULL ) |
driver |
Must be postgresql . |
host |
Server hostname to connect to |
port |
Server port to connect to. Defaults to 5432 . (Optional) |
username |
Login username |
password |
Login password (Optional when using TLS Certificate authentication) |
database |
Database to select. (Optional) |
applicationName |
The name of the application connecting to the database. Defaults to r2dbc-postgresql . (Optional) |
autodetectExtensions |
Whether to auto-detect and register Extension s from the class path. Defaults to true . (Optional) |
schema |
The schema to set. (Optional) |
sslMode |
SSL mode to use, see SSLMode enum. Supported values: DISABLE , ALLOW , PREFER , REQUIRE , VERIFY_CA , VERIFY_FULL . (Optional) |
sslRootCert |
Path to SSL CA certificate in PEM format. (Optional) |
sslKey |
Path to SSL key for TLS authentication in PEM format. (Optional) |
sslCert |
Path to SSL certificate for TLS authentication in PEM format. (Optional) |
sslPassword |
Key password to decrypt SSL key. (Optional) |
sslHostnameVerifier |
javax.net.ssl.HostnameVerifier implementation. (Optional) |
Programmatic Configuration
ConnectionFactory connectionFactory = new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
.host("...")
.port("..."). // optional, defaults to 5432
.username("...")
.password("...")
.database("...") // optional
.build());
Mono<Connection> mono = connectionFactory.create();
PostgreSQL uses index parameters that are prefixed with $
. The following SQL statement makes use of parameters:
INSERT INTO person (id, first_name, last_name) VALUES ($1, $2, $3)
Parameters are referenced using the same identifiers when binding these:
mono.flatMapMany(connection -> connection
.createStatement("INSERT INTO person (id, first_name, last_name) VALUES ($1, $2, $3)")
.bind("$1", 1)
.bind("$2", "Walter")
.bind("$3", "White")
.execute());
Binding also allowed positional index (zero-based) references. The parameter index is derived from the parameter discovery order when parsing the query.
Maven configuration
Add the Maven dependency and use our Maven milestone repository:
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-postgresql</artifactId>
<version>0.8.0.RC1</version>
</dependency>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
</repository>
If you'd rather like the latest snapshots of the upcoming major version, use our Maven snapshot repository and declare the appropriate dependency version.
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-postgresql</artifactId>
<version>${version}.BUILD-SNAPSHOT</version>
</dependency>
<repository>
<id>spring-libs-snapshot</id>
<name>Spring Snapshot Repository</name>
<url>https://repo.spring.io/libs-snapshot</url>
</repository>
Listen/Notify
Listen and Notify provide a simple form of signal or inter-process communication mechanism for processes accessing the same Postgres database. For Listen/Notify, two actors are involved: The sender (notify) and the receiver (listen). The following example uses two connections to illustrate how they work together:
Connection sender = …;
Connection receiver = …;
Flux<Notification> listen = receiver.createStatement("LISTEN mymessage")
.execute()
.flatMap(PostgresqlResult::getRowsUpdated)
.thenMany(listen.getNotifications());
Mono<Void> notify = sender.createStatement("NOTIFY mymessage, 'Hello World'")
.execute()
.flatMap(PostgresqlResult::getRowsUpdated)
.then();
Upon subscription, the first connection enters listen mode and publishes incoming Notification
s as Flux
.
The second connection broadcasts a notification to the mymessage
channel upon subscription.
JSON/JSONB support
Postgres supports JSON by storing values in JSON
/JSONB
columns. These values can be consumed and written using the regular R2DBC SPI and by using driver-specific extensions with the io.r2dbc.postgresql.codec.Json
type.
You can choose from two approaches:
- Native JSONB encoding using the
Json
wrapper type. - Using scalar types.
The difference between the Json
type and scalar types is that Json
values are written encoded as JSONB
to the database.
byte[]
and String
types are represented as BYTEA
respective VARCHAR
and require casting ($1::JSON
) when used with parameterized statements.
The following code shows INSERT
and SELECT
cases for JSON interaction:
CREATE TABLE my_table (my_json JSON);
Write JSON
connection.createStatement("INSERT INTO my_table (my_json) VALUES($1)")
.bind("$1", Json.of("{\"hello\": \"world\"}")).execute();
Consume JSON
connection.createStatement("SELECT my_json FROM my_table")
.execute()
.flatMap(it -> it.map((row, rowMetadata) -> row.get("my_json", Json.class)))
.map(Json::asString);
Write JSON using casting
connection.createStatement("INSERT INTO my_table (my_json) VALUES($1::JSON)")
.bind("$1", "{\"hello\": \"world\"}").execute();
Consume JSON as scalar type
connection.createStatement("SELECT my_json FROM my_table")
.execute()
.flatMap(it -> it.map((row, rowMetadata) -> row.get("my_json", String.class)));
The following types are supported for JSON exchange:
io.r2dbc.postgresql.codec.Json
ByteBuf
(must be released after usage to avoid memory leaks)ByteBuffer
byte[]
String
InputStream
(must be closed after usage to avoid memory leaks)
Data Type Mapping
This reference table shows the type mapping between PostgreSQL and Java data types:
Types in bold indicate the native (default) Java type.
Support for the following single-dimensional arrays (read and write):
PostgreSQL Type | Supported Data Type |
---|---|
text[] |
String[] |
integer[] or int[] |
Integer[] , Long[] , Short[] |
Extension mechanism
This driver accepts the following extensions:
CodecRegistrar
to contributeCodec
s for Postgres ObjectIDs.
Extensions can be registered programmatically using PostgresConnectionConfiguration
or discovered using Java's ServiceLoader
mechanism (from META-INF/services/io.r2dbc.postgresql.extension.Extension
).
License
This project is released under version 2.0 of the Apache License.