The Reactive SQL Clients have a straightforward API focusing on scalability and low-overhead. Currently, the following database servers are supported:
-
IBM Db2
-
PostgreSQL
-
MariaDB/MySQL
-
Microsoft SQL Server
-
Oracle
The Reactive SQL Client for Oracle is considered tech preview. In tech preview mode, early feedback is requested to mature the idea. There is no guarantee of stability in the platform until the solution matures. Feedback is welcome on our mailing list or as issues in our GitHub issue tracker. |
In this guide, you will learn how to implement a simple CRUD application exposing data stored in PostgreSQL over a RESTful API.
Extension and connection pool class names for each client can be found at the bottom of this document. |
If you are not familiar with the Quarkus Vert.x extension, consider reading the Using Eclipse Vert.x guide first. |
The application shall manage fruit entities:
public class Fruit {
public Long id;
public String name;
public Fruit() {
}
public Fruit(String name) {
this.name = name;
}
public Fruit(Long id, String name) {
this.id = id;
this.name = name;
}
}
Do you need a ready-to-use PostgreSQL server to try out the examples?
|
Installing
Reactive PostgreSQL Client extension
First, make sure your project has the quarkus-reactive-pg-client
extension enabled.
If you are creating a new project, use the following command:
If you have an already created project, the reactive-pg-client
extension can be added to an existing Quarkus project with the add-extension
command:
quarkus extension add 'reactive-pg-client'
./mvnw quarkus:add-extension -Dextensions="reactive-pg-client"
./gradlew addExtension --extensions="reactive-pg-client"
Otherwise, you can manually add the dependency to your build file:
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-reactive-pg-client</artifactId>
</dependency>
implementation("io.quarkus:quarkus-reactive-pg-client")
Mutiny
Reactive REST endpoints in your application that return Uni or Multi need Mutiny support for RESTEasy
extension (io.quarkus:quarkus-resteasy-mutiny
) to work properly:
quarkus extension add 'resteasy-mutiny'
./mvnw quarkus:add-extension -Dextensions="resteasy-mutiny"
./gradlew addExtension --extensions="resteasy-mutiny"
In this guide, we will use the Mutiny API of the Reactive PostgreSQL Client. If you are not familiar with Mutiny, check Mutiny - an intuitive reactive programming library. |
JSON Binding
We will expose Fruit
instances over HTTP in the JSON format.
Consequently, you also need to add the quarkus-resteasy-jackson
extension:
quarkus extension add 'resteasy-jackson'
./mvnw quarkus:add-extension -Dextensions="resteasy-jackson"
./gradlew addExtension --extensions="resteasy-jackson"
If you prefer not to use the command line, manually add the dependency to your build file:
<dependency>
<groupId>io.quarkus</groupId>
<artifactId>quarkus-resteasy-jackson</artifactId>
</dependency>
implementation("io.quarkus:quarkus-resteasy-jackson")
Of course, this is only a requirement for this guide, not any application using the Reactive PostgreSQL Client.
Configuring
The Reactive PostgreSQL Client can be configured with standard Quarkus datasource properties and a reactive URL:
quarkus.datasource.db-kind=postgresql
quarkus.datasource.username=quarkus_test
quarkus.datasource.password=quarkus_test
quarkus.datasource.reactive.url=postgresql://localhost:5432/quarkus_test
With that you may create your FruitResource
skeleton and @Inject
a io.vertx.mutiny.pgclient.PgPool
instance:
@Path("fruits")
public class FruitResource {
@Inject
io.vertx.mutiny.pgclient.PgPool client;
}
Database schema and seed data
Before we implement the REST endpoint and data management code, we need to setup the database schema. It would also be convenient to have some data inserted up-front.
For production we would recommend to use something like the Flyway database migration tool. But for development we can simply drop and create the tables on startup, and then insert a few fruits.
@Inject
@ConfigProperty(name = "myapp.schema.create", defaultValue = "true") (1)
boolean schemaCreate;
@PostConstruct
void config() {
if (schemaCreate) {
initdb();
}
}
private void initdb() {
// TODO
}
You may override the default value of the myapp.schema.create property in the application.properties file.
|
Almost ready!
To initialize the DB in development mode, we will use the client simple query
method.
It returns a Uni
and thus can be composed to execute queries sequentially:
client.query("DROP TABLE IF EXISTS fruits").execute()
.flatMap(r -> client.query("CREATE TABLE fruits (id SERIAL PRIMARY KEY, name TEXT NOT NULL)").execute())
.flatMap(r -> client.query("INSERT INTO fruits (name) VALUES ('Orange')").execute())
.flatMap(r -> client.query("INSERT INTO fruits (name) VALUES ('Pear')").execute())
.flatMap(r -> client.query("INSERT INTO fruits (name) VALUES ('Apple')").execute())
.await().indefinitely();
Wondering why we need block until the latest query is completed?
This code is part of a @PostConstruct method and Quarkus invokes it synchronously.
As a consequence, returning prematurely could lead to serving requests while the database is not ready yet.
|
That’s it! So far we have seen how to configure a pooled client and execute simple queries. We are now ready to develop the data management code and implement our RESTful endpoint.
Using
Query results traversal
In development mode, the database is set up with a few rows in the fruits
table.
To retrieve all the data, we will use the query
method again:
Uni<RowSet<Row>> rowSet = client.query("SELECT id, name FROM fruits ORDER BY name ASC").execute();
When the operation completes, we will get a RowSet
that has all the rows buffered in memory.
A RowSet
is an java.lang.Iterable<Row>
and thus can be converted to a Multi
:
Multi<Fruit> fruits = rowSet
.onItem().transformToMulti(set -> Multi.createFrom().iterable(set))
.onItem().transform(Fruit::from);
The Fruit#from
method converts a Row
instance to a Fruit
instance.
It is extracted as a convenience for the implementation of the other data management methods:
private static Fruit from(Row row) {
return new Fruit(row.getLong("id"), row.getString("name"));
}
Putting it all together, the Fruit.findAll
method looks like:
public static Multi<Fruit> findAll(PgPool client) {
return client.query("SELECT id, name FROM fruits ORDER BY name ASC").execute()
.onItem().transformToMulti(set -> Multi.createFrom().iterable(set))
.onItem().transform(Fruit::from);
}
And the endpoint to get all fruits from the backend:
@GET
public Multi<Fruit> get() {
return Fruit.findAll(client);
}
Now start Quarkus in dev mode with:
quarkus dev
./mvnw quarkus:dev
./gradlew --console=plain quarkusDev
Lastly, open your browser and navigate to http://localhost:8080/fruits, you should see:
[{"id":3,"name":"Apple"},{"id":1,"name":"Orange"},{"id":2,"name":"Pear"}]
Prepared queries
The Reactive PostgreSQL Client can also prepare queries and take parameters that are replaced in the SQL statement at execution time:
client.preparedQuery("SELECT id, name FROM fruits WHERE id = $1").execute(Tuple.of(id))
For PostgreSQL, the SQL string can refer to parameters by position, using $1 , $2 , …etc.
Please refer to the Database Clients details section for other databases.
|
Similar to the simple query
method, preparedQuery
returns an instance of PreparedQuery<RowSet<Row>>
.
Equipped with this tooling, we are able to safely use an id
provided by the user to get the details of a particular fruit:
public static Uni<Fruit> findById(PgPool client, Long id) {
return client.preparedQuery("SELECT id, name FROM fruits WHERE id = $1").execute(Tuple.of(id)) (1)
.onItem().transform(RowSet::iterator) (2)
.onItem().transform(iterator -> iterator.hasNext() ? from(iterator.next()) : null); (3)
}
1 | Create a Tuple to hold the prepared query parameters. |
2 | Get an Iterator for the RowSet result. |
3 | Create a Fruit instance from the Row if an entity was found. |
And in the JAX-RS resource:
@GET
@Path("{id}")
public Uni<Response> getSingle(@PathParam Long id) {
return Fruit.findById(client, id)
.onItem().transform(fruit -> fruit != null ? Response.ok(fruit) : Response.status(Status.NOT_FOUND)) (1)
.onItem().transform(ResponseBuilder::build); (2)
}
1 | Prepare a JAX-RS response with either the Fruit instance if found or the 404 status code. |
2 | Build and send the response. |
The same logic applies when saving a Fruit
:
public Uni<Long> save(PgPool client) {
return client.preparedQuery("INSERT INTO fruits (name) VALUES ($1) RETURNING id").execute(Tuple.of(name))
.onItem().transform(pgRowSet -> pgRowSet.iterator().next().getLong("id"));
}
And in the web resource we handle the POST
request:
@POST
public Uni<Response> create(Fruit fruit) {
return fruit.save(client)
.onItem().transform(id -> URI.create("/fruits/" + id))
.onItem().transform(uri -> Response.created(uri).build());
}
Result metadata
A RowSet
does not only hold your data in memory, it also gives you some information about the data itself, such as:
-
the number of rows affected by the query (inserted/deleted/updated/retrieved depending on the query type),
-
the column names.
Let’s use this to support removal of fruits in the database:
public static Uni<Boolean> delete(PgPool client, Long id) {
return client.preparedQuery("DELETE FROM fruits WHERE id = $1").execute(Tuple.of(id))
.onItem().transform(pgRowSet -> pgRowSet.rowCount() == 1); (1)
}
1 | Inspect metadata to determine if a fruit has been actually deleted. |
And to handle the HTTP DELETE
method in the web resource:
@DELETE
@Path("{id}")
public Uni<Response> delete(@PathParam Long id) {
return Fruit.delete(client, id)
.onItem().transform(deleted -> deleted ? Status.NO_CONTENT : Status.NOT_FOUND)
.onItem().transform(status -> Response.status(status).build());
}
With GET
, POST
and DELETE
methods implemented, we may now create a minimal web page to try the RESTful application out.
We will use jQuery to simplify interactions with the backend:
<!doctype html>
<html>
<head>
<meta charset="utf-8"/>
<title>Reactive PostgreSQL Client - Quarkus</title>
<script src="https://code.jquery.com/jquery-3.3.1.min.js"
integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>
<script type="application/javascript" src="fruits.js"></script>
</head>
<body>
<h1>Fruits API Testing</h1>
<h2>All fruits</h2>
<div id="all-fruits"></div>
<h2>Create Fruit</h2>
<input id="fruit-name" type="text">
<button id="create-fruit-button" type="button">Create</button>
<div id="create-fruit"></div>
</body>
</html>
In the Javascript code, we need a function to refresh the list of fruits when:
-
the page is loaded, or
-
a fruit is added, or
-
a fruit is deleted.
function refresh() {
$.get('/fruits', function (fruits) {
var list = '';
(fruits || []).forEach(function (fruit) { (1)
list = list
+ '<tr>'
+ '<td>' + fruit.id + '</td>'
+ '<td>' + fruit.name + '</td>'
+ '<td><a href="#" onclick="deleteFruit(' + fruit.id + ')">Delete</a></td>'
+ '</tr>'
});
if (list.length > 0) {
list = ''
+ '<table><thead><th>Id</th><th>Name</th><th></th></thead>'
+ list
+ '</table>';
} else {
list = "No fruits in database"
}
$('#all-fruits').html(list);
});
}
function deleteFruit(id) {
$.ajax('/fruits/' + id, {method: 'DELETE'}).then(refresh);
}
$(document).ready(function () {
$('#create-fruit-button').click(function () {
var fruitName = $('#fruit-name').val();
$.post({
url: '/fruits',
contentType: 'application/json',
data: JSON.stringify({name: fruitName})
}).then(refresh);
});
refresh();
});
1 | The fruits parameter is not defined when the database is empty. |
All done! Navigate to http://localhost:8080/fruits.html and read/create/delete some fruits.
Database Clients details
Database | Extension name | Pool class name | Placeholders |
---|---|---|---|
IBM Db2 |
|
|
|
MariaDB/MySQL |
|
|
|
Microsoft SQL Server |
|
|
|
Oracle |
|
|
|
PostgreSQL |
|
|
|
Transactions
The reactive SQL clients support transactions.
A transaction is started with io.vertx.mutiny.sqlclient.SqlConnection#begin
and terminated with either io.vertx.mutiny.sqlclient.Transaction#commit
or io.vertx.mutiny.sqlclient.Transaction#rollback
.
All these operations are asynchronous:
-
connection.begin()
returns aUni<Transaction>
-
transaction.commit()
andtransaction.rollback()
returnUni<Void>
Managing transactions in the reactive programming world can be cumbersome.
Instead of writing repetitive and complex (thus error-prone!) code, you can use the io.vertx.mutiny.sqlclient.Pool#withTransaction
helper method.
The following snippet shows how to run 2 insertions in the same transaction:
public static Uni<Void> insertTwoFruits(PgPool client, Fruit fruit1, Fruit fruit2) {
return client.withTransaction(conn -> {
Uni<RowSet<Row>> insertOne = conn.preparedQuery("INSERT INTO fruits (name) VALUES ($1) RETURNING id")
.execute(Tuple.of(fruit1.name));
Uni<RowSet<Row>> insertTwo = conn.preparedQuery("INSERT INTO fruits (name) VALUES ($1) RETURNING id")
.execute(Tuple.of(fruit2.name));
return Uni.combine().all().unis(insertOne, insertTwo)
// Ignore the results (the two ids)
.discardItems();
});
}
In this example, the transaction is automatically committed on success or rolled back on failure.
You can also create dependent actions as follows:
return client.withTransaction(conn -> conn
.preparedQuery("INSERT INTO person (firstname,lastname) VALUES ($1,$2) RETURNING id")
.execute(Tuple.of(person.getFirstName(), person.getLastName()))
.onItem().transformToUni(id -> conn.preparedQuery("INSERT INTO addr (person_id,addrline1) VALUES ($1,$2)")
.execute(Tuple.of(id.iterator().next().getLong("id"), person.getLastName())))
.onItem().ignore().andContinueWithNull());
Working with batch query results
When executing batch queries, reactive SQL clients return a RowSet
that corresponds to the results of the first element in the batch.
To get the results of the following batch elements, you must invoke the RowSet#next
method until it returns null
.
Let’s say you want to update some rows and compute the total number of affected rows.
You must inspect each RowSet
:
PreparedQuery<RowSet<Row>> preparedQuery = client.preparedQuery("UPDATE fruits SET name = $1 WHERE id = $2");
Uni<RowSet<Row>> rowSet = preparedQuery.executeBatch(Arrays.asList(
Tuple.of("Orange", 1),
Tuple.of("Pear", 2),
Tuple.of("Apple", 3)));
Uni<Integer> totalAffected = rowSet.onItem().transform(res -> {
int total = 0;
do {
total += res.rowCount(); (1)
} while ((res = res.next()) != null); (2)
return total;
});
1 | Compute the sum of RowSet#rowCount . |
2 | Invoke RowSet#next until it returns null . |
As another example, if you want to load all the rows you just inserted, you must concatenate the contents of each RowSet
:
PreparedQuery<RowSet<Row>> preparedQuery = client.preparedQuery("INSERT INTO fruits (name) VALUES ($1) RETURNING *");
Uni<RowSet<Row>> rowSet = preparedQuery.executeBatch(Arrays.asList(
Tuple.of("Orange"),
Tuple.of("Pear"),
Tuple.of("Apple")));
// Generate a Multi of RowSet items
Multi<RowSet<Row>> rowSets = rowSet.onItem().transformToMulti(res -> {
return Multi.createFrom().generator(() -> res, (rs, emitter) -> {
RowSet<Row> next = null;
if (rs != null) {
emitter.emit(rs);
next = rs.next();
}
if (next == null) {
emitter.complete();
}
return next;
});
});
// Transform each RowSet into Multi of Row items and Concatenate
Multi<Row> rows = rowSets.onItem().transformToMultiAndConcatenate(Multi.createFrom()::iterable);
Multiple Datasources
The reactive SQL clients support defining several datasources.
A typical configuration with several datasources would look like:
quarkus.datasource.db-kind=postgresql (1)
quarkus.datasource.username=user-default
quarkus.datasource.password=password-default
quarkus.datasource.reactive.url=postgresql://localhost:5432/default
quarkus.datasource."additional1".db-kind=postgresql (2)
quarkus.datasource."additional1".username=user-additional1
quarkus.datasource."additional1".password=password-additional1
quarkus.datasource."additional1".reactive.url=postgresql://localhost:5432/additional1
quarkus.datasource."additional2".db-kind=mysql (3)
quarkus.datasource."additional2".username=user-additional2
quarkus.datasource."additional2".password=password-additional2
quarkus.datasource."additional2".reactive.url=mysql://localhost:3306/additional2
1 | The default datasource - using PostgreSQL. |
2 | A named datasource called additional1 - using PostgreSQL. |
3 | A named datasource called additional2 - using MySQL. |
You can then inject the clients as follows:
@Inject (1)
PgPool defaultClient;
@Inject
@ReactiveDataSource("additional1") (2)
PgPool additional1Client;
@Inject
@ReactiveDataSource("additional2")
MySQLPool additional2Client;
1 | Injecting the client for the default datasource does not require anything special. |
2 | For a named datasource, you use the @ReactiveDataSource CDI qualifier with the datasource name as its value. |
UNIX Domain Socket connections
The PostgreSQL and MariaDB/MySQL clients can be configured to connect to the server through a UNIX domain socket.
First make sure that native transport support is enabled.
Then configure the database connection url. This step depends on the database type.
PostgreSQL
PostgresSQL domain socket paths have the following form: <directory>/.s.PGSQL.<port>
The database connection url must be configured so that:
-
the
host
is thedirectory
in the socket path -
the
port
is theport
in the socket path
Consider the following socket path: /var/run/postgresql/.s.PGSQL.5432
.
In application.properties
add:
quarkus.datasource.reactive.url=postgresql://:5432/quarkus_test?host=/var/run/postgresql
Pooled Connection idle-timeout
Reactive datasources can be configured with an idle-timeout
(in milliseconds).
It is the maximum time a connection remains unused in the pool before it is closed.
The idle-timeout is disabled by default.
|
For example, you could expire idle connections after 60 minutes:
quarkus.datasource.reactive.idle-timeout=PT60M