Pyranid Logo

Core Concepts

Parameter Binding

When you execute a SQL statement, Pyranid will convert it to a java.sql.PreparedStatement and ask your PreparedStatementBinder to provide values for any named parameters (e.g. :account_id). Use of java.sql.PreparedStatement is important: among other things, it prevents SQL injection, permits your DBMS to re-use execution plans for performance, and enables typesafe parameter binding.

@FunctionalInterface
public interface PreparedStatementBinder {
  <T> void bindParameter(
    @NonNull StatementContext<T> statementContext,
    @NonNull PreparedStatement preparedStatement,
    @NonNull Integer parameterIndex,
    @NonNull Object parameter
  ) throws SQLException;
}

The out-of-the-box implementation supports binding common JDK types and generally "just works" as you would expect.

For example:

UUID departmentId = ...;
Long accountId = ...;

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  WHERE department_id = :departmentId
  """)
  .bind("departmentId", departmentId)
  .fetchList(Employee.class);

database.query("""
  INSERT INTO account_award (account_id, award_type)
  VALUES (:accountId, :awardType)
  """)
  .bind("accountId", accountId)
  .bind("awardType", AwardType.BIG)
  .execute();

Java enum parameters use name() and bind as text by default. PostgreSQL native ENUM columns need an explicit parameter cast; see Native ENUM values. MySQL/MariaDB BINARY(16) UUID columns need a matching binary representation for writes and predicates; see UUID storage.

If you need to customize binding behavior, you might bring your own list of CustomParameterBinder...

// See "Custom Parameters" section below for details
PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(...));

Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

...or you may choose to directly implement the PreparedStatementBinder interface for fine-grained control:

PreparedStatementBinder preparedStatementBinder =
  new PreparedStatementBinder() {
    @Override
    public <T> void bindParameter(
      @NonNull StatementContext<T> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex, 
      @NonNull Object parameter
    ) throws SQLException {
      // Do your binding here
    }
  };

Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

Supported Primitives

Supported JDK Types

Character parameters bind as single-character text, including characters expanded by Parameters.inList(char[]). Custom binders receive the original Character before default normalization. The same normalization applies to built-in SQL ARRAY elements and STRUCT attributes.

DuckDB decimal parameters with negative scale, such as new BigDecimal("1000").stripTrailingZeros(), bind through an exact scale-zero representation. Other dialects retain their decimal representation. DuckDB's current JDBC decimal representation must have precision and nonnegative scale no greater than 38 after normalization. Unsupported representations fail before binding, avoiding the driver's silent SQL NULL behavior. This bound concerns the supplied representation: excess trailing zeros can exceed it even when the mathematical value could fit. Canonicalize with stripTrailingZeros() when appropriate, or provide a custom binder for another representation. Custom binders receive the original value; caller-owned values and arrays are not modified.

Oracle UUID parameters use canonical 36-character text when parameter metadata identifies a character target (CHAR, VARCHAR2, NCHAR, NVARCHAR2, or a character LOB). Binary targets such as RAW(16), and targets whose metadata is unavailable or inconclusive, retain the 16-byte UUID representation. For text storage with unknown metadata, bind uuid.toString() explicitly or provide a custom UUID binder. Custom binders receive the original UUID before default normalization. Existing 32-hex-digit character rows need an application migration to canonical UUID text, including referencing keys, or WHERE id = :uuid can stop matching. Widen CHAR(32)/VARCHAR2(32) columns before 36-character inserts to avoid ORA-12899; audit duplicate/case collisions and constraints as part of the migration. This change does not rewrite stored data.

Temporal binding uses JDBC ParameterMetaData when available to distinguish TIMESTAMP from TIMESTAMP WITH TIME ZONE targets. For known zone-less TIMESTAMP targets, Pyranid converts Instant and OffsetDateTime parameters through Database.Builder::timeZone(...) and binds the resulting local timestamp. For known TIMESTAMP WITH TIME ZONE targets, Pyranid binds them as time-zone-aware timestamps. ZonedDateTime parameters are normalized to OffsetDateTime first and follow the same rules.

If parameter metadata is unavailable or identifies neither timestamp kind, Instant and OffsetDateTime generally default to TIMESTAMP_WITH_TIME_ZONE. MySQL and MariaDB instead bind the local timestamp in Database.timeZone, including with missing or generic parameter metadata. Their timestamp types do not carry an offset. For other drivers with ambiguous zone-less targets, configure Database.Builder::ambiguousTimestampBindingStrategy(TIMESTAMP_WITHOUT_TIME_ZONE). That option also applies when metadata reports another type such as VARCHAR or DATE; positive TIMESTAMP WITH TIME ZONE metadata retains precedence.

When an Oracle driver metadata-parser failure prevents Pyranid from identifying a timestamp target, Pyranid rejects nonnull temporal binding rather than risking a different stored instant. This applies to instant-bearing and local temporal values, even after an earlier SQL NULL encountered the same driver limitation. Use a cast matching the actual target column, such as SELECT CAST(:at AS TIMESTAMP(9)) FROM dual or CAST(:at AS TIMESTAMP(9) WITH TIME ZONE), or a custom binder that selects the intended JDBC type. Changing the ambiguous strategy alone does not bypass this check. Plain NULL and interval-zero behavior is described below.

Oracle MERGE can instead report ordinary unsupported or nonidentifying metadata, which retains the ambiguous strategy contract. For a known zone-less target, explicitly select TIMESTAMP_WITHOUT_TIME_ZONE or cast the parameter to the target timestamp family. The default zoned strategy is appropriate for zoned targets; Oracle's implicit conversion from a zoned bind into a zone-less column can preserve the input wall clock instead of the local timestamp in Database.timeZone.

MySQL Connector/J’s connectionTimeZone does not configure Database.timeZone, which defaults to the JVM zone. For example, a connection configured for UTC and a Database left at a non-UTC JVM default still have different settings. Choose Database.timeZone explicitly for the actual connection/session wall-clock policy and check the server’s time_zone; forceConnectionTimeZoneToSession=false does not set that session to the configured driver zone. Direct JDBC temporal getters and custom/raw work can use the driver’s conversion settings too. MariaDB Connector/J has version-specific options and defaults, so configure and qualify the selected driver rather than assuming all settings behave identically.

For example, with a UTC server session, leaving Database at a New York JVM default can round-trip an Instant while storing a server instant four hours early for a July value. Explicitly choose Database.timeZone to match the intended storage policy, and check the server's stored instant as well as Java round trips. Raw JDBC getTimestamp calls can still follow the driver's separate conversion settings.

LocalDateTime and LocalDate parameters use Database.timeZone when metadata identifies a zoned timestamp target. A date starts at the beginning of that day in the configured zone. Java's normal zone rules apply: gaps advance to a valid time, and overlaps select the earlier offset. When metadata cannot identify a zoned target, local inputs retain their local JDBC representation; keep the connection/session time zone consistent with Database.timeZone when the database must infer a zone.

java.sql.Timestamp parameters represent an instant and follow the same rules as Instant; use LocalDateTime for a wall-clock value. java.sql.Time parameters retain their millisecond fraction when converted to LocalTime. Oracle binds LocalTime through its native, untyped JDBC path, preserving its clock and available fractional precision. SQL Server binds LocalTime as ISO time text to avoid the driver's default sendTimeAsDatetime conversion; the target column still determines precision, up to seven fractional digits for TIME.

SQLite writes instant-bearing values as UTC text with nine fractional digits, such as 2024-01-02T03:04:05.000000000Z. LocalDateTime, including an instant converted by TIMESTAMP_WITHOUT_TIME_ZONE, uses local text with nine fractional digits and no offset. For years 0000–9999, these consistent formats preserve textual ordering within each representation; signed or expanded years do not have that guarantee. New instant-bearing writes normalize offsets to UTC; store the original offset separately if the application needs it.

SQLite 4.9 upgrade: older text remains readable, but reading it does not rewrite storage. A value read from an old row and rebound by 4.9 can fail an equality lookup or optimistic-lock update because the new text differs. Mixed formats also affect joins, UNIQUE and ON CONFLICT keys, GROUP BY/DISTINCT, ordering and range predicates. Normalize affected columns before switching writers, with one declared model per column: local wall-clock timestamps stay local, while instant columns normalize to UTC. Preserve all fractional digits and resolve logical uniqueness collisions before rewriting. Follow the tested SQLite timestamp migration recipe. Pyranid performs no automatic stored-data migration.

For SQL Server legacy DATETIME columns, configure datetimeParameterType=datetime in the JDBC URL or DataSource. The driver's default datetime2 parameter type can make a fetched DATETIME value fail an equality predicate, causing an optimistic-lock update to affect zero rows. This connection setting also applies to other date/timestamp parameters and can reduce their precision; choose connections and column types accordingly.

PostgreSQL can contact the server while obtaining parameter metadata. Pyranid protects those lookups during manual-commit transactions and reuses statement metadata across batch groups and chunks. Custom binders and wrapped parameters retain their normal callback order and inputs; application-supplied JDBC wrappers are used as supplied. If a later batch value needs target metadata that cannot safely be established after earlier values were bound, binding fails with 0A000 rather than converting it using an earlier input's type. A SQL NULL can bind without establishing a target type for subsequent values. A recovered lookup failure can fall back to ordinary binding; uncertain setup or failed savepoint cleanup prevents the owning transaction from committing. These safeguards are scoped to the prepared statement, do not span arbitrary application callbacks, and do not invoke application savepoint hooks or metrics.

SQL Intervals

SqlInterval represents independent signed total months, calendar days and elapsed time. It does not assume a month is 30 days or a calendar day is 24 hours:

SqlInterval elapsed = SqlInterval.of(Duration.ofDays(3)); // 72 elapsed hours
SqlInterval calendar = SqlInterval.of(Period.ofDays(3));  // 3 calendar days
SqlInterval mixed = SqlInterval.of(Period.ofMonths(1), Duration.ofHours(2));

database.query("UPDATE policy SET timeout = :timeout WHERE name = :name")
  .bind("timeout", SqlInterval.of(Duration.ofMinutes(90)))
  .bind("name", "webhooks").execute();
DatabaseBuilt-in representation and limits
PostgreSQLTyped native interval; preserves independent months/days/time through unrestricted targets. The elapsed-time field must fit an exact signed 64-bit number of microseconds. Sub-microsecond input, overflow and the two reserved infinity field combinations fail before binding.
DuckDBInterval text accepted by native interval targets, with the same microsecond/range limits. Use CAST(:value AS INTERVAL) for operator expressions; some contexts cannot infer an interval from its bound string.
OracleCalendar-month-only values bind as YEAR TO MONTH; elapsed-time-only values use an exactly constructed driver-native DAY TO SECOND value. Native interval binding preserves fractional seconds and their signs. Nonzero calendar days and mixed months/time fail rather than being reinterpreted; supported nanoseconds are retained before column coercion.
GENERICDriver-specific native Period/Duration support using the same family split; HSQLDB supports these native values. JDBC 4.2 compliance alone does not establish interval support.
MySQL, MariaDB, SQL Server, SQLiteNo native interval type; the default binder rejects SqlInterval. Choose an explicit representation through a custom binder.

SqlInterval.ZERO is the same value whichever factory created it, and so is any computed zero such as SqlInterval.of(Duration.between(start, start)). Oracle/GENERIC need recognizable parameter metadata to select YEAR TO MONTH versus DAY TO SECOND for zero. Missing, ambiguous or conflicting family metadata fails; Pyranid does not guess, and does not try a write with each type. An explicit SQL cast can help expose the target family but is not a universal conversion between families. PostgreSQL interval values, including zero, need no interval parameter describe; other parameter types in the same statement retain their metadata safety checks. DuckDB also binds zero without metadata.

On Oracle (observed with ojdbc11 23.26), the driver reports the interval family when the parameter is assigned or compared directly to an interval column: INSERT … VALUES, UPDATE … SET col = :v, and comparisons such as WHERE col = :v, BETWEEN or CASE. It also reports the family for an explicit interval cast. Zero fails in uncast expressions such as SYSTIMESTAMP + :v, COALESCE(col, :v) and SELECT :v FROM dual; in uncast MERGE, both in a USING subquery and in UPDATE SET col = :v; in PL/SQL blocks; and in uncast INSERT … SELECT, where family metadata is unavailable. When Oracle cannot report an interval family, plain NULL binding can use its ordinary fallback, while zero reports the interval-family error. Nonzero values can bind in these forms, so exercise zero values explicitly when qualifying the statement.

A cast matching the actual target family is an alternative in SELECT, arithmetic, COALESCE, INSERT SELECT, MERGE USING and MERGE UPDATE forms. For DAY TO SECOND:

SqlInterval zero = database.query("SELECT CAST(:amount AS INTERVAL DAY(9) TO SECOND(9)) FROM dual")
  .bind("amount", SqlInterval.ZERO)
  .fetchObject(SqlInterval.class)
  .orElseThrow();

Use CAST(:amount AS INTERVAL YEAR(9) TO MONTH) for a YEAR TO MONTH target. A cast does not make the families interchangeable: a DAY TO SECOND zero assigned to a YEAR TO MONTH column still fails. Do not assume a cast exposes metadata in every context, including PL/SQL blocks. When the target family cannot be exposed to the driver, register a binder that selects the family your schema uses:

// Binds SqlInterval.ZERO as DAY TO SECOND. Every other value falls back to Pyranid's built-in binding.
// Use Period.ZERO instead if your zero intervals target INTERVAL YEAR TO MONTH.
final class DaySecondZeroBinder implements CustomParameterBinder {
  @Override
  public Boolean appliesTo(TargetType targetType) {
    return targetType.matchesClass(SqlInterval.class);
  }

  @Override
  public BindingResult bind(StatementContext<?> context, PreparedStatement statement,
                            Integer index, Object parameter) throws SQLException {
    if (!SqlInterval.ZERO.equals(parameter)) return BindingResult.fallback();
    statement.setObject(index, Duration.ZERO);
    return BindingResult.handled();
  }
}

Database database = Database.with(dataSource, DatabaseType.ORACLE)
  .preparedStatementBinder(PreparedStatementBinder.withCustomParameterBinders(List.of(new DaySecondZeroBinder())))
  .build();

A zero of the wrong family is never stored silently: Oracle rejects it with ORA-00932. HSQLDB 2.7.4 can expose VARCHAR rather than interval metadata for a bare SELECT :amount or MERGE USING (SELECT :amount ...); default SqlInterval binding then fails rather than guessing. An explicit family cast, such as CAST(:amount AS INTERVAL DAY(9) TO SECOND(9)), supplies usable metadata in those SELECT and MERGE forms.

Nonzero Oracle/GENERIC values select their native family from their fields. Binding fails when target metadata positively identifies the opposite interval family. An expression's timestamp type does not by itself rule out an interval input. Reading intervals and choosing a zero's family still require unambiguous interval metadata.

Oracle also has a separate, pre-existing untyped-NULL limitation in expressions such as SYSTIMESTAMP + :v: the driver reports TIMESTAMP WITH TIME ZONE, so the default NULL binder supplies a datetime NULL and Oracle rejects the addition with ORA-30087. This also occurs with plain Duration batches on 4.8.0. If such an interval parameter can be SQL NULL, use a TypedParameter with a CustomParameterBinder.bindNull that explicitly selects the schema's family (PreparedStatement.setNull(index, -104) for Oracle DAY TO SECOND, or -103 for YEAR TO MONTH). Return fallback() for nonzero values to retain built-in exact interval binding. The zero-only binder above does not handle SQL NULL.

A bind/read equals round trip requires the target's field qualifier, precision and SQL coercions to preserve the fields. Restricted PostgreSQL interval columns can drop fields; Oracle can round fractions, and HSQLDB can truncate them. HSQLDB 2.7.4 also wraps some enormous YEAR(9) literals in the server before JDBC reads them; a wide declared year field is not an exactness guarantee. SQL normalization and triggers can change storage too. Pyranid preserves the stored value on read and does not promise to detect every column coercion.

Existing plain Duration/Period driver pass-through and custom binders remain unchanged. The new type does not make that pass-through portable. Custom binders receive the original SqlInterval before built-in handling; whole-container custom overrides retain their precedence. Built-in SQL ARRAY elements and STRUCT attributes of SqlInterval are outside this release's scope and fail clearly. Plain nulls follow the existing null-binding path rather than the non-null zero-family rule.

Special Parameters

Special support is provided for JSON/JSONB, vector, SQL ARRAY, SQL STRUCT, and secure diagnostics wrapper parameters. SqlInterval is described in SQL Intervals. PostgreSQL-specific helpers require pgjdbc or the explicitly supported YugabyteDB smart driver on your application's classpath, and pgvector parameters require the pgvector database extension. DuckDB vector parameters bind via the driver's SQL ARRAY support and require no extension.

JSON/JSONB

Useful for storing "stringified" JSON data by taking advantage of the DBMS' native JSON storage facilities, if available (for example, PostgreSQL JSONB, MySQL/MariaDB JSON, DuckDB JSON, SQLite JSON text, SQL Server NVARCHAR + ISJSON, or Oracle JSON/CLOB columns).

Supported methods:

You might create a JSONB storage table...

CREATE TABLE example (
  example_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  data JSONB NOT NULL
);

...and write JSON to it:

String json = "{\"testing\": 123}";

database.query("INSERT INTO example (data) VALUES (:data)")
  .bind("data", Parameters.json(json))
  .execute();

By default, Pyranid will use your database's binary/native JSON format if supported and fall back to a text representation otherwise. MySQL-family databases bind JSON as text to avoid driver character-set traps.

To request text JSON binding, specify BindingPreference.TEXT:

database.query("INSERT INTO example (data) VALUES (:data)")
  .bind("data", Parameters.json(json, BindingPreference.TEXT))
  .execute();

This preference controls the bound parameter's representation, not the destination column's storage format. The example still writes to a PostgreSQL JSONB column, which normalizes the JSON. To preserve whitespace and formatting on PostgreSQL, use a JSON or TEXT column instead of JSONB.

Passing null to Parameters::json(...) is supported. Pyranid will bind a typed null (using JSON/JSONB for PostgreSQL and a text fallback for other databases) instead of an untyped Types.NULL.

Vector

Useful for storing vector embeddings, often used for Artificial Intelligence tasks - pgvector columns on PostgreSQL and fixed-size FLOAT[n]/DOUBLE[n] ARRAY columns on DuckDB. Currently supported for PostgreSQL and DuckDB.

Supported methods:

You might create a vector storage table...

CREATE TABLE vector_embedding (
  vector_embedding_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  embedding VECTOR(1536) NOT NULL,
  content TEXT NOT NULL
);

...and write vector data to it:

double[] embedding = ...;
String content = "...";

database.query("INSERT INTO vector_embedding (embedding, content) VALUES (:embedding, :content)")
  .bind("embedding", Parameters.vectorOfDoubles(embedding))
  .bind("content", content)
  .execute();

Vector columns also read back into float[] and double[] targets - Pyranid parses the vector literal (e.g. [0.1,0.2,0.3]) that drivers surface for vector columns:

public record Document(Long documentId, float[] embedding) {}

Optional<Document> document = database.query("""
  SELECT document_id, embedding
  FROM vector_embedding
  ORDER BY embedding <-> :query
  LIMIT 1
  """)
  .bind("query", Parameters.vectorOfFloats(queryEmbedding))
  .fetchObject(Document.class);

On DuckDB, vectors bind to fixed-size ARRAY columns and read back the same way. INSERT needs no cast, but comparison functions like array_cosine_distance require an explicit cast of the bound parameter to the fixed-size ARRAY type:

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

Passing null to the Parameters::vectorOfDoubles(...), Parameters::vectorOfFloats(...), and Parameters::vectorOfBigDecimals(...) helpers is supported. Pyranid binds a typed VECTOR null for PostgreSQL and an ARRAY-typed null for DuckDB.

SQL ARRAY

SQL ARRAY binding is supported out-of-the-box for databases whose JDBC drivers support Connection::createArrayOf. PostgreSQL and DuckDB support ordinary one-dimensional arrays; DuckDB LIST and fixed-size ARRAY columns both accept bound arrays. MySQL, MariaDB, SQLite, SQL Server, and Oracle are guarded as unsupported and fail with a clear DatabaseException; use IN-list expansion, JSON, or a database-specific custom binder for those engines.

Supported methods:

You might create a table with some array columns...

CREATE TABLE product (
  product_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT NOT NULL,
  vendor_flags INTEGER[] NOT NULL,
  tags VARCHAR[]
);

...and write array data to it:

String name = "...";
Integer[] vendorFlags = { 1, 2, 3 };
List<String> tags = List.of("alpha", "beta");

database.query("""
  INSERT INTO product (name, vendor_flags, tags)
  VALUES (:name, :vendor_flags, :tags)
  """)
  .bind("name", name)
  .bind("vendor_flags", Parameters.sqlArrayOf("INTEGER", vendorFlags))
  .bind("tags", Parameters.sqlArrayOf("VARCHAR", tags))
  .execute();

For multidimensional arrays on DuckDB, nest SqlArrayParameter values. Other JDBC drivers may impose different element-type or nesting rules. Represent each dimension with its own Parameters::sqlArrayOf(...), and make each containing level's baseTypeName name its element type. For example, the outer array below has VARCHAR[] elements and each inner array has VARCHAR elements:

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

For DuckDB elements represented by Instant, OffsetDateTime, ZonedDateTime, java.sql.Timestamp, or java.util.Date, use an explicit TIMESTAMP or TIMESTAMPTZ base type. Pyranid uses it to apply the intended timestamp and configured Database.Builder::timeZone(...) semantics. A named DuckDB type alias does not expose its underlying timestamp type through this binding path, so Pyranid fails fast for these instant-bearing values instead of guessing.

PostgreSQL arrays with a recognized built-in timestamp base type also convert instant-bearing elements through Database.timeZone, consistently with scalar zone-less timestamps. Zoned timestamp elements retain their instant/offset representation. Pyranid does not infer timestamp semantics for PostgreSQL domains or other user-defined type names; use explicit local elements or a custom binder when those types require conversion.

Use a CustomParameterBinder for database-specific collection types, table-valued parameters, or a driver whose multidimensional representation differs.

Passing a null container to Parameters::sqlArrayOf(...) represents SQL NULL, distinct from an empty array. For example, Parameters.sqlArrayOf("text", (String[]) null) binds a typed null PostgreSQL text[], even in a query such as SELECT pg_typeof(:value). PostgreSQL resolves the array type through its JDBC driver; supply the element type name, with schema qualification/quoting supported by that driver. A null for a multidimensional PostgreSQL column uses the same leaf element name, such as integer for integer[][]; this does not add support for arbitrary nested-array name syntax. Other dialects retain their existing null-binding behavior.

SQL STRUCT

Parameters::sqlStructOf(...) binds positional attributes through JDBC Connection::createStruct(...). SQL STRUCT binding is currently supported for DuckDB; other dialects fail fast rather than passing a driver-specific wrapper to JDBC.

Supported methods:

The type name is database-specific. DuckDB accepts the complete inline STRUCT declaration, and values must appear in the same order as its attributes:

CREATE TABLE person (
  person_id BIGINT PRIMARY KEY,
  details STRUCT(name VARCHAR, age INTEGER)
);
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 DuckDB attributes represented by Instant, OffsetDateTime, ZonedDateTime, java.sql.Timestamp, or java.util.Date, always provide the complete inline STRUCT declaration. Pyranid uses it to distinguish TIMESTAMP from TIMESTAMPTZ and to apply the configured Database.Builder::timeZone(...) for zone-less timestamps. A named DuckDB CREATE TYPE alias does not expose its attribute types through this binding path, so Pyranid fails fast for these instant-bearing values instead of guessing. Named aliases remain usable for attributes that do not require instant-to-timestamp normalization.

Both overloads defensively copy their attributes. Attributes may be null and may include nested SqlStructParameter or SqlArrayParameter values. Use the array overload when an attribute is null and List.of(...) would reject it:

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

A null attribute array/list means the whole STRUCT is SQL NULL; cast the null to select the intended overload:

Parameters.sqlStructOf(
  "STRUCT(name VARCHAR, age INTEGER)",
  (Object[]) null);

Secure Parameters

Use Parameters::secure(...) for sensitive values that should bind normally but render as a mask in Pyranid diagnostics.

For example, a logger that renders the full StatementLog will include Pyranid's parameter diagnostics:

// Configure a simple StatementLogger that writes to stdout
Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .statementLogger(statementLog -> System.out.println(statementLog))
  .build();

Before wrapping a sensitive value, that log output can display the raw bound value:

database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", tokenHash)
  .execute();
parameters=[acct_123, token_hash_abc123]

After wrapping the value, Pyranid still binds tokenHash normally, but the same log output renders the mask:

database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", Parameters.secure(tokenHash))
  .execute();
parameters=[acct_123, <redacted>]

Parameters::secure(Object) renders as <redacted>. Use Parameters::secure(Object, String) for a custom display token. This is display-only: the underlying value is still passed to the JDBC PreparedStatement as if it had not been wrapped.

Parameters::secure(...) composes with Parameters::inList(...), Parameters::json(...), vector parameters, Parameters::sqlArrayOf(...), Parameters::sqlStructOf(...), typed parameters, Optional values, and null values.

For broader policies, configure a database-wide ParameterRedactor:

// Use a "redact everything" globally
Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .parameterRedactor(ParameterRedactor.redactAll())
  .build();

The default redactor is ParameterRedactor::none(), which leaves non-secure, non-batch values unchanged. ParameterRedactor::redactAll() masks every non-secure value. Note that use of SecureParameter always takes precedence; its wrapped value is never passed to the redactor.

Heads Up!

By default, non-secure parameter values render verbatim in statement logs and exception text. If diagnostics may leave a trusted boundary, wrap sensitive values with Parameters::secure(...) or configure ParameterRedactor::redactAll().

What Redaction Does and Does Not Cover

Because the real value is bound to the PreparedStatement, the database driver may echo it back in its own error text - for example, PostgreSQL constraint violations include Key (email)=(...) already exists. Pyranid's coverage:

SurfaceCovered?
Pyranid's parameters=[...] rendering (StatementContext/StatementLog diagnostics)Yes - masks/redactor always apply
DatabaseException messages, toString(), and DBMS text fields (getDetail(), getDbmsMessage(), ...)Best-effort - verbatim occurrences of Parameters::secure(...) values are scrubbed and replaced with the mask
DatabaseException.getSqlState()Preserved verbatim when available, even if a secure parameter has matching text; operational codes remain usable by error-handling policies
The raw driver exception (getCause()), stack traces, and anything that renders them (log appenders, error trackers, OpenTelemetry exception events)No - deliberately preserved unsanitized. Treat the cause chain as sensitive.
Driver-transformed echoes (re-formatted numbers/temporals, truncated strings, encoded bytes), vector parameters; null/Boolean/very short secure valuesNo - the scrub is verbatim-only and skips values that would corrupt unrelated diagnostics
Pyranid's own mapping-error messages (e.g. Cannot map value '...' when a value round-trips into a ResultSet)No
Exceptions raised outside statement execution - commit/rollback time (e.g. deferred constraint violations), connection acquisition, raw-connection operationsNo - no statement context exists at those points

Only values explicitly wrapped with Parameters::secure(...) trigger the driver-text scrub; a ParameterRedactor governs Pyranid's parameter-list rendering only.

Custom redactors implement the single three-argument redactParameter(context, index, value) method and return non-null Optional<Object> in 4.9.0; wrap a value with Optional.ofNullable(value), use Optional.of("<redacted>") for a mask, or return Optional.empty() for a null display entry. Existing custom implementations/lambdas and direct callers need updating and recompiling. An old compiled implementation can fail with AbstractMethodError during diagnostic rendering, and an old direct call can fail with NoSuchMethodError; see the redactor migration details. Name-aware display redaction reads each expanded slot's original binding name from context.getParameterNames().get(index), using null-safe matching for unknown names. Diagnostics, direct calls and decorators use the same policy. See Redact by binding name. This does not replace Parameters.secure(...) for driver-text scrubbing.

IN-List Expansion

When using named parameters with Database::query(String), collections and arrays are not expanded automatically. Wrap them with Parameters::inList(...) to expand into multiple SQL IN-list placeholders. IN-list parameters must be non-empty and must not contain null or empty Optional elements. Empty collections, empty arrays, null elements, and empty Optional elements will throw an IllegalArgumentException.

SQL IN does not match NULL; use an explicit IS NULL predicate when null matching is required.

List<UUID> ids = List.of(
  UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"),
  UUID.fromString("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb")
);

List<Account> accounts = database.query("""
  SELECT *
  FROM account
  WHERE account_id IN (:ids)
  """)
  .bind("ids", Parameters.inList(ids))
  .fetchList(Account.class);

Supported methods:

Custom Parameters

You may register instances of CustomParameterBinder to bind application-specific types to java.sql.PreparedStatement however you like.

For implicit enum parameters, custom binder selection uses the declaring enum class even when a constant has its own class body. The binder receives the original enum value, and explicit TypedParameter hints take precedence. Without a handling custom binder, default enum binding still uses Enum.name().

This allows you to use your objects as-is with Pyranid instead of sprinkling "convert this object to database format" code throughout your system.

When multiple custom parameter binders apply, Pyranid tries them in the order supplied. Returning BindingResult::fallback() lets the next applicable binder run; if none handles the value, Pyranid's normal binding rules apply.

Because a binder can be asked speculatively before falling back, only mutate the PreparedStatement or other externally-visible state after you have decided to handle the value.

Typed parameters (such as Parameters::listOf(...) or Parameters::arrayOf(Class, ...) always require a CustomParameterBinder. If you need to bind typed nulls, implement CustomParameterBinder::bindNull(...) in addition to CustomParameterBinder::bind(...); otherwise binding fails fast.

Arbitrary Types

Let's define a simple type.

class HexColor {
  int r, g, b;

  HexColor(int r, int g, int b) {
    this.r = r; this.g = g; this.b = b;
  }

  String toHexString() {
    return String.format("#%02X%02X%02X", r, g, b);
  }

  static HexColor fromHexString(String s) {
    int r = Integer.parseInt(s.substring(1, 3), 16);
    int g = Integer.parseInt(s.substring(3, 5), 16);
    int b = Integer.parseInt(s.substring(5, 7), 16);
    return new HexColor(r, g, b);
  }
}

Then, we'll register a CustomParameterBinder to handle binding it:

PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      return targetType.matchesClass(HexColor.class);
    }		
			
    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext, 
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      HexColor hexColor = (HexColor) parameter; 

      // Bind to the PreparedStatement as a value like "#6a5acd"
      preparedStatement.setString(parameterIndex, hexColor.toHexString());

      // Or return BindingResult.fallback() to let the next applicable custom binder run.
      // If none handles the value, Pyranid's normal binding rules apply.
      return BindingResult.handled();
    }

    @NonNull
    @Override
    public BindingResult bindNull(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull TargetType targetType,
      @NonNull Integer sqlType
    ) throws SQLException {
      // Handle typed nulls if you want null TypedParameter values to bind successfully
      preparedStatement.setNull(parameterIndex, sqlType);
      return BindingResult.handled();
    }
  }  
));

Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

With the custom binder in place, your application code might look like this:

// Given a reference to a hex color...
UUID themeId = ...;
HexColor backgroundColor = HexColor.fromHexString("#6a5acd");

// ...we use the reference as-is and Pyranid will apply the custom binder
database.query("""
  UPDATE theme
  SET background_color = :backgroundColor
  WHERE theme_id = :themeId 
  """)
  .bind("backgroundColor", backgroundColor)
  .bind("themeId", themeId)
  .execute();

Collections and Arrays

Runtime binding of generic types is made difficult by type erasure. For convenience, Pyranid offers special parameters that perform type capture for standard List<E>, Set<E>, and Map<K,V> types:

This makes it easy to create custom binders for common scenarios.

For example, this code...

List<UUID> ids = List.of(
  UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"),
  UUID.fromString("bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb")
);

database.query("INSERT INTO t(v) VALUES (:v)")
  .bind("v", Parameters.listOf(UUID.class, ids))
  .execute();

...would be handled by this custom binder, because TargetType::matchesParameterizedType(...) returns true thanks to runtime type capturing:

PreparedStatementBinder preparedStatementBinder = 
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      // For Parameters::mapOf(Class<K>, Class<V>, Map<K,V>), you'd say:
      // matchesParameterizedType(Map.class, MyKey.class, MyValue.class)
      return targetType.matchesParameterizedType(List.class, UUID.class);
    }		
			
    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext, 
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      // Convert UUIDs to a comma-delimited string, or null for the empty list 
      List<UUID> uuids = (List<UUID>) parameter;
      String uuidsAsString = uuids.isEmpty()
        ? null
        : uuids.stream().map(Object::toString).collect(Collectors.joining(","));

      // Bind to the PreparedStatement
      preparedStatement.setString(parameterIndex, uuidsAsString);
			
      return BindingResult.handled();
    }
  }  
));

Arrays can also be type-captured for custom binding by wrapping them with Parameters::arrayOf(...):

String[] tags = { "alpha", "beta" };

database.query("INSERT INTO product(tags) VALUES (:tags)")
  .bind("tags", Parameters.arrayOf(String.class, tags))
  .execute();

When your database and JDBC driver support SQL ARRAY values, SQL ARRAY binding is usually a better fit than custom string encoding. SQL arrays are handled directly by the JDBC driver and map cleanly to database array column types, which avoids bespoke encoding/decoding and keeps values visible to database tools.

Parameterized-Type Example

You can also target specific generic shapes. For example, suppose you want to store Map values keyed by Locale with String values in a PostgreSQL JSONB column.

Map<Locale, String> nameTranslations = Map.of(
  Locale.ENGLISH, "Sparkling Water",
  Locale.forLanguageTag("es-MX"), "Agua mineral"
);

PreparedStatementBinder preparedStatementBinder =
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      return targetType.matchesParameterizedType(Map.class, Locale.class, String.class);
    }

    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      Map<Locale, String> valuesByLocale = (Map<Locale, String>) parameter;

      PGobject jsonbValue = new PGobject();
      jsonbValue.setType("jsonb");
      jsonbValue.setValue(GSON.toJson(valuesByLocale));
      preparedStatement.setObject(parameterIndex, jsonbValue);

      return BindingResult.handled();
    }

    @NonNull
    @Override
    public BindingResult bindNull(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull TargetType targetType,
      @Nullable Integer sqlType
    ) throws SQLException {
      if (!appliesTo(targetType))
        return BindingResult.fallback();

      preparedStatement.setNull(parameterIndex, Types.OTHER);
      return BindingResult.handled();
    }
  }));

Database database = Database.with(dataSource, DatabaseType.POSTGRESQL)
  .preparedStatementBinder(preparedStatementBinder)
  .build();

database.query("""
  INSERT INTO product (name_translations)
  VALUES (:nameTranslations)
  """)
  .bind("nameTranslations", Parameters.mapOf(Locale.class, String.class, nameTranslations))
  .execute();

This pattern pairs naturally with a matching CustomColumnMapper. See the Parameterized-Type Example in the result-mapping guide for the read side.

Heads Up!

If you use any of these collection/array typed parameters, you must define a corresponding CustomParameterBinder to handle them:

These special parameter types do not automatically work out-of-the-box because Pyranid cannot reliably guess how you intend to bind them. This applies even when the wrapped value is null; implement CustomParameterBinder::bindNull(...) if you want typed nulls to bind successfully. Pyranid will detect a missing-binder scenario and throw an exception to indicate programmer error.

SQL Injection and Dynamic SQL

Pyranid named parameters bind SQL values through JDBC PreparedStatement placeholders. Use them for values such as IDs, names, timestamps, limits, and status codes.

Named Parameters Bind Values, Not SQL Structure

Table names, column names, sort directions, operators, and other SQL syntax must not be built from untrusted input. If SQL structure needs to vary, map application-level choices to hardcoded SQL fragments with an allowlist.

// For example - don't use untrusted input to build ORDER BY.
// Instead, map to trusted types internally like this:
String orderBy = switch (sort) {
  case NAME -> "name";
  case CREATED_AT -> "created_at";
};

String direction = descending ? "DESC" : "ASC";

List<Employee> employees = database.query("""
  SELECT *
  FROM employee
  ORDER BY %s %s
  """.formatted(orderBy, direction))
  .fetchList(Employee.class);

Pyranid exception messages include bounded SQL and bounded parameter display values. Under the default ParameterRedactor::none(), non-secure, non-batch values render verbatim. Wrap sensitive bind values with Parameters::secure(...) or configure Database.Builder::parameterRedactor(...) when values should not appear in diagnostics. Custom StatementLogger implementations that read StatementContext::getParameters() receive raw values; use StatementContext::getRedactedParameters() for safe display.

Previous
Transactions