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
Codecs 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 Extensions 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 Notifications 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
Jsonwrapper 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.JsonByteBuf(must be released after usage to avoid memory leaks)ByteBufferbyte[]StringInputStream(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:
CodecRegistrarto contributeCodecs 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.