Pyranid Logo

Core Concepts

Database-Specific Recipes

Pyranid does not translate SQL between database products. You still write the SQL your database expects, and Pyranid handles named parameters, binding, mapping, transactions, and the JDBC details it can safely make portable.

These recipes cover database-specific cases that are easy to get subtly wrong.

Cross-Database Patterns

Configure The Database Type Behind Proxies

Pyranid can auto-detect supported databases from JDBC metadata. If a proxy, pool, or wrapper obscures that metadata, configure the DatabaseType explicitly so dialect-specific binding and mapping still apply.

Database database = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.SQL_SERVER)
  .build();

Prefer IN-List Expansion To SQL ARRAY For Portable Filters

SQL ARRAY binding is not supported by every database or JDBC driver. For ordinary filters, Parameters::inList(...) is the portable shape.

List<Long> employeeIds = List.of(1L, 2L, 3L);

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE employee_id IN (:employeeIds)
  """)
  .bind("employeeIds", Parameters.inList(employeeIds))
  .fetchList(Employee.class);

PostgreSQL

The portable API, durable-reconciliation pattern, callback lifecycle, interruption behavior, and supervision model are covered on the Notifications page.

LISTEN/NOTIFY Semantics

Pyranid sends through bound pg_notify(?,?) SQL. PostgreSQL converts a SQL NULL payload to "", so null, omitted, and explicitly empty payloads are all received as the empty string. PostgreSQL publishes transactional notifications only after commit and discards them on rollback. Within one transaction, PostgreSQL can coalesce duplicate notifications that have the same channel and payload; distinct payloads remain distinct.

Receiving requires pgjdbc at runtime. Sending remains pure SQL and does not require the driver's notification API.

Pyranid identifier-quotes every LISTEN channel. Prefer stable lowercase channel names for interoperability, because channel matching is case-sensitive after PostgreSQL parses the identifier. The standard-build compatibility ceilings are 63 UTF-8 bytes for a channel and 7,999 UTF-8 bytes for a payload; custom PostgreSQL builds can differ.

PostgreSQL notifications can be delivered back to the sending backend if it is also listening. Pyranid does not expose PostgreSQL's sending process identifier, so your notification-handling logic should remain idempotent rather than relying on sender filtering.

PgBouncer And Session Affinity

LISTEN state belongs to one PostgreSQL backend session. PgBouncer session pooling can preserve it. Using transaction or statement pooling as the listener source is unsupported and can fail silently: registration can appear to succeed before a later checkout reaches another backend and notification delivery stops. Pyranid does not inspect PgBouncer mode or diagnose that loss of affinity at runtime.

If ordinary traffic goes through transaction or statement pooling, build a second Database over a session-affine listener source:

Database listenerDatabase = Database.withDataSource(listenerDataSource)
  .databaseType(DatabaseType.POSTGRESQL)
  .build();

Ensure the publishing, listening, and reconciliation instances address the same active logical PostgreSQL database and primary. Database::isNotificationListeningSupported() reports dialect/runtime receive capability; it does not inspect PgBouncer mode, prove session affinity, compare separately configured Database instances, or actively test listener liveness.

Socket And Queue Behavior

A positive awaitNotifications(...) duration is not a hard socket deadline. Pyranid neutralizes a positive JDBC network timeout around pgjdbc receive calls to avoid returning a connection after a timeout interrupts partial protocol-frame parsing. A driver call already parsing an incomplete frame can therefore overrun the requested wait. The same partial-frame limitation applies to drainNotifications() after its non-waiting probe begins consuming a frame.

Configure pgjdbc tcpKeepAlive, operating-system TCP keepalive, proxy health checks, and bounded application shutdown escalation for the deployment. A quiet receive timeout is not proof that bytes crossed the connection or that the listener remains healthy. When the deployment requires a hard deadline, process-level termination is the universal fallback for a listener that does not join in time.

When the JDBC connection supports network-timeout inspection and mutation, Pyranid caps the cleanup UNLISTEN * round trip at 30 seconds while preserving any stricter positive JDBC network timeout. If a pool, proxy, or wrapper reports those operations as unsupported, Pyranid retains the legacy cleanup path, whose duration is bounded only by configured driver and socket behavior. Consider a positive pgjdbc socketTimeout appropriate to the listener topology for other setup and registration statement round trips. Size it for healthy statement latency because expiry makes the listener connection unsafe for reuse. Neither timeout bounds the guarded post-UNLISTEN drain after pgjdbc begins parsing a partial protocol frame.

Drain notifications promptly and monitor PostgreSQL's pg_notification_queue_usage(). PostgreSQL uses a shared notification queue and can retain queue entries while listeners remain inside long-running transactions; sufficient pressure can eventually make publishing transactions fail. Pyranid forbids notification consumption inside an ambient Pyranid transaction, but you should also avoid unmanaged JDBC transactions on listener connections.

SQL Server

Return Multiple Identity Values With OUTPUT

SQL Server's JDBC generated-key behavior is limited for multi-row identity inserts. Use SQL Server's OUTPUT clause and map the result set directly.

List<Long> employeeIds = database.query("""
  INSERT INTO employee (name)
  OUTPUT inserted.employee_id
  VALUES (:firstName), (:secondName)
  """)
  .bind("firstName", "Ada")
  .bind("secondName", "Grace")
  .executeForList(Long.class);

Use OUTPUT ... INTO For Trigger Tables

SQL Server rejects plain OUTPUT against tables with enabled triggers. Capture the output into a table, then read it back. A local temp table works when both statements run inside one Pyranid transaction, because the transaction keeps them on the same physical connection.

database.transaction(() -> {
  database.query("""
    CREATE TABLE #employee_insert_result (
      employee_id BIGINT NOT NULL
    )
    """)
    .execute();

  database.query("""
    INSERT INTO employee (name)
    OUTPUT inserted.employee_id INTO #employee_insert_result
    VALUES (:firstName), (:secondName)
    """)
    .bind("firstName", "Ada")
    .bind("secondName", "Grace")
    .execute();

  List<Long> employeeIds = database.query("""
    SELECT employee_id
    FROM #employee_insert_result
    ORDER BY employee_id
    """)
    .fetchList(Long.class);

  // Use employeeIds here.
});

Return Upsert Actions From MERGE

SQL Server MERGE statements require a trailing semicolon. If you need to know what happened, return $action with the affected row data.

public record MergeResult(String mergeAction, Long employeeId, String name) {}

List<MergeResult> results = database.query("""
  MERGE employee AS target
  USING (VALUES (:email, :name)) AS source(email, name)
  ON target.email = source.email
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (email, name) VALUES (source.email, source.name)
  OUTPUT $action AS merge_action, inserted.employee_id, inserted.name;
  """)
  .bind("email", "ada@example.com")
  .bind("name", "Ada")
  .executeForList(MergeResult.class);

Map datetimeoffset To Java Time Types

SQL Server datetimeoffset carries an offset. Pyranid maps it to OffsetDateTime and can also map it to the corresponding Instant.

OffsetDateTime eventAt = OffsetDateTime.parse("2020-11-01T01:30:15.123456700-04:00");

database.query("""
  INSERT INTO audit_event (audit_event_id, event_at)
  VALUES (:id, :eventAt)
  """)
  .bind("id", 1L)
  .bind("eventAt", eventAt)
  .execute();

OffsetDateTime stored = database.query("""
  SELECT event_at
  FROM audit_event
  WHERE audit_event_id = :id
  """)
  .bind("id", 1L)
  .fetchObject(OffsetDateTime.class)
  .orElseThrow();

Oracle

Request Generated Keys By Column Name

Oracle's default generated-key path can return a ROWID. Ask for the identity column explicitly.

Long employeeId = database.query("""
  INSERT INTO employee (name)
  VALUES (:name)
  """)
  .bind("name", "Ada")
  .executeReturningGeneratedKey(Long.class, "EMPLOYEE_ID")
  .orElseThrow();

Treat Empty Strings As Null

Oracle stores "" as NULL. Do not write cross-database code that expects an empty string to round-trip on Oracle.

database.query("""
  INSERT INTO employee_note (employee_id, note)
  VALUES (:employeeId, :note)
  """)
  .bind("employeeId", employeeId)
  .bind("note", "")
  .execute();

Boolean storedAsNull = database.query("""
  SELECT CASE WHEN note IS NULL THEN 1 ELSE 0 END
  FROM employee_note
  WHERE employee_id = :employeeId
  """)
  .bind("employeeId", employeeId)
  .fetchObject(Boolean.class)
  .orElseThrow();

Store UUIDs In RAW(16)

Pyranid binds Java UUID values as RFC-4122 bytes for Oracle, which is a good fit for RAW(16) columns.

UUID employeeId = UUID.fromString("f81d4fae-7dec-11d0-a765-00a0c91e6bf6");

database.query("""
  INSERT INTO employee (employee_id, name)
  VALUES (:employeeId, :name)
  """)
  .bind("employeeId", employeeId)
  .bind("name", "Ada")
  .execute();

MySQL And MariaDB

Use Generated Keys On MySQL

MySQL does not support INSERT ... RETURNING. Use JDBC-generated keys for auto-increment columns.

Long employeeId = database.query("""
  INSERT INTO employee (name)
  VALUES (:name)
  """)
  .bind("name", "Ada")
  .executeReturningGeneratedKey(Long.class)
  .orElseThrow();

Use RETURNING On MariaDB

MariaDB supports INSERT ... RETURNING, so you can map database-returned rows directly.

public record EmployeeRow(Long employeeId, String name) {}

EmployeeRow employee = database.query("""
  INSERT INTO employee (name)
  VALUES (:name)
  RETURNING employee_id, name
  """)
  .bind("name", "Ada")
  .executeForObject(EmployeeRow.class)
  .orElseThrow();

Keep MySQL Streams Self-Contained

MySQL streaming result sets keep the underlying connection busy until the stream is consumed and closed. Do all work that consumes the stream inside the callback, and avoid issuing another query on the same transaction connection while the stream is open.

List<Long> activeEmployeeIds = database.query("""
  SELECT employee_id
  FROM employee
  ORDER BY employee_id
  """)
  .fetchStream(Long.class, stream ->
    stream
      .filter(id -> id > 0)
      .limit(10_000)
      .toList());

Bind JSON As A JSON Parameter

Use Parameters::json(...) for JSON columns. Pyranid binds MySQL-family JSON as text, which avoids the character-set problems that can happen with generic binary-looking binds.

database.query("""
  INSERT INTO employee_profile (employee_id, profile)
  VALUES (:employeeId, :profile)
  """)
  .bind("employeeId", employeeId)
  .bind("profile", Parameters.json("{\"department\":\"engineering\"}"))
  .execute();

SQLite

Use RETURNING For Multi-Row Generated IDs

SQLite can return generated row IDs directly with RETURNING, which is clearer than relying on driver-specific generated-key labels.

List<Long> employeeIds = database.query("""
  INSERT INTO employee (name)
  VALUES (:firstName), (:secondName)
  RETURNING employee_id
  """)
  .bind("firstName", "Ada")
  .bind("secondName", "Grace")
  .executeForList(Long.class);

Store UUIDs As Text

SQLite has dynamic typing, so a TEXT UUID column is the most straightforward representation. Pyranid binds Java UUID values as strings for SQLite.

UUID employeeId = UUID.fromString("f81d4fae-7dec-11d0-a765-00a0c91e6bf6");

database.query("""
  INSERT INTO employee (employee_id, name)
  VALUES (:employeeId, :name)
  """)
  .bind("employeeId", employeeId)
  .bind("name", "Ada")
  .execute();

Store Exact Decimals As Text

SQLite numeric affinity can store decimal-looking values as floating point. If exact decimal precision matters, store the canonical string and map it back to BigDecimal.

BigDecimal amount = new BigDecimal("12345678901234567890.123456789012345678");

database.query("""
  INSERT INTO invoice (invoice_id, amount)
  VALUES (:invoiceId, :amount)
  """)
  .bind("invoiceId", 1L)
  .bind("amount", amount.toPlainString())
  .execute();

BigDecimal stored = database.query("""
  SELECT amount
  FROM invoice
  WHERE invoice_id = :invoiceId
  """)
  .bind("invoiceId", 1L)
  .fetchObject(BigDecimal.class)
  .orElseThrow();

Be Careful With In-Memory Databases

SQLite :memory: databases are scoped to a physical JDBC connection. A pool with multiple physical connections can produce multiple independent empty databases. For integration tests, a temporary file database is usually less surprising.

Path dbFile = Files.createTempFile("pyranid-", ".db");

SQLiteDataSource dataSource = new SQLiteDataSource();
dataSource.setUrl("jdbc:sqlite:" + dbFile);

Database database = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.SQLITE)
  .build();
Previous
Metrics Collection
Next
Javadoc