Helidon DB Client

It was always problematic to work with databases in reactive applications. It’s not anymore. In Helidon 2.0.0-M1 we introduced the pre-release version of Helidon DB Client — a unified, reactive API for working with databases in non-blocking way. This API was updated in 2.0.0 release to make it more user friendly.

Features Overview

The Helidon 2.0.0 DB Client includes the following key features:

• Unified API for data access and query
  The API was implemented as a layer above JDBC or MongoDB Reactive Streams Java Driver, so any relational databases with JDBC driver or MongoDB are supported.
• Reactive database access with non-reactive drivers
  Most JDBC drivers are blocking. Using them in a reactive application is problematic. Helidon DB Client allows the use of blocking JDBC drivers in your reactive application by wrapping a blocking driver in an executor service.
• Observability
  Support for health checks, metrics and tracing is being added to all Helidon SE APIs and the Helidon DB Client is no exception.
• Backpressure
  Helidon DB Client performs database operations only when it’s requested by the consumer. This is propagated all the way to the TCP layer.
• Improved portability between relational database drivers
  The DB Client works with native database statements that can be used inline in the code or defined as named statements in database configuration. By moving the native query code to configuration files, the Helidon DB Client allows you to switch to another database by changing the configuration files, not the code.

The DB Client must be configured before you begin. Let’s take a look at how you can use Helidon Config to set up your JDBC-based client.

The Helidon Configuration node includes the following sections:

• source: jdbc or mongodb
• connection: database connection parameters
• statements: named statements when used

Configuration for JDBC-based Database Client:

db:
  source: "jdbc"
  connection:
    url: "jdbc:mysql://127.0.0.1:3306/pokemon?useSSL=false"
    username: "user"
    password: "password"
    poolName: "mysql"
  statements:
    ping: "DO 0"
    select-all-pokemons: "SELECT id, name FROM Pokemons"

DB Client API

The Helidon DB Client API contains many methods to run various statements with parameters and to retrieve statement execution results. The following sections describe the options you can use to build and execute your statements.

Executor Selection

DBClient class has two methods to select whether statements will be executed in transaction or not:

• execute(Function<DbExecute, T> executor)
• inTransaction(Function<DbTransaction, T> executor)

Both methods require Function interface argument with statements executor.

Statement Building and Execution

DbExecute class offers many methods for various statements builders:

• DML statements: createDmlStatement, createNamedDmlStatement
• insert statements: createInsert, createNamedInsert
• update statements: createUpdate, createNamedUpdate
• delete statements: createDelete, createNamedDelete
• query statements: createQuery, createNamedQuery

Methods with “Named” in their name expect statement name from statements config section.

All statement builders offer methods to set statement parameters. Those parameters can be ordered parameters or named parameters. Ordered and named parameters can’t be mixed in a single statement.

Ordered Parameters

Ordered parameters are written down as ? in the SQL statement:

SELECT name FROM Pokemons WHERE id = ?

They are equivalent to JDBC PreparedStatement parameters.

Methods to set ordered parameters are:

• params(List<?> parameters) with all parameters as List
• params(Object… parameters) with all parameters as array
• indexedParam(Object parameters) POJO used with registered mapper
• addParam(Object parameter) with single parameter, can be called repeatedly

Named parameters

Named parameters are written down as :<name> in the SQL statement:

SELECT name FROM Pokemons WHERE id = :id

or as $<name> in the MongoDB statement:

{
    "collection": "pokemons",
    "operation": "update",
    "value":{ $set: { "name": $name } },
    "query": { id: $id }
}

Methods to set named parameters are:

• params(Map<String, ?> parameters) with all parameters as Map
• namedParam(Object parameters) POJO used with registered mapper
• addParam(String name, Object parameter) with single parameter, can be called repeatedly

Statement Execution

Statements are executed by calling execute() method after statement parameters are set. This method returns Single<Long>for DML statements and Multi<DbRow>for query statements.

JDBC query with ordered parameters and query that does not run in the transaction:

dbClient.execute(exec -> exec
    .createQuery("SELECT name FROM Pokemons WHERE id = ?")
    .params(1)
    .execute()
);

JDBC query with named parameters and the query runs in transaction:

dbClient.inTransaction(tx -> tx
    .createQuery("SELECT name FROM Pokemons WHERE id = :id")
    .addParam("id", 1)
    .execute()
);

Both examples will return Multi<DbRow> with rows returned by the query.

This example shows a MongoDB update statement with named parameters and the query does not run in transaction:

dbClient.execute(exec -> exec
    .createUpdate("{\"collection\": \"pokemons\","
        + "\"value\":{$set:{\"name\":$name}},"
        + "\"query\":{id:$id}}")
    .addParam("id", 1)
    .addParam("name", "Pikachu")
    .execute()
);

This update statement will return Single<Long> with the number of modified records in the database.

DML Statement Result

Execution of DML statements will always return Single<Long> with the number of modified records in the database.

In following example, the number of modified records is being printed to standard output:

dbClient.execute(exec -> exec
    .insert("INSERT INTO Pokemons (id, name) VALUES(?, ?)",
        1, "Pikachu"))
    .thenAccept(count -> 
        System.out.printf("Inserted %d records, count\n"));

Query Statement Result

Execution of a query statement will always return Multi<DbRow>. Class Multi<DbRow> offers many methods to work with this result, for example:

• Single<Void> forEach(Consumer<? super DbRow> consumer) to process individual result rows using implementation of Consumer functional interface,
• Single<List<DbRow>> collect() to collect all rows and return them as List<DbRow>,
• Multi<U> map(Function<? super DbRow,? extends U> mapper) to map returned result using provided mapper.

Refer to the pokemon sample to see how to collect all rows and process them as List<DbRow>.

Samples

This sample project will help you get started. More samples demonstrating how to use Helidon DB Client with both relational databases and MongoDB are available here.

Summary

Helidon DB Client is still an experimental feature. Before making it final we need your feedback. Please try it and let us know how we can improve it by leaving a comment to this article, as an issue in our issues tracker or in our official Slack channel.