Closing a connection from R2DBC pool

Question

I'm trying to configure R2DBC connection to PostgreSQL database with connection pool. I have followed this documentation: https://github.com/r2dbc/r2dbc-pool/blob/main/README.md.

ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder()
        .option(DRIVER, "pool")
        .option(PROTOCOL, "postgresql") // driver identifier, PROTOCOL is delegated as DRIVER by the pool.
        .option(HOST, "192.168.1.200")
        .option(PORT, 5433)
        .option(DATABASE, "XXX")
        .option(USER, "XXX")
        .option(PASSWORD, "XXX")
        .build());

ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory)
        .maxIdleTime(Duration.ofMillis(1000))
        .initialSize(10)
        .maxSize(100)
        .build();
ConnectionPool pool = new ConnectionPool(configuration);

Mono<Connection> connection = pool.create();
return Flux.from(connection)
        .flatMap(conn -> conn
                .createStatement("SELECT id, name, description FROM assortment")
                .execute())
        .flatMap(result -> result
                .map((row, rowMetadata) ->
                        new AssortmentItem(
                                row.get("id", Long.class),
                                row.get("name", String.class),
                                row.get("description", String.class))
                ));

But I don't know how to close connection after query, to get it back to pool. I've tried:

connection.close();

but it doesn't work, try-with-resources to. Without this after 10 calls I got "io.r2dbc.postgresql.ExceptionFactory$PostgresqlNonTransientResourceException" with message "sorry, too many clients already".

How to close connection and get it back to pool in my example? Why I can only call 10 queries from pool (initial pool size) when max pool size is 100?

Lukas Eder · Answer 1 · 2021-07-15T19:29:53.973

Using R2DBC directly, with reactor

Here's one way to do it with reactor, inspired by the OracleTestKit:

pool.create()
    .flatMapMany(connection ->
        Flux.from(connection.createStatement("SELECT ...").execute())
            .flatMap(it -> it.map((r, m) -> new AssortmentItem(...)))
    )
    .concatWith(Mono.from(connection.close()).cast(AssortmentItem.class))

Other examples are given in this discussion on the mailing list, e.g. via Flux.usingWhen():

Flux.usingWhen(
        pool.create(),
        c -> c.createStatement("SELECT ...").execute(),
        c -> c.close()
    )
    .flatMap(it -> it.map((r, m) -> r.get("id", Long.class)));

Ultimately, this isn't about R2DBC, but about using your reactive streams API (e.g. reactor) to correctly sequence the execute() and close() operations.

Using R2DBC with jOOQ

A number of third party APIs are already wrapping R2DBC. jOOQ 3.15 will, too. It will make this particular task a bit easier:

// Inject this into your beans, or whatever
DSLContext ctx = DSL.using(pool);

// jOOQ queries are publishers
Flux.from(ctx.resultQuery("SELECT id, name, description FROM assortment"))

// Use jOOQ's mapping to automatically map fields by reflection
    .map(r -> r.into(AssortItem.class));

This isn't using or depending on jOOQ's DSL (though you can also do that, if you like). I imagine that JDBI and other APIs will offer similar simplifications?

Disclaimer: I work for the company behind jOOQ.

It should be marked as accepted answer. Flux.usingWhen(connection creation, connection usage, connection cleanup) is very effective indeed — khawarizmi, Dec 31 '22 at 12:54

Closing a connection from R2DBC pool

1 Answers1

Using R2DBC directly, with reactor

Using R2DBC with jOOQ