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
Specify The Database Type
Specify the DatabaseType when creating each Database builder so dialect-specific binding and mapping match the database product. Both the data source and type are required and must be non-null.
Database database = Database.with(dataSource, DatabaseType.SQL_SERVER)
.build();
Database database = Database.with(dataSource, 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);
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);
Execute A List Of Complete Commands
A Query represents one logical SQL statement. It is not a script runner: the current SQLite driver executes only the first statement of a multi-statement string passed to query(...).execute(). Other drivers have different rules. For application-owned commands that do not return rows, keep each complete command separate and execute them on one transaction connection:
List<String> commands = List.of(
"INSERT INTO item (id, label) VALUES (1, 'literal;semicolon')",
"INSERT INTO item (id, label) VALUES (2, 'second')"
);
database.transaction(() -> database.useRawConnection(connection -> {
try (java.sql.Statement statement = connection.createStatement()) {
for (String command : commands) statement.executeUpdate(command);
}
return Optional.empty();
}));
List<String> commands = List.of(
"INSERT INTO item (id, label) VALUES (1, 'literal;semicolon')",
"INSERT INTO item (id, label) VALUES (2, 'second')"
);
database.transaction(() -> database.useRawConnection(connection -> {
try (java.sql.Statement statement = connection.createStatement()) {
for (String command : commands) statement.executeUpdate(command);
}
return Optional.empty();
}));
Use this as a top-level operation: transaction() starts an independent transaction even when another transaction is visible. Use the join/savepoint helper when ambient participation is required. This recipe assumes the table already exists. If a command fails, let its exception escape so Pyranid can roll back earlier transactional DML. DDL transactionality depends on the database. Do not split arbitrary SQL on semicolons: literals, comments, PostgreSQL function bodies and SQLite trigger bodies can contain them. Use a migration tool or a parser for the specific dialect when the input is an actual script file. A complete SQLite CREATE TRIGGER ... BEGIN ...; ...; END belongs in one list element.
These commands are trusted SQL, not user input. Use a raw PreparedStatement with JDBC setters for variable values, and close any results inside the callback. Do not close the supplied connection or call its transaction methods. Pyranid does not add a semicolon-based rejection to query(), since that would reject valid single statements containing procedural bodies.
PostgreSQL
Store Fixed Elapsed Intervals As Duration
PostgreSQL stores interval months, days and elapsed time separately. A calendar day can differ from 24 elapsed hours across a daylight saving transition. Choose the domain semantics first; Duration is appropriate for fixed elapsed time. See PostgreSQL interval storage.
For a fixed-time column, reject calendar fields at the schema boundary:
CREATE TABLE elapsed_item (
id INTEGER PRIMARY KEY,
elapsed INTERVAL CHECK (
EXTRACT(YEAR FROM elapsed) = 0 AND
EXTRACT(MONTH FROM elapsed) = 0 AND
EXTRACT(DAY FROM elapsed) = 0
)
);
CREATE TABLE elapsed_item (
id INTEGER PRIMARY KEY,
elapsed INTERVAL CHECK (
EXTRACT(YEAR FROM elapsed) = 0 AND
EXTRACT(MONTH FROM elapsed) = 0 AND
EXTRACT(DAY FROM elapsed) = 0
)
);
The following application extensions bind decimal seconds and read a numeric projection. They avoid parsing PGInterval or output affected by IntervalStyle. Imports are from com.pyranid, java.sql, java.math, java.time and java.util.
final class DurationBinder implements CustomParameterBinder {
@Override public Boolean appliesTo(TargetType type) {
return type.matchesClass(Duration.class);
}
@Override public BindingResult bind(StatementContext<?> context,
PreparedStatement statement, Integer index, Object value) throws SQLException {
Duration duration = (Duration) value;
if (duration.getNano() % 1_000 != 0) {
throw new SQLException("Round explicitly to PostgreSQL microsecond precision", "22003");
}
BigDecimal seconds = BigDecimal.valueOf(duration.getSeconds())
.add(BigDecimal.valueOf(duration.getNano(), 9));
statement.setString(index, seconds.toPlainString() + " seconds");
return BindingResult.handled();
}
}
final class DurationSecondsMapper implements CustomColumnMapper {
@Override public Boolean appliesTo(TargetType type) {
return type.matchesClass(Duration.class);
}
@Override public MappingResult map(StatementContext<?> context, ResultSet rows,
Object value, TargetType target, Integer index, String label,
InstanceProvider provider) throws SQLException {
if (!(value instanceof BigDecimal seconds)) {
throw new SQLException("Duration requires numeric seconds from a fixed-time interval", "22023");
}
try {
BigDecimal[] parts = seconds.divideAndRemainder(BigDecimal.ONE);
return MappingResult.of(Duration.ofSeconds(parts[0].longValueExact(),
parts[1].movePointRight(9).longValueExact()));
} catch (ArithmeticException failure) {
throw new SQLException("Seconds cannot be represented exactly as Duration", "22003", failure);
}
}
}
final class DurationBinder implements CustomParameterBinder {
@Override public Boolean appliesTo(TargetType type) {
return type.matchesClass(Duration.class);
}
@Override public BindingResult bind(StatementContext<?> context,
PreparedStatement statement, Integer index, Object value) throws SQLException {
Duration duration = (Duration) value;
if (duration.getNano() % 1_000 != 0) {
throw new SQLException("Round explicitly to PostgreSQL microsecond precision", "22003");
}
BigDecimal seconds = BigDecimal.valueOf(duration.getSeconds())
.add(BigDecimal.valueOf(duration.getNano(), 9));
statement.setString(index, seconds.toPlainString() + " seconds");
return BindingResult.handled();
}
}
final class DurationSecondsMapper implements CustomColumnMapper {
@Override public Boolean appliesTo(TargetType type) {
return type.matchesClass(Duration.class);
}
@Override public MappingResult map(StatementContext<?> context, ResultSet rows,
Object value, TargetType target, Integer index, String label,
InstanceProvider provider) throws SQLException {
if (!(value instanceof BigDecimal seconds)) {
throw new SQLException("Duration requires numeric seconds from a fixed-time interval", "22023");
}
try {
BigDecimal[] parts = seconds.divideAndRemainder(BigDecimal.ONE);
return MappingResult.of(Duration.ofSeconds(parts[0].longValueExact(),
parts[1].movePointRight(9).longValueExact()));
} catch (ArithmeticException failure) {
throw new SQLException("Seconds cannot be represented exactly as Duration", "22003", failure);
}
}
}
Configure these extensions only for queries using that representation:
PreparedStatementBinder binder = PreparedStatementBinder.withCustomParameterBinders(
List.of(new DurationBinder()));
ResultSetMapper mapper = ResultSetMapper.withCustomColumnMappers(
List.of(new DurationSecondsMapper())).build();
database.query("INSERT INTO elapsed_item VALUES (:id, CAST(:duration AS INTERVAL))")
.preparedStatementBinder(binder)
.bind("id", 1).bind("duration", Duration.ofSeconds(1, 123_456_000))
.execute();
Duration elapsed = database.query("SELECT EXTRACT(EPOCH FROM elapsed) FROM elapsed_item WHERE id=1")
.resultSetMapper(mapper).fetchObject(Duration.class).orElseThrow();
PreparedStatementBinder binder = PreparedStatementBinder.withCustomParameterBinders(
List.of(new DurationBinder()));
ResultSetMapper mapper = ResultSetMapper.withCustomColumnMappers(
List.of(new DurationSecondsMapper())).build();
database.query("INSERT INTO elapsed_item VALUES (:id, CAST(:duration AS INTERVAL))")
.preparedStatementBinder(binder)
.bind("id", 1).bind("duration", Duration.ofSeconds(1, 123_456_000))
.execute();
Duration elapsed = database.query("SELECT EXTRACT(EPOCH FROM elapsed) FROM elapsed_item WHERE id=1")
.resultSetMapper(mapper).fetchObject(Duration.class).orElseThrow();
Nulls follow normal binder/mapper handling. Sub-microsecond input fails instead of silently rounding, and values outside PostgreSQL's interval range can still fail at the server. Hours above 24 remain elapsed hours; do not call justify_hours on this column. The conversion is independent of the session's IntervalStyle setting.
Do not apply EXTRACT(EPOCH) to arbitrary calendar intervals and call the result a lossless Duration. Keep years/months/days as a separate calendar representation, such as an explicitly limited Period model. For example, adding PostgreSQL INTERVAL '1 day' across a New York spring daylight-saving transition advances 23 elapsed hours, while adding INTERVAL '24 hours' advances 24. This recipe adds no built-in Duration or Period conversion.
Call Procedures With OUT Parameters
Given a PostgreSQL procedure with two input parameters and two output parameters:
CREATE PROCEDURE app.calculate(IN amount INTEGER, IN label TEXT,
OUT doubled INTEGER, OUT echoed TEXT)
LANGUAGE plpgsql AS $$
BEGIN
doubled := amount * 2;
echoed := label;
END
$$;
CREATE PROCEDURE app.calculate(IN amount INTEGER, IN label TEXT,
OUT doubled INTEGER, OUT echoed TEXT)
LANGUAGE plpgsql AS $$
BEGIN
doubled := amount * 2;
echoed := label;
END
$$;
Native CALL returns its OUT values as a row. Include literal NULL placeholders for the OUT arguments, and bind the inputs:
public record CallOutput(Integer doubled, String echoed) {}
CallOutput output = database.query("CALL app.calculate(:amount, :label, NULL, NULL)")
.bind("amount", 21).bind("label", "example")
.fetchObject(CallOutput.class).orElseThrow();
public record CallOutput(Integer doubled, String echoed) {}
CallOutput output = database.query("CALL app.calculate(:amount, :label, NULL, NULL)")
.bind("amount", 21).bind("label", "example")
.fetchObject(CallOutput.class).orElseThrow();
The placeholders are part of PostgreSQL CALL. Native SQL does not require pgjdbc's escape-call setting. If you use JDBC {call ...} syntax through a raw CallableStatement, configure the data source before creating the Database:
source.setEscapeSyntaxCallMode("callIfNoReturn"); // PGSimpleDataSource
Database procedureDatabase = Database.with(source, DatabaseType.POSTGRESQL).build();
CallOutput output = procedureDatabase.useRawConnection(connection -> {
try (CallableStatement call = connection.prepareCall("{call app.calculate(?, ?, ?, ?)}")) {
call.setInt(1, 21);
call.setString(2, "example");
call.registerOutParameter(3, Types.INTEGER);
call.registerOutParameter(4, Types.VARCHAR);
call.execute();
int doubled = call.getInt(3);
Integer nullableDoubled = call.wasNull() ? null : doubled;
return Optional.of(new CallOutput(nullableDoubled, call.getString(4)));
}
}).orElseThrow();
source.setEscapeSyntaxCallMode("callIfNoReturn"); // PGSimpleDataSource
Database procedureDatabase = Database.with(source, DatabaseType.POSTGRESQL).build();
CallOutput output = procedureDatabase.useRawConnection(connection -> {
try (CallableStatement call = connection.prepareCall("{call app.calculate(?, ?, ?, ?)}")) {
call.setInt(1, 21);
call.setString(2, "example");
call.registerOutParameter(3, Types.INTEGER);
call.registerOutParameter(4, Types.VARCHAR);
call.execute();
int doubled = call.getInt(3);
Integer nullableDoubled = call.wasNull() ? null : doubled;
return Optional.of(new CallOutput(nullableDoubled, call.getString(4)));
}
}).orElseThrow();
The default pgjdbc escape mode targets functions; pgjdbc's procedure guidance explains the distinction. A procedure invoked inside a Pyranid transaction must leave transaction control to Pyranid. Functions returning ordinary sets are simpler: use SELECT * FROM app.function(:input) with the usual fetch methods.
Fetch A Refcursor Within Its Transaction
An application-owned function can open a fixed cursor name and return that name:
CREATE FUNCTION app.open_cursor(label TEXT) RETURNS refcursor
LANGUAGE plpgsql AS $$
DECLARE cursor_name refcursor := 'pyranid_recipe_cursor';
BEGIN
OPEN cursor_name FOR
SELECT item, label FROM generate_series(1, 2) AS rows(item) ORDER BY item;
RETURN cursor_name;
END
$$;
CREATE FUNCTION app.open_cursor(label TEXT) RETURNS refcursor
LANGUAGE plpgsql AS $$
DECLARE cursor_name refcursor := 'pyranid_recipe_cursor';
BEGIN
OPEN cursor_name FOR
SELECT item, label FROM generate_series(1, 2) AS rows(item) ORDER BY item;
RETURN cursor_name;
END
$$;
Open and fetch on the same transaction connection before commit. The existing result mapper can map each current JDBC row; it does not advance the cursor for you:
public record CursorRow(int item, String label) {}
ResultSetMapper mapper = ResultSetMapper.withDefaultConfiguration();
InstanceProvider instances = new InstanceProvider() {};
String fetchSql = "FETCH ALL FROM pyranid_recipe_cursor";
List<CursorRow> rows = database.transaction(() -> database.useRawConnection(connection -> {
try (PreparedStatement open = connection.prepareStatement("SELECT app.open_cursor(?)")) {
open.setString(1, "example");
try (ResultSet cursor = open.executeQuery()) {
if (!cursor.next() || !"pyranid_recipe_cursor".equals(cursor.getString(1))) {
throw new SQLException("Unexpected application cursor name");
}
}
}
StatementContext<CursorRow> context = StatementContext.<CursorRow>with(
com.pyranid.Statement.of("recipe.fetch-cursor", fetchSql), database)
.resultSetRowType(CursorRow.class).build();
List<CursorRow> values = new ArrayList<>();
try (java.sql.Statement fetch = connection.createStatement();
ResultSet result = fetch.executeQuery(fetchSql)) {
while (result.next()) {
values.add(mapper.map(context, result, CursorRow.class, instances).orElseThrow());
}
}
try (java.sql.Statement close = connection.createStatement()) {
close.execute("CLOSE pyranid_recipe_cursor");
}
return Optional.of(List.copyOf(values));
})).orElseThrow();
public record CursorRow(int item, String label) {}
ResultSetMapper mapper = ResultSetMapper.withDefaultConfiguration();
InstanceProvider instances = new InstanceProvider() {};
String fetchSql = "FETCH ALL FROM pyranid_recipe_cursor";
List<CursorRow> rows = database.transaction(() -> database.useRawConnection(connection -> {
try (PreparedStatement open = connection.prepareStatement("SELECT app.open_cursor(?)")) {
open.setString(1, "example");
try (ResultSet cursor = open.executeQuery()) {
if (!cursor.next() || !"pyranid_recipe_cursor".equals(cursor.getString(1))) {
throw new SQLException("Unexpected application cursor name");
}
}
}
StatementContext<CursorRow> context = StatementContext.<CursorRow>with(
com.pyranid.Statement.of("recipe.fetch-cursor", fetchSql), database)
.resultSetRowType(CursorRow.class).build();
List<CursorRow> values = new ArrayList<>();
try (java.sql.Statement fetch = connection.createStatement();
ResultSet result = fetch.executeQuery(fetchSql)) {
while (result.next()) {
values.add(mapper.map(context, result, CursorRow.class, instances).orElseThrow());
}
}
try (java.sql.Statement close = connection.createStatement()) {
close.execute("CLOSE pyranid_recipe_cursor");
}
return Optional.of(List.copyOf(values));
})).orElseThrow();
All JDBC resources close inside the callback; only detached row values escape. If open/fetch/mapping fails, let the failure escape so rollback closes the server cursor. The fixed name supports one such cursor at a time in a transaction. Do not interpolate an untrusted returned cursor name into SQL; applications needing dynamic names must quote identifiers correctly. JDBC ? setters above are raw JDBC, while Pyranid query() accepts named parameters.
A normal refcursor expires at transaction end, so opening it in autocommit and fetching later fails. FETCH ALL materializes the complete result here; it is not a bounded-memory streaming recipe. Prefer a set-returning function and Query::fetchStream(...) (see streaming queries) for that use case. Use query() for custom binders that allocate JDBC resources, so Pyranid can manage their cleanup. This raw recipe uses ordinary JDBC setters and closes its own resources.
Set Transaction-Local Tenant Context
For PostgreSQL row-level security, use an application role that is neither the table owner nor a superuser and does not have BYPASSRLS. Grant only the needed table operations. Owners ordinarily bypass policies, and superusers/BYPASSRLS roles bypass them even with FORCE ROW LEVEL SECURITY; test with the actual application role. RLS does not cover TRUNCATE. See PostgreSQL row security.
For a table app.account(account_id, org_id UUID NOT NULL, balance), an owner-run migration can install the following policies. Apply the migration before granting application access:
CREATE FUNCTION app.current_org_id() RETURNS uuid
LANGUAGE SQL STABLE AS $$
SELECT NULLIF(pg_catalog.current_setting('app.org_id', true), '')::uuid
$$;
ALTER TABLE app.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.account FORCE ROW LEVEL SECURITY;
CREATE POLICY application_access ON app.account TO app_user
USING (true) WITH CHECK (true);
CREATE POLICY tenant_isolation ON app.account AS RESTRICTIVE TO app_user
USING (org_id = app.current_org_id())
WITH CHECK (org_id = app.current_org_id());
CREATE FUNCTION app.current_org_id() RETURNS uuid
LANGUAGE SQL STABLE AS $$
SELECT NULLIF(pg_catalog.current_setting('app.org_id', true), '')::uuid
$$;
ALTER TABLE app.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.account FORCE ROW LEVEL SECURITY;
CREATE POLICY application_access ON app.account TO app_user
USING (true) WITH CHECK (true);
CREATE POLICY tenant_isolation ON app.account AS RESTRICTIVE TO app_user
USING (org_id = app.current_org_id())
WITH CHECK (org_id = app.current_org_id());
The permissive policy supplies application access; the restrictive policy adds the tenant condition to it. Missing or empty context yields no matching tenant, and invalid UUID text fails. The function remains an invoker function. Keep this schema and its functions under migration ownership, and grant the application role schema usage and the required table/function permissions.
Given an authorized organization identifier (UUID organizationId) and a public Account row type, initialize context inside the transaction that performs the work:
List<Account> accounts = database.transaction(() -> {
database.query("SELECT pg_catalog.set_config('app.org_id', :org, true)")
.bind("org", organizationId.toString())
.fetchObject(String.class)
.orElseThrow();
database.query("SET LOCAL lock_timeout = '2s'").execute();
database.query("SET LOCAL statement_timeout = '5s'").execute();
return Optional.of(database.query("""
SELECT account_id, org_id, balance
FROM app.account
ORDER BY account_id
""").fetchList(Account.class));
}).orElseThrow();
List<Account> accounts = database.transaction(() -> {
database.query("SELECT pg_catalog.set_config('app.org_id', :org, true)")
.bind("org", organizationId.toString())
.fetchObject(String.class)
.orElseThrow();
database.query("SET LOCAL lock_timeout = '2s'").execute();
database.query("SET LOCAL statement_timeout = '5s'").execute();
return Optional.of(database.query("""
SELECT account_id, org_id, balance
FROM app.account
ORDER BY account_id
""").fetchList(Account.class));
}).orElseThrow();
set_config(..., true) limits the setting to the current transaction. Use fetchObject because the function returns a row; keep setting names and timeout SQL fixed, and bind the tenant as text. Repeat initialization inside every retry attempt. The UUID must come from trusted authorization: a custom setting is mutable SQL session state, not an authentication mechanism.
A setter in autocommit finishes before the next query and cannot establish context for that query. An independent nested transaction uses another connection and needs its own context. A rollback to an earlier savepoint also restores the preceding setting; SET LOCAL describes those lifetime rules. Keep different tenants out of one shared transaction.
Completion hooks run after their transaction ends. Top-level hooks cannot retain that transaction's local tenant; a nested hook can instead see an enclosing transaction restored on the thread. Establish the required transaction/context explicitly rather than assuming the completed scope's tenant. Keep the tenant context explicit whenever a callback opens another transaction or changes savepoint scope.
Retrieve IDs From Bulk Inserts
For PostgreSQL, return actual IDs and correlation fields directly from one statement. For example, a public record Inserted(long id, String label) {} can receive every inserted row:
List<String> labels = List.of("first", "second", "third");
List<Inserted> inserted = database.query("""
INSERT INTO item (label)
SELECT input.label FROM unnest(:labels::text[]) AS input(label)
RETURNING id, label
""")
.bind("labels", Parameters.sqlArrayOf("text", labels))
.executeForList(Inserted.class);
List<String> labels = List.of("first", "second", "third");
List<Inserted> inserted = database.query("""
INSERT INTO item (label)
SELECT input.label FROM unnest(:labels::text[]) AS input(label)
RETURNING id, label
""")
.bind("labels", Parameters.sqlArrayOf("text", labels))
.executeForList(Inserted.class);
An empty SQL array inserts no rows and returns an empty list. A fixed-size multi-values statement works too:
List<Inserted> inserted = database.query("""
INSERT INTO item (label) VALUES (:first), (:second)
RETURNING id, label
""")
.bind("first", "first")
.bind("second", "second")
.executeForList(Inserted.class);
List<Inserted> inserted = database.query("""
INSERT INTO item (label) VALUES (:first), (:second)
RETURNING id, label
""")
.bind("first", "first")
.bind("second", "second")
.executeForList(Inserted.class);
Return a unique application correlation value when it matters which input produced an ID. Do not infer an ID range or depend on result order. Returned IDs alone do not establish that an enclosing transaction committed.
For a PostgreSQL JDBC generated-key batch, keep the JDBC resources inside useRawConnection and request the key column explicitly:
List<Long> ids = database.transaction(() -> database.useRawConnection(connection -> {
try (PreparedStatement statement = connection.prepareStatement(
"INSERT INTO item (label) VALUES (?)", new String[]{"id"})) {
for (String label : labels) {
statement.setString(1, label);
statement.addBatch();
}
statement.executeBatch();
List<Long> keys = new ArrayList<>();
try (ResultSet rows = statement.getGeneratedKeys()) {
while (rows.next()) keys.add(rows.getLong(1));
}
return Optional.of(List.copyOf(keys));
}
})).orElseThrow();
List<Long> ids = database.transaction(() -> database.useRawConnection(connection -> {
try (PreparedStatement statement = connection.prepareStatement(
"INSERT INTO item (label) VALUES (?)", new String[]{"id"})) {
for (String label : labels) {
statement.setString(1, label);
statement.addBatch();
}
statement.executeBatch();
List<Long> keys = new ArrayList<>();
try (ResultSet rows = statement.getGeneratedKeys()) {
while (rows.next()) keys.add(rows.getLong(1));
}
return Optional.of(List.copyOf(keys));
}
})).orElseThrow();
This example uses one PostgreSQL INSERT per batch group. Driver key behavior varies; it does not define general row-to-group correlation for arbitrary statements such as INSERT ... SELECT. The example uses ordinary JDBC binding and cleanup. Pyranid's executeBatch returns update counts and does not expose key rows; statements passed to it must not return RETURNING/OUTPUT result sets.
Bind Native ENUM Values
Pyranid binds a Java enum's name() as text. For a PostgreSQL native enum, cast the parameter to the actual enum type in both writes and predicates. For example, assume orders.status has type app.order_status with labels NEW and SHIPPED:
enum OrderStatus { NEW, SHIPPED }
database.query("""
INSERT INTO orders (order_id, status)
VALUES (:id, CAST(:status AS app.order_status))
""")
.bind("id", 42L)
.bind("status", OrderStatus.NEW)
.execute();
OrderStatus status = database.query("""
SELECT status FROM orders
WHERE order_id = :id AND status = CAST(:status AS app.order_status)
""")
.bind("id", 42L)
.bind("status", OrderStatus.NEW)
.fetchObject(OrderStatus.class)
.orElseThrow();
enum OrderStatus { NEW, SHIPPED }
database.query("""
INSERT INTO orders (order_id, status)
VALUES (:id, CAST(:status AS app.order_status))
""")
.bind("id", 42L)
.bind("status", OrderStatus.NEW)
.execute();
OrderStatus status = database.query("""
SELECT status FROM orders
WHERE order_id = :id AND status = CAST(:status AS app.order_status)
""")
.bind("id", 42L)
.bind("status", OrderStatus.NEW)
.fetchObject(OrderStatus.class)
.orElseThrow();
The same cast works for null parameters and batch groups. Enum labels must match Java names, including case; an overridden toString() does not change default binding. An uncast varchar parameter is not a native enum value, so ordinary inserts/comparisons can fail even though literal SQL works. Keep the enum type name fixed in the SQL; parameter binding supplies values, not SQL identifiers. This recipe leaves text-column enum binding and connection-wide string typing unchanged.
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.with(listenerDataSource, DatabaseType.POSTGRESQL)
.build();
Database listenerDatabase = Database.with(listenerDataSource, 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 supports YugabyteDB's com.yugabyte smart driver for PostgreSQL-specific parameters, exception metadata, and notification receive, including compatible connection-pool wrappers. Supply the driver on your application's runtime classpath. Arbitrary repackaged pgjdbc forks are unsupported.
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. 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);
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 IDs in a table variable and read them in the same SQL batch. SET NOCOUNT ON suppresses intermediate update counts so the final SELECT supplies the result set. This assumes the trigger does not emit its own result sets.
List<Long> employeeIds = database.query("""
SET NOCOUNT ON;
DECLARE @employee_insert_result TABLE (
employee_id BIGINT NOT NULL
);
INSERT INTO employee (name)
OUTPUT inserted.employee_id INTO @employee_insert_result
VALUES (:firstName), (:secondName);
SELECT employee_id
FROM @employee_insert_result
ORDER BY employee_id;
""")
.bind("firstName", "Ada")
.bind("secondName", "Grace")
.executeForList(Long.class);
List<Long> employeeIds = database.query("""
SET NOCOUNT ON;
DECLARE @employee_insert_result TABLE (
employee_id BIGINT NOT NULL
);
INSERT INTO employee (name)
OUTPUT inserted.employee_id INTO @employee_insert_result
VALUES (:firstName), (:secondName);
SELECT employee_id
FROM @employee_insert_result
ORDER BY employee_id;
""")
.bind("firstName", "Ada")
.bind("secondName", "Grace")
.executeForList(Long.class);
Run this one query inside database.transaction(...) when it belongs to a larger atomic operation. The insert and trigger changes roll back with that transaction; returned IDs do not prove that it committed. OUTPUT values are produced before AFTER triggers run, so do not treat returned non-ID columns as the final values after a trigger changes them.
Do not split CREATE TABLE #employee_insert_result, the insert and the read across separate prepared queries. The Microsoft driver's normal prepared execution creates a dynamic SQL scope; a local temp table created there can be dropped when that scope ends, even inside a transaction that keeps one physical connection. Variables are batch-scoped, so the declaration, write and read above deliberately stay together.
Configure Transaction Isolation Before The Work
Use Pyranid's transaction options for supported JDBC isolation levels:
TransactionOptions options = TransactionOptions
.withIsolation(TransactionIsolation.SERIALIZABLE)
.build();
database.transaction(options, () -> {
Long employeeCount = database.query("SELECT COUNT_BIG(*) FROM employee")
.fetchObject(Long.class)
.orElseThrow();
// Use employeeCount within this transaction.
});
TransactionOptions options = TransactionOptions
.withIsolation(TransactionIsolation.SERIALIZABLE)
.build();
database.transaction(options, () -> {
Long employeeCount = database.query("SELECT COUNT_BIG(*) FROM employee")
.fetchObject(Long.class)
.orElseThrow();
// Use employeeCount within this transaction.
});
A separate database.query("SET TRANSACTION ISOLATION LEVEL SNAPSHOT").execute() does not configure later prepared queries reliably. SQL Server restores SET options after a dynamic SQL batch returns; using the same connection does not combine the execution scopes. Session settings have option-specific scope; this example concerns transaction isolation.
TransactionIsolation has no SNAPSHOT value. For transaction-level snapshot isolation, first have the database administrator enable ALLOW_SNAPSHOT_ISOLATION on every accessed database. With Microsoft JDBC 13.6 or later, configure an existing, dedicated SQLServerDataSource before it creates connections:
snapshotDataSource.setDefaultTransactionIsolation("SNAPSHOT");
Database snapshotDatabase = Database
.with(snapshotDataSource, DatabaseType.SQL_SERVER)
.build();
snapshotDatabase.transaction(
TransactionOptions.withIsolation(TransactionIsolation.DEFAULT).build(),
() -> {
Long employeeCount = snapshotDatabase.query("SELECT COUNT_BIG(*) FROM employee")
.fetchObject(Long.class)
.orElseThrow();
// Further reads in this transaction use the same snapshot.
});
snapshotDataSource.setDefaultTransactionIsolation("SNAPSHOT");
Database snapshotDatabase = Database
.with(snapshotDataSource, DatabaseType.SQL_SERVER)
.build();
snapshotDatabase.transaction(
TransactionOptions.withIsolation(TransactionIsolation.DEFAULT).build(),
() -> {
Long employeeCount = snapshotDatabase.query("SELECT COUNT_BIG(*) FROM employee")
.fetchObject(Long.class)
.orElseThrow();
// Further reads in this transaction use the same snapshot.
});
The driver's defaultTransactionIsolation=SNAPSHOT URL property is equivalent. DEFAULT preserves that connection's configured isolation; an explicit Pyranid JDBC isolation option replaces it for the transaction. Ensure any pool preserves the configured isolation on checkout. The database option READ_COMMITTED_SNAPSHOT is a different, statement-level read-committed mode, and does not turn a transaction into SNAPSHOT isolation.
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);
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();
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();
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();
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 RAW(16) columns. Positively identified character targets receive canonical 36-character UUID text. When parameter metadata cannot identify the target, Pyranid retains the binary representation for compatibility.
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();
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 UUIDs In Text Columns
For CHAR(36) or VARCHAR2(36), Pyranid uses UUID.toString() when parameter metadata identifies the character target. If metadata is unavailable, bind the string explicitly so Oracle does not implicitly turn 16 raw bytes into 32 hex digits:
database.query("INSERT INTO employee (employee_id) VALUES (:id)")
.bind("id", employeeId.toString())
.execute();
database.query("INSERT INTO employee (employee_id) VALUES (:id)")
.bind("id", employeeId.toString())
.execute();
Use the same representation for equality predicates and IN-list values. Custom UUID binders can enforce an application-wide text convention. Existing 32-hex-digit text rows need migration before canonical UUID reads or predicates can work; Pyranid does not rewrite them.
MySQL And MariaDB
Store UUIDs In Text Or Binary Columns
Pyranid binds UUID parameters as canonical text on MySQL and MariaDB. Bind directly for CHAR(36) columns and MariaDB's native UUID type. For an RFC byte-order BINARY(16) column, convert the parameter in both inserts and predicates:
UUID employeeId = UUID.fromString("f81d4fae-7dec-11d0-a765-00a0c91e6bf6");
database.query("""
INSERT INTO employee (employee_id, name)
VALUES (UNHEX(REPLACE(:id, '-', '')), :name)
""")
.bind("id", employeeId)
.bind("name", "Ada")
.execute();
UUID storedId = database.query("""
SELECT employee_id FROM employee
WHERE employee_id = UNHEX(REPLACE(:id, '-', ''))
""")
.bind("id", employeeId)
.fetchObject(UUID.class)
.orElseThrow();
UUID employeeId = UUID.fromString("f81d4fae-7dec-11d0-a765-00a0c91e6bf6");
database.query("""
INSERT INTO employee (employee_id, name)
VALUES (UNHEX(REPLACE(:id, '-', '')), :name)
""")
.bind("id", employeeId)
.bind("name", "Ada")
.execute();
UUID storedId = database.query("""
SELECT employee_id FROM employee
WHERE employee_id = UNHEX(REPLACE(:id, '-', ''))
""")
.bind("id", employeeId)
.fetchObject(UUID.class)
.orElseThrow();
UNHEX converts the hyphen-free hex text to 16 bytes on both databases. A plain UUID parameter remains text: a direct binary-column write can fail or truncate according to SQL mode, and a direct binary equality predicate can match nothing. Pyranid reads raw 16-byte UUIDs in RFC order; it cannot infer another application's byte layout.
MySQL also provides UUID_TO_BIN and BIN_TO_UUID. With swap flag 0, UUID_TO_BIN(:id, 0) uses the same RFC layout as the recipe above. If a table uses swap flag 1, use it consistently for writes and predicates, and decode on reads:
UUID storedId = database.query("""
SELECT BIN_TO_UUID(employee_id, 1) AS employee_id FROM employee
WHERE employee_id = UUID_TO_BIN(:id, 1)
""")
.bind("id", employeeId)
.fetchObject(UUID.class)
.orElseThrow();
UUID storedId = database.query("""
SELECT BIN_TO_UUID(employee_id, 1) AS employee_id FROM employee
WHERE employee_id = UUID_TO_BIN(:id, 1)
""")
.bind("id", employeeId)
.fetchObject(UUID.class)
.orElseThrow();
Selecting swapped bytes directly as UUID gives the wrong UUID. For application-specific layouts, a custom UUID binder/mapper can enforce the pair of conversions; the default binding remains text.
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();
Long employeeId = database.query("""
INSERT INTO employee (name)
VALUES (:name)
""")
.bind("name", "Ada")
.executeReturningGeneratedKey(Long.class)
.orElseThrow();
Retrieve The ID From A MySQL Upsert
For a single-row INSERT ... ON DUPLICATE KEY UPDATE, execute the statement and read LAST_INSERT_ID() on the same connection. Assign employee_id = LAST_INSERT_ID(employee_id) in the update clause so an existing row's ID becomes the session value too. This example assumes employee_id is an auto-increment primary key and email has a unique constraint:
Long employeeId = database.transaction(() -> {
database.query("""
INSERT INTO employee (email, name)
VALUES (:email, :name)
ON DUPLICATE KEY UPDATE
employee_id = LAST_INSERT_ID(employee_id),
name = :name
""")
.bind("email", "ada@example.com")
.bind("name", "Ada")
.execute();
return database.query("SELECT LAST_INSERT_ID()")
.fetchObject(Long.class);
}).orElseThrow();
Long employeeId = database.transaction(() -> {
database.query("""
INSERT INTO employee (email, name)
VALUES (:email, :name)
ON DUPLICATE KEY UPDATE
employee_id = LAST_INSERT_ID(employee_id),
name = :name
""")
.bind("email", "ada@example.com")
.bind("name", "Ada")
.execute();
return database.query("SELECT LAST_INSERT_ID()")
.fetchObject(Long.class);
}).orElseThrow();
The transaction keeps both calls on one connection. Read the value immediately after the successful upsert, before another operation changes it; LAST_INSERT_ID() is connection-scoped. This pattern covers one inserted or matched row, including an unchanged update, and does not retrieve every ID from a multi-row upsert or batch.
Connector/J can derive JDBC-generated key rows from the upsert's affected-row count. A changed update can therefore return extra IDs, even with LAST_INSERT_ID(employee_id) in the SQL: executeReturningGeneratedKey(...) can throw after the update has executed, and executeReturningGeneratedKeys(...) can include an unrelated row's ID. Supplying an explicit key column name does not resolve this. Use JDBC-generated keys for ordinary inserts and the session-value pattern above for single-row upserts.
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();
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();
For every ID from a multi-row insert, return the ID column explicitly and use executeForList(...):
List<Long> employeeIds = database.query("""
INSERT INTO employee (name)
VALUES (:firstName), (:secondName)
RETURNING employee_id
""")
.bind("firstName", "Ada")
.bind("secondName", "Grace")
.executeForList(Long.class);
List<Long> employeeIds = database.query("""
INSERT INTO employee (name)
VALUES (:firstName), (:secondName)
RETURNING employee_id
""")
.bind("firstName", "Ada")
.bind("secondName", "Grace")
.executeForList(Long.class);
With its default settings, MariaDB Connector/J exposes only the first generated ID for one multi-values INSERT. executeReturningGeneratedKeys(...) therefore returns one key even when more rows were inserted, and executeReturningGeneratedKey(...) can succeed without detecting that more than one row was inserted. Supplying key column names does not change this. Do not reconstruct the other IDs from the first one; use the database-returned rows above. This is distinct from executeBatch(...), whose Pyranid API returns update counts.
The driver's legacy returnMultiValuesGeneratedIds=true compatibility option changes its generated-key behavior. MariaDB documents it as incompatible with Galera servers. Pyranid does not enable this option or infer missing ID ranges; the RETURNING recipe does not depend on that option.
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());
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();
database.query("""
INSERT INTO employee_profile (employee_id, profile)
VALUES (:employeeId, :profile)
""")
.bind("employeeId", employeeId)
.bind("profile", Parameters.json("{\"department\":\"engineering\"}"))
.execute();
SQLite
Migrate SQLite Timestamp Text For 4.9
Pyranid 4.9 writes timestamp text with exactly nine fractional digits. Values that represent instants use UTC Z; local timestamps keep their wall-clock fields without an offset. Existing variable-width and offset-bearing text remains readable, but binding a value just read from a legacy row can produce a different string. This affects equality lookups, joins, UNIQUE/ON CONFLICT keys, GROUP BY/DISTINCT, optimistic-lock predicates, ordering and ranges. A logical duplicate can bypass a text unique key until the stored representations are normalized.
Choose each column's semantics before migration. LocalDateTime means local wall-clock fields, including an unresolved daylight-saving gap or overlap. An instant column requires an explicit offset in the source text and normalizes it to UTC. If the original offset or zone matters, preserve it separately before rewriting. Never infer an instant from offset-free text without an application-specific zone policy.
Download the tested Java 17 migration example. It is application code for this exact sample schema, not an automatic Pyranid migration:
CREATE TABLE event_stamp (
id INTEGER PRIMARY KEY,
account_id INTEGER NOT NULL,
local_at TEXT,
instant_at TEXT,
UNIQUE (account_id, instant_at)
);
CREATE TABLE event_stamp (
id INTEGER PRIMARY KEY,
account_id INTEGER NOT NULL,
local_at TEXT,
instant_at TEXT,
UNIQUE (account_id, instant_at)
);
The example owns a fresh connection and an IMMEDIATE transaction. It streams raw values into a temporary staging table, parses local and offset timestamps strictly with java.time, preserves all nine fractional digits and SQL NULLs, and checks the declared composite unique key for normalization collisions before updating any source row. It then updates only changed rows and commits. Malformed text, offset-free instant text, non-text timestamp storage and duplicate canonical keys stop the migration. Resolve those records explicitly and rerun; the program does not select a duplicate winner.
Back up the database using a SQLite-aware backup procedure, test on a copy, and stop all application writers before running it. Include the SQLite JDBC driver and its required logging dependencies in the classpath. From the directory containing the downloaded source:
java -cp "$SQLITE_JDBC_CLASSPATH" SqliteTimestampMigration.java /absolute/path/to/existing.sqlite
java -cp "$SQLITE_JDBC_CLASSPATH" SqliteTimestampMigration.java /absolute/path/to/existing.sqlite
The program checks that the file exists and uses its own connection; do not run it inside a Pyranid transaction. A second successful run changes zero rows. Invalid-data, collision and update-trigger failures roll back the transaction; if commit itself fails, inspect the durable result before retrying or restore the verified backup because a missing acknowledgment does not prove rollback.
Adapt the code to your real schema before using it: enumerate every timestamp column and every unique, composite, partial or expression key; review collations, foreign-key references, triggers and dependent tables. The sample checks only UNIQUE(account_id, instant_at) with the declared schema and keeps multiple NULL values legal. Related text keys must be migrated together under an application-specific plan. The example deliberately limits normalized years to 0000–9999 and refuses to guess the meaning of other storage formats.
The recipe uses Java parsing/formatting to retain nanoseconds. SQLite's date/time functions have different precision and conversion rules; a blanket strftime rewrite is not this migration. See SQLite's transaction modes for the writer reservation used by IMMEDIATE.
The example preserves nanosecond precision, normalizes explicit offsets to UTC, leaves local wall-clock fields unchanged and retains SQL NULLs. It is idempotent for already canonical values and rolls back the migration if validation or rewriting fails. Its collision checks cover only the sample schema; adapt them before migrating an application's data.
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);
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();
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();
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.with(dataSource, DatabaseType.SQLITE)
.build();
Path dbFile = Files.createTempFile("pyranid-", ".db");
SQLiteDataSource dataSource = new SQLiteDataSource();
dataSource.setUrl("jdbc:sqlite:" + dbFile);
Database database = Database.with(dataSource, 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);
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();
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 });
Parameters.sqlStructOf(
"STRUCT(name VARCHAR, nickname VARCHAR)",
new Object[] { "Ada", null });
Bind Multidimensional Arrays Recursively
For multidimensional arrays on DuckDB, nest SqlArrayParameter values. 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();
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);
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);
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);
// {'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 can fail with a TransactionContext Error conflict rather than blocking. Pyranid classifies these as serialization failures, but classification alone does not authorize replay. A conflict in the transaction body can reach Database::transactionWithRetry(...)'s RetryPolicy after successful rollback. With the DuckDB JDBC driver, commit-time conflicts have no SQLState and report IN_DOUBT; Pyranid never automatically retries them, even if a follow-up rollback succeeds. Reconcile an uncertain result under the original durable operation identity. See the full retry contract.
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);
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.with(new HikariDataSource(config), DatabaseType.DUCK_DB)
.build();
Long total = database.query("SELECT amount FROM sale")
.fetchStream(Long.class, amounts -> amounts.mapToLong(Long::longValue).sum());
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:duckdb:analytics.duckdb");
config.addDataSourceProperty("jdbc_stream_results", "true");
Database database = Database.with(new HikariDataSource(config), 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);
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.with(new HikariDataSource(config), DatabaseType.DUCK_DB)
.build();
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:duckdb:memory:pyranid-tests");
Database database = Database.with(new HikariDataSource(config), 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.with(dataSource, DatabaseType.DUCK_DB)
.build();
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.with(dataSource, 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();
});
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, ResultSet, DuckDBAppender, ArrowReader, and native result before returning. These APIs also require the DuckDB JDBC types on your compile classpath.
Bulk-Load With The Appender
DuckDB's DuckDBAppender avoids SQL parsing and per-row JDBC overhead for large inserts. See the Appender guide for driver-specific usage.
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();
});
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();
});
The close() method 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();
});
});
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();
});
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();
});
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 DuckDBDataChunkReader and its DuckDBReadableVector values become invalid on the next nextChunk() call or when the DuckDBChunkedResult 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 cancelation are available through Pyranid's metrics, Query::queryTimeout(...), and standard JDBC cancelation. 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");
}
}
});
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();
});
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.

