Core Concepts
Queries
Queries are for pulling data out of your database and saying "I expect a single object" or "I expect a list (or stream) of objects".
An object for each row is created by your InstanceProvider.
Rules for how Pyranid binds your plain-Java parameters to PreparedStatement placeholders are outlined in the Parameter Binding section.
Rules for how Resultset data gets copied back into your Java objects are outlined in the ResultSet Mapping section.
Pyranid is opinionated regarding nullability and embraces the use of Optional<T>.
If you ask for a single object, e.g. querying by an identifier, Optional::empty() is returned if the resultset has no rows. Similarly, if you ask for a list of objects and none match your query criteria, the empty list is returned as opposed to null.
Querying Basics
Use Database::query(String) and bind values by name with Query::bind(...). Only named parameters are supported.
Optional<Employee> employee = database.query("""
SELECT *
FROM employee
WHERE id = :id
""")
.bind("id", 42)
.fetchObject(Employee.class);
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id IN (:departmentIds)
""")
.bind("departmentIds", Parameters.inList(List.of(1, 2, 3)))
.fetchList(Employee.class);
Optional<Employee> employee = database.query("""
SELECT *
FROM employee
WHERE id = :id
""")
.bind("id", 42)
.fetchObject(Employee.class);
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id IN (:departmentIds)
""")
.bind("departmentIds", Parameters.inList(List.of(1, 2, 3)))
.fetchList(Employee.class);
You can also bind multiple parameters at once:
Map<String, Object> params = Map.of(
"departmentId", 8,
"minSalary", 100_000
);
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
AND salary >= :minSalary
""")
.bindAll(params)
.fetchList(Employee.class);
Map<String, Object> params = Map.of(
"departmentId", 8,
"minSalary", 100_000
);
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
AND salary >= :minSalary
""")
.bindAll(params)
.fetchList(Employee.class);
Common JDBC statement settings are available directly on Query:
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
""")
.bind("departmentId", departmentId)
.queryTimeout(Duration.ofSeconds(10))
.fetchSize(500)
.maxRows(1_000)
.fetchList(Employee.class);
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
""")
.bind("departmentId", departmentId)
.queryTimeout(Duration.ofSeconds(10))
.fetchSize(500)
.maxRows(1_000)
.fetchList(Employee.class);
Use Database.Builder::queryTimeout(...), Database.Builder::fetchSize(...), and Database.Builder::maxRows(...) to configure database-wide defaults. Per-query settings override database defaults. Query::customize(...) runs after those settings, so it can override them, and before Pyranid binds parameters.
Query::queryTimeout(...) maps to JDBC Statement::setQueryTimeout(...). Query::maxRows(...) maps to JDBC Statement::setMaxRows(...) and is enforced by the driver; DuckDB's driver accepts but ignores it - use SQL LIMIT there. For driver-specific cancelation beyond timeouts, capture the PreparedStatement in Query::customize(...) and call Statement::cancel() from your application's cancelation path.
You may customize the underlying PreparedStatement before execution beyond built-in statement settings with a PreparedStatementCustomizer:
List<Employee> employees = database.query("""
SELECT *
FROM employee
""")
.customize((statementContext, preparedStatement) -> {
preparedStatement.setPoolable(false);
})
.fetchList(Employee.class);
List<Employee> employees = database.query("""
SELECT *
FROM employee
""")
.customize((statementContext, preparedStatement) -> {
preparedStatement.setPoolable(false);
})
.fetchList(Employee.class);
Plain Old Java Objects
Suppose we have a custom Car like this:
public enum Color { BLUE, RED }
// Follows JavaBean conventions for getters/setters
public class Car {
private Long id;
private Color color;
// You may explicitly specify the name of the resultset column
// if you'd like a different name in your Java code
@DatabaseColumn("vehicle_identifier")
private String vin;
public Long getId() { return this.id; }
public void setId(Long id) { this.id = id; }
public Color getColor() { return this.color; }
public void setColor(Color color) { this.color = color; }
public String getVin() { return this.vin; }
public void setVin(String vin) { this.vin = vin; }
}
public enum Color { BLUE, RED }
// Follows JavaBean conventions for getters/setters
public class Car {
private Long id;
private Color color;
// You may explicitly specify the name of the resultset column
// if you'd like a different name in your Java code
@DatabaseColumn("vehicle_identifier")
private String vin;
public Long getId() { return this.id; }
public void setId(Long id) { this.id = id; }
public Color getColor() { return this.color; }
public void setColor(Color color) { this.color = color; }
public String getVin() { return this.vin; }
public void setVin(String vin) { this.vin = vin; }
}
We might query for it like this:
// A single car
Optional<Car> car = database.query("""
SELECT *
FROM car
WHERE id = :id
""")
.bind("id", 123)
.fetchObject(Car.class);
// Multiple cars
List<Car> blueCars = database.query("""
SELECT *
FROM car
WHERE color = :color
""")
.bind("color", Color.BLUE)
.fetchList(Car.class);
// In addition to custom types, you can map to primitives
// and many JDK builtins out of the box.
// See 'ResultSet Mapping' section for details
Optional<UUID> id = database.query("""
SELECT id
FROM widget
LIMIT 1
""")
.fetchObject(UUID.class);
List<BigDecimal> balances = database.query("""
SELECT balance
FROM account
""")
.fetchList(BigDecimal.class);
// A single car
Optional<Car> car = database.query("""
SELECT *
FROM car
WHERE id = :id
""")
.bind("id", 123)
.fetchObject(Car.class);
// Multiple cars
List<Car> blueCars = database.query("""
SELECT *
FROM car
WHERE color = :color
""")
.bind("color", Color.BLUE)
.fetchList(Car.class);
// In addition to custom types, you can map to primitives
// and many JDK builtins out of the box.
// See 'ResultSet Mapping' section for details
Optional<UUID> id = database.query("""
SELECT id
FROM widget
LIMIT 1
""")
.fetchObject(UUID.class);
List<BigDecimal> balances = database.query("""
SELECT balance
FROM account
""")
.fetchList(BigDecimal.class);
By default, Pyranid will invoke your mutator methods as opposed to directly assigning values to fields. For example, Car::setColor(Color) would be used instead of Car::color.
References:
Records
Record types are also supported:
public record Employee(String name, @DatabaseColumn("email") String emailAddress) {}
Optional<Employee> employee = database.query("""
SELECT *
FROM employee
WHERE email = :email
""")
.bind("email", "name@example.com")
.fetchObject(Employee.class);
public record Employee(String name, @DatabaseColumn("email") String emailAddress) {}
Optional<Employee> employee = database.query("""
SELECT *
FROM employee
WHERE email = :email
""")
.bind("email", "name@example.com")
.fetchObject(Employee.class);
By default, Pyranid will invoke the canonical constructor for Record types.
Streaming Results
If you'd like to process large resultsets (e.g. millions of rows) without loading everything into memory, use Query::fetchStream(Class<T>, Function<Stream<T>, R>). The Stream<T> passed to your callback is backed by the underlying java.sql.ResultSet, and Pyranid closes JDBC resources automatically when the callback returns (or throws).
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
""")
.bind("departmentId", 42)
.fetchStream(Employee.class, (stream) ->
stream.filter((employee) -> employee.departmentId() == 42)
.toList());
List<Employee> employees = database.query("""
SELECT *
FROM employee
WHERE department_id = :departmentId
""")
.bind("departmentId", 42)
.fetchStream(Employee.class, (stream) ->
stream.filter((employee) -> employee.departmentId() == 42)
.toList());
Heads Up!
Keep all stream consumption within the callback; consuming only part of it is supported. Don't store off any references to the stream which would cause it to "escape" the callback. Inside a Pyranid transaction, the stream must be closed by the thread that opened it.
PostgreSQL and MariaDB streams default to a JDBC fetch size of 256, both inside and outside Pyranid transactions. PostgreSQL temporarily disables autocommit for a standalone stream; a stream inside a transaction keeps that transaction's connection and completion ownership. MariaDB uses forward-only/read-only statements and a positive fetch size instead of MySQL's streaming sentinel. Positive query/database settings take precedence, and a positive customizer value is retained. An explicit per-query fetchSize(0) requests the driver's default behavior, which can buffer the entire result.
MySQL streams use Connector/J's row-streaming sentinel unless a positive fetch size, useCursorFetch=true, and a recognized server-prepared statement enable cursor fetching. A positive hint alone cannot enable a MySQL cursor, so Pyranid keeps row streaming instead of silently buffering; opaque wrappers also keep row streaming when the required driver evidence is unavailable. Supported connection/statement wrappers are unwrapped without inspecting JDBC URLs. For cursor-eligible queries, enable useCursorFetch=true on the JDBC connection and use a positive Query::fetchSize(...) or database default to choose a chunk size. Explicit per-query fetchSize(0) remains a deliberate request for driver-default behavior. A database-wide zero still receives the dialect streaming default.
Keep stream consumption inside the callback, including when stopping early. Close a MySQL/MariaDB stream before another statement uses the same connection: some drivers drain unread rows when closing, so an early return can still take time. Streaming bounds client-side driver buffering; collecting every row into a list still retains every mapped object, and the database may materialize work on the server. Dialect setup runs after the customizer; with no per-query fetch-size setting, a customizer's zero can receive the dialect default. Use an explicit fetchSize(0) when zero is intentional.
List<Employee> employees = database.query("""
SELECT *
FROM employee
ORDER BY employee_id
""")
.fetchSize(1_000)
.fetchStream(Employee.class, (stream) ->
stream.limit(10_000).toList());
List<Employee> employees = database.query("""
SELECT *
FROM employee
ORDER BY employee_id
""")
.fetchSize(1_000)
.fetchStream(Employee.class, (stream) ->
stream.limit(10_000).toList());
References:
Raw JDBC Connection Access
If you need a JDBC escape hatch for driver-specific features, stored procedures, CallableStatement, or other operations that do not fit Pyranid's query API, use Database::useRawConnection(...).
Optional<Integer> result = database.useRawConnection(connection -> {
try (CallableStatement statement = connection.prepareCall("{ ? = call calculate_bonus(?) }")) {
statement.registerOutParameter(1, Types.INTEGER);
statement.setLong(2, employeeId);
statement.execute();
return Optional.of(statement.getInt(1));
}
});
Optional<Integer> result = database.useRawConnection(connection -> {
try (CallableStatement statement = connection.prepareCall("{ ? = call calculate_bonus(?) }")) {
statement.registerOutParameter(1, Types.INTEGER);
statement.setLong(2, employeeId);
statement.execute();
return Optional.of(statement.getInt(1));
}
});
Database::useRawConnection(...) automatically participates in an active Pyranid transaction. Outside a transaction, Pyranid borrows a connection for the raw connection callback and closes it when the callback returns.
Failures carrying interruption evidence restore the thread's interrupt flag outside a transaction. Inside a transaction, restoration occurs at transaction completion if the failure escapes the closure; a caller that handles it inside the closure decides whether to re-interrupt. See Interruption and Cancelation.
database.transaction(() -> {
database.useRawConnection(connection -> {
try (PreparedStatement statement = connection.prepareStatement("INSERT INTO audit_log VALUES (?)")) {
statement.setString(1, "bonus calculated");
statement.executeUpdate();
}
return Optional.empty();
});
});
database.transaction(() -> {
database.useRawConnection(connection -> {
try (PreparedStatement statement = connection.prepareStatement("INSERT INTO audit_log VALUES (?)")) {
statement.setString(1, "bonus calculated");
statement.executeUpdate();
}
return Optional.empty();
});
});
Pyranid Owns the Connection Lifecycle
The Connection passed by the one-argument overload is a Pyranid-managed guarded handle. Do not close it, retain it, perform transaction lifecycle operations on it, or mutate connection-wide state on it. Methods such as close(), commit(), rollback(), setAutoCommit(...), setTransactionIsolation(...), setCatalog(...), setSchema(...), setClientInfo(...), setNetworkTimeout(...), and JDBC Savepoint controls throw immediately. Statement::getConnection() and DatabaseMetaData::getConnection() return the guarded Pyranid handle, and ResultSet::getStatement() returns a guarded statement. Connection::unwrap(...) may return a guarded, callback-scoped proxy for a vendor interface, but it never exposes a castable physical Connection; the proxy blocks lifecycle methods and expires when the callback returns. Guarded statements, resultsets, and metadata refuse driver-specific unwrap(...) calls that could expose the driver's underlying connection. Use Database::transaction(...), Database::participate(...), and Transaction savepoint APIs for transaction management. Close any Statement or ResultSet instances you create inside the callback.
When a driver feature requires its concrete connection type, use Database::useRawConnection(Class<C>, RawConnectionOperation<? super C, R>). Pyranid passes the managed connection directly when it already has the requested type, or obtains that type with JDBC Wrapper::unwrap(...). This typed overload is deliberately unguarded because concrete driver APIs cannot be safely proxied. Guarding is selected by the overload, not the requested class, so useRawConnection(Connection.class, ...) also supplies an unguarded connection. It still uses the active Pyranid transaction's connection when one exists, but driver-extension transaction rules may differ from ordinary JDBC work.
The typed connection and everything obtained from it remain callback-scoped. Do not close, commit, roll back, retain, or change connection-wide state on it, and close every derived resource before returning. See the DuckDB recipes for concrete examples.
SQL Parsing Rules
Pyranid scans SQL before handing it to JDBC so it can translate named parameters into PreparedStatement placeholders. It ignores parameter-looking text inside string literals, quoted identifiers, comments, PostgreSQL dollar-quoted strings, and SQL Server-style bracket-quoted identifiers. For a configured DuckDB database, brackets are treated as list/subscript syntax instead, so list literals such as [:first, :second] can contain named parameters. Unterminated quotes, dollar-quoted strings, and block comments fail fast with an IllegalArgumentException.
Unquoted ? placeholders are rejected for every database type except PostgreSQL's recognized operator forms, including a stray placeholder after an expression such as 'Hello, ' || ?. The check runs before JDBC acquisition or execution, so positional slots cannot shift named bindings. Question marks inside the dialect's quoted text or comments remain literal text. Use named parameters with Query; raw JDBC access retains JDBC's own parameter syntax.
PostgreSQL JSONB/HSTORE ?, ?|, and ?& operators are supported. When the Database is configured as PostgreSQL, Pyranid automatically emits pgjdbc's escaped ?? form for those operators while preserving named-parameter binding. Already escaped ??, ??|, ??& and @?? forms are accepted and escaped once. A pair ?? represents one literal question mark; a custom PostgreSQL operator containing two adjacent question marks must use ???? to preserve both through pgjdbc.
Block comments nest for PostgreSQL, DuckDB and SQL Server. MySQL/MariaDB, Oracle, SQLite and Generic SQL end a block comment at its first */; parameter-like text after that point is active SQL. Oracle q'[...]' and national nq'[...]' alternative literals protect their contents. MySQL/MariaDB backslash escapes in single/double-quoted strings follow the default SQL modes; when using alternate ANSI_QUOTES or NO_BACKSLASH_ESCAPES modes, prefer backtick identifiers and bound values instead of mode-sensitive quoted literals. Pyranid selects parsing rules from DatabaseType and does not inspect session SQL modes.
For SQL Server and SQLite, brackets always quote identifiers, including in predicates and implicit aliases such as SELECT 1 [a:b]. Other database types retain their subscript rules. Pyranid selects the lexical mode using the database type supplied to Database.with(dataSource, databaseType), including for ARRAY[:a, :b] and arr[:i].
DuckDB SQL also uses : inside struct literals ({'key':expr}) and list slicing (list[a:b]); when the character after : can start an identifier, Pyranid parses it as a named parameter. Write a space after the colon ({'key': expr} - bound parameters still work, e.g. {'key': :value}), or use struct_pack(key := expr) / list_slice(...). DuckDB :: casts and ->/->> JSON operators need no escaping.

