skip to content

How does the DatabaseClient fluent API work for executing SQL in R2DBC?

level: seniorimportance: should knowfreq 45%

answer

  1. client.sql(...).bind(...).fetch()/map(...)
  2. one/first/all/rowsUpdated
  3. Named bind markers = injection-safe
  4. No entity mapping — map rows by hand
  5. returnGeneratedValues for keys

basics

~10 s

DatabaseClient is the low-level fluent API: databaseClient.sql("...").bind("name", value).map(row -> ...).all() (or .one()/.first()/.rowsUpdated()). It runs raw SQL with named parameter binding and manual row mapping, returning Mono/Flux.

solid answer

~40 s

DatabaseClient is R2DBC's lowest-level, non-blocking SQL executor and the foundation R2dbcEntityTemplate is built on. You write SQL with sql("SELECT ... WHERE email = :email"), bind named parameters with bind("email", value) (or bindNull), then choose how to consume: fetch().one()/first()/all() for generic Map rows, fetch().rowsUpdated() for DML counts, or map((row, meta) -> ...) for custom mapping to your type. All results are Mono/Flux and lazy. Use it for vendor-specific SQL, joins/CTEs, batch updates, or projections that entity mapping can't express, and when you deliberately want no ORM overhead. You give up automatic entity mapping and must map columns yourself, and you must use named or indexed bind markers — never string-concatenate values, to avoid SQL injection.

code

java · 22 lines
java
@Repository
public class UserDao {
    private final DatabaseClient client;

    UserDao(DatabaseClient client) { this.client = client; }

    Mono<User> findByEmail(String email) {
        return client.sql("SELECT id, email FROM users WHERE email = :email")
                     .bind("email", email)
                     .map((row, meta) -> new User(
                             row.get("id", Long.class),
                             row.get("email", String.class)))
                     .one();
    }

    Mono<Long> deactivateStale(Instant before) {
        return client.sql("UPDATE users SET active = false WHERE last_login < :before")
                     .bind("before", before)
                     .fetch()
                     .rowsUpdated();
    }
}

go deeper

for a junior

Know it runs raw SQL and returns Mono/Flux.

for a middle

Can bind params and consume with fetch()/map() and the one/first/all/rowsUpdated variants.

for a senior

Handles generated keys, null binding, injection safety, and picks it over the template for vendor SQL.

for a principal

Governs where raw SQL is allowed vs entity abstractions, and reviews for injection/consistency across the team.

**DatabaseClient** (`org.springframework.r2dbc.core.DatabaseClient`) is the thin, fully non-blocking, fluent wrapper over a `ConnectionFactory`. It is the base layer: `R2dbcEntityTemplate` uses one internally, and Boot auto-configures a `DatabaseClient` bean. **The pipeline.** A call reads left to right: 1. `client.sql("SELECT id, email FROM users WHERE email = :email")` — the SQL, with **named bind markers** (`:email`) or indexed (`$1`) depending on style. `sql(...)` also accepts a `Supplier<String>` so the string is built lazily. 2. `.bind("email", email)` / `.bind(0, value)` — binds a parameter. Use `.bindNull("email", String.class)` for nulls (a plain null needs a type). Binding is what makes it injection-safe: values never touch the SQL text. 3. **Consume** the result. Two families: - Generic: `.fetch().one()` (`Mono`, errors if >1), `.fetch().first()` (`Mono`, first row or empty), `.fetch().all()` (`Flux`), `.fetch().rowsUpdated()` (`Mono<Long>` for INSERT/UPDATE/DELETE). `fetch()` yields rows as `Map<String, Object>`. - Custom mapping: `.map((row, rowMetadata) -> new User(row.get("id", Long.class), row.get("email", String.class)))` then `.one()/.first()/.all()`. 4. Optionally `.filter(...)` to customize the underlying `Statement` (e.g. fetch size). **Laziness and threading.** Like all R2DBC, the returned `Mono`/`Flux` is cold; SQL executes on subscription on a non-blocking driver thread. Never block. **Generated keys.** For inserts needing the generated id, use `.filter(statement -> statement.returnGeneratedValues("id"))` and then `.map(...)` the returned row, since `fetch().rowsUpdated()` only gives a count. **When to use it.** - Vendor-specific SQL, complex joins, CTEs, window functions, `INSERT ... ON CONFLICT`. - Projections/DTOs that don't map to a single entity. - When you explicitly want the least abstraction and overhead. **Trade-offs / gotchas.** - **No entity mapping** — you write the row→object code (or accept `Map`). More boilerplate, full control. - **SQL injection**: only safe if you use bind markers; never concatenate user input into the SQL string. - **Named vs indexed markers** depend on the dialect/driver; Postgres natively uses `$1` but Spring's named `:param` works and is translated. - **Nulls** need `bindNull(name, type)` because the driver must know the type. - It participates in reactive transactions (via `TransactionalOperator`/`@Transactional` + `R2dbcTransactionManager`) just like repositories, because it shares the `ConnectionFactory` and the reactive transaction context.

  • How do you retrieve a database-generated key when inserting via DatabaseClient?
    Add .filter(statement -> statement.returnGeneratedValues("id")) to the chain and .map(...) the returned row to read the key. fetch().rowsUpdated() only returns the affected-row count, not the key.
  • What is the difference between .fetch().one() and .fetch().first()?
    one() expects exactly zero or one row and signals an error if more than one row comes back; first() simply takes the first row (or empty) and ignores the rest. Use one() when uniqueness is an invariant.
  • How do you keep DatabaseClient queries safe from SQL injection?
    Always pass values through bind()/bindNull() named or indexed markers so they are sent as parameters, never concatenated into the SQL string. The SQL text should only ever contain markers, not user input.

saying these in an interview costs you the question

  • Concatenating user input into the sql() string instead of binding
  • Using fetch().rowsUpdated() and expecting the generated id
  • Passing a raw null to bind() instead of bindNull(name, type)
  • Thinking DatabaseClient maps rows to entities automatically

context

resolving links…