Vert.x + Reactive Postgres

# CLAUDE.md — Vert.x + Reactive Postgres

## Stack

- **vertx-pg-client** — fully reactive, non-blocking Postgres client. No JDBC.
- For Mutiny-flavored API: `io.vertx.mutiny.pgclient.PgPool`.
- Pair with **Flyway** or **Liquibase** for schema migrations (those are blocking; run as a separate startup step).

## Pool setup

```java
PgConnectOptions connect = new PgConnectOptions()
    .setHost("localhost").setPort(5432)
    .setDatabase("app").setUser("app").setPassword(secret);

PoolOptions pool = new PoolOptions().setMaxSize(10);

Pool client = PgPool.pool(vertx, connect, pool);
```

- Pool size: start at 10. Tune based on `EXPLAIN`-driven query duration and concurrent users.
- Reuse the `Pool` for the app's lifetime. Don't create per-request.

## Queries

```java
client.query("SELECT id, email FROM users").execute()
    .onSuccess(rows -> rows.forEach(row -> log.info(row.getString("email"))))
    .onFailure(Throwable::printStackTrace);
```

- `query(...)` for simple queries. `preparedQuery(...)` for parameterized — use it always except for static SQL.
- Parameterized:
  ```java
  client.preparedQuery("SELECT id FROM users WHERE email = $1")
      .execute(Tuple.of(email))
      .onSuccess(rs -> ...);
  ```
- Postgres uses `$1`, `$2`, ... — not `?`.

## Mapping rows

- For typed access, use the row mapper:
  ```java
  RowMapper<User> mapper = row -> new User(row.getUUID("id"), row.getString("email"));
  ```
- Or define a function and `.map(mapper)` over the results.
- Don't return raw `Row` to callers. Wrap into a domain type.

## Transactions

```java
client.withTransaction(conn -> {
    return conn.query("INSERT INTO orders ...").execute()
        .compose(__ -> conn.query("UPDATE inventory ...").execute());
}).onComplete(ar -> ...);
```

- `withTransaction` commits on success and rolls back on failure automatically.
- Use it for any multi-statement write.
- Read-only transactions: skip — postgres handles them efficiently without explicit BEGIN.

## Prepared statements

- The pool caches prepared statements per connection. Reuse query strings for free benefit.
- Avoid string-concatenating SQL — that defeats the cache and exposes you to injection.
- For dynamic IN clauses, use `ARRAY[$1::uuid[]]` patterns rather than building lists.

## Migrations

- Use Flyway for migrations. It's JDBC-based, runs at startup, blocking — that's fine because it's a one-time bootstrap step.
- After migrations succeed, the app boots Vert.x and uses the reactive client.
- Don't `executeBlocking(flywayMigrate)` from inside a verticle.

## Bulk operations

- For large inserts: `executeBatch(batchedTuples)` — single round-trip.
- For COPY: use the `pgPool.connection()` API to access raw COPY support if needed.

## Connection limits

- Postgres default is 100 connections. With pgbouncer, can be much higher.
- Set `setIdleTimeout` to release idle connections eventually.
- Don't size the pool larger than `(server max conns / replicas)` minus overhead.

## Don't

- Don't use JDBC drivers in Vert.x apps. They block the event loop.
- Don't share a connection across coroutines/handlers. Use the pool — it manages connection lifecycle.
- Don't put migrations in the same `Pool` lifecycle as queries — JDBC vs reactive are different APIs.
- Don't ignore the `onFailure` callback. Postgres errors silently disappearing is a common bug.