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 42.7.13 or newer, or the explicitly supported YugabyteDB smart driver, at runtime. Pyranid does not promise compatibility with arbitrary repackaged pgjdbc forks. 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 supported-driver 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 driver 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 the driver 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.

YugabyteDB

Pyranid explicitly supports YugabyteDB's com.yugabyte smart driver for PostgreSQL-specific parameters, exception metadata, and notification receive. Driver-specific types are resolved at runtime without static driver-type linkage, and compatible pool wrappers are unwrapped to the smart-driver API. Arbitrary repackaged pgjdbc forks are not part of this compatibility promise.

YugabyteDB provides LISTEN/NOTIFY as an Early Access feature in v2025.2.3 and later. It is disabled by default; enable ysql_yb_enable_listen_notify=true on both Masters and TServers. Pyranid cannot inspect that flag through JDBC. If the feature is disabled or absent in the current server version, its 0A000 response becomes an UnsupportedOperationException explaining that notifications are unavailable in the current version or configuration. Pyranid's advisory integration leg exercises enabled send/receive with YugabyteDB 2026.1.1.1 and smart driver 42.7.3-yb-4. See the YugabyteDB feature documentation.

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();

DuckDB

Map STRUCT Columns To Records

DuckDB STRUCT columns map onto Java records by attribute name, recursively - nested STRUCTs become nested records and STRUCT lists become record lists. A single-column STRUCT result maps directly to a record target. See SQL STRUCT Results for the full rules.

public record Person(String name, String emailAddress) {}

Optional<Person> person = database.query("""
  SELECT person
  FROM employee
  WHERE employee_id=:employeeId
  """)
  .bind("employeeId", 1L)
  .fetchObject(Person.class);

Bind STRUCT Values With An Explicit Type

Use Parameters::sqlStructOf(...) when a bound value must be a DuckDB STRUCT. Declare the complete SQL type and supply its attributes in declaration order.

database.query("""
  INSERT INTO person (person_id, details)
  VALUES (:personId, :details)
  """)
  .bind("personId", 1L)
  .bind("details", Parameters.sqlStructOf(
    "STRUCT(name VARCHAR, age INTEGER)",
    List.of("Ada", 36)))
  .execute();

For attributes represented by Instant, OffsetDateTime, ZonedDateTime, java.sql.Timestamp, or java.util.Date, the complete inline declaration is required. Pyranid uses it to distinguish TIMESTAMP from TIMESTAMPTZ and to apply the configured Database.Builder::timeZone(...) for zone-less timestamps. A named CREATE TYPE alias does not expose its attribute types through this JDBC binding path, so Pyranid fails fast for instant-bearing values rather than guessing. Named aliases remain usable for other attributes.

The list and SQL type must describe the same number of attributes. Use the Object[] factory when an attribute is null, because List.of(...) rejects null elements:

Parameters.sqlStructOf(
  "STRUCT(name VARCHAR, nickname VARCHAR)",
  new Object[] { "Ada", null });

Bind Multidimensional Arrays Recursively

Pyranid recursively materializes nested SqlArrayParameter values, and multidimensional binding is integration-verified on DuckDB. Represent each dimension with its own Parameters::sqlArrayOf(...). Each containing level's baseTypeName names its element type, so an outer VARCHAR[][] value uses VARCHAR[] while each inner array uses VARCHAR:

SqlArrayParameter<SqlArrayParameter<String>> matrix = Parameters.sqlArrayOf(
  "VARCHAR[]",
  List.of(
    Parameters.sqlArrayOf("VARCHAR", List.of("a", "b")),
    Parameters.sqlArrayOf("VARCHAR", List.of("c", "d"))));

database.query("INSERT INTO matrix_store (matrix) VALUES (:matrix)")
  .bind("matrix", matrix)
  .execute();

For elements represented by Instant, OffsetDateTime, ZonedDateTime, java.sql.Timestamp, or java.util.Date, use an explicit TIMESTAMP or TIMESTAMPTZ base type. A named type alias does not expose its underlying timestamp semantics through this JDBC binding path, so Pyranid fails fast for instant-bearing values rather than guessing.

Other JDBC drivers may impose different element-type or nesting rules; Pyranid does not currently claim portable multidimensional binding beyond DuckDB.

Use RETURNING With Sequences

DuckDB's JDBC driver has no generated-key support - Query::executeReturningGeneratedKey(...) fails fast with a clear DatabaseException. Use a sequence default plus RETURNING instead.

database.query("CREATE SEQUENCE employee_seq").execute();
database.query("""
  CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY DEFAULT nextval('employee_seq'),
    name VARCHAR NOT NULL
  )
  """).execute();

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

Cast Bound Vectors In Similarity Queries

Vector parameters bind as lists, and DuckDB's fixed-size array functions require the fixed-size ARRAY type. INSERT casts implicitly, but comparison functions need an explicit CAST of the bound parameter.

Optional<Document> mostSimilarDocument = database.query("""
  SELECT document_id, embedding
  FROM vector_embedding
  ORDER BY array_cosine_distance(embedding, CAST(:query AS FLOAT[1536]))
  LIMIT 1
  """)
  .bind("query", Parameters.vectorOfFloats(queryEmbedding))
  .fetchObject(Document.class);

Mind The Colon In Struct Literals

Named parameters work inside DuckDB list literals, so SELECT [:first, :second] binds as you would expect. Struct literals and list slices are the cases to watch: DuckDB uses : there too, and when the character after : can start an identifier, Pyranid parses it as a named parameter. Write a space after the colon - bound parameters still work - or use struct_pack(key := expr) / list_slice(...).

// {'name':name} would parse ':name' as a parameter; a space keeps the column reference,
// and ': :' binds a parameter inside a struct literal
Optional<Person> person = database.query("""
  SELECT {'name': name, 'email_address': :emailAddress} AS person
  FROM employee
  WHERE employee_id=:employeeId
  """)
  .bind("emailAddress", "ada@example.com")
  .bind("employeeId", 1L)
  .fetchObject(Person.class);

Retry Write-Write Conflicts

DuckDB uses optimistic concurrency control: concurrent writes to the same rows fail with a TransactionContext Error conflict rather than blocking. Pyranid classifies these as retryable serialization failures, so Database::transactionWithRetry(...) with a RetryPolicy handles them cleanly.

Prefer SQL LIMIT To maxRows

DuckDB's driver accepts Query::maxRows(...) but does not enforce it. Express row limits in SQL.

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  ORDER BY employee_id
  LIMIT :limit
  """)
  .bind("limit", 100)
  .fetchList(Employee.class);

Enable JDBC Streaming For Large Results

Query::fetchStream(...) keeps mapping callback-scoped, but the DuckDB JDBC driver materializes a result by default. Set its jdbc_stream_results connection property to opt into lazy result streaming.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:duckdb:analytics.duckdb");
config.addDataSourceProperty("jdbc_stream_results", "true");

Database database = Database.withDataSource(new HikariDataSource(config))
  .databaseType(DatabaseType.DUCK_DB)
  .build();

Long total = database.query("SELECT amount FROM sale")
  .fetchStream(Long.class, amounts -> amounts.mapToLong(Long::longValue).sum());

Consume the stream completely inside the callback. The result set, statement, and borrowed connection are closed when the callback returns.

Read Strings From Legacy Parquet Writers

Some legacy Parquet writers stored strings as binary columns without the UTF-8 annotation, so DuckDB correctly exposes them as BLOBs by default. For those files, opt into binary_as_string on the individual read_parquet(...) call:

List<LegacyRow> rows = database.query("""
  SELECT *
  FROM read_parquet(:path, binary_as_string = true)
  """)
  .bind("path", parquetPath.toString())
  .fetchList(LegacyRow.class);

Do not enable this broadly for modern files: it also interprets genuine unannotated binary data as text. See DuckDB's Parquet option reference.

Be Careful With In-Memory Databases

Like SQLite, a plain in-memory DuckDB (jdbc:duckdb: or jdbc:duckdb:memory:) is scoped to one physical JDBC connection, so a pool sees independent empty databases. Give the in-memory database a name to let pooled connections share one process-local instance:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:duckdb:memory:pyranid-tests");

Database database = Database.withDataSource(new HikariDataSource(config))
  .databaseType(DatabaseType.DUCK_DB)
  .build();

Use a unique label when tests need isolation. Every connection that reaches the same named or file-backed instance must request the same connection-time options; DuckDB applies those options when the first connection creates the instance and rejects later connections with inconsistent configuration. Configure the pool in one place. A named in-memory database disappears after its last connection closes unless the driver is configured to pin it.

For tests that need persistence across pool shutdowns, a temporary file is still less surprising:

Path dbFile = Files.createTempDirectory("pyranid-").resolve("test.duckdb");

// DuckDB's driver ships no DataSource implementation; any JDBC-URL-based one works
DataSource dataSource = new HikariDataSource(new HikariConfig() {{
  setJdbcUrl("jdbc:duckdb:" + dbFile);
}});

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

See DuckDB's connection and instance-cache documentation for the full URL and lifetime rules.

Keep Typed Raw JDBC Work Callback-Scoped

Pyranid's ordinary Database::useRawConnection(...) overload exposes a guarded standard JDBC connection. When a DuckDB feature requires DuckDBConnection, request that type explicitly with the typed overload:

database.useRawConnection(DuckDBConnection.class, connection -> {
  // DuckDB-specific work goes here.
  return Optional.empty();
});

The typed overload is deliberately unguarded. Pyranid supplies the active transaction's connection when one exists; otherwise it borrows the connection only for this callback. Each driver-extension API's own transaction rules still apply. Do not close, commit, roll back, retain, or change connection-wide state on the connection. Close every derived statement, result set, Appender, Arrow reader, and native result before returning. These APIs also require the DuckDB JDBC types on your compile classpath.

Bulk-Load With The Appender

DuckDB's Appender avoids SQL parsing and per-row JDBC overhead for large inserts.

database.useRawConnection(DuckDBConnection.class, connection -> {
  try (DuckDBAppender appender =
         connection.createAppender(DuckDBConnection.DEFAULT_SCHEMA, "employee")) {
    appender.beginRow()
      .append(1L)
      .append("Ada")
      .endRow();
    appender.beginRow()
      .append(2L)
      .append("Grace")
      .endRow();

    appender.flush();
  }

  return Optional.empty();
});

close() also flushes, but an explicit flush() reports buffered constraint and conversion failures before cleanup and before Pyranid returns the connection.

DuckDB JDBC starts its native transaction lazily when the first ordinary JDBC statement executes; creating an Appender does not trigger that step. If Appender is the first operation inside a Pyranid transaction, prime the transaction with an ordinary query before creating it:

database.transaction(() -> {
  database.query("SELECT 1").fetchObject(Integer.class).orElseThrow();

  database.useRawConnection(DuckDBConnection.class, connection -> {
    try (DuckDBAppender appender = connection.createAppender("employee")) {
      appender.beginRow()
        .append(3L)
        .append("Katherine")
        .endRow();
      appender.flush();
    }

    return Optional.empty();
  });
});

Without a preceding JDBC statement, a first-operation Appender can autocommit its flushed rows even though Pyranid supplied the transaction's connection. Any real Pyranid query in the transaction can serve as the primer; SELECT 1 is only the minimal example.

Exchange Arrow Data Inside The Callback

The DuckDB driver can export a DuckDBResultSet as an Apache Arrow stream. Arrow is an optional dependency; add the Arrow Java modules your application uses and keep the allocator, reader, result set, and statement nested inside the raw callback.

database.useRawConnection(DuckDBConnection.class, connection -> {
  try (DuckDBPreparedStatement statement = connection.prepare("SELECT * FROM employee");
       DuckDBResultSet resultSet = (DuckDBResultSet) statement.executeQuery();
       RootAllocator allocator = new RootAllocator();
       ArrowReader reader = (ArrowReader) resultSet.arrowExportStream(allocator, 2_048)) {
    while (reader.loadNextBatch()) {
      consume(reader.getVectorSchemaRoot());
    }
  }

  return Optional.empty();
});

For Arrow import, allocate and export an ArrowArrayStream, call connection.registerArrowStream(name, stream), and finish every query that reads the registered name before closing the stream or leaving the callback. See DuckDB's Arrow export and import examples.

Consume Columnar Chunks Before Advancing

For basic scalar results, DuckDBPreparedStatement::query() exposes column vectors without the per-row ResultSet layer:

database.useRawConnection(DuckDBConnection.class, connection -> {
  try (DuckDBPreparedStatement statement = connection.prepare("SELECT ? AS value")) {
    statement.setInt(1, 42);

    try (DuckDBChunkedResult result = statement.query()) {
      while (result.nextChunk()) {
        DuckDBDataChunkReader chunk = result.chunk();
        DuckDBReadableVector values = chunk.vector(0);

        for (long row = 0; row < chunk.rowCount(); row++)
          consume(values.getInt(row));
      }
    }
  }

  return Optional.empty();
});

The current chunk and its vectors become invalid on the next nextChunk() call or when the result closes; never retain them. The driver currently supports prepared statements and basic scalar types only, not LIST or STRUCT results. For most application queries, Pyranid's normal mapping and Query::fetchStream(...) remain simpler.

Profile And Monitor On The Same Connection

Portable timing, timeout, and cancellation are available through Pyranid's metrics, Query::queryTimeout(...), and standard JDBC cancellation. EXPLAIN ANALYZE also works as ordinary DuckDB SQL. Use the driver API when you need its formatted profile for the most recent query:

Optional<String> profile = database.useRawConnection(DuckDBConnection.class, connection -> {
  try (Statement statement = connection.createStatement()) {
    statement.execute("PRAGMA enable_profiling = 'json'");

    try (ResultSet resultSet =
           statement.executeQuery("SELECT count(*) FROM range(1_000_000)")) {
      resultSet.next();
    }

    return Optional.of(connection.getProfilingInformation(ProfilerPrintFormat.JSON));
  } finally {
    try (Statement statement = connection.createStatement()) {
      statement.execute("PRAGMA disable_profiling");
    }
  }
});

Profiling state and “most recent query” are connection-local, which is why setup, query, retrieval, and reset belong in one callback. For live progress, run the query on one thread and poll DuckDBPreparedStatement::getQueryProgress() from another; join the query thread and close its result set and statement before returning. Do not run a second query on that connection while polling. See DuckDB's profiling and progress guide.

Register Java Functions Deliberately

The driver can register Java scalar and table functions in a DuckDB database instance:

database.useRawConnection(DuckDBConnection.class, connection -> {
  DuckDBFunctions.scalarFunction()
    .withName("java_add_one")
    .withParameter(int.class)
    .withReturnType(int.class)
    .withIntFunction(value -> value + 1)
    .register(connection);

  return Optional.empty();
});

Register functions once during controlled application/database initialization, before preparing SQL that calls them; do not register on every query. The registered Java callback can outlive the raw callback, so it must not capture the connection or any callback-scoped JDBC object. Table-function state cleanup may run on DuckDB native worker threads and must be fast, idempotent, thread-safe, and must not issue SQL on the same connection. The current UDF API does not support composite LIST or STRUCT parameters or results. See DuckDB's Java function guide for vectorized and table-function contracts.

Previous
Metrics Collection
Next
Javadoc