////
This guide is maintained in the main Quarkus repository
and pull requests should be submitted there:
https://github.com/quarkusio/quarkus/tree/master/docs/src/main/asciidoc
////
= Quarkus - Reactive SQL Clients

include::./attributes.adoc[]
:config-file: application.properties

The Reactive SQL Clients have a straightforward API focusing on scalability and low-overhead.
Currently, the following database servers are supported:

* PostgreSQL
* MySQL

In this guide, you will learn how to implement a simple CRUD application exposing data stored in *PostgreSQL* over a RESTful API.

NOTE: Extension and connection pool class names for each client can be found at the bottom of this document.

IMPORTANT: If you are not familiar with the Quarkus Vert.x extension, consider reading the link:vertx.html[Using Eclipse Vert.x] guide first.

The application shall manage fruit entities:

[source,java]
----
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;
    }
}
----

[TIP]
====
Do you need a ready-to-use PostgreSQL server to try out the examples?

[source,shell]
----
docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name quarkus_test -e POSTGRES_USER=quarkus_test -e POSTGRES_PASSWORD=quarkus_test -e POSTGRES_DB=quarkus_test -p 5432:5432 postgres:10.5
----
====

== 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, set the `extensions` parameter as follows:

[source, shell, subs=attributes+]
----
mvn io.quarkus:quarkus-maven-plugin:{quarkus-version}:create \
    -DprojectGroupId=org.acme \
    -DprojectArtifactId=reactive-pg-client-quickstart \
    -Dextensions="reactive-pg-client"
cd reactive-pg-client-quickstart
----

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:

[source,shell]
----
./mvnw quarkus:add-extension -Dextensions="reactive-pg-client"
----

Otherwise, you can manually add this to the dependencies section of your `pom.xml` file:

[source,xml]
----
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-reactive-pg-client</artifactId>
</dependency>
----

NOTE: In this guide, we will use the Axle API of the Reactive PostgreSQL Client.
Read the link:vertx.html[Using Eclipse Vert.x] guide to understand the differences between the callback, RxJava and Axle based APIs.

=== JSON Binding

We will expose `Fruit` instances over HTTP in the JSON format.
Consequently, you also need to add the `quarkus-resteasy-jsonb` extension:

[source,shell]
----
./mvnw quarkus:add-extension -Dextensions="resteasy-jsonb"
----

If you prefer not to use the command line, manually add this to the dependencies section of your `pom.xml` file:

[source,xml]
----
<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-resteasy-jsonb</artifactId>
</dependency>
----

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:

[source]
.src/main/resources/application.properties
----
quarkus.datasource.url=vertx-reactive:postgresql://localhost:5432/quarkus_test
quarkus.datasource.username=quarkus_test
quarkus.datasource.password=quarkus_test
----

IMPORTANT: The eagle eyes among you might have noticed the `url` prefix is different from usual JDBC urls.
It is required to use the `vertx-reactive:` prefix otherwise the Reactive PostgreSQL Client extension will not use the `quarkus.datasource.url` config property.

With that you may create your `FruitResource` skeleton and `@Inject` a `io.vertx.axle.pgclient.PgPool` instance:

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
@Path("fruits")
@Produces(MediaType.APPLICATION_JSON)
@Consumes(MediaType.APPLICATION_JSON)
public class FruitResource {

    @Inject
    io.vertx.axle.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 upfront.

For production we would recommend to use something like the link:flyway.html[Flyway database migration tool].
But for development we can simply drop and create the tables on startup, and then insert a few fruits.

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
    @Inject
    @ConfigProperty(name = "myapp.schema.create", defaultValue = "true") // <1>
    boolean schemaCreate;

    @PostConstruct
    void config() {
        if (schemaCreate) {
            initdb();
        }
    }

    private void initdb() {
        // TODO
    }
}
----

TIP: 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 `CompletionStage` and thus can be composed to execute queries sequentially:

[source,java]
----
        client.query("DROP TABLE IF EXISTS fruits")
                .thenCompose(r -> client.query("CREATE TABLE fruits (id SERIAL PRIMARY KEY, name TEXT NOT NULL)"))
                .thenCompose(r -> client.query("INSERT INTO fruits (name) VALUES ('Orange')"))
                .thenCompose(r -> client.query("INSERT INTO fruits (name) VALUES ('Pear')"))
                .thenCompose(r -> client.query("INSERT INTO fruits (name) VALUES ('Apple')"))
                .toCompletableFuture()
                .join();
----

NOTE: Wondering why we need to convert the result to `CompletableFuture` and 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:

[source,java]
----
CompletionStage<RowSet> rowSet = client.query("SELECT id, name FROM fruits ORDER BY name ASC");
----

When the operation completes, we will get a `RowSet` that has all the rows buffered in memory.
As an `java.lang.Iterable<Row>`, it can be traversed with a _for-each_ loop:

[source,java]
----
CompletionStage<List<Fruit>> fruits = rowSet.thenApply(pgRowSet -> {
    List<Fruit> list = new ArrayList<>(pgRowSet.size());
    for (Row row : pgRowSet) {
        list.add(from(row));
    }
    return list;
});
----

The `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:

[source,java]
.src/main/java/org/acme/vertx/Fruit.java
----
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:

[source,java]
.src/main/java/org/acme/vertx/Fruit.java
----
    public static CompletionStage<List<Fruit>> findAll(PgPool client) {
        return client.query("SELECT id, name FROM fruits ORDER BY name ASC").thenApply(pgRowSet -> {
            List<Fruit> list = new ArrayList<>(pgRowSet.size());
            for (Row row : pgRowSet) {
                list.add(from(row));
            }
            return list;
        });
    }
----

And the endpoint to get all fruits from the backend:

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
@GET
public CompletionStage<Response> get() {
    return Fruit.findAll(client)
            .thenApply(Response::ok)
            .thenApply(ResponseBuilder::build);
}
----

Now start Quarkus in `dev` mode with:

[source, shell]
----
./mvnw compile quarkus:dev
----

Lastly, open your browser and navigate to http://localhost:8080/fruits, you should see:

[source, shell]
----
[{"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:

[source,java]
----
client.preparedQuery("SELECT name FROM fruits WHERE id = $1", Tuple.of(id))
----

TIP: The SQL string can refer to parameters by position, using $1, $2, ...etc.

Like the simple `query` method, `preparedQuery` returns an instance of `CompletionStage<RowSet>`.
Equipped with this tooling, we are able to safely use an `id` provided by the user to get the details of a particular fruit:

[source,java]
.src/main/java/org/acme/vertx/Fruit.java
----
public static CompletionStage<Fruit> findById(PgPool client, Long id) {
    return client.preparedQuery("SELECT id, name FROM fruits WHERE id = $1", Tuple.of(id)) // <1>
            .thenApply(RowSet::iterator) // <2>
            .thenApply(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:

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
@GET
@Path("{id}")
public CompletionStage<Response> getSingle(@PathParam Long id) {
    return Fruit.findById(client, id)
            .thenApply(fruit -> fruit != null ? Response.ok(fruit) : Response.status(Status.NOT_FOUND)) // <1>
            .thenApply(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`:

[source,java]
.src/main/java/org/acme/vertx/Fruit.java
----
public CompletionStage<Long> save(PgPool client) {
    return client.preparedQuery("INSERT INTO fruits (name) VALUES ($1) RETURNING (id)", Tuple.of(name))
            .thenApply(pgRowSet -> pgRowSet.iterator().next().getLong("id"));
}
----

And in the web resource we handle the `POST` request:

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
@POST
public CompletionStage<Response> create(Fruit fruit) {
    return fruit.save(client)
            .thenApply(id -> URI.create("/fruits/" + id))
            .thenApply(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:

[source,java]
.src/main/java/org/acme/vertx/Fruit.java
----
public static CompletionStage<Boolean> delete(PgPool client, Long id) {
    return client.preparedQuery("DELETE FROM fruits WHERE id = $1", Tuple.of(id))
            .thenApply(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:

[source,java]
.src/main/java/org/acme/vertx/FruitResource.java
----
@DELETE
@Path("{id}")
public CompletionStage<Response> delete(@PathParam Long id) {
    return Fruit.delete(client, id)
            .thenApply(deleted -> deleted ? Status.NO_CONTENT : Status.NOT_FOUND)
            .thenApply(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 https://jquery.com/[jQuery] to simplify interactions with the backend:

[source,html]
----
<!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.

[source,javascript]
----
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

[cols="15,35,50"]
|===
|Database |Extension name |Pool class name

|PostgreSQL
|`quarkus-reactive-pg-client`
|`io.vertx.axle.pgclient.PgPool`

|MySQL
|`quarkus-reactive-mysql-client`
|`io.vertx.axle.mysqlclient.MySQLPool`
|===

== Configuration Reference

=== MySQL

include::{generated-dir}/config/quarkus-reactive-mysql-client.adoc[opts=optional, leveloffset=+1]

=== PostgreSQL

include::{generated-dir}/config/quarkus-reactive-pg-client.adoc[opts=optional, leveloffset=+1]
