# Pyranid Full Documentation
> Full-text export of the public Pyranid documentation.

Canonical site: https://pyranid.com
Index: https://pyranid.com/llms.txt

# Why Pyranid?
URL: https://pyranid.com/
Description: A zero-dependency JDBC interface for modern Java applications, powering production systems since 2015.

Pyranid makes working with JDBC pleasant and puts your relational database first. Writing SQL "just works".  Closure-based transactions are simple to reason about.  Modern Java language features are supported.

No new query languages to learn.  No leaky object-relational abstractions.  No kitchen-sink frameworks that come along for the ride.  No magic.

Pyranid is [commercially-friendly Open Source Software](https://pyranid.com/docs/licensing), proudly powering production systems since 2015.

---

## Design Goals

* Small codebase
* Immutability/thread-safety
* Zero dependencies
* [DI-friendly](https://en.wikipedia.org/wiki/Dependency_injection)
* Contract/interface-driven: bring your own implementations for almost anything

Pyranid is designed to be small and easy to understand - auditable end-to-end by a human or AI agent.

Its [API design](https://javadoc.pyranid.com) aims for a minimal footprint with a high strength-to-weight ratio.

**Do Zero-Dependency Libraries Interest You?**
Similarly-flavored commercially-friendly OSS libraries are available.

* [Soklet](https://www.soklet.com) is a DI-friendly HTTP 1.1 server that supports [Virtual Threads](https://openjdk.org/jeps/444) and [Server-Sent Events](https://developer.mozilla.org/en-US/docs/Web/API/Server-sent_events/Using_server-sent_events)
* [Lokalized](https://www.lokalized.com) enables natural-sounding translations (i18n) via an expression language

---

## Installation

Pyranid is a single JAR, available on Maven Central.

### Maven

#### Java 17+

```xml
<dependency>
  <groupId>com.pyranid</groupId>
  <artifactId>pyranid</artifactId>
  <version>4.7.0</version>
</dependency>
```

#### Java 8+ (legacy; only critical fixes will be applied)

```xml
<dependency>
  <groupId>com.pyranid</groupId>
  <artifactId>pyranid</artifactId>
  <version>1.0.17</version>
</dependency>
```

### Gradle

```js
repositories {
  mavenCentral()
}

dependencies {
  implementation 'com.pyranid:pyranid:4.7.0'
}
```

### Direct Download

For released builds, you can download the Pyranid jar directly from [Maven Central](https://repo1.maven.org/maven2/com/pyranid/pyranid/4.7.0/pyranid-4.7.0.jar).  No other dependencies are required.

## Example Usage

First, obtain a [`javax.sql.DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) and use it to back your [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html).

```java
// Use any javax.sql.DataSource you like
DataSource dataSource = new HikariDataSource(new HikariConfig() {{
  setJdbcUrl("jdbc:postgresql://localhost:5432/my-database");
  setUsername("example");
  setPassword("secret");
  setConnectionInitSql("SET TIME ZONE 'UTC'");
}});

// Initialize with default configuration.
// These instances are threadsafe and intended to be shared across your app
Database database = Database.withDataSource(dataSource).build();
```

Then, define some types...

```java
public enum DepartmentId {
  ACCOUNTING,
  HR
}

public record Employee (
  UUID employeeId,
  DepartmentId departmentId,
  String name,
  BigDecimal salary,
  ZoneId timeZone,
  Locale locale,
  Instant createdAt
) {}
```

...and do some work:

```java
void awardAnnualRaises(DepartmentId departmentId) {
  payrollSystem.startLengthyWarmupProcess();

  // Ensure this set of operations commits or rolls back atomically.
  // A rollback occurs if an exception bubbles out
  database.transaction(() -> {
    // Pull a list of all employees in the department
    List<Employee> employees = database.query("""
      SELECT *
      FROM employee
      WHERE department_id = :departmentId
      """)
      .bind("departmentId", departmentId)
      .fetchList(Employee.class);

    // Get a reference to the transaction that's scoped to this closure
    Transaction transaction = database.currentTransaction().orElseThrow();

    // Calculate and apply a raise for everyone
    for(Employee employee : employees) {
      BigDecimal newSalary = payrollSystem.salaryWithAnnualRaise(employee);

      // Make a savepoint we can roll back to if something goes wrong
      Savepoint savepoint = transaction.createSavepoint();

      try {
        database.query("""
          UPDATE employee
          SET salary = :salary
          WHERE employee_id = :employeeId
          """)
          .bind("salary", newSalary)
          .bind("employeeId", employee.employeeId())
          .execute();
      } catch(DatabaseException e) {
        // Detect a constraint violation and gracefully continue on
        if("salary_too_big".equals(e.getConstraint().orElse(null))) {
          // Put transaction back in good state
          // (prior to constraint violation)
          transaction.rollback(savepoint);

          out.printf("Salary %s is too big for employee %s\n",
            newSalary, employee.employeeId());
        } else {
          // There must have been some other problem, bubble out
          throw e;
        }
      }
    }

    // Schedule some work to be done after this transaction ends
    transaction.addPostTransactionOperation((transactionResult) -> {
      if(transactionResult == TransactionResult.COMMITTED) {
        // Successful commit?
        // Email everyone with the good news
        sendCongratulationsEmail(employees);
      } else if(transactionResult == TransactionResult.ROLLED_BACK) {
        // Rollback completed?
        // Do some additional cleanup
        payrollSystem.cancelLengthyWarmupProcess();
      } else if(transactionResult == TransactionResult.IN_DOUBT) {
        // The final database outcome is unknown.
        // Avoid commit-only side effects and reconcile separately
        payrollSystem.queueReconciliation();
      }
    });
  });
}
```

Want to see what else you can do?  Start with [Configuration](https://pyranid.com/docs/configuration).

---

# Configuration
URL: https://pyranid.com/docs/configuration
Description: How to connect Pyranid to your Database and configure it for use

All data access in Pyranid is performed through a [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance. You can configure it by providing your own implementations of "hook" interfaces to a builder at construction time. Once the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) is created, it is thread-safe and designed to be shared across your app. Mutable [`Query`](https://javadoc.pyranid.com/com/pyranid/Query.html) builders are single-use, single-thread objects. Any database-wide hooks you supply can be invoked concurrently and must be thread-safe.

By design, there is a 1:1 relationship between your [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) and its [`javax.sql.DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html). Create a separate [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) for each [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html)—for example, one backed by a writable primary and another by a load-balanced pool of read replicas.

---

## Minimal Setup

This approach uses out-of-the-box Pyranid defaults.  It gets you up and running quickly to kick the tires.

```java
// Create a Database backed by a DataSource
DataSource dataSource = obtainDataSource();
Database database = Database.withDataSource(dataSource).build();
```

## Customized Setup

This example shows all of the different "hook" interfaces and configuration knobs you might use to customize behavior.

In production systems, at a minimum you will want to provide your own [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html) to keep an eye on your SQL and its performance.  If your app relies on [Dependency Injection](https://en.wikipedia.org/wiki/Dependency_injection), you'll want to wire in your own [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html).

```java
// Controls how Pyranid creates instances of objects
// that represent ResultSet rows
InstanceProvider instanceProvider = new InstanceProvider() {
  @Override
  @NonNull
  public <T> T provide(
    @NonNull StatementContext<T> statementContext,
    @NonNull Class<T> instanceType
  ) {
    // You might have your DI framework vend regular object instances
    return guiceInjector.getInstance(instanceType);
  }

  @Override
  @NonNull
  public <T extends Record> T provideRecord(
    @NonNull StatementContext<T> statementContext,
    @NonNull Class<T> recordType,
    Object @Nullable ... initargs
  ) {
    // If you use Record types, customize their instantiation here.
    // Default implementation will use the canonical constructor
    return InstanceProvider.super.provideRecord(statementContext, recordType, initargs);
  }
};

// Handles copying data from a ResultSet row to an instance of the specified type.
// Supports JavaBeans, records, and standard JDK types out-of-the-box.
// Plan caching (on by default) trades memory for faster mapping of wide ResultSets.
// Normalization locale should match the language of your database tables/column names.
// CustomColumnMappers supply "surgical" overrides to handle custom types.
// If multiple mappers apply, Pyranid tries them in list order.
ResultSetMapper resultSetMapper =
  ResultSetMapper.withPlanCachingEnabled(false)
  .normalizationLocale(Locale.forLanguageTag("pt-BR"))
  .customColumnMappers(List.of(new CustomColumnMapper() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      // Can also apply to parameterized types, e.g.
      // targetType.matchesParameterizedType(List.class, UUID.class) for List<UUID>
      return targetType.matchesClass(Money.class);
    }

    @NonNull
    @Override
    public MappingResult map(
      @NonNull StatementContext<?> statementContext,
      @NonNull ResultSet resultSet,
      @NonNull Object resultSetValue,
      @NonNull TargetType targetType,
      @NonNull Integer columnIndex,
      @Nullable String columnLabel,
      @NonNull InstanceProvider instanceProvider
    ) {
      // Convert the ResultSet column's value to the "appliesTo" Java type.
      // Don't need null checks - this method is only invoked when the value is non-null
      String moneyAsString = resultSetValue.toString();
      Money money = Money.parse(moneyAsString);

      // Or return MappingResult.fallback() to let the next applicable custom mapper run.
      // If none handles the value, Pyranid continues with normal mapping behavior.
      return MappingResult.of(money);
    }
  }))
  .build();

// Binds parameters to a SQL PreparedStatement.
// CustomParameterBinders supply "surgical" overrides to handle custom types.
// If multiple binders apply, Pyranid tries them in list order.
// Here, we transform Money instances into a DB-friendly string representation
PreparedStatementBinder preparedStatementBinder =
  PreparedStatementBinder.withCustomParameterBinders(List.of(
  new CustomParameterBinder() {
    @NonNull
    @Override
    public Boolean appliesTo(@NonNull TargetType targetType) {
      return targetType.matchesClass(Money.class);
    }

    @NonNull
    @Override
    public BindingResult bind(
      @NonNull StatementContext<?> statementContext,
      @NonNull PreparedStatement preparedStatement,
      @NonNull Integer parameterIndex,
      @NonNull Object parameter
    ) throws SQLException {
      // Convert Money to a string representation for binding.
      // Don't need null checks - this method is only invoked when the value is non-null
      Money money = (Money) parameter;
      String moneyAsString = money.stringValue();

      // Bind to the PreparedStatement
      preparedStatement.setString(parameterIndex, moneyAsString);

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

// Optionally logs SQL statements
StatementLogger statementLogger = new StatementLogger() {
  @Override
  public void log(@NonNull StatementLog statementLog) {
    // Send to whatever output sink you'd like
    out.println(statementLog);
  }
};

// Useful if your JVM's default timezone doesn't match your Database's default timezone
ZoneId timeZone = ZoneId.of("UTC");

Database customDatabase = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.POSTGRESQL)
  .timeZone(timeZone)
  .ambiguousTimestampBindingStrategy(TIMESTAMP_WITH_TIME_ZONE)
  .instanceProvider(instanceProvider)
  .resultSetMapper(resultSetMapper)
  .preparedStatementBinder(preparedStatementBinder)
  .statementLogger(statementLogger)
  .queryTimeout(Duration.ofSeconds(30))
  .fetchSize(500)
  .parsedSqlCacheCapacity(1024)
  .build();
```

## Interfaces

### [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html)

* When results are returned from a query, Pyranid needs to create an instance of an object to hold data for each row in the resultset.  The default implementation assumes you have either a [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) instantiable via its canonical constructor or an [`Object`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Object.html) instantiable via [`Class<T>::getDeclaredConstructor(java.lang.Class...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Class.html#getDeclaredConstructor(java.lang.Class...)).  In production systems, you might find it useful to have a Dependency Injection library like [Google Guice](https://github.com/google/guice) vend these instances.

### [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html)

* For each instance created by your [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html), data from the resultset needs to be mapped to it.  By default, reflection is used to determine how to map DB column names to Java property names, with `snake_case` being automatically mapped to `camelCase`. More details are available in the [ResultSet Mapping](https://pyranid.com/docs/resultset-mapping) documentation.

### [`PreparedStatementBinder`](https://javadoc.pyranid.com/com/pyranid/PreparedStatementBinder.html)

* Parameterized SQL statments are ultimately converted to [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) and passed along to your JDBC driver for processing.  This interface allows you to control per-parameter how binding should be performed. More details are available in the [Parameter Binding](https://pyranid.com/docs/parameter-binding) documentation.

### [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html)

* Every database operation has diagnostic information captured and made accessible via a [`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) instance.  You might want to write log slow queries to a logging system like [Logback](https://logback.qos.ch/), send tracing information to [New Relic](https://newrelic.com/) or just write everything to stdout - it's up to you! More details are available in the [Logging and Diagnostics](https://pyranid.com/docs/logging-and-diagnostics) documentation.
* Logger failures are fail-fast and may roll back an active Pyranid transaction. Keep logger implementations lightweight and failure-safe; catch inside the logger, or use [`MetricsCollector`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html), when observability must not affect database behavior.

### Database Type

Pyranid normally auto-detects your [`DatabaseType`](https://javadoc.pyranid.com/com/pyranid/DatabaseType.html) from JDBC metadata when database-type-sensitive behavior is first requested. [`Database.Builder::build()`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#build()) does not open a connection for detection. Detection recognizes PostgreSQL, Oracle, MySQL, MariaDB, SQLite, SQL Server, and DuckDB from JDBC product, URL, driver, and MariaDB-version signals, then falls back to [`GENERIC`](https://javadoc.pyranid.com/com/pyranid/DatabaseType.html#GENERIC). If your [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html), proxy, or pool obscures the underlying product information, or if you want to avoid lazy metadata lookups, override detection explicitly with [`Database.Builder::databaseType(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#databaseType(com.pyranid.DatabaseType)).

Correct database type detection matters because Pyranid has vendor-specific behavior for features like:

* JSON/JSONB binding
* vector binding
* UUID binding
* SQL ARRAY support guards
* temporal mappings
* streaming setup
* `RETURNING`/`OUTPUT`-style statement handling
* exception classification

For example:

```java
Database database = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.POSTGRESQL)
  .build();
```

Pass `null` to [`Database.Builder::databaseType`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#databaseType(com.pyranid.DatabaseType)) if you want to re-enable auto-detection.

Calling [`Database::getDatabaseType()`](https://javadoc.pyranid.com/com/pyranid/Database.html#getDatabaseType()) outside an active query may acquire a fresh connection. Configure [`Database.Builder::databaseType(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#databaseType(com.pyranid.DatabaseType)) explicitly when using very small pools, database proxies, or startup paths that must avoid surprise connection checkouts.

A [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance has one effective database type. If your application uses multiple database engines, create a separate [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) for each and configure its type explicitly.

### Connectivity Health Checks

Use [`Database::performHealthCheck(Duration)`](https://javadoc.pyranid.com/com/pyranid/Database.html#performHealthCheck(java.time.Duration)) when you want an explicit startup or readiness validation step:

```java
database.performHealthCheck(Duration.ofSeconds(2));
```

This borrows a fresh connection, calls JDBC [`Connection::isValid(int)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#isValid(int)), closes the connection, and throws [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) if acquisition or validation fails. It does not execute SQL, so it is portable across JDBC drivers that implement the standard validation API. It does not participate in an active Pyranid transaction.

### Temporal Configuration

[`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) models the database or session zone Pyranid should use for zone-less `TIMESTAMP` values. It affects result-set mapping and parameter binding differently:

* Mapping: `TIMESTAMP` has no zone, so Pyranid interprets it in [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) when producing [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), or [`Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html); [`LocalDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDateTime.html) keeps the wall clock unchanged. `TIMESTAMP WITH TIME ZONE` already identifies an instant.
* Binding: known `TIMESTAMP` targets convert [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html) and [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) through [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) and bind a local timestamp. Known `TIMESTAMP WITH TIME ZONE` targets bind as time-zone-aware timestamps. [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html) parameters are normalized to [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html).

By default, if JDBC parameter metadata is unavailable or non-identifying and Pyranid cannot tell whether an [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html) or [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) parameter targets `TIMESTAMP` or `TIMESTAMP WITH TIME ZONE`, Pyranid binds it as `TIMESTAMP WITH TIME ZONE`.

For drivers or proxies that cannot provide identifying parameter metadata when your target columns are zone-less `TIMESTAMP` values, opt into local timestamp binding:

```java
Database database = Database.withDataSource(dataSource)
  .timeZone(ZoneId.of("UTC"))
  .ambiguousTimestampBindingStrategy(TIMESTAMP_WITHOUT_TIME_ZONE)
  .build();
```

### Parsed SQL Cache

Pyranid caches parsed SQL strings using an LRU cache. The default capacity is 1024; set [`Database.Builder::parsedSqlCacheCapacity`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#parsedSqlCacheCapacity(java.lang.Integer)) to disable caching if you prefer to parse on every query or a non-negative value to tune for your workload.  Using the cache trades a little more memory use for a little less CPU use.

## Addendum: Obtaining a DataSource

Pyranid works with any [javax.sql.DataSource](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) implementation. If you have the freedom to choose, [HikariCP](https://github.com/brettwooldridge/HikariCP) (application-level) and [PgBouncer](https://www.pgbouncer.org/) (external; Postgres-only) are good options.

```java
// HikariCP
DataSource hikariDataSource = new HikariDataSource(new HikariConfig() {{
  setJdbcUrl("jdbc:postgresql://localhost:5432/my-database");
  setUsername("example");
  setPassword("secret");
  setConnectionInitSql("SET TIME ZONE 'UTC'");
}});

// PgBouncer (using Postgres' JDBC driver-provided DataSource impl)
DataSource pgBouncerDataSource = new PGSimpleDataSource() {{
  setServerNames(new String[] {"localhost"});
  setPortNumber(5432);
  setDatabaseName("my-database");
  setUser("example");
  setPassword("secret");
  setPreferQueryMode(PreferQueryMode.SIMPLE);
}};
```

---

# Contributing
URL: https://pyranid.com/docs/contributing
Description: How to contribute code to Pyranid

We are happy to receive contributions!  [Please submit pull requests via GitHub](https://github.com/pyranid/pyranid/pulls).

We require all contributors to electronically sign the below License Agreement as part of creating a pull request.  This Agreement is important because it protects both parties and makes clear the ownership rights and licensing rules as they pertain to your contributions.

Generally speaking, the License Agreement specifies that you must "own" your contributions.  You are not, for example, permitted to contribute proprietary code owned by your employer without their consent.  This protects all users of Pyranid from depending on "poison pill" code or features that would need to be reverted if they were discovered to have been contributed without permission.

The Agreement also stipulates that, should Pyranid change its licensing model, it cannot retroactively "unlicense" your contribution.  This protects your rights as a contributor.  For example, suppose at the time of your contribution, Pyranid falls under the terms of the Apache 2.0 License, and then at some future date Pyranid changes its license to the Mozilla Public License (MPL).  While Pyranid would retain the right to distribute your contribution under the MPL at that time, your contribution would also continue to be available under the terms of the original Apache 2.0 License.

Please direct any questions to [legal@revetware.com](mailto:legal@revetware.com).

## API Compatibility

Pyranid follows semantic-versioning intent for the public `com.pyranid` API: patch and minor releases should remain source- and binary-compatible with the previous released baseline, while intentional breaking changes are reserved for major releases or explicitly called out in migration notes.

The core build runs [`japicmp`](https://siom79.github.io/japicmp/MavenPlugin.html) during `mvn verify` to compare public API against the latest configured release baseline. If an intentional break is being prepared, the check can be skipped with `-Djapicmp.skip=true` and the migration notes should describe the change.

---

## Pyranid Contributor License Agreement

Thank you for your interest in contributing to Pyranid ("We" or "Us").

This contributor agreement ("Agreement") describes the terms and conditions under which you may Submit a Contribution to Us. By Submitting a Contribution to Us, you accept the terms and conditions in the Agreement. If you do not accept the terms and conditions in the Agreement, you must not Submit any Contribution to Us.

This is a legally binding document, so please read it carefully before accepting the terms and conditions. If you accept this Agreement, the then-current version of this Agreement shall apply each time you Submit a Contribution. The Agreement may cover more than one software project managed by Us.

### 1. Definitions

"We" or "Us" means Revetware LLC and its duly appointed and authorized representatives.

"You" means the individual or entity who Submits a Contribution to Us.

"Contribution" means any work of authorship that is Submitted by You to Us in which You own or assert ownership of the Copyright. You may not Submit a Contribution if you do not own the Copyright in the entire work of authorship.

"Copyright" means all rights protecting works of authorship owned or controlled by You, including copyright, moral and neighboring rights, as appropriate, for the full term of their existence including any extensions by You.

"Material" means the work of authorship which is made available by Us to third parties. When this Agreement covers more than one software project, the Material means the work of authorship to which the Contribution was Submitted. After You Submit the Contribution, it may be included in the Material.

"Submit" means any form of electronic, verbal, or written communication sent to Us or our representatives, including but not limited to electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, Us for the purpose of discussing and improving the Material, but excluding communication that is conspicuously marked or otherwise designated in writing by You as "Not a Contribution."

"Submission Date" means the date on which You Submit a Contribution to Us.

"Effective Date" means the date You execute this Agreement or the date You first Submit a Contribution to Us, whichever is earlier.

"Media" means any portion of a Contribution which is not software.

### 2. Grant of Rights

#### 2.1 Copyright License

(a) You retain ownership of the Copyright in Your Contribution and have the same rights to use or license the Contribution which You would have had without entering into the Agreement.

(b) To the maximum extent permitted by the relevant law, You grant to Us a perpetual, worldwide, non-exclusive, transferable, royalty-free, irrevocable license under the Copyright covering the Contribution, with the right to sublicense such rights through multiple tiers of sublicensees, to reproduce, modify, display, perform and distribute the Contribution as part of the Material; provided that this license is conditioned upon compliance with Section 2.3.

#### 2.2 Patent License

For patent claims including, without limitation, method, process, and apparatus claims which You own, control or have the right to grant, now or in the future, You grant to Us a perpetual, worldwide, non-exclusive, transferable, royalty-free, irrevocable patent license, with the right to sublicense these rights to multiple tiers of sublicensees, to make, have made, use, sell, offer for sale, import and otherwise transfer the Contribution and the Contribution in combination with the Material (and portions of such combination). This license is granted only to the extent that the exercise of the licensed rights infringes such patent claims; and provided that this license is conditioned upon compliance with Section 2.3.

#### 2.3 Outbound License

Based on the grant of rights in Sections 2.1 and 2.2, if We include Your Contribution in a Material, We may license the Contribution under any license, including copyleft, permissive, commercial, or proprietary licenses. As a condition on the exercise of this right, We agree to also license the Contribution under the terms of the license or licenses which We are using for the Material on the Submission Date.

#### 2.4 Moral Rights

If moral rights apply to the Contribution, to the maximum extent permitted by law, You waive and agree not to assert such moral rights against Us or our successors in interest, or any of our licensees, either direct or indirect.

#### 2.5 Our Rights

You acknowledge that We are not obligated to use Your Contribution as part of the Material and may decide to include any Contribution We consider appropriate.

#### 2.6 Reservation of Rights

Any rights not expressly licensed under this section are expressly reserved by You.

### 3. Agreement

You confirm that:

(a) You have the legal authority to enter into this Agreement.

(b) You own the Copyright and patent claims covering the Contribution which are required to grant the rights under Section 2.

(c) The grant of rights under Section 2 does not violate any grant of rights which You have made to third parties, including Your employer. If You are an employee, You have had Your employer approve this Agreement or sign the Entity version of this document. If You are less than eighteen years old, please have Your parents or guardian sign the Agreement.

### 4. Disclaimer

EXCEPT FOR THE EXPRESS WARRANTIES IN SECTION 3, THE CONTRIBUTION IS PROVIDED "AS IS". MORE PARTICULARLY, ALL EXPRESS OR IMPLIED WARRANTIES INCLUDING, WITHOUT LIMITATION, ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NON-INFRINGEMENT ARE EXPRESSLY DISCLAIMED BY YOU TO US [AND BY US TO YOU]. TO THE EXTENT THAT ANY SUCH WARRANTIES CANNOT BE DISCLAIMED, SUCH WARRANTY IS LIMITED IN DURATION TO THE MINIMUM PERIOD PERMITTED BY LAW.

### 5. Consequential Damage Waiver

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT WILL YOU [OR US] BE LIABLE FOR ANY LOSS OF PROFITS, LOSS OF ANTICIPATED SAVINGS, LOSS OF DATA, INDIRECT, SPECIAL, INCIDENTAL, CONSEQUENTIAL AND EXEMPLARY DAMAGES ARISING OUT OF THIS AGREEMENT REGARDLESS OF THE LEGAL OR EQUITABLE THEORY (CONTRACT, TORT OR OTHERWISE) UPON WHICH THE CLAIM IS BASED.

### 6. Miscellaneous

#### 6.1

This Agreement will be governed by and construed in accordance with the laws of the state of Pennsylvania, in the United States of America, excluding its conflicts of law provisions. Under certain circumstances, the governing law in this section might be superseded by the United Nations Convention on Contracts for the International Sale of Goods ("UN Convention") and the parties intend to avoid the application of the UN Convention to this Agreement and, thus, exclude the application of the UN Convention in its entirety to this Agreement.

#### 6.2

This Agreement sets out the entire agreement between You and Us for Your Contributions to Us and overrides all other agreements or understandings.

#### 6.3

If You or We assign the rights or obligations received through this Agreement to a third party, as a condition of the assignment, that third party must agree in writing to abide by all the rights and obligations in the Agreement.

#### 6.4

The failure of either party to require performance by the other party of any provision of this Agreement in one situation shall not affect the right of a party to require such performance at any time in the future. A waiver of performance under a provision in one situation shall not be considered a waiver of the performance of the provision in the future or a waiver of the provision in its entirety.

#### 6.5

If any provision of this Agreement is found void and unenforceable, such provision will be replaced to the extent possible with a provision that comes closest to the meaning of the original provision and which is enforceable. The terms and conditions set forth in this Agreement shall apply notwithstanding any failure of essential purpose of this Agreement or any limited remedy to the maximum extent possible under law.

---

# Database-Specific Recipes
URL: https://pyranid.com/docs/database-specific-recipes
Description: Code snippets for less obvious Pyranid behavior on specific databases

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

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

---

## Cross-Database Patterns

### Configure The Database Type Behind Proxies

Pyranid can auto-detect supported databases from JDBC metadata. If a proxy, pool, or wrapper obscures that metadata, configure the [`DatabaseType`](https://javadoc.pyranid.com/com/pyranid/DatabaseType.html) explicitly so dialect-specific binding and mapping still apply.

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

### Prefer IN-List Expansion To SQL ARRAY For Portable Filters

SQL ARRAY binding is not supported by every database or JDBC driver. For ordinary filters, [`Parameters::inList(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(java.util.Collection)) is the portable shape.

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

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

## PostgreSQL

The portable API, durable-reconciliation pattern, callback lifecycle, interruption behavior, and supervision model are covered on the [Notifications](https://pyranid.com/docs/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](https://jdbc.postgresql.org/) 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`](https://javadoc.pyranid.com/com/pyranid/Database.html) over a session-affine listener source:

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

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

### Socket And Queue Behavior

A positive [`awaitNotifications(...)`](<https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#awaitNotifications(java.time.Duration)>) 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()`](<https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#drainNotifications()>) after its non-waiting probe begins consuming a frame.

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

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

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

## YugabyteDB

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

YugabyteDB provides `LISTEN`/`NOTIFY` as an Early Access feature in v2025.2.3 and later. It is disabled by default; enable `ysql_yb_enable_listen_notify=true` on both Masters and TServers. Pyranid cannot inspect that flag through JDBC. If the feature is disabled or absent in the current server version, its `0A000` response becomes an [`UnsupportedOperationException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/UnsupportedOperationException.html) explaining that notifications are unavailable in the current version or configuration. Pyranid's advisory integration leg exercises enabled send/receive with YugabyteDB 2026.1.1.1 and smart driver 42.7.3-yb-4. See the [YugabyteDB feature documentation](https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/cmd_listen_notify/).

## 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.

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

### Use `OUTPUT ... INTO` For Trigger Tables

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

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

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

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

  // Use employeeIds here.
});
```

### Return Upsert Actions From `MERGE`

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

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) and can also map it to the corresponding [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html).

```java
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.

```java
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.

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/UUID.html) values as RFC-4122 bytes for Oracle, which is a good fit for `RAW(16)` columns.

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

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

## MySQL And MariaDB

### Use Generated Keys On MySQL

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

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

### Use `RETURNING` On MariaDB

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

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

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

### Keep MySQL Streams Self-Contained

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

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#json(java.lang.String)) for JSON columns. Pyranid binds MySQL-family JSON as text, which avoids the character-set problems that can happen with generic binary-looking binds.

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

## SQLite

### Use `RETURNING` For Multi-Row Generated IDs

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

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/UUID.html) values as strings for SQLite.

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/math/BigDecimal.html).

```java
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.

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

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

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

## DuckDB

### Map STRUCT Columns To Records

DuckDB STRUCT columns map onto Java records by attribute name, recursively - nested STRUCTs become nested records and STRUCT lists become record lists. A single-column STRUCT result maps directly to a record target. See [SQL STRUCT Results](https://pyranid.com/docs/resultset-mapping#sql-struct-results) for the full rules.

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlStructOf(java.lang.String,java.util.List)) when a bound value must be a DuckDB STRUCT. Declare the complete SQL type and supply its attributes in declaration order.

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html), or [`java.util.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html), the complete inline declaration is required. Pyranid uses it to distinguish `TIMESTAMP` from `TIMESTAMPTZ` and to apply the configured [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) 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[]`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Object.html) factory when an attribute is `null`, because [`List.of(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/List.html#of(E...)) rejects null elements:

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

### Bind Multidimensional Arrays Recursively

Pyranid recursively materializes nested [`SqlArrayParameter`](https://javadoc.pyranid.com/com/pyranid/SqlArrayParameter.html) values, and multidimensional binding is integration-verified on DuckDB. Represent each dimension with its own [`Parameters::sqlArrayOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,java.util.List)). Each containing level's [`baseTypeName`](https://javadoc.pyranid.com/com/pyranid/SqlArrayParameter.html#getBaseTypeName()) names its element type, so an outer `VARCHAR[][]` value uses `VARCHAR[]` while each inner array uses `VARCHAR`:

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html), or [`java.util.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html), 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(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKey(java.lang.Class,java.lang.String...)) fails fast with a clear [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html). Use a sequence default plus `RETURNING` instead.

```java
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.

```java
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(...)`.

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

### Retry Write-Write Conflicts

DuckDB uses optimistic concurrency control: concurrent writes to the same rows fail with a `TransactionContext Error` conflict rather than blocking. Pyranid classifies these as retryable serialization failures, so [`Database::transactionWithRetry(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transactionWithRetry(com.pyranid.RetryPolicy,com.pyranid.TransactionalOperation)) with a [`RetryPolicy`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.html) handles them cleanly.

### Prefer SQL `LIMIT` To `maxRows`

DuckDB's driver accepts [`Query::maxRows(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#maxRows(java.lang.Integer)) but does not enforce it. Express row limits in SQL.

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchStream(java.lang.Class,java.util.function.Function)) keeps mapping callback-scoped, but the DuckDB JDBC driver materializes a result by default. Set its [`jdbc_stream_results`](https://duckdb.org/docs/current/clients/java/result_handling#streaming-results) connection property to opt into lazy result streaming.

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

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

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

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

### Read Strings From Legacy Parquet Writers

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

```java
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](https://duckdb.org/docs/current/data/parquet/overview#parameters).

### 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:

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

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

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

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

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

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

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

See DuckDB's [connection and instance-cache documentation](https://duckdb.org/docs/current/clients/java/connecting#database-instances-and-instance-caching) for the full URL and lifetime rules.

### Keep Typed Raw JDBC Work Callback-Scoped

Pyranid's ordinary [`Database::useRawConnection(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(com.pyranid.RawConnectionOperation)) overload exposes a guarded standard JDBC connection. When a DuckDB feature requires `DuckDBConnection`, request that type explicitly with the [typed overload](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(java.lang.Class,com.pyranid.RawConnectionOperation)):

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

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

### Bulk-Load With The Appender

DuckDB's [Appender](https://duckdb.org/docs/current/clients/java/data_import#appender) avoids SQL parsing and per-row JDBC overhead for large inserts.

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

    appender.flush();
  }

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

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

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

```java
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.

```java
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](https://duckdb.org/docs/current/clients/java/result_handling#arrow-methods).

### Consume Columnar Chunks Before Advancing

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

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

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

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

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

The current chunk and its vectors become invalid on the next `nextChunk()` call or when the result closes; never retain them. The driver currently supports prepared statements and basic scalar types only, not LIST or STRUCT results. For most application queries, Pyranid's normal mapping and [`Query::fetchStream(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchStream(java.lang.Class,java.util.function.Function)) remain simpler.

### Profile And Monitor On The Same Connection

Portable timing, timeout, and cancellation are available through Pyranid's metrics, [`Query::queryTimeout(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#queryTimeout(java.time.Duration)), and standard JDBC cancellation. `EXPLAIN ANALYZE` also works as ordinary DuckDB SQL. Use the driver API when you need its formatted profile for the most recent query:

```java
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](https://duckdb.org/docs/current/clients/java/profiling).

### Register Java Functions Deliberately

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

```java
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](https://duckdb.org/docs/current/clients/java/functions) for vectorized and table-function contracts.

---

# Error Handling
URL: https://pyranid.com/docs/error-handling
Description: How to handle errors with Pyranid

Checked exceptions are an important feature of Java, but were a design misstep as implemented in JDBC.

Pyranid generally reports JDBC and database failures as a runtime [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html), often wrapping the checked [`java.sql.SQLException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/SQLException.html). Notification session and receive methods deliberately declare [`InterruptedException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/InterruptedException.html) so you can distinguish normal cooperative cancellation from a database failure; see [Interruption And Supervision](https://pyranid.com/docs/notifications#interruption-and-supervision).

---

## Practical Application

Here we detect if a unique constraint was violated by examining [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html).
We then handle that case specially by rolling back to a known-good savepoint.

```java
// Gives someone at most one big award
database.transaction(() -> {
  Transaction transaction = database.currentTransaction().orElseThrow();
  Savepoint savepoint = transaction.createSavepoint();

  try {
    // We don't want to give someone the same award twice!
    // Let the DBMS worry about constraint checking to avoid race conditions
    database.query("""
      INSERT INTO account_award (account_id, award_type)
      VALUES (:accountId, :awardType)
      """)
      .bind("accountId", accountId)
      .bind("awardType", AwardType.BIG)
      .execute();
  } catch(DatabaseException e) {
    // Detect a unique constraint violation and gracefully continue on.
    if(e.isUniqueConstraintViolation()) {
      out.printf("The %s award was already given to account ID %s\n",
        AwardType.BIG, accountId);
      // Puts transaction back in good state
      // (prior to constraint violation)
      transaction.rollback(savepoint);
    } else {
      // There must have been some other problem, bubble out
      throw e;
    }
  }
});
```

#### References:

* [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html)
* [`Transaction::createSavepoint()`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#createSavepoint())
* [`Transaction::rollback(java.sql.Savepoint)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#rollback(java.sql.Savepoint))

## Exception Properties

For convenience, [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) exposes additional properties, which are populated if provided by the underlying [`java.sql.SQLException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/SQLException.html):

* [`errorCode`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getErrorCode()) (optional)
* [`sqlState`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getSqlState()) (optional)

It also exposes conservative classification predicates:

* [`isUniqueConstraintViolation()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isUniqueConstraintViolation())
* [`isForeignKeyViolation()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isForeignKeyViolation())
* [`isDeadlock()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isDeadlock())
* [`isTransient()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isTransient())
* [`isSerializationFailure()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isSerializationFailure())
* [`isTimeout()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isTimeout())

These predicates are intentionally conservative and database-type-aware. They recognize well-known SQLState and vendor error codes for PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, Oracle, and generic JDBC transient/recoverable exception classes; they return `false` when Pyranid cannot reliably classify the failure.

DuckDB's driver reports no SQLStates or vendor codes, so classification there matches DuckDB's stable diagnostic message shapes: `Constraint Error:` duplicate-key and foreign-key messages drive the constraint predicates, and optimistic-concurrency `TransactionContext Error: ... conflict` failures classify as retryable serialization failures, integrating with [`RetryPolicy`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.html). DuckDB query timeouts surface as JDBC [`SQLTimeoutException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/SQLTimeoutException.html) and classify generically.
The predicates return non-null `Boolean` values.

For PostgreSQL, the following properties are also available:

* [`column`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getColumn()) (optional)
* [`constraint`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getConstraint()) (optional)
* [`datatype`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getDatatype()) (optional)
* [`detail`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getDetail()) (optional)
* [`file`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getFile()) (optional)
* [`hint`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getHint()) (optional)
* [`internalPosition`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getInternalPosition()) (optional)
* [`internalQuery`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getInternalQuery()) (optional)
* [`line`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getLine()) (optional)
* [`dbmsMessage`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getDbmsMessage()) (optional)
* [`position`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getPosition()) (optional)
* [`routine`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getRoutine()) (optional)
* [`schema`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getSchema()) (optional)
* [`severity`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getSeverity()) (optional)
* [`table`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getTable()) (optional)
* [`where`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getWhere()) (optional)

---

# Licensing
URL: https://pyranid.com/docs/licensing
Description: How Pyranid is Licensed

Pyranid was created by [Mark Allen](https://www.revetkn.com) in 2015.  Its development is sponsored by [Revetware LLC](https://www.revetware.com), [Transmogrify, LLC](https://www.xmog.com), and [Cobalt Innovations, Inc.](https://www.cobaltinnovations.org)

Pyranid is released under the terms of the [Apache 2.0 License](https://www.apache.org/licenses/LICENSE-2.0), reproduced below.

---

## Apache 2.0 License

```text
                             Apache License
                       Version 2.0, January 2004
                    http://www.apache.org/licenses/

TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION

1. Definitions.

  "License" shall mean the terms and conditions for use, reproduction,
  and distribution as defined by Sections 1 through 9 of this document.

  "Licensor" shall mean the copyright owner or entity authorized by
  the copyright owner that is granting the License.

  "Legal Entity" shall mean the union of the acting entity and all
  other entities that control, are controlled by, or are under common
  control with that entity. For the purposes of this definition,
  "control" means (i) the power, direct or indirect, to cause the
  direction or management of such entity, whether by contract or
  otherwise, or (ii) ownership of fifty percent (50%) or more of the
  outstanding shares, or (iii) beneficial ownership of such entity.

  "You" (or "Your") shall mean an individual or Legal Entity
  exercising permissions granted by this License.

  "Source" form shall mean the preferred form for making modifications,
  including but not limited to software source code, documentation
  source, and configuration files.

  "Object" form shall mean any form resulting from mechanical
  transformation or translation of a Source form, including but
  not limited to compiled object code, generated documentation,
  and conversions to other media types.

  "Work" shall mean the work of authorship, whether in Source or
  Object form, made available under the License, as indicated by a
  copyright notice that is included in or attached to the work
  (an example is provided in the Appendix below).

  "Derivative Works" shall mean any work, whether in Source or Object
  form, that is based on (or derived from) the Work and for which the
  editorial revisions, annotations, elaborations, or other modifications
  represent, as a whole, an original work of authorship. For the purposes
  of this License, Derivative Works shall not include works that remain
  separable from, or merely link (or bind by name) to the interfaces of,
  the Work and Derivative Works thereof.

  "Contribution" shall mean any work of authorship, including
  the original version of the Work and any modifications or additions
  to that Work or Derivative Works thereof, that is intentionally
  submitted to Licensor for inclusion in the Work by the copyright owner
  or by an individual or Legal Entity authorized to submit on behalf of
  the copyright owner. For the purposes of this definition, "submitted"
  means any form of electronic, verbal, or written communication sent
  to the Licensor or its representatives, including but not limited to
  communication on electronic mailing lists, source code control systems,
  and issue tracking systems that are managed by, or on behalf of, the
  Licensor for the purpose of discussing and improving the Work, but
  excluding communication that is conspicuously marked or otherwise
  designated in writing by the copyright owner as "Not a Contribution."

  "Contributor" shall mean Licensor and any individual or Legal Entity
  on behalf of whom a Contribution has been received by Licensor and
  subsequently incorporated within the Work.

2. Grant of Copyright License. Subject to the terms and conditions of
  this License, each Contributor hereby grants to You a perpetual,
  worldwide, non-exclusive, no-charge, royalty-free, irrevocable
  copyright license to reproduce, prepare Derivative Works of,
  publicly display, publicly perform, sublicense, and distribute the
  Work and such Derivative Works in Source or Object form.

3. Grant of Patent License. Subject to the terms and conditions of
  this License, each Contributor hereby grants to You a perpetual,
  worldwide, non-exclusive, no-charge, royalty-free, irrevocable
  (except as stated in this section) patent license to make, have made,
  use, offer to sell, sell, import, and otherwise transfer the Work,
  where such license applies only to those patent claims licensable
  by such Contributor that are necessarily infringed by their
  Contribution(s) alone or by combination of their Contribution(s)
  with the Work to which such Contribution(s) was submitted. If You
  institute patent litigation against any entity (including a
  cross-claim or counterclaim in a lawsuit) alleging that the Work
  or a Contribution incorporated within the Work constitutes direct
  or contributory patent infringement, then any patent licenses
  granted to You under this License for that Work shall terminate
  as of the date such litigation is filed.

4. Redistribution. You may reproduce and distribute copies of the
  Work or Derivative Works thereof in any medium, with or without
  modifications, and in Source or Object form, provided that You
  meet the following conditions:

  (a) You must give any other recipients of the Work or
      Derivative Works a copy of this License; and

  (b) You must cause any modified files to carry prominent notices
      stating that You changed the files; and

  (c) You must retain, in the Source form of any Derivative Works
      that You distribute, all copyright, patent, trademark, and
      attribution notices from the Source form of the Work,
      excluding those notices that do not pertain to any part of
      the Derivative Works; and

  (d) If the Work includes a "NOTICE" text file as part of its
      distribution, then any Derivative Works that You distribute must
      include a readable copy of the attribution notices contained
      within such NOTICE file, excluding those notices that do not
      pertain to any part of the Derivative Works, in at least one
      of the following places: within a NOTICE text file distributed
      as part of the Derivative Works; within the Source form or
      documentation, if provided along with the Derivative Works; or,
      within a display generated by the Derivative Works, if and
      wherever such third-party notices normally appear. The contents
      of the NOTICE file are for informational purposes only and
      do not modify the License. You may add Your own attribution
      notices within Derivative Works that You distribute, alongside
      or as an addendum to the NOTICE text from the Work, provided
      that such additional attribution notices cannot be construed
      as modifying the License.

  You may add Your own copyright statement to Your modifications and
  may provide additional or different license terms and conditions
  for use, reproduction, or distribution of Your modifications, or
  for any such Derivative Works as a whole, provided Your use,
  reproduction, and distribution of the Work otherwise complies with
  the conditions stated in this License.

5. Submission of Contributions. Unless You explicitly state otherwise,
  any Contribution intentionally submitted for inclusion in the Work
  by You to the Licensor shall be under the terms and conditions of
  this License, without any additional terms or conditions.
  Notwithstanding the above, nothing herein shall supersede or modify
  the terms of any separate license agreement you may have executed
  with Licensor regarding such Contributions.

6. Trademarks. This License does not grant permission to use the trade
  names, trademarks, service marks, or product names of the Licensor,
  except as required for reasonable and customary use in describing the
  origin of the Work and reproducing the content of the NOTICE file.

7. Disclaimer of Warranty. Unless required by applicable law or
  agreed to in writing, Licensor provides the Work (and each
  Contributor provides its Contributions) on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
  implied, including, without limitation, any warranties or conditions
  of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A
  PARTICULAR PURPOSE. You are solely responsible for determining the
  appropriateness of using or redistributing the Work and assume any
  risks associated with Your exercise of permissions under this License.

8. Limitation of Liability. In no event and under no legal theory,
  whether in tort (including negligence), contract, or otherwise,
  unless required by applicable law (such as deliberate and grossly
  negligent acts) or agreed to in writing, shall any Contributor be
  liable to You for damages, including any direct, indirect, special,
  incidental, or consequential damages of any character arising as a
  result of this License or out of the use or inability to use the
  Work (including but not limited to damages for loss of goodwill,
  work stoppage, computer failure or malfunction, or any and all
  other commercial damages or losses), even if such Contributor
  has been advised of the possibility of such damages.

9. Accepting Warranty or Additional Liability. While redistributing
  the Work or Derivative Works thereof, You may choose to offer,
  and charge a fee for, acceptance of support, warranty, indemnity,
  or other liability obligations and/or rights consistent with this
  License. However, in accepting such obligations, You may act only
  on Your own behalf and on Your sole responsibility, not on behalf
  of any other Contributor, and only if You agree to indemnify,
  defend, and hold each Contributor harmless for any liability
  incurred by, or claims asserted against, such Contributor by reason
  of your accepting any such warranty or additional liability.

END OF TERMS AND CONDITIONS
```

---

# Logging and Diagnostics
URL: https://pyranid.com/docs/logging-and-diagnostics
Description: How to log your SQL with Pyranid

Pyranid provides tooling for runtime insight into SQL execution.

For many applications, it's important to understand exactly what queries are being run to keep on top of potential performance problems.

Further, it's often useful to mark specific queries for special processing - for example, you might want to log particularly slow or "special" SQL, or you might want to flag a query as needing [Custom ResultSet Mapping](https://pyranid.com/docs/resultset-mapping#custom-mapping).

---

## Statement Logging

You may customize your [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) with a [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html).

Reasons you might do this include:

* Writing queries and timing information to your logging system
* Picking out slow queries for special logging/reporting
* Collecting a set of queries executed across a unit of work for bulk analysis (e.g. a [`ThreadLocal<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/ThreadLocal.html) or [`ScopedValue<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/ScopedValue.html) scoped to a single web request)

```java
Database database = Database.withDataSource(dataSource)
  .statementLogger(new StatementLogger() {
    Duration SLOW_QUERY_THRESHOLD = Duration.ofMillis(500);

    @Override
    public void log(@NonNull StatementLog statementLog) {
      if(statementLog.getTotalDuration().compareTo(SLOW_QUERY_THRESHOLD) > 0)
        out.printf("Slow query: %s\n", statementLog);
    }
  }).build();
```

[`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html) failures are intentionally fail-fast. If your logger throws after a statement otherwise succeeds, that exception propagates to the caller. Inside a Pyranid transaction, logger failures participate in normal transaction failure handling and cause rollback. If the statement itself failed, the logger failure is attached as a suppressed exception to the primary failure.

Keep logger implementations lightweight and failure-safe. If logging must never affect database writes, catch and handle exceptions inside your logger implementation, or use [`MetricsCollector`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html) for best-effort observability.

[`StatementContext::toString()`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html#toString()) and [`StatementLog::toString()`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#toString()) use [`StatementContext::getRedactedParameters()`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html#getRedactedParameters()). Values wrapped with [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) render as their mask:

```java
// Configure a simple StatementLogger that writes to stdout
Database database = Database.withDataSource(dataSource)
  .statementLogger(statementLog -> System.out.println(statementLog))
  .build();

database.query("INSERT INTO api_credential (account_id, token_hash) VALUES (:accountId, :tokenHash)")
  .bind("accountId", "acct_123")
  .bind("tokenHash", Parameters.secure("token_hash_abc123"))
  .execute();
```

The logged [`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) includes a redacted parameter display:

```text
parameters=[acct_123, <redacted>]
```

Custom loggers can choose raw or display-safe parameters explicitly:

```java
// Raw values, trusted code only
statementLog.getStatementContext().getParameters();

// Safe display values
statementLog.getStatementContext().getRedactedParameters();
```

Pyranid exception messages use the same bounded parameter display. Under the default [`ParameterRedactor::none()`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html#none()), non-secure, non-batch values render verbatim; wrap sensitive bind values with [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) or configure [`Database.Builder::parameterRedactor(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#parameterRedactor(com.pyranid.ParameterRedactor)) when exception text may leave a trusted boundary.

Pyranid also makes a best-effort attempt to scrub verbatim occurrences of [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) values from [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) messages and DBMS metadata fields when the database *driver* echoed them into its own error text (for example, PostgreSQL constraint-violation detail). The raw driver exception is deliberately preserved as the [`getCause()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Throwable.html#getCause()) and is never sanitized - treat the cause chain as sensitive. See [What Redaction Does and Does Not Cover](https://pyranid.com/docs/parameter-binding#what-redaction-does-and-does-not-cover) for the full coverage table.

On driver-failure paths, the [`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) carries Pyranid's wrapped [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) - so logged diagnostics render scrubbed, redaction-aware text - with the raw driver exception available via its `getCause()`.

[`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) instances give you access to the following for each SQL statement executed:

* [`statementContext`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getStatementContext())
* [`connectionAcquisitionDuration`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getConnectionAcquisitionDuration()) (optional)
* [`preparationDuration`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getPreparationDuration()) (optional)
* [`executionDuration`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getExecutionDuration()) (optional)
* [`resultSetMappingDuration`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getResultSetMappingDuration()) (optional)
* [`batchSize`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getBatchSize()) (optional)
* [`exception`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html#getException()) (optional)

#### References:

* [`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html)
* [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html)

## Statement Identifiers

Use [`Query::id(Object)`](https://javadoc.pyranid.com/com/pyranid/Query.html#id(java.lang.Object)) to tag statements.

This is useful for tagging queries that should be handled specially.  Why might you do this?

* To mark a query as "hot" so we don't pollute logs with it
* To mark a query as "known to be slow" so we don't flag slow query alerts for it
* Your [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html) might key on it to provide custom instances (e.g. during resultset mapping)

For example:

```java
// This query fires every 3 seconds -
// let's give it a special identifier so we know not to log it.
Executors.newScheduledThreadPool(1).scheduleAtFixedRate(() -> {
  // Safely "claim" some messages off the queue
  List<Message> messages = database.transaction(() ->
      Optional.of(database.query("""
        UPDATE message_queue
        SET message_status_id = :processing
        WHERE id IN (
          SELECT id
          FROM message_queue
          WHERE message_status_id = :unprocessed
          ORDER BY created_at
          LIMIT :limit
          FOR UPDATE
          SKIP LOCKED
        )
        RETURNING *
        """)
        .id("message-queue-claim")
        .bind("unprocessed", MessageStatusId.UNPROCESSED)
        .bind("processing", MessageStatusId.PROCESSING)
        .bind("limit", BATCH_SIZE)
        .fetchList(Message.class)))
      .orElseThrow();

  // Implementation not shown
  processMessages(messages);
}, 0, 3, TimeUnit.SECONDS);
```

A corresponding [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) setup:

```java
// Ensure our StatementLogger implementation takes HOT_QUERY into account
Database database = Database.withDataSource(dataSource)
  .statementLogger(new StatementLogger() {
    @Override
    public void log(@NonNull StatementLog statementLog) {
      // Log everything except "message-queue-claim"
      Statement statement = statementLog.getStatementContext().getStatement();
      if(!statement.getId().equals("message-queue-claim"))
        out.println(statementLog);
    }
  }).build();
```

#### References:

* [`Query::id(Object)`](https://javadoc.pyranid.com/com/pyranid/Query.html#id(java.lang.Object))
* [`StatementContext<T>`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html)

## Database Metadata

The JDBC standard defines a [`DatabaseMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/DatabaseMetaData.html) type, which provides comprehensive vendor-specific information about a database as a whole.

Pyranid exposes a [`Database::readDatabaseMetaData(DatabaseMetaDataReader)`](https://javadoc.pyranid.com/com/pyranid/Database.html#readDatabaseMetaData(com.pyranid.DatabaseMetaDataReader)) method which acquires a transient [`DatabaseMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/DatabaseMetaData.html) instance on its own newly-borrowed JDBC [`Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html) (managed by Pyranid internally).

The [`Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html) does *not* participate in the active transaction, if one exists.

The [`Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html) is automatically closed as soon as the closure completes.

```java
Database database = Database.withDataSource(...).build();

database.readDatabaseMetaData(databaseMetaData -> {
  // e.g. "PostgreSQL"
  out.println(databaseMetaData.getDatabaseProductName());
});
```

**Warning: Caution!**
Your code should _not_ retain a reference to the [`DatabaseMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/DatabaseMetaData.html) instance outside the scope of the closure.

#### References:

* [`DatabaseMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/DatabaseMetaData.html)
* [`DatabaseMetaDataReader`](https://javadoc.pyranid.com/com/pyranid/DatabaseMetaDataReader.html)
* [`Database::readDatabaseMetaData(DatabaseMetaDataReader)`](https://javadoc.pyranid.com/com/pyranid/Database.html#readDatabaseMetaData(com.pyranid.DatabaseMetaDataReader))

---

# Metrics Collection
URL: https://pyranid.com/docs/metrics-collection
Description: Collect and export operational metrics from Pyranid

Pyranid exposes a [`MetricsCollector`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html) hook for low-cardinality database metrics. It is separate from [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html): use the logger for diagnostics and SQL detail, and use metrics collectors for counters, gauges, and histograms.

Metrics collection is disabled by default. Collector failures are swallowed by Pyranid so metrics cannot change database behavior.

## Core Configuration

Use [`MetricsCollector::inMemoryInstance()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#inMemoryInstance()>) for in-process counters in tests or ad-hoc inspection:

```java
MetricsCollector metricsCollector = MetricsCollector.inMemoryInstance();

Database database = Database.withDataSource(dataSource)
  .metricsCollector(metricsCollector)
  .build();

MetricsCollector.Snapshot snapshot = metricsCollector.snapshot().orElseThrow();
```

Passing `null` or omitting [`Database.Builder::metricsCollector(...)`](<https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#metricsCollector(com.pyranid.MetricsCollector)>) disables metrics collection:

```java
Database database = Database.withDataSource(dataSource)
  .metricsCollector(null)
  .build();
```

The collector is fixed at [`Database.Builder::build()`](<https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#build()>) time. To reconfigure metrics, build another [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html).

## Notification Metrics

See [Notifications](https://pyranid.com/docs/notifications) for the public API, reconciliation model, and listener lifecycle.

Pyranid supports low-cardinality lifecycle callbacks for callback-scoped notification sessions:

- [`willOpenNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#willOpenNotificationSession(com.pyranid.DatabaseType,java.util.UUID)>)
- [`didOpenNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#didOpenNotificationSession(com.pyranid.DatabaseType,java.util.UUID,java.time.Duration)>)
- [`didFailToOpenNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#didFailToOpenNotificationSession(com.pyranid.DatabaseType,java.util.UUID,java.time.Duration,java.lang.Throwable)>)
- [`didDeliverNotificationBatch(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#didDeliverNotificationBatch(com.pyranid.DatabaseType,java.util.UUID,java.lang.Long)>)
- [`didLoseNotificationConnection(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#didLoseNotificationConnection(com.pyranid.DatabaseType,java.util.UUID,java.lang.Throwable)>)
- [`didCloseNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#didCloseNotificationSession(com.pyranid.DatabaseType,java.util.UUID,com.pyranid.MetricsCollector.NotificationSessionOutcome,java.time.Duration,java.lang.Throwable)>)

An invocation that reaches [`willOpenNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#willOpenNotificationSession(com.pyranid.DatabaseType,java.util.UUID)>) produces exactly one open or failed-open event. An opened session produces exactly one close event after cleanup, with an outcome of [`CALLBACK_RETURNED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#CALLBACK_RETURNED), [`INTERRUPTED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#INTERRUPTED), or [`FAILED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#FAILED). Delivered-batch metrics report only a count; Pyranid never puts channel names or payload contents into notification metrics.

The in-memory collector exposes a separate [`MetricsCollector.NotificationSnapshot`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSnapshot.html) through [`notificationSnapshot()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#notificationSnapshot()>). Its seven counters cover sessions started, opened, callback-returned, interrupted, and failed, plus delivered batches and delivered notifications. Notification counters deliberately do not change the existing [`MetricsCollector.Snapshot`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.Snapshot.html) record shape.

These are cumulative counters, not current-state gauges: [`sessionsOpened()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSnapshot.html#sessionsOpened()>) does not mean that a session is still open. The [`CALLBACK_RETURNED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#CALLBACK_RETURNED) outcome means the callback and cleanup completed normally, [`INTERRUPTED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#INTERRUPTED) means cooperative interruption ended an opened session cleanly, and [`FAILED`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSessionOutcome.html#FAILED) includes real callback, transport, or cleanup failure. A sustained zero delivery count alongside session starts/opens is a reason to inspect your application's traffic and database topology; it is not itself proof of a broken or healthy quiet connection.

Metrics belong to the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance that performs the work. Session lifecycle metrics go to the collector configured on the instance whose [`withNotificationSession(...)`](<https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.util.Set,com.pyranid.NotificationSessionOperation)>) method is invoked; send and ordinary statement metrics go to the executing instance. If your application uses separate listener and ordinary-work [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instances, pass the same thread-safe collector to both if their metrics should be aggregated.

## OpenTelemetry

Use on the optional [`pyranid-otel`](https://github.com/pyranid/pyranid-otel) artifact if you'd like to export metrics to [OpenTelemetry](https://opentelemetry.io/):

```xml
<dependency>
  <groupId>com.pyranid</groupId>
  <artifactId>pyranid-otel</artifactId>
  <version>1.3.0</version>
</dependency>
```

...and wire it in like this:

```java
OpenTelemetryMetricsCollector metricsCollector = OpenTelemetryMetricsCollector
  .withOpenTelemetry(openTelemetry)
  .poolName("primary")
  .namespace("orders")
  .recordCollectionName(false)
  .build();

Database database = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.POSTGRESQL)
  .metricsCollector(metricsCollector)
  .build();
```

Specifying [`poolName(null)`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.Builder.html#poolName(java.lang.String)>) or leaving it unset skips pool-name-gated connection metrics. Setting [`namespace(null)`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.Builder.html#namespace(java.lang.String)>) omits `db.namespace`; Pyranid does not look up a namespace from JDBC metadata. Using [`recordCollectionName(false)`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.Builder.html#recordCollectionName(java.lang.Boolean)>) is the default because collection/table names can be too high-cardinality for some deployments.

If first-use transaction metrics need a precise `db.system.name`, configure [`Database.Builder::databaseType(...)`](<https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#databaseType(com.pyranid.DatabaseType)>). Otherwise lazy detection may report `other_sql` for transaction-only events before a statement has detected the database type.

The `pyranid-otel` adapter exports notification-session opening, lifetime, active-session, delivery-batch, and connection-loss metrics. The batch-size histogram's aggregation count is the number of delivered batches and its sum is the number of delivered notifications. Notification sends continue through Pyranid's ordinary statement metrics because sending is an ordinary database operation.

## OpenTelemetry Metrics

| Metric | Stability | Type | Unit | Notes |
| --- | --- | --- | --- | --- |
| `db.client.operation.duration` | Stable | Histogram | `s` | Statement operation duration |
| `db.client.response.returned_rows` | Development | Histogram | `{row}` | Emitted when Pyranid observes returned rows |
| `db.client.connection.wait_time` | Development | Histogram | `s` | Emitted only when [`poolName(...)`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.Builder.html#poolName(java.lang.String)>) is configured |
| `db.client.connection.use_time` | Development | Histogram | `s` | Connection hold time; not exact physical transaction lifetime |

Connection pool internals such as idle count, max count, pending requests, timeouts, and create time are intentionally not emitted by Pyranid. Use your pool or proxy exporter for those.

## Pyranid Metrics

| Metric | Type | Unit |
| --- | --- | --- |
| `pyranid.statement.preparation.duration` | Histogram | `s` |
| `pyranid.statement.execution.duration` | Histogram | `s` |
| `pyranid.statement.mapping.duration` | Histogram | `s` |
| `pyranid.statement.errors` | Counter | `{statement}` |
| `pyranid.statement.batch.size` | Histogram | `{statement}` |
| `pyranid.statement.rows_affected` | Histogram | `{row}` |
| `pyranid.transaction.closure.duration` | Histogram | `s` |
| `pyranid.transaction.commit.duration` | Histogram | `s` |
| `pyranid.transaction.rollback.duration` | Histogram | `s` |
| `pyranid.transaction.physical.begin_failures` | Counter | `{transaction}` |
| `pyranid.transaction.count` | Counter | `{transaction}` |
| `pyranid.transaction.active` | Up-down counter | `{transaction}` |
| `pyranid.savepoint.operations` | Counter | `{operation}` |
| `pyranid.fetchstream.duration` | Histogram | `s` |
| `pyranid.fetchstream.rows_consumed` | Histogram | `{row}` |
| `pyranid.post_transaction.operations` | Counter | `{operation}` |
| `pyranid.post_transaction.duration` | Histogram | `s` |
| `pyranid.notification.session.open.duration` | Histogram | `s` |
| `pyranid.notification.session.duration` | Histogram | `s` |
| `pyranid.notification.session.active` | Up-down counter | `{session}` |
| `pyranid.notification.batch.size` | Histogram | `{notification}` |
| `pyranid.notification.connection.losses` | Counter | `{connection}` |

## Attribute Policy

[`OpenTelemetryMetricsCollector`](https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.html) keeps default attributes bounded. It emits `db.system.name`, `db.operation.name`, configured `db.namespace`, the full SQLSTATE as `db.response.status_code` on failures by default, and `error.type` using an underlying [`SQLException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/SQLException.html) or otherwise the deepest available application cause. Configure [`recordFullSqlState(false)`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.Builder.html#recordFullSqlState(java.lang.Boolean)>) to emit only the two-character SQLSTATE class.

Every notification instrument includes `db.system.name` and includes configured `db.namespace` and `db.client.connection.pool.name` when present. Session-open duration adds `pyranid.notification.session.open_outcome` with `success` or `failure`; session duration adds `pyranid.notification.session.outcome` with `callback_returned`, `interrupted`, or `failed`. The `error.type` attribute is included for failed session opens, connection losses, and failed session closes when a throwable is available.

Notification metrics never include channel names, payloads, session UUIDs, or backend process identifiers. This keeps the default attribute set bounded and avoids exporting application data.

[`Statement::getId()`](https://javadoc.pyranid.com/com/pyranid/Statement.html#getId()) from [`Query::id(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#id(java.lang.Object)) is not emitted by the OTel collector. It remains available to custom collectors and [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html).

## Snapshot Semantics

The default [`MetricsCollector::disabledInstance()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#disabledInstance()>) collector collects nothing, so [`snapshot()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#snapshot()>) and [`notificationSnapshot()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#notificationSnapshot()>) return [`Optional::empty()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html#empty()).

The opt-in in-memory collector returned by [`MetricsCollector::inMemoryInstance()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#inMemoryInstance()>) maintains local counters and returns nonempty optionals from both methods: [`MetricsCollector.Snapshot`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.Snapshot.html) from [`snapshot()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#snapshot()>) and [`MetricsCollector.NotificationSnapshot`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.NotificationSnapshot.html) from [`notificationSnapshot()`](<https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#notificationSnapshot()>). These snapshots are intended for tests or local inspection. [`MetricsCollector::reset()`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html#reset()) is best-effort; concurrent updates may race with reset operations.

[`OpenTelemetryMetricsCollector`](https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.html) does not maintain a parallel set of in-process counters. Its [`snapshot()`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.html#snapshot()>) and [`notificationSnapshot()`](<https://otel.javadoc.pyranid.com/com/pyranid/otel/OpenTelemetryMetricsCollector.html#notificationSnapshot()>) methods therefore return [`Optional::empty()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html#empty()); read its metrics through the configured OpenTelemetry SDK or exporter instead.

---

# Notifications
URL: https://pyranid.com/docs/notifications
Description: Send and receive transient database notifications with Pyranid

Pyranid provides a small, database-neutral API for named notifications. PostgreSQL [`LISTEN`](https://www.postgresql.org/docs/current/sql-listen.html)/[`NOTIFY`](https://www.postgresql.org/docs/current/sql-notify.html) is the only currently supported implementation. The public API deliberately contains no PostgreSQL or JDBC-driver types; Pyranid might support other database notification systems in the future.

**Warning: Notifications are just nice-to-haves**
Database notifications are transient, lossy hints. Use them to wake code that reconciles authoritative database state. Do not treat them as a durable event log, exact event counter, change-data-capture stream, or work queue.

## Sending Notifications

Use [`Database::sendNotification(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#sendNotification(java.lang.String)) to send a named notification with an optional string payload:

```java
// Send a hint without a payload.
database.sendNotification("car_changed");

// Include the changed record's identifier when it helps reconciliation.
database.sendNotification("car_changed", carId.toString());
```

The one-argument overload is equivalent to passing `null` to the two-argument overload, which also accepts `""` or a nonempty string. Pyranid does not normalize payloads generically: [`Notification::getPayload()`](https://javadoc.pyranid.com/com/pyranid/Notification.html#getPayload()) is nullable, and each database determines how null and empty payloads are represented. PostgreSQL converts a null payload to `""`, so PostgreSQL receivers observe an empty string for null, omitted, and explicitly empty payloads. Portable code should not assume that null and empty payloads remain distinct.

Sending follows Pyranid's ordinary statement and transaction-selection path. When the durable change and notification should commit together, perform both through the same [`Database::transaction(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation)) call:

```java
// Commit the durable change and its notification atomically.
database.transaction(() -> {
  database.query("""
    UPDATE car
    SET color = :color
    WHERE car_id = :carId
    """)
    .bind("color", Color.BLUE)
    .bind("carId", carId)
    .execute();

  // PostgreSQL delivers this only if the transaction commits.
  database.sendNotification("car_changed", carId.toString());
});
```

The backend determines the precise delivery point. PostgreSQL publishes the notification after commit and discards it on rollback.

Notification payloads should contain only enough information to locate or version durable state. Treat them as untrusted input and avoid secrets. Sends use normal Pyranid statement logging, parameter redaction, timeout, and metrics behavior.

## Receiving Notifications

[`Database::withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.util.Set,com.pyranid.NotificationSessionOperation)) opens one callback-scoped listener session for either one channel or a fixed set:

```java
// Register both channels on one listener connection.
database.withNotificationSession(
  Set.of("car_changed", "owner_changed"),
  session -> {
    // Reconcile once after registration to close the setup gap.
    reconcileCars();

    while (true) {
      List<Notification> notifications =
        session.awaitNotifications(Duration.ofSeconds(30));

      // Treat the batch as a hint to reread durable state.
      if (!notifications.isEmpty())
        reconcileCars();
    }
  }
);
```

The callback begins only after every requested channel has been registered. Performing the initial reconciliation inside the callback closes the setup gap: notifications committed while that reconciliation runs remain available to a later receive.

One [`withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.util.Set,com.pyranid.NotificationSessionOperation)) invocation:

- runs synchronously on the calling thread;
- acquires at most one listener connection from that [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance's configured [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html);
- invokes the callback at most once;
- never replaces or reconnects the listener connection;
- expires the supplied [`NotificationSession`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html) when the callback exits; and
- completes listener cleanup before returning or throwing.

Pyranid does not create a listener thread, executor, timer, scheduler, or reconnect task. Your application owns the calling thread and any supervision or retry policy.

## Durable Progress Without A Notification

Callback-entry reconciliation closes the listener-registration gap, but it cannot recover every publication gap while one listener remains active. For example, your application can crash between committing durable work and sending a best-effort notification in a separate step. The notification is also inherently lossy after publication.

When notifications must provide only lower latency - not the sole source of progress - schedule an authoritative poll independently of the listener thread. Do not run the only periodic poll after [`awaitNotifications(...)`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#awaitNotifications(java.time.Duration)) returns: a receive that is stuck inside driver protocol work cannot reach it.

```java
// Poll durable state independently of notification delivery.
ScheduledExecutorService durablePoller =
  Executors.newSingleThreadScheduledExecutor();

durablePoller.scheduleWithFixedDelay(
  this::reconcilePendingJobs,
  0, // Start immediately.
  1, // Wait one minute after each completed poll.
  TimeUnit.MINUTES
);
```

An outbox with persistent relay retries can close the commit-to-publication crash gap, but consumers must still reconcile durable state because notification delivery itself is not durable.

## Waiting And Draining

[`NotificationSession::awaitNotifications(...)`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#awaitNotifications(java.time.Duration)) waits for a nonempty batch until its best-effort elapsed-time budget expires:

```java
// Wait up to 30 seconds for at least one notification.
List<Notification> notifications =
  session.awaitNotifications(Duration.ofSeconds(30));
```

An empty result means only that no notification was observed before that call's final check. It does not prove that a quiet connection is still healthy.

[`NotificationSession::drainNotifications()`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#drainNotifications()) performs one driver-specific non-waiting poll:

```java
// Poll once without waiting for a notification.
List<Notification> pending = session.drainNotifications();

if (!pending.isEmpty())
  reconcileCars();
```

Calling [`awaitNotifications(Duration.ZERO)`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#awaitNotifications(java.time.Duration)) has the same behavior as [`drainNotifications()`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#drainNotifications()). Returned batches are immutable and preserve the encounter order reported by the adapter. A conceptual burst can span multiple batches, so you should normally reconcile once per nonempty batch rather than interpreting each [`Notification`](https://javadoc.pyranid.com/com/pyranid/Notification.html) as one durable event.

Positive waits are best-effort rather than hard JDBC deadlines. Pyranid divides them into short driver calls so interruption can normally be observed between calls, but it cannot preempt a JDBC call already in progress.

For PostgreSQL, neither a positive wait nor [`drainNotifications()`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#drainNotifications()) has a hard completion bound after the supported JDBC driver begins parsing a partial protocol frame. Give the listener a bounded shutdown deadline and escalate to process-level termination if it does not join; no in-process timeout can universally preempt arbitrary JDBC I/O.

When a nonempty batch and a stop race, the batch wins and the interrupt flag remains set. Process the returned batch - or intentionally discard it according to your application's policy - before acting on that later stop.

## Session Scope And Transaction Boundaries

[`NotificationSession`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html) is confined to the callback thread and may not be retained. Calls from another thread, calls after callback exit, concurrent/reentrant receives, and receives after a terminal transport failure throw [`IllegalStateException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/IllegalStateException.html).

Opening a notification session or consuming notifications while any Pyranid transaction is active on the calling thread is also illegal. Notification consumption is not transactional: rolling back or retrying a transaction cannot put an already-consumed notification back.

This guard observes Pyranid's thread-local transaction stack only. It cannot detect a raw JDBC transaction created by your application or transaction-control SQL such as `BEGIN` issued through [`Database::useRawConnection(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(com.pyranid.RawConnectionOperation)). Ordinary lifecycle calls such as [`Connection::setAutoCommit(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setAutoCommit(boolean)), [`Connection::commit()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#commit()), and [`Connection::rollback()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#rollback()) remain blocked on Pyranid's guarded raw connection, but SQL text can still establish transaction state that the notification guard cannot see. Do not consume notifications while any such unmanaged transaction is active.

Complete each bounded reconciliation transaction before waiting again:

```java
// Reconcile after registration, then after each observed batch.
listenerDatabase.withNotificationSession("job_ready", session -> {
  reconcilePendingJobs();

  while (true) {
    if (!session.awaitNotifications(Duration.ofSeconds(30)).isEmpty())
      reconcilePendingJobs();
  }
});
```

Nested notification sessions are permitted, but strongly discouraged. The outer listener is not serviced while its callback runs the inner session, and the inner call needs another connection. Against a capacity-one source, that checkout can block indefinitely and deadlock your application. Prefer separately supervised listener threads and provision at least one checkout for every simultaneously live listener. Concurrent top-level calls are thread-safe, but Pyranid promises no acquisition fairness or progress beyond the configured [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html).

## Interruption And Supervision

The methods [`withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.lang.String,com.pyranid.NotificationSessionOperation)), [`awaitNotifications(...)`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#awaitNotifications(java.time.Duration)), and [`drainNotifications()`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html#drainNotifications()) declare [`InterruptedException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/InterruptedException.html). An interrupt observed before driver work or after an empty receive is normal cooperative cancellation, not a database failure.

If a supervisor catches the exception, it should normally restore the thread's interrupt flag before stopping:

```java
try {
  listenerDatabase.withNotificationSession(
    "job_ready",
    this::consumeJobNotifications
  );
} catch (InterruptedException interruptedException) {
  // Preserve the cancellation signal for the caller.
  Thread.currentThread().interrupt();
}
```

A successfully decoded nonempty batch wins over an interrupt that races after the driver returns. Pyranid returns the batch and leaves the interrupt flag set so your code can perform its final bounded reconciliation before stopping.

On Java 21+, interrupting a virtual thread blocked in an applicable JDK system-default socket can close the socket and surface as a terminal [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) rather than clean [`InterruptedException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/InterruptedException.html). If a returned batch carries a late interrupt and final database work is required, clear and remember the flag, perform only bounded work, and restore the flag in `finally`:

```java
// Capture an interrupt that raced with a returned batch.
List<Notification> hints =
  session.awaitNotifications(Duration.ofSeconds(30));
boolean stopRequested = Thread.interrupted();

if (stopRequested && hints.isEmpty())
  throw new InterruptedException();

try {
  reconcilePendingJobs();
} finally {
  // Restore the signal after bounded reconciliation.
  if (stopRequested)
    Thread.currentThread().interrupt();
}
```

A later interrupt can still race with that bounded work; this pattern preserves a stop already observed but does not make socket I/O immune to interruption.

Connection loss is terminal for the current invocation and surfaces as [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html). Pyranid does not reinvoke the callback. To recover, your application should observe the failure, apply its own backoff and retry policy, and call [`withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.lang.String,com.pyranid.NotificationSessionOperation)) again. The new callback should reconcile authoritative state again after registration.

Catching a terminal receive failure inside the callback does not make the session healthy: later receives throw [`IllegalStateException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/IllegalStateException.html). When the callback exits, [`withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.lang.String,com.pyranid.NotificationSessionOperation)) completes cleanup and rethrows the retained failure. A retained transport [`Error`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Error.html) propagates unwrapped; only a distinct callback [`Error`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Error.html) takes precedence, with the retained failure suppressed beneath it.

There are intentionally no listener-health accessors. A [`Boolean`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Boolean.html) snapshot cannot certify that a quiet physical connection remains healthy and registered.

### Supervising A Listener

A terminal [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) ends the current physical session. Restart by calling [`withNotificationSession(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#withNotificationSession(java.lang.String,com.pyranid.NotificationSessionOperation)) again, which registers a new session before invoking the callback. Your application owns retry classification and backoff; this Java 17-compatible sketch uses a fixed delay for clarity. Here `listenerDatabase` owns the session-affine source, while `jobRunner` runs bounded reconciliation transactions through the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) your application uses for ordinary work:

```java
private static final Duration RECEIVE_INTERVAL =
  Duration.ofSeconds(30);
private static final Duration RETRY_DELAY =
  Duration.ofSeconds(1);

private void superviseJobNotifications(
  Predicate<? super DatabaseException> retryable
) {
  while (true) {
    try {
      listenerDatabase.withNotificationSession("job_ready", session -> {
        // Registration is complete. Close the setup gap first.
        jobRunner.drainClaimableBatch();

        while (true) {
          List<Notification> hints =
            session.awaitNotifications(RECEIVE_INTERVAL);

          if (!hints.isEmpty()) {
            // A decoded batch wins a racing interrupt. Clear and remember
            // that signal while the final bounded transaction runs.
            boolean stopRequested = Thread.interrupted();

            try {
              jobRunner.drainClaimableBatch();
            } finally {
              if (stopRequested)
                Thread.currentThread().interrupt();
            }
          }
        }
      });
      return;
    } catch (InterruptedException interruptedException) {
      Thread.currentThread().interrupt();
      return;
    } catch (DatabaseException failure) {
      if (Thread.currentThread().isInterrupted()
        || !retryable.test(failure))
        throw failure;

      try {
        Thread.sleep(RETRY_DELAY.toMillis());
      } catch (InterruptedException interruptedException) {
        Thread.currentThread().interrupt();
        return;
      }
    }
  }
}
```

If startup depends on the listener, publish readiness only once, after the callback begins and its initial authoritative reconciliation succeeds. A retry must perform that reconciliation again because it creates a newly registered session. The late-interrupt pattern above applies when a final bounded reconciliation must run before stopping.

Run the supervisor on your application's dedicated executor and retain its worker [`Future`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/concurrent/Future.html). On shutdown, interrupt the worker, wait only for a finite deadline you choose, and inspect the [`Future`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/concurrent/Future.html). If the worker does not terminate, report the stuck listener and escalate at the process boundary.

Your application defines the retry predicate; do not use [`DatabaseException::isTransient()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isTransient()) as the sole classifier. Authentication, configuration, transport, and reconciliation failures can require different actions, and equivalent disconnects can surface through different SQLSTATEs. Your production retry policy should normally add exponential backoff, jitter, and anti-flap behavior.

## Choosing The Listener [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html)

Listening requires a [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) that preserves one backend session for the complete callback. The [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) your application uses for ordinary work is suitable when its source provides that affinity and has enough pool capacity:

```java
// Reconcile after registration and after each observed batch.
database.withNotificationSession("car_changed", session -> {
  reconcileCars();

  while (true) {
    if (!session.awaitNotifications(Duration.ofSeconds(30)).isEmpty())
      reconcileCars();
  }
});
```

If ordinary traffic uses a source that cannot preserve listener state, construct a second [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) over a session-affine listener source:

```java
// Dedicate this Database to a session-affine listener source.
Database listenerDatabase = Database.withDataSource(listenerDataSource)
  .databaseType(DatabaseType.POSTGRESQL)
  .build();
```

This is ordinary [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) composition; Pyranid has no notification-specific [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) setting. Reconciliation and transactional publication should continue through the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) your application uses for ordinary work. Each [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) retains its one caller-owned [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html); Pyranid neither compares separately configured sources nor closes them.

Each live listener holds one connection for its full callback lifetime. A capacity-one listener source cannot simultaneously serve callback queries or sends, even if another [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) object wraps that same source.

Configure the listener instance's [`DatabaseType`](https://javadoc.pyranid.com/com/pyranid/DatabaseType.html) explicitly when practical. Otherwise [`Database::isNotificationListeningSupported()`](https://javadoc.pyranid.com/com/pyranid/Database.html#isNotificationListeningSupported()) or the first session open may resolve an uncached type through that instance's source, which performs a metadata checkout and can throw [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html). Within one session invocation, detection finishes before listener acquisition; a concurrent first-time detection can still contend with a live listener for a capacity-one source.

Call [`Database::isNotificationListeningSupported()`](https://javadoc.pyranid.com/com/pyranid/Database.html#isNotificationListeningSupported()) on the listener [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) when it differs from the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) your application uses for ordinary work. It reports whether that instance's configured dialect and currently loadable runtime adapter expose the APIs needed to attempt listening. It performs no notification-session checkout or lifecycle metric emission, but it does not inspect proxy mode, prove backend-session affinity, test a quiet connection, or guarantee that independently configured publisher, listener, and reconciliation instances reach the same database.

Pyranid performs no runtime topology validation or active liveness query. In particular, using PgBouncer transaction or statement pooling as the listener source is unsupported and can appear to register successfully before delivery silently stops. Use a direct or session-pooled listener source, and route every participating instance to the same active logical database and primary.

## Portable Contract

Pyranid standardizes the callback scope and common value model while leaving database semantics with the backend.

| Pyranid guarantees | Backend-specific behavior |
| --- | --- |
| Nonblank, NUL-free channel names | Channel case matching and additional limits |
| Nullable string payloads with no generic normalization | Whether null and empty remain distinct, whether payloads are supported, and their size limit |
| Immutable notification batches | Delivery timing, ordering, fan-out, and coalescing |
| One physical listener session per invocation | Proxy and pooling compatibility |
| No backend sender identifier or occurrence count | Any metadata that Pyranid deliberately omits |

Send and listen support are independent. A backend may support one without the other. Each backend defines whether it accepts, preserves, transforms, or rejects null and empty payloads; a backend without payload support may reject any supplied payload.

## PostgreSQL

PostgreSQL notification receiving supports and is tested against pgjdbc 42.7.13 as its baseline. Pyranid also explicitly supports YugabyteDB's `com.yugabyte` smart driver and tests notification send/receive against driver 42.7.3-yb-4. The drivers are not runtime dependencies of Pyranid; applications that receive notifications must provide pgjdbc 42.7.13 or newer, or the YugabyteDB smart driver. Pyranid does not promise compatibility with arbitrary repackaged pgjdbc forks. Sending uses bound `pg_notify(?, ?)` SQL and remains available without the receive adapter.

YugabyteDB exposes `LISTEN`/`NOTIFY` as an Early Access feature in v2025.2.3 and later. It is disabled by default and requires `ysql_yb_enable_listen_notify=true` on both Masters and TServers. [`Database::isNotificationListeningSupported()`](https://javadoc.pyranid.com/com/pyranid/Database.html#isNotificationListeningSupported()) checks only the configured/detected dialect and loadable driver adapter; it cannot inspect this server flag. If notifications are disabled or unavailable in the current server version, setup or sending fails with [`UnsupportedOperationException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/UnsupportedOperationException.html). See the [YugabyteDB documentation](https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/cmd_listen_notify/).

CockroachDB does not implement `LISTEN`/`NOTIFY`, although it is detected as PostgreSQL. Pyranid translates the capability errors from its generated notification SQL to [`UnsupportedOperationException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/UnsupportedOperationException.html). A `true` result from [`Database::isNotificationListeningSupported()`](https://javadoc.pyranid.com/com/pyranid/Database.html#isNotificationListeningSupported()) therefore means the dialect and runtime adapter are available, not that every wire-compatible server exposes the feature in its current version or configuration.

PostgreSQL-specific behavior - including commit/rollback delivery, duplicate coalescing, channel and payload limits, PgBouncer pooling modes, TCP/socket considerations, and notification-queue operations - is covered under [Database-Specific Recipes](https://pyranid.com/docs/database-specific-recipes#postgre-sql).

## Metrics

Notification sessions emit low-cardinality lifecycle callbacks through the [`MetricsCollector`](https://javadoc.pyranid.com/com/pyranid/MetricsCollector.html) configured on the listener [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html). Sends and ordinary statements emit through the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance that executes them.

See [Metrics Collection](https://pyranid.com/docs/metrics-collection#notification-metrics) for lifecycle outcomes, in-memory counters, and current OpenTelemetry adapter coverage.

#### References:

- [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html)
- [`Notification`](https://javadoc.pyranid.com/com/pyranid/Notification.html)
- [`NotificationSession`](https://javadoc.pyranid.com/com/pyranid/NotificationSession.html)
- [`NotificationSessionOperation`](https://javadoc.pyranid.com/com/pyranid/NotificationSessionOperation.html)

---

# Parameter Binding
URL: https://pyranid.com/docs/parameter-binding
Description: How parameters are bound to Prepared Statements in Pyranid

When you execute a SQL statement, Pyranid will convert it to a [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) and ask your [`PreparedStatementBinder`](https://javadoc.pyranid.com/com/pyranid/PreparedStatementBinder.html) to provide values for any named parameters (e.g. `:account_id`). Use of [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) is important: among other things, it prevents SQL injection, permits your DBMS to re-use execution plans for performance, and enables typesafe parameter binding.

```java
@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:

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

If you need to customize binding behavior, you might bring your own list of [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html)...

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

Database database = Database.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();
```

...or you may choose to directly implement the [`PreparedStatementBinder`](https://javadoc.pyranid.com/com/pyranid/PreparedStatementBinder.html) interface for fine-grained control:

```java
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.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();
```

---

## Supported Primitives

* [`Byte`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Byte.html)
* [`Short`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Short.html)
* [`Integer`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Integer.html)
* [`Long`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Long.html)
* [`Float`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Float.html)
* [`Double`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Double.html)
* [`Boolean`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Boolean.html)
* [`Character`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Character.html)
* [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html)
* [`byte[]`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Byte.html)

## Supported JDK Types

* [`Enum<E>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Enum.html)
* [`UUID`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/UUID.html)
* [`BigDecimal`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/math/BigDecimal.html)
* [`BigInteger`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/math/BigInteger.html)
* [`Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html)
* [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html)
* [`LocalDate`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDate.html)
* [`LocalTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalTime.html)
* [`LocalDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDateTime.html)
* [`OffsetTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetTime.html)
* [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html)
* [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html)
* [`Year`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Year.html) (bound as `INTEGER`)
* [`YearMonth`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/YearMonth.html) (bound as its ISO-8601 string form, e.g. `2027-12`)
* [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html)
* [`java.sql.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Date.html)
* [`java.sql.Time`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Time.html)
* [`ZoneId`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZoneId.html)
* [`TimeZone`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/TimeZone.html)
* [`Locale`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Locale.html) (IETF BCP 47 "language tag" format)
* [`Currency`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Currency.html)

Temporal binding uses JDBC [`ParameterMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ParameterMetaData.html) when available to distinguish `TIMESTAMP` from `TIMESTAMP WITH TIME ZONE` targets. For known zone-less `TIMESTAMP` targets, Pyranid converts [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html) and [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) parameters through [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) and binds the resulting local timestamp. For known `TIMESTAMP WITH TIME ZONE` targets, Pyranid binds them as time-zone-aware timestamps. [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html) parameters are normalized to [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) first and follow the same rules.

If parameter metadata is unavailable or non-identifying, [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html) and [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) default to [`TIMESTAMP_WITH_TIME_ZONE`](https://javadoc.pyranid.com/com/pyranid/AmbiguousTimestampBindingStrategy.html#TIMESTAMP_WITH_TIME_ZONE). For drivers or proxies that cannot provide identifying parameter metadata when your target columns are zone-less `TIMESTAMP` values, configure [`Database.Builder::ambiguousTimestampBindingStrategy(TIMESTAMP_WITHOUT_TIME_ZONE)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#ambiguousTimestampBindingStrategy(com.pyranid.AmbiguousTimestampBindingStrategy)).

## Special Parameters

Special support is provided for JSON/JSONB, vector, SQL ARRAY, SQL STRUCT, and secure diagnostics wrapper parameters. 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:

* [`Parameters::json(String)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#json(java.lang.String))
* [`Parameters::json(String, BindingPreference)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#json(java.lang.String,com.pyranid.JsonParameter.BindingPreference))

You might create a JSONB storage table...

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

...and write JSON to it:

```java
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.

If you want to force text storage (e.g. if whitespace is important), specify a binding preference like this:

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

Passing `null` to [`Parameters::json(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#json(java.lang.String)) 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Types.html#NULL).

### Vector

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

Supported methods:

* [`Parameters::vectorOfDoubles(double[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfDoubles(double%5B%5D))
* [`Parameters::vectorOfDoubles(List<Double>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfDoubles(java.util.List))
* [`Parameters::vectorOfFloats(float[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfFloats(float%5B%5D))
* [`Parameters::vectorOfFloats(List<Float>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfFloats(java.util.List))
* [`Parameters::vectorOfBigDecimals(List<BigDecimal>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfBigDecimals(java.util.List))

You might create a vector storage table...

```sql
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:

```java
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:

```java
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`](https://duckdb.org/docs/stable/sql/functions/array.html) require an explicit cast of the bound parameter to the fixed-size ARRAY type:

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfDoubles(double%5B%5D)), [`Parameters::vectorOfFloats(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfFloats(float%5B%5D)), and [`Parameters::vectorOfBigDecimals(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#vectorOfBigDecimals(java.util.List)) 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#createArrayOf(java.lang.String,java.lang.Object%5B%5D)). Pyranid's integration suites verify ordinary one-dimensional arrays on PostgreSQL and DuckDB; 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`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html); use IN-list expansion, JSON, or a database-specific custom binder for those engines.

Supported methods:

* [`Parameters::sqlArrayOf(String, E[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,E%5B%5D))
* [`Parameters::sqlArrayOf(String, List<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,java.util.List))

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

```sql
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:

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

Pyranid recursively materializes nested [`SqlArrayParameter`](https://javadoc.pyranid.com/com/pyranid/SqlArrayParameter.html) values. Multidimensional binding is integration-verified on DuckDB; other JDBC drivers may impose different element-type or nesting rules. Represent each dimension with its own [`Parameters::sqlArrayOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,java.util.List)), and make each containing level's [`baseTypeName`](https://javadoc.pyranid.com/com/pyranid/SqlArrayParameter.html#getBaseTypeName()) name its element type. For example, the outer array below has `VARCHAR[]` elements and each inner array has `VARCHAR` elements:

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html), or [`java.util.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html), use an explicit `TIMESTAMP` or `TIMESTAMPTZ` base type. Pyranid uses it to apply the intended timestamp and configured [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) 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.

Use a [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html) for database-specific collection types, table-valued parameters, or a driver whose multidimensional representation differs.

Passing `null` to [`Parameters::sqlArrayOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,java.util.List)) is supported. Pyranid will bind a typed SQL array null using the base type name when possible.

### SQL STRUCT

[`Parameters::sqlStructOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlStructOf(java.lang.String,java.util.List)) binds positional attributes through JDBC [`Connection::createStruct(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#createStruct(java.lang.String,java.lang.Object%5B%5D)). SQL STRUCT binding is currently supported for DuckDB; other dialects fail fast rather than passing a driver-specific wrapper to JDBC.

Supported methods:

* [`Parameters::sqlStructOf(String, List<?>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlStructOf(java.lang.String,java.util.List))
* [`Parameters::sqlStructOf(String, Object[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlStructOf(java.lang.String,java.lang.Object%5B%5D))

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

```sql
CREATE TABLE person (
  person_id BIGINT PRIMARY KEY,
  details STRUCT(name VARCHAR, age INTEGER)
);
```

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html), or [`java.util.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html), always provide the complete inline STRUCT declaration. Pyranid uses it to distinguish `TIMESTAMP` from `TIMESTAMPTZ` and to apply the configured [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)) 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`](https://javadoc.pyranid.com/com/pyranid/SqlStructParameter.html) or [`SqlArrayParameter`](https://javadoc.pyranid.com/com/pyranid/SqlArrayParameter.html) values. Use the array overload when an attribute is null and [`List.of(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/List.html#of(E...)) would reject it:

```java
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:

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

### Secure Parameters

Use [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) for sensitive values that should bind normally but render as a mask in Pyranid diagnostics.

For example, a logger that renders the full [`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) will include Pyranid's parameter diagnostics:

```java
// Configure a simple StatementLogger that writes to stdout
Database database = Database.withDataSource(dataSource)
  .statementLogger(statementLog -> System.out.println(statementLog))
  .build();
```

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

```java
database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", tokenHash)
  .execute();
```

```text
parameters=[acct_123, token_hash_abc123]
```

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

```java
database.query("""
  INSERT INTO api_credential (account_id, token_hash)
  VALUES (:accountId, :tokenHash)
  """)
  .bind("accountId", accountId)
  .bind("tokenHash", Parameters.secure(tokenHash))
  .execute();
```

```text
parameters=[acct_123, <redacted>]
```

[`Parameters::secure(Object)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) renders as `<redacted>`. Use [`Parameters::secure(Object, String)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object,java.lang.String)) for a custom display token. This is display-only: the underlying value is still passed to the JDBC [`PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) as if it had not been wrapped.

[`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) composes with [`Parameters::inList(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(java.util.Collection)), [`Parameters::json(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#json(java.lang.String)), vector parameters, [`Parameters::sqlArrayOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlArrayOf(java.lang.String,java.util.List)), [`Parameters::sqlStructOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#sqlStructOf(java.lang.String,java.util.List)), typed parameters, [`Optional`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html) values, and `null` values.

For broader policies, configure a database-wide [`ParameterRedactor`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html):

```java
// Use a "redact everything" globally
Database database = Database.withDataSource(dataSource)
  .parameterRedactor(ParameterRedactor.redactAll())
  .build();
```

The default redactor is [`ParameterRedactor::none()`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html#none()), which leaves non-secure, non-batch values unchanged. [`ParameterRedactor::redactAll()`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html#redactAll()) masks every non-secure value. Note that use of [`SecureParameter`](https://javadoc.pyranid.com/com/pyranid/SecureParameter.html) always takes precedence; its wrapped value is never passed to the redactor.

**Warning: 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(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) or configure [`ParameterRedactor::redactAll()`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html#redactAll()).

### What Redaction Does and Does Not Cover

Because the real value is bound to the [`PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html), 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:

| Surface | Covered? |
| --- | --- |
| Pyranid's `parameters=[...]` rendering ([`StatementContext`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html)/[`StatementLog`](https://javadoc.pyranid.com/com/pyranid/StatementLog.html) diagnostics) | Yes - masks/redactor always apply |
| [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) messages, `toString()`, and DBMS metadata fields ([`getDetail()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getDetail()), [`getDbmsMessage()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#getDbmsMessage()), ...) | Best-effort - verbatim occurrences of [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) values are scrubbed and replaced with the mask |
| The raw driver exception ([`getCause()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Throwable.html#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 values | No - 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html)) | No |
| Exceptions raised outside statement execution - commit/rollback time (e.g. deferred constraint violations), connection acquisition, raw-connection operations | No - no statement context exists at those points |

Only values explicitly wrapped with [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) trigger the driver-text scrub; a [`ParameterRedactor`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html) governs Pyranid's parameter-list rendering only.

## IN-List Expansion

When using named parameters with [`Database::query(String)`](https://javadoc.pyranid.com/com/pyranid/Database.html#query(java.lang.String)), collections and arrays are not expanded automatically. Wrap them with [`Parameters::inList(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(java.util.Collection)) to expand into multiple SQL IN-list placeholders.
IN-list parameters must be non-empty and must not contain `null` or empty [`Optional`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html) elements. Empty collections, empty arrays, `null` elements, and empty `Optional` elements will throw an [`IllegalArgumentException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/IllegalArgumentException.html).

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

```java
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:

* [`Parameters::inList(Collection<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(java.util.Collection))
* [`Parameters::inList(E[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(java.lang.Object%5B%5D))
* [`Parameters::inList(byte[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(byte%5B%5D))
* [`Parameters::inList(short[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(short%5B%5D))
* [`Parameters::inList(int[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(int%5B%5D))
* [`Parameters::inList(long[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(long%5B%5D))
* [`Parameters::inList(float[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(float%5B%5D))
* [`Parameters::inList(double[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(double%5B%5D))
* [`Parameters::inList(boolean[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(boolean%5B%5D))
* [`Parameters::inList(char[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#inList(char%5B%5D))

## Custom Parameters

You may register instances of [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html) to bind application-specific types to [`java.sql.PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) however you like.

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()`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.BindingResult.html#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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) or other externally-visible state after you have decided to handle the value.

Typed parameters (such as [`Parameters::listOf(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#listOf(java.lang.Class,java.util.List)) or [`Parameters::arrayOf(Class, ...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#arrayOf(java.lang.Class,java.lang.Object)) always require a [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html). If you need to bind typed nulls, implement [`CustomParameterBinder::bindNull(...)`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html#bindNull(com.pyranid.StatementContext,java.sql.PreparedStatement,java.lang.Integer,com.pyranid.TargetType,java.lang.Integer)) in addition to [`CustomParameterBinder::bind(...)`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html#bind(com.pyranid.StatementContext,java.sql.PreparedStatement,java.lang.Integer,java.lang.Object)); otherwise binding fails fast.

### Arbitrary Types

Let's define a simple type.

```java
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`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html) to handle binding it:

```java
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.withDataSource(dataSource)
  .preparedStatementBinder(preparedStatementBinder)
  .build();
```

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

```java
// 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>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/List.html), [`Set<E>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Set.html), and [`Map<K,V>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) types:

* [`Parameters::listOf(Class<E>, List<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#listOf(java.lang.Class,java.util.List))
* [`Parameters::setOf(Class<E>, Set<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#setOf(java.lang.Class,java.util.Set))
* [`Parameters::mapOf(Class<K>, Class<V>, Map<K,V>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#mapOf(java.lang.Class,java.lang.Class,java.util.Map))

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

For example, this code...

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/TargetType.html#matchesParameterizedType(java.lang.Class,java.lang.Class...)) returns `true` thanks to runtime type capturing:

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#arrayOf(java.lang.Class,java.lang.Object)):

```java
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](https://pyranid.com/docs/parameter-binding#sql-array) 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) values keyed by [`Locale`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Locale.html) with [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html) values in a PostgreSQL `JSONB` column.

```java
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.withDataSource(dataSource)
  .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`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html).  See the [Parameterized-Type Example](https://pyranid.com/docs/resultset-mapping#parameterized-type-example) in the ResultSet Mapping docs for the read side.

**Warning: Heads Up!**
If you use any of these collection/array typed parameters, you must define a corresponding [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html) to handle them:

* [`Parameters::listOf(Class<E>, List<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#listOf(java.lang.Class,java.util.List))
* [`Parameters::setOf(Class<E>, Set<E>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#setOf(java.lang.Class,java.util.Set))
* [`Parameters::mapOf(Class<K>, Class<V>, Map<K,V>)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#mapOf(java.lang.Class,java.lang.Class,java.util.Map))
* [`Parameters::arrayOf(Class<E>, E[])`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#arrayOf(java.lang.Class,java.lang.Object))

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(...)`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html#bindNull(com.pyranid.StatementContext,java.sql.PreparedStatement,java.lang.Integer,com.pyranid.TargetType,java.lang.Integer)) 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) placeholders. Use them for values such as IDs, names, timestamps, limits, and status codes.

**Warning: 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.

```java
// 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()`](https://javadoc.pyranid.com/com/pyranid/ParameterRedactor.html#none()), non-secure, non-batch values render verbatim. Wrap sensitive bind values with [`Parameters::secure(...)`](https://javadoc.pyranid.com/com/pyranid/Parameters.html#secure(java.lang.Object)) or configure [`Database.Builder::parameterRedactor(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#parameterRedactor(com.pyranid.ParameterRedactor)) when values should not appear in diagnostics. Custom [`StatementLogger`](https://javadoc.pyranid.com/com/pyranid/StatementLogger.html) implementations that read [`StatementContext::getParameters()`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html#getParameters()) receive raw values; use [`StatementContext::getRedactedParameters()`](https://javadoc.pyranid.com/com/pyranid/StatementContext.html#getRedactedParameters()) for safe display.

---

# Privacy
URL: https://pyranid.com/docs/privacy
Description: How the Pyranid website uses Google Analytics

This site uses Google Analytics to understand aggregate site usage. Google Analytics uses first-party cookies and processes information about visits, including site interactions, approximate location, and browser and device characteristics, to provide usage reports. Google explains this collection and processing in [Safeguarding your data](https://support.google.com/analytics/answer/6004245).

---

# Queries
URL: https://pyranid.com/docs/queries
Description: How to pull data from your database with Pyranid

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`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html).

Rules for how Pyranid binds your plain-Java parameters to Prepared Statement placeholders are outlined in the [Parameter Binding](https://pyranid.com/docs/parameter-binding) section.

Rules for how Resultset data gets copied back into your Java objects are outlined in the [ResultSet Mapping](https://pyranid.com/docs/resultset-mapping) section.

Pyranid is opinionated regarding nullability and embraces the use of [`Optional<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html).

If you ask for a single object, e.g. querying by an identifier, [`Optional::empty()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html#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)`](https://javadoc.pyranid.com/com/pyranid/Database.html#query(java.lang.String)) and bind values by name with [`Query::bind(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#bind(java.lang.String,java.lang.Object)). Only named parameters are supported.

### SQL Parsing Rules

Pyranid scans SQL before handing it to JDBC so it can translate named parameters into [`PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) 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 or detected 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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/IllegalArgumentException.html).

PostgreSQL JSONB/hstore `?`, `?|`, and `?&` operators are supported. When the [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) is configured or detected as PostgreSQL, Pyranid automatically emits pgjdbc's escaped `??` form for those operators while preserving named-parameter binding.

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.

```java
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:

```java
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`](https://javadoc.pyranid.com/com/pyranid/Query.html):

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#queryTimeout(java.time.Duration)), [`Database.Builder::fetchSize(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#fetchSize(java.lang.Integer)), and [`Database.Builder::maxRows(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#maxRows(java.lang.Integer)) to configure database-wide defaults. Per-query settings override database defaults. [`Query::customize(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#customize(com.pyranid.PreparedStatementCustomizer)) runs after those settings, so it can override them, and before Pyranid binds parameters.

[`Query::queryTimeout(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#queryTimeout(java.time.Duration)) maps to JDBC [`Statement::setQueryTimeout(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html#setQueryTimeout(int)). [`Query::maxRows(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#maxRows(java.lang.Integer)) maps to JDBC [`Statement::setMaxRows(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html#setMaxRows(int)) and is enforced by the driver; DuckDB's driver accepts but ignores it - use SQL `LIMIT` there. For driver-specific cancellation beyond timeouts, capture the [`PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) in [`Query::customize(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#customize(com.pyranid.PreparedStatementCustomizer)) and call [`Statement::cancel()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html#cancel()) from your application's cancellation path.

You may customize the underlying [`PreparedStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html) before execution beyond built-in statement settings with a [`PreparedStatementCustomizer`](https://javadoc.pyranid.com/com/pyranid/PreparedStatementCustomizer.html):

```java
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:

```java
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:

```java
// 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:

* [`Database::query(String)`](https://javadoc.pyranid.com/com/pyranid/Database.html#query(java.lang.String))
* [`Query::fetchObject(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchObject(java.lang.Class))
* [`Query::fetchList(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchList(java.lang.Class))

## Records

[`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) types are also supported:

```java
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`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) 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>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchStream(java.lang.Class,java.util.function.Function)). The [`Stream<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/stream/Stream.html) passed to your callback is backed by the underlying [`java.sql.ResultSet`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html), and Pyranid closes JDBC resources automatically when the callback returns (or throws).

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

**Warning: Heads Up!**
To avoid resource leaks, make sure that you consume the entire stream within the callback.  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.

Supported dialects apply driver-specific streaming setup automatically. PostgreSQL streams use an autocommit-disabled connection and a positive JDBC fetch size when no Pyranid transaction is active. MySQL streams use forward-only/read-only statements and Connector/J's streaming fetch-size sentinel. MariaDB streams use forward-only/read-only statements without the MySQL sentinel. Use [`Query::fetchSize(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchSize(java.lang.Integer)) to override the dialect default when needed, including `0` when you deliberately want the driver default.

```java
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:

* [`Query::fetchStream(Class<T>, Function<Stream<T>, R>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchStream(java.lang.Class,java.util.function.Function))

## Raw JDBC Connection Access

If you need a JDBC escape hatch for driver-specific features, stored procedures, [`CallableStatement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/CallableStatement.html), or other operations that do not fit Pyranid's query API, use [`Database::useRawConnection(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(com.pyranid.RawConnectionOperation)).

```java
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(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(com.pyranid.RawConnectionOperation)) 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.

```java
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();
  });
});
```

**Warning: Pyranid Owns the Connection Lifecycle**
The [`Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html) 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()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#close()), [`commit()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#commit()), [`rollback()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#rollback()), [`setAutoCommit(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setAutoCommit(boolean)), [`setTransactionIsolation(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setTransactionIsolation(int)), [`setCatalog(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setCatalog(java.lang.String)), [`setSchema(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setSchema(java.lang.String)), [`setClientInfo(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setClientInfo(java.lang.String,java.lang.String)), [`setNetworkTimeout(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setNetworkTimeout(java.util.concurrent.Executor,int)), and JDBC [`Savepoint`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Savepoint.html) controls throw immediately. [`Statement::getConnection()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html#getConnection()) and [`DatabaseMetaData::getConnection()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/DatabaseMetaData.html#getConnection()) return the guarded Pyranid handle, and [`ResultSet::getStatement()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html#getStatement()) returns a guarded statement. [`Connection::unwrap(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Wrapper.html#unwrap(java.lang.Class)) may return a guarded, callback-scoped proxy for a vendor interface, but it never exposes a castable physical [`Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html); the proxy blocks lifecycle methods and expires when the callback returns. Guarded statements, resultsets, and metadata refuse driver-specific [`unwrap(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Wrapper.html#unwrap(java.lang.Class)) calls that could expose the driver's underlying connection. Use [`Database::transaction(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation)), [`Database::participate(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation)), and [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html) savepoint APIs for transaction management. Close any [`Statement`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html) or [`ResultSet`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html) instances you create inside the callback.

When a driver feature requires its concrete connection type, use [`Database::useRawConnection(Class<C>, RawConnectionOperation<? super C, R>)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(java.lang.Class,com.pyranid.RawConnectionOperation)). Pyranid passes the managed connection directly when it already has the requested type, or obtains that type with JDBC [`Wrapper::unwrap(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Wrapper.html#unwrap(java.lang.Class)). 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, ...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#useRawConnection(java.lang.Class,com.pyranid.RawConnectionOperation)) 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](https://pyranid.com/docs/database-specific-recipes#keep-typed-raw-jdbc-work-callback-scoped) for concrete examples.

---

# ResultSet Mapping
URL: https://pyranid.com/docs/resultset-mapping
Description: How to map ResultSet rows to objects in Pyranid

When you execute a SQL query, Pyranid will walk each row in the [`java.sql.ResultSet`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html) and ask your [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) to provide an object representation of that row.

```java
@FunctionalInterface
public interface ResultSetMapper {
  // T is the type to which we're mapping this row.
  @NonNull
  public <T> Optional<T> map(
    @NonNull StatementContext<T> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Class<T> resultSetRowType,
    @NonNull InstanceProvider instanceProvider
  ) throws SQLException;
}
```

The [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) is given an [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html) so it can create an object to hold the row's data. It's the mapper's job to copy the row's data into that object and return it.

Return [`Optional.empty()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html#empty()) to represent a mapped SQL `NULL`; a mapper itself must not return `null`.

The out-of-the-box implementation supports mapping common JDK types as well as your JavaBeans and [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) types, and generally "just works" as you would expect.

If you need to customize mapping behavior, you might bring your own list of [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html)...

```java
// CustomColumnMappers supply "surgical" overrides to handle custom types.
// If multiple mappers apply, Pyranid tries them in list order.
// Normalization locale should match the language of your database tables/column names.
// Plan caching (on by default) trades memory for faster mapping of wide ResultSets
ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(...))
  .normalizationLocale(Locale.forLanguageTag("pt-BR"))
  .planCachingEnabled(false)
  .build();

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();
```

...or you might choose to directly implement the [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) interface for fine-grained control:

```java
ResultSetMapper resultSetMapper = new ResultSetMapper() {
  @Override
  @NonNull
  public <T> Optional<T> map(
    @NonNull StatementContext<T> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Class<T> resultSetRowType,
    @NonNull InstanceProvider instanceProvider
  ) throws SQLException {
    // Do your mapping here
    return Optional.empty();
  }
};

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();
```

---

## Standard Types

When querying for a single column, e.g. a SQL `COUNT`, it's often useful to map to a standard type like [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html), [`Integer`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Integer.html), or [`Boolean`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Boolean.html).

There's no need to create a custom "row" type to hold the result.

```java
// Returns Optional<Long>, which we immediately unwrap because COUNT(*) is never null
Long count = database.query("SELECT COUNT(*) FROM car")
  .fetchObject(Long.class)
  .orElseThrow();

// Standard primitives and JDK types are supported by default
Optional<UUID> id = database.query("SELECT id FROM employee LIMIT 1")
  .fetchObject(UUID.class);

// Lists work as you would expect
List<String> names = database.query("SELECT name FROM employee")
  .fetchList(String.class);
```

## User-defined Types

In the case of user-defined types and [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) types, the standard [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) examines the names of columns in the [`ResultSet`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSet.html) and matches them to corresponding fields via reflection.  The [`@DatabaseColumn`](https://javadoc.pyranid.com/com/pyranid/DatabaseColumn.html) annotation allows per-field customization of mapping behavior.

JavaBean and [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) mapping require at least one selected column to match a writable property or record component. If no selected columns match, Pyranid raises a [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) instead of returning an object with all default values. Use column aliases or a custom [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) when the default property matching is not appropriate.

After that one-match minimum, matching is otherwise partial: Pyranid does not require every writable JavaBean property or [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) component to have a selected column. Pyranid supplies `null` for an unmatched reference-type Record component; an unmatched primitive Record component raises a [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html). For an unmatched JavaBean property, Pyranid does not invoke its setter, so the instance supplied by the [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html) keeps its current value, including constructor or field-initializer values and primitive defaults such as `0` or `false`.

An omitted column differs from a selected column containing SQL `NULL`. A selected `NULL` is mapped as `null` for reference targets - including invoking a JavaBean setter with `null` - and raises a [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) for a primitive target. Prefer a projection type whose members match the selected columns, or provide a custom [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html), when your application requires every target member to be populated.

The default [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html) uses public constructors and the standard mapper uses public JavaBean accessors. Define mapped beans, records, constructors, getters, and setters as public. Your own [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html) can instantiate an intentionally non-public record. Mapping a non-public JavaBean generally requires a custom [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html), because the standard mapper invokes the bean's setters itself.

By default, column names are assumed to be separated by `_` characters and are mapped to their camel-case equivalent.  For example:

```java
public class Car {
  private Long carId;
  private Color color;

  // For schema flexibility, Pyranid will match both "deposit_amount1" and "deposit_amount_1" column names
  private BigDecimal depositAmount1;

  // Use this annotation to specify variants if the field name doesn't match the column name
  @DatabaseColumn({"systok", "sys_tok"})
  private UUID systemToken;

  public Long getCarId() { return this.carId; }
  public void setCarId(Long carId) { this.carId = carId; }

  public Color getColor() { return this.color; }
  public void setColor(Color color) { this.color = color; }

  public BigDecimal getDepositAmount1() { return this.depositAmount1; }
  public void setDepositAmount1(BigDecimal depositAmount1) { this.depositAmount1 = depositAmount1; }

  public UUID getSystemToken() { return this.systemToken; }
  public void setSystemToken(UUID systemToken) { this.systemToken = systemToken; }
}

Car car = database.query("""
  SELECT car_id, color, systok
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();

// Output might be "Car ID is 123 and color is BLUE. Token is d73c523a-8344-44ef-819c-40467662d619"
out.printf("Car ID is %s and color is %s. Token is %s\n",
  car.getCarId(), car.getColor(), car.getSystemToken());

// Column names will work with wildcard queries as well
car = database.query("""
  SELECT *
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();

// Column aliases work too
car = database.query("""
  SELECT some_id AS car_id, some_color AS color
  FROM car
  LIMIT 1
""")
  .fetchObject(Car.class)
  .orElseThrow();
```

## Rows As Maps

When you don't want to define a type at all - exploratory queries, admin tooling, dynamic-shape `SELECT *`, exports - fetch rows as maps with [`Query::mapRowType()`](https://javadoc.pyranid.com/com/pyranid/Query.html#mapRowType()):

```java
List<Map<String, Object>> rows = database.query("SELECT * FROM car")
  .fetchList(Query.mapRowType());
Object make = rows.get(0).get("make");

Optional<Map<String, Object>> row = database.query("""
  SELECT *
  FROM car
  WHERE id = :id
  """)
  .bind("id", 123)
  .fetchObject(Query.mapRowType());
```

Each row is an insertion-ordered [`LinkedHashMap`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/LinkedHashMap.html) in column order. Keys are *normalized (lowercase)* column labels, so lookups behave identically across databases regardless of whether the driver reports unquoted labels uppercased (Oracle, HSQLDB), lowercased (PostgreSQL), or as written (MySQL, SQL Server). `AS` aliases are respected (and lowercased); `NULL` columns are present with a `null` value; values use the same dialect-aware extraction as record/bean mapping. Duplicate column labels - e.g. an unaliased multi-table join - fail fast with a [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html); use column aliases to disambiguate.

The raw [`Map`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html)`.class` and [`LinkedHashMap`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/LinkedHashMap.html)`.class` tokens are also accepted anywhere a result type token is accepted, but Java's type erasure means they produce raw result types:

```java
List<Map> rows = database.query("SELECT * FROM car").fetchList(Map.class);
```

The well-known JDK map tokens (`HashMap.class`, `TreeMap.class`, `Hashtable.class`, `ConcurrentHashMap.class`, and the `SortedMap`/`NavigableMap`/`ConcurrentMap` interfaces) are rejected with a clear error; other classes that happen to implement [`Map`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) continue to map via the JavaBean path.

The special case lives in the default [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) implementation only - a custom database-wide or per-query [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) receiving the token decides for itself how to handle it.

## Supported Primitives

* [`Byte`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Byte.html)
* [`Short`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Short.html)
* [`Integer`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Integer.html)
* [`Long`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Long.html)
* [`Float`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Float.html)
* [`Double`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Double.html)
* [`Boolean`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Boolean.html)
* [`Character`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Character.html)
* [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html)
* [`byte[]`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Byte.html)

## Supported JDK Types

* [`Enum<E>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Enum.html)
* [`UUID`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/UUID.html)
* [`BigDecimal`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/math/BigDecimal.html)
* [`BigInteger`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/math/BigInteger.html)
* [`Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html)
* [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html)
* [`LocalDate`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDate.html) for `DATE`
* [`LocalTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalTime.html) for `TIME`
* [`LocalDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDateTime.html) for `TIMESTAMP`
* [`OffsetTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetTime.html) for `TIME WITH TIMEZONE`
* [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html) for `TIMESTAMP WITH TIMEZONE`
* [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html) for `TIMESTAMP` and `TIMESTAMP WITH TIMEZONE`
* [`Year`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Year.html) from `INTEGER`-like, string, or `DATE`-surfaced year columns (e.g. MySQL `YEAR`)
* [`YearMonth`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/YearMonth.html) from ISO-8601 string columns (e.g. `2027-12`)
* [`java.sql.Timestamp`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Timestamp.html)
* [`java.sql.Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Date.html)
* [`ZoneId`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZoneId.html)
* [`TimeZone`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/TimeZone.html)
* [`Locale`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Locale.html) (IETF BCP 47 "language tag" format)
* [`Currency`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Currency.html)

Temporal result-set mapping uses JDBC [`ResultSetMetaData`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/ResultSetMetaData.html) for the returned column. Zone-less `TIMESTAMP` values are wall-clock values; when mapping them to [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html), or [`Date`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Date.html), Pyranid interprets that wall clock in [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)). Mapping a zone-less `TIMESTAMP` to [`LocalDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDateTime.html) keeps the wall clock unchanged. `TIMESTAMP WITH TIME ZONE` values already identify an instant; mapping to [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html) preserves that instant, and mapping to [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html) represents it in the configured [`Database.Builder::timeZone(...)`](https://javadoc.pyranid.com/com/pyranid/Database.Builder.html#timeZone(java.time.ZoneId)).

Some JDBC drivers expose temporal columns as strings instead of JDBC temporal objects. For JavaBean and record mapping, Pyranid parses ISO-8601 strings and common JDBC timestamp strings such as `2020-01-02 03:04:05` when the target property or component is [`LocalDate`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDate.html), [`LocalTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalTime.html), [`LocalDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/LocalDateTime.html), [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), or [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html).

Pyranid preserves the fractional-second precision returned by your JDBC driver; it does not round or truncate [`Instant`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/Instant.html), [`OffsetDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/OffsetDateTime.html), or [`ZonedDateTime`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/time/ZonedDateTime.html) values to milliseconds. The maximum precision is still determined by the database column and driver (for example, PostgreSQL timestamps are stored at microsecond precision).

Invalid [`TimeZone`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/TimeZone.html) or [`Locale`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Locale.html) values in your resultset will raise a [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) instead of silently falling back to defaults.

## SQL ARRAY and JSON Results

SQL ARRAY columns can be mapped to Java array targets such as [`String[]`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html) or [`UUID[]`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/UUID.html). They can also be mapped to [`List<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/List.html) or [`Set<T>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Set.html) when Pyranid can see the generic element type from a [`Record`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Record.html) component or JavaBean setter, for example `record Row(List<String> tags, Set<String> labels) {}` or `void setTags(Set<String> tags)`. Raw scalar [`List.class`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/List.html) and [`Set.class`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Set.html) targets are supported too, but the element type is whatever the JDBC driver returns because Java's [`Class<List>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Class.html) and [`Class<Set>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/Class.html) tokens carry no generic type argument. [`Set`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Set.html) targets use insertion order from the SQL ARRAY and apply normal [`Set`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Set.html) semantics, so duplicates collapse and multiple SQL `NULL` elements become one `null`.

PostgreSQL `JSON`/`JSONB` values returned by supported PostgreSQL JDBC drivers as [`PGobject`](https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html), and DuckDB `JSON` values returned as driver nodes, map to [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html) by default. Pyranid does not parse JSON into application objects; register a [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html) when you want to inflate JSON into your own type.

Text-backed vector columns such as pgvector, which supported PostgreSQL JDBC drivers surface as literals like `[0.1,0.2,0.3]`, map to `float[]` and `double[]` targets by parsing that literal. DuckDB fixed-size vector ARRAYs use Pyranid's SQL ARRAY conversion path. See [Vector](https://pyranid.com/docs/parameter-binding#vector) in the Parameter Binding docs for the bind side.

## SQL STRUCT Results

On databases whose dialects surface SQL STRUCT columns as attribute-name-to-value maps - currently DuckDB - STRUCT values map onto Java records, recursively. Attribute names follow the same matching rules as result columns: `camelCase` components match `snake_case` attributes, and [`@DatabaseColumn`](https://javadoc.pyranid.com/com/pyranid/DatabaseColumn.html) aliases apply.

```sql
CREATE TABLE employee (
  employee_id BIGINT PRIMARY KEY,
  person STRUCT(name VARCHAR, email_address VARCHAR),
  badges STRUCT(label VARCHAR, level INTEGER)[]
);
```

```java
public record Person(String name, String emailAddress) {}
public record Badge(String label, Integer level) {}
public record Employee(Long employeeId, Person person, List<Badge> badges) {}

// STRUCT columns become record components; STRUCT lists become record lists
List<Employee> employees = database.query("SELECT * FROM employee")
  .fetchList(Employee.class);

// A single-column STRUCT result maps directly to a record target...
Optional<Person> person = database.query("SELECT person FROM employee WHERE employee_id=:id")
  .bind("id", 1L)
  .fetchObject(Person.class);

// ...including STRUCT projections built in SQL (note the space after ':' - see SQL Parsing Rules)
Optional<Person> projected = database.query("""
  SELECT {'name': name, 'email_address': email_address} AS person
  FROM legacy_employee
  WHERE employee_id=:id
  """)
  .bind("id", 1L)
  .fetchObject(Person.class);
```

Nested STRUCTs map to nested records to any depth, and a record component typed [`Map<String, Object>`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) receives the attribute map itself. [`Map`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) row targets surface STRUCT values as plain insertion-ordered maps with no driver-specific types, including elements of top-level STRUCT/JSON list columns. Ordinary by-name row mapping always takes precedence: the single-column direct mapping applies only when no result column matches a record component. A `NULL` STRUCT maps to a `null` component (or an empty [`Optional`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html) for single-column fetches). Top-level row records - the single-column direct mapping included - are constructed via the configured [`InstanceProvider`](https://javadoc.pyranid.com/com/pyranid/InstanceProvider.html); nested records are constructed via their canonical constructors, and [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html)s are not consulted for individual STRUCT attributes.

## Custom Mapping

Fine-grained control of mapping is supported by registering [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html) instances.  For example, you might want to "inflate" a `JSONB` column into a Java type:

When multiple custom column mappers apply, Pyranid tries them in the order supplied. Returning [`MappingResult::fallback()`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.MappingResult.html#fallback()) lets the next applicable mapper run; if none handles the value, normal mapping continues.

When mapping JavaBeans or records, custom column mappers run against matched properties or components. Row-type custom mapping is for single-column results that represent the target value itself.

```java
// Your application-specific type
class MySpecialType {
  List<UUID> uuids;
  Currency currency;
}
```

Just add a [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html) that handles it:

```java
ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(new CustomColumnMapper() {
  @NonNull
  @Override
  public Boolean appliesTo(@NonNull TargetType targetType) {
    // Can also apply to parameterized types, e.g.
    // targetType.matchesParameterizedType(List.class, UUID.class) for List<UUID>
    return targetType.matchesClass(MySpecialType.class);
  }

  @NonNull
  @Override
  public MappingResult map(
    @NonNull StatementContext<?> statementContext,
    @NonNull ResultSet resultSet,
    @NonNull Object resultSetValue,
    @NonNull TargetType targetType,
    @NonNull Integer columnIndex,
    @Nullable String columnLabel,
    @NonNull InstanceProvider instanceProvider
  ) {
    // Pull JSON String data from the ResultSet and inflate it
    String json = resultSetValue.toString();
    MySpecialType mySpecialType = GSON.fromJson(json, MySpecialType.class);

    // Or return MappingResult.fallback() to let the next applicable custom mapper run.
    // If none handles the value, Pyranid continues with normal mapping behavior.
    return MappingResult.of(mySpecialType);
  }
}))
.build();

// Construct your database with the custom mapper
Database database = Database.withDataSource(...)
  .resultSetMapper(resultSetMapper)
  .build();
```

With the custom mapper in place, and a table like this...

```sql
CREATE TABLE row (
  row_id UUID PRIMARY KEY,
  my_special_type JSONB NOT NULL
);

INSERT INTO row (row_id, my_special_type) VALUES (
  'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
  '
    {
      "uuids": ["bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb"],
      "currency": "BRL"
    }
  '::jsonb
);
```

...your application code might look like this:

```java
// A ResultSet row with our special type as a column
public record MyRow(UUID rowId, MySpecialType mySpecialType) {}

// Query for data
List<MyRow> rows = database.query("SELECT * FROM row").fetchList(MyRow.class);

// Examine the first row of the ResultSet
MyRow myRow = rows.get(0);
// Our custom mapper has instantiated this for us
MySpecialType mySpecialType = myRow.mySpecialType();
// Prints contents of List<UUID>, as expected
out.println(mySpecialType.uuids);
// e.g. "Real brasileiro" for Brazilian Real
out.println(mySpecialType.currency.getDisplayName(Locale.forLanguageTag("pt-BR")));
```

Your [`CustomColumnMapper`](https://javadoc.pyranid.com/com/pyranid/CustomColumnMapper.html) also works for the single-column "Standard Type" scenario.

```java
// Pull back the column for a single row
Optional<MySpecialType> mySpecialType =
  database.query("SELECT my_special_type FROM row LIMIT 1")
    .fetchObject(MySpecialType.class);

// Pull back a list of just the column values
List<MySpecialType> mySpecialTypes =
  database.query("SELECT my_special_type FROM row")
    .fetchList(MySpecialType.class);
```

## Parameterized-Type Example

Custom mapping is not limited to concrete classes.  You can target parameterized types directly.

Suppose your database stores localized strings in a `JSONB` column and you want Pyranid to map it to a [`Map`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Map.html) from [`Locale`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Locale.html) to [`String`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/String.html).

```java
ResultSetMapper resultSetMapper = ResultSetMapper.withCustomColumnMappers(List.of(new CustomColumnMapper() {
  @NonNull
  @Override
  public Boolean appliesTo(@NonNull TargetType targetType) {
    return targetType.matchesParameterizedType(Map.class, Locale.class, String.class);
  }

  @NonNull
  @Override
  public MappingResult map(
    @NonNull StatementContext<?> statementContext,
    @NonNull ResultSet resultSet,
    @Nullable Object rawColumnValue,
    @NonNull TargetType targetType,
    @Nullable Integer columnIndex,
    @Nullable String columnLabel,
    @NonNull InstanceProvider instanceProvider
  ) {
    if (rawColumnValue == null)
      return MappingResult.of(null);

    Type mapType = new TypeToken<Map<Locale, String>>() {}.getType();
    Map<Locale, String> valuesByLocale = GSON.fromJson(rawColumnValue.toString(), mapType);
    return MappingResult.of(valuesByLocale);
  }
})).build();

Database database = Database.withDataSource(dataSource)
  .resultSetMapper(resultSetMapper)
  .build();
```

With that mapper in place, a row type like this works as expected:

```java
public record Product(
  UUID productId,
  Map<Locale, String> nameTranslations
) {}

Optional<Product> product = database.query("""
  SELECT product_id, name_translations
  FROM product
  WHERE product_id = :productId
  """)
  .bind("productId", UUID.fromString("aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"))
  .fetchObject(Product.class);
```

If you also need to write the same type, pair this mapper with a matching [`CustomParameterBinder`](https://javadoc.pyranid.com/com/pyranid/CustomParameterBinder.html) as shown in the [Parameterized-Type Example](https://pyranid.com/docs/parameter-binding#parameterized-type-example) in the Parameter Binding docs.

## Per-Query Mapping and Binding

The [`ResultSetMapper`](https://javadoc.pyranid.com/com/pyranid/ResultSetMapper.html) and [`PreparedStatementBinder`](https://javadoc.pyranid.com/com/pyranid/PreparedStatementBinder.html) SPIs are normally configured database-wide at build time. They can also be overridden for a single query via [`Query::resultSetMapper(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#resultSetMapper(com.pyranid.ResultSetMapper)) and [`Query::preparedStatementBinder(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#preparedStatementBinder(com.pyranid.PreparedStatementBinder)); the same contracts apply per query.

This is the idiomatic way to inline-map an ad-hoc projection (a join, computed columns, a tuple) without defining a database-wide mapper - both SPIs are functional interfaces, so a lambda works:

```java
public record NameCount(String name, Long total) {}

List<NameCount> counts = database.query("""
  SELECT name, COUNT(*) AS total
  FROM employee
  GROUP BY name
  """)
  .resultSetMapper((ctx, rs, type, ip) ->
    Optional.of(type.cast(new NameCount(rs.getString(1), rs.getLong(2)))))
  .fetchList(NameCount.class);
```

The override applies to every row this query maps - including [`Query::fetchStream(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#fetchStream(java.lang.Class,java.util.function.Function)) rows and DML-returning results - and to every parameter this query binds, including expanded IN-list elements and each batch group. Other queries on the same [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) are unaffected; passing `null` restores the database-wide instance. Metrics, statement logging, and exception diagnostics behave identically with an override present.

Custom binders receive bound-ready raw values: [`SecureParameter`](https://javadoc.pyranid.com/com/pyranid/SecureParameter.html) and [`Optional`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/Optional.html) wrappers are unwrapped by Pyranid *before* the binder is invoked, and `null` parameters never reach the binder (Pyranid binds them via [`PreparedStatement::setNull(...)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/PreparedStatement.html#setNull(int,int)) even when an override is present).

---

# Statements
URL: https://pyranid.com/docs/statements
Description: How to update data in your database with Pyranid

Statements are used for general-purpose DML execution (`INSERT`, `UPDATE`, `DELETE`, functions, ...)  They can also be expressions that return nothing, such as DDL operations (`CREATE TABLE`, `ALTER TABLE`, ...)

---

## Basic Statements

Most statements follow this pattern - execute them and see how many rows were affected:

```java
// Returns the number of affected rows
long updateCount = database.query("""
  UPDATE car
  SET color = :color
  """)
  .bind("color", Color.RED)
  .execute();
```

#### References:

* [`Query::execute()`](https://javadoc.pyranid.com/com/pyranid/Query.html#execute())

## Returning Statements

You may return single or multiple objects via database-native DML-returning syntax. PostgreSQL, SQLite, and DuckDB use SQL `RETURNING` for DML. MariaDB supports `INSERT ... RETURNING`. SQL Server uses `OUTPUT`, including `OUTPUT ... INTO` for trigger-enabled tables. For Oracle, prefer JDBC-generated keys with explicit key column names.

```java
// Returns a single object - here, the sequence value used for the insert
Optional<BigInteger> insertedCarId = database.query("""
  INSERT INTO car (id, color)
  VALUES (nextval('car_seq'), :color)
  RETURNING id
  """)
  .bind("color", Color.GREEN)
  .executeForObject(BigInteger.class);

// Returns a list of objects - here, the rows that were updated
List<Car> repaintedCars = database.query("""
  UPDATE car
  SET color = :newColor
  WHERE color = :oldColor
  RETURNING *
  """)
  .bind("newColor", Color.GREEN)
  .bind("oldColor", Color.BLUE)
  .executeForList(Car.class);
```

#### References:

* [`Query::executeForObject(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeForObject(java.lang.Class))
* [`Query::executeForList(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeForList(java.lang.Class))

## Generated Keys

For identity/auto-increment columns, use JDBC-generated keys. This is useful for databases that do not support `RETURNING` for inserts. Oracle requires explicit generated-key column names. On PostgreSQL and SQLite, prefer explicit SQL `RETURNING`; on MariaDB, use `INSERT ... RETURNING`; and on SQL Server, use `OUTPUT`. DuckDB's JDBC driver has no generated-key support at all - [`Query::executeReturningGeneratedKey(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKey(java.lang.Class,java.lang.String...)) fails fast there with a clear [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html) directing you to `RETURNING`. Map the returned rows with [`Query::executeForObject(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeForObject(java.lang.Class)) or [`Query::executeForList(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeForList(java.lang.Class)).

```java
Optional<BigInteger> generatedCarId = database.query("""
  INSERT INTO car (color)
  VALUES (:color)
  """)
  .bind("color", Color.GREEN)
  .executeReturningGeneratedKey(BigInteger.class);

// Some drivers require the generated-key column names to be specified.
Optional<BigInteger> generatedCarIdByColumn = database.query("""
  INSERT INTO car (color)
  VALUES (:color)
  """)
  .bind("color", Color.BLUE)
  .executeReturningGeneratedKey(BigInteger.class, "id");
```

Use [`Query::executeReturningGeneratedKeys(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKeys(java.lang.Class)) when one statement can produce multiple generated-key rows.

#### References:

* [`Query::executeReturningGeneratedKey(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKey(java.lang.Class))
* [`Query::executeReturningGeneratedKey(Class<T>, String...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKey(java.lang.Class,java.lang.String...))
* [`Query::executeReturningGeneratedKeys(Class<T>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKeys(java.lang.Class))
* [`Query::executeReturningGeneratedKeys(Class<T>, String...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeReturningGeneratedKeys(java.lang.Class,java.lang.String...))

## Batch Operations

Batch operations can be more efficient than execution of discrete statements - they are useful for inserting a lot of data at once.

```java
// Batch parameters are lists of maps keyed by named parameters
List<Map<String, Object>> parameterGroups = List.of(
  Map.of("id", 123, "color", Color.BLUE),
  Map.of("id", 456, "color", Color.RED)
);

// Insert both cars
List<Long> updateCounts = database.query("""
  INSERT INTO car (id, color)
  VALUES (:id, :color)
  """)
  .executeBatch(parameterGroups);
```

Each parameter group must provide a complete set of values after merging with any values bound on the query, and all groups must expand to the same number of JDBC parameters (for example, IN-list sizes must match).

Use [`Query::batchChunkSize(Integer)`](https://javadoc.pyranid.com/com/pyranid/Query.html#batchChunkSize(java.lang.Integer)) to bound how many parameter groups Pyranid sends in each JDBC batch execution:

```java
List<Long> updateCounts = database.query("""
  INSERT INTO car (id, color)
  VALUES (:id, :color)
  """)
  .batchChunkSize(500)
  .executeBatch(parameterGroups);
```

Chunking is performed by Pyranid; each chunk is still executed by the JDBC driver as a normal batch. If unset, Pyranid preserves the default behavior of sending all parameter groups in one JDBC batch. [`Query::batchChunkSize(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#batchChunkSize(java.lang.Integer)) applies only to [`Query::executeBatch(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeBatch(java.util.List)); using it with a non-batch terminal operation throws [`IllegalStateException`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/lang/IllegalStateException.html). For all-or-nothing behavior when a later chunk fails, wrap chunked batches in [`Database::transaction(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation)); outside an explicit transaction, earlier chunks may already be committed depending on autocommit and driver behavior.

Pyranid will automatically determine if your JDBC driver supports "large" updates and batch operations and uses them if available. If the driver reports that large updates or large batches are unsupported, Pyranid falls back to the standard JDBC update APIs and caches that support decision for the current [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) instance.

#### References:

* [`Query::batchChunkSize(Integer)`](https://javadoc.pyranid.com/com/pyranid/Query.html#batchChunkSize(java.lang.Integer))
* [`Query::executeBatch(List<Map<String, Object>>)`](https://javadoc.pyranid.com/com/pyranid/Query.html#executeBatch(java.util.List))

---

# Transactions
URL: https://pyranid.com/docs/transactions
Description: How to manage database transactions with Pyranid

Transactions are a critical part of any DBMS - they allow multiple discrete operations to be combined into atomic units of work.  While a transaction is in-progress, any work it performs is not visible to concurrent transactions until the transaction has successfully completed.  In SQL, transactions are performed via `BEGIN` and `COMMIT` commands.

Outside a Pyranid transaction, each operation borrows a connection and leaves transaction control to the configured [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) and JDBC driver. Pyranid does not force `autocommit=true` or call `commit()` for standalone operations. Production pools normally return connections with autocommit enabled; configure that explicitly if your [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) does not.

However, any code that runs inside of the [`Database::transaction(TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation)) closure operates within the context of a transaction - no state changes will propagate until closure finishes its execution non-exceptionally.

```java
database.transaction(() -> {
  // Anything that happens in here is part of the transaction.
  // If an exception bubbles out, the transaction will be rolled back.
});
```

All data access performed via [`Database::query(String)`](https://javadoc.pyranid.com/com/pyranid/Database.html#query(java.lang.String)) detects the presence of a transaction on the current thread of execution and automatically participates when the transaction belongs to the same [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) instance. This is usually the behavior you want. See the [Context](https://pyranid.com/docs/transactions#context) section below for more information on the mechanics, and for how to get a handle to the current transaction in order to manipulate it (e.g. creating a [`java.sql.Savepoint`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Savepoint.html)).

While less common, it is sometimes useful to share a transaction across multiple threads of execution.  The [Sharing Across Threads](https://pyranid.com/docs/transactions#sharing-across-threads) section below describes this functionality in more detail.

For performance reasons, no [`java.sql.Connection`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html) is fetched from the [`javax.sql.DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) until the first data access operation occurs.

---

## Basics

The canonical example - a bank transfer between two accounts.

```java
database.transaction(() -> {
  // Pull initial account balances
  BigDecimal balance1 = database.query("SELECT balance FROM account WHERE id = :id")
    .bind("id", 1)
    .fetchObject(BigDecimal.class)
    .orElseThrow();
  BigDecimal balance2 = database.query("SELECT balance FROM account WHERE id = :id")
    .bind("id", 2)
    .fetchObject(BigDecimal.class)
    .orElseThrow();

  // Debit one and credit the other
  balance1 = balance1.subtract(amount);
  balance2 = balance2.add(amount);

  // Persist changes
  database.query("UPDATE account SET balance = :balance WHERE id = :id")
    .bind("balance", balance1)
    .bind("id", 1)
    .execute();
  database.query("UPDATE account SET balance = :balance WHERE id = :id")
    .bind("balance", balance2)
    .bind("id", 2)
    .execute();
});

// For convenience, transactional operations may return values
Optional<BigDecimal> newBalance = database.transaction(() -> {
  // Make some changes
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();
  database.query("UPDATE account SET balance = balance + 10 WHERE id = :id")
    .bind("id", 2)
    .execute();

  // Return the new value
  return database.query("SELECT balance FROM account WHERE id = :id")
    .bind("id", 2)
    .fetchObject(BigDecimal.class);
});
```

#### References:

* [`Database::transaction(TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation))
* [`Database::transaction(ReturningTransactionalOperation<T>)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.ReturningTransactionalOperation))

## Context

Internally, [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) manages a thread-local stack of [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html) instances, so it can always provide a handle to the currently-scoped transaction, if one exists.

The handle is useful for creating/restoring savepoints, marking as "force rollback", registering post-transaction operations, etc.

You cannot directly issue commits or rollbacks via [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html).  In order to provide a simple closure-based API, Pyranid performs those operations internally.

```java
// Gets a handle to the current transaction, if any.
Optional<Transaction> transaction = database.currentTransaction();

// Output is "false"
out.println(transaction.isPresent());

database.transaction(() -> {
  // A transaction only exists for the life of the closure
  Optional<Transaction> actualTransaction = database.currentTransaction();

  // Output is "true"
  out.println(actualTransaction.isPresent());
});
```

#### References:

* [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html)
* [`Database::currentTransaction()`](https://javadoc.pyranid.com/com/pyranid/Database.html#currentTransaction())

## Sharing Across Threads

Should you need to share the same transaction across multiple threads, use the [`Database::participate(Transaction, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation)) API.

Transactions are scoped to the [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) instance that created them. A second [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) built with the same [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) instance can participate in the transaction; a [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html) built with a different [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) fails fast instead of silently joining the wrong connection.

On Java 21+, a clean way to do this is with a virtual-thread executor:

```java
database.transaction(() -> {
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();

  // Get a handle to the current transaction
  Transaction transaction = database.currentTransaction().orElseThrow();

  try (ExecutorService executor = Executors.newVirtualThreadPerTaskExecutor()) {
    CompletableFuture.runAsync(() -> {
      // In a different thread and participating in the existing transaction.
      // No commit or rollback will occur when the closure completes, but if an
      // exception bubbles out the transaction will be marked as rollback-only
      database.participate(transaction, () -> {
        database.query("UPDATE account SET balance = balance + 10 WHERE id = :id")
          .bind("id", 2)
          .execute();
      });
    }, executor).join();
  }
});
```

If you are already using an executor-based or virtual-thread-based concurrency model, the pattern is the same: capture the current [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html) on the originating thread, then call [`Database::participate(Transaction, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation)) inside the worker thread.

**Warning: Transaction Context Is Thread-Local**
Pyranid transaction context follows the current Java thread. Async frameworks that suspend work and resume it on another thread do not automatically carry the transaction context with them. This includes Kotlin coroutines when a suspension point resumes on a different dispatcher thread. Keep transactional database access on the transaction thread, or capture the current [`Transaction`](https://javadoc.pyranid.com/com/pyranid/Transaction.html) and call [`Database::participate(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation)) on the thread that performs the database work. All participating work must complete before the owning transaction closure returns.

If a participating thread is interrupted while waiting for another participant to release the transaction connection, Pyranid restores the interrupt flag and throws [`DatabaseException`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html). This makes blocked participants cancellable; the thread currently executing a JDBC call still relies on driver behavior, [`Query::queryTimeout(...)`](https://javadoc.pyranid.com/com/pyranid/Query.html#queryTimeout(java.time.Duration)), or application-managed [`Statement::cancel()`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Statement.html#cancel()).

#### References:

* [`Database::participate(Transaction, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation))

## Rolling Back

Rollbacks are performed by Pyranid in 3 scenarios:

* An exception bubbles out of a transaction closure
* You explicitly mark a transaction as "rollback only"
* You create a savepoint and roll back to it

```java
// Any exception that bubbles out will cause a rollback
database.transaction(() -> {
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();
  throw new IllegalStateException("Something's wrong!");
});

// You may mark a transaction as rollback-only, and it will roll back
// after the closure execution has completed
database.transaction(() -> {
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();

  // Hmm...I changed my mind
  Transaction transaction = database.currentTransaction().orElseThrow();
  transaction.setRollbackOnly(true);
});

// You may roll back part of a transaction with a savepoint
database.transaction(() -> {
  Transaction transaction = database.currentTransaction().orElseThrow();

  try {
    transaction.withSavepoint(() -> {
      database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
        .bind("id", 1)
        .execute();

      // Hmm...I changed my mind about this part of the transaction
      throw new IllegalStateException("Undo the savepoint work");
    });
  } catch (IllegalStateException ignored) {
    // Work inside the savepoint was rolled back, and the outer transaction continues
  }
});
```

The lower-level [`Transaction::createSavepoint()`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#createSavepoint()), [`Transaction::rollback(Savepoint)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#rollback(java.sql.Savepoint)), and [`Transaction::releaseSavepoint(Savepoint)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#releaseSavepoint(java.sql.Savepoint)) methods remain available for callers that need manual control. Prefer [`Transaction::withSavepoint(...)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#withSavepoint(com.pyranid.TransactionalOperation)) for common partial-rollback workflows because it handles rollback, release, and suppressed cleanup failures consistently. DuckDB's JDBC driver does not support savepoints.

Savepoints are stack-like on most drivers. Avoid manual out-of-order rollback or release of nested savepoints unless your driver documents the behavior you need.

#### References:

* [`Transaction::setRollbackOnly(Boolean)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#setRollbackOnly(java.lang.Boolean))
* [`Transaction::createSavepoint()`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#createSavepoint())
* [`Transaction::rollback(java.sql.Savepoint)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#rollback(java.sql.Savepoint))
* [`Transaction::releaseSavepoint(java.sql.Savepoint)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#releaseSavepoint(java.sql.Savepoint))
* [`Transaction::withSavepoint(TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#withSavepoint(com.pyranid.TransactionalOperation))

## Nesting

Each lexically-scoped "nested" transaction is independent of its enclosing transaction. There is no parent-child relationship.

However, if you would like this behavior, you may explicitly use the [`Database::participate(Transaction, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#participate(com.pyranid.Transaction,com.pyranid.TransactionalOperation)) API to have a "child" participate in its parent transaction.

Nested transactions borrow separate physical connections and can pressure small connection pools. Participating worker threads must use a transaction from the same [`DataSource`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/javax/sql/DataSource.html) instance and must complete before the owning transaction closure returns; coordinate this with primitives in your application, such as [`CompletableFuture::join`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/concurrent/CompletableFuture.html#join()), [`ExecutorService::awaitTermination`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/concurrent/ExecutorService.html#awaitTermination(long,java.util.concurrent.TimeUnit)), or a [`CountDownLatch`](https://docs.oracle.com/en/java/javase/26/docs/api/java.base/java/util/concurrent/CountDownLatch.html).

```java
database.transaction(() -> {
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();

  // This transaction will commit
  database.transaction(() -> {
    database.query("UPDATE account SET balance = balance + 10 WHERE id = :id")
      .bind("id", 2)
      .execute();
  });

  // This transaction will not!
  throw new IllegalStateException("I should not have used nested transactions here...");
});
```

## Isolation

You may specify the normal DBMS isolation levels per-transaction as needed:
* [`READ_COMMITTED`](https://javadoc.pyranid.com/com/pyranid/TransactionIsolation.html#READ_COMMITTED)
* [`READ_UNCOMMITTED`](https://javadoc.pyranid.com/com/pyranid/TransactionIsolation.html#READ_UNCOMMITTED)
* [`REPEATABLE_READ`](https://javadoc.pyranid.com/com/pyranid/TransactionIsolation.html#REPEATABLE_READ)
* [`SERIALIZABLE`](https://javadoc.pyranid.com/com/pyranid/TransactionIsolation.html#SERIALIZABLE)

```java
// If not specified, DEFAULT is assumed (whatever your DBMS prefers)
database.transaction(TransactionOptions.withIsolation(TransactionIsolation.SERIALIZABLE).build(), () -> {
  database.query("UPDATE account SET balance = balance - 10 WHERE id = :id")
    .bind("id", 1)
    .execute();
  database.query("UPDATE account SET balance = balance + 10 WHERE id = :id")
    .bind("id", 2)
    .execute();
});
```

#### References:

* [`TransactionIsolation`](https://javadoc.pyranid.com/com/pyranid/TransactionIsolation.html)
* [`TransactionOptions`](https://javadoc.pyranid.com/com/pyranid/TransactionOptions.html)
* [`Database::transaction(TransactionOptions, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionOptions,com.pyranid.TransactionalOperation))
* [`Database::transaction(TransactionOptions, ReturningTransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionOptions,com.pyranid.ReturningTransactionalOperation))

## Read-Only Transactions

```java
database.transaction(TransactionOptions.withReadOnly(true).build(), () -> {
  List<Account> accounts = database.query("SELECT * FROM account")
    .fetchList(Account.class);
});

database.transaction(
  TransactionOptions.withIsolation(TransactionIsolation.REPEATABLE_READ)
    .readOnly(true)
    .build(),
  () -> {
    List<Account> accounts = database.query("SELECT * FROM account")
      .fetchList(Account.class);
  });
```

[`TransactionOptions::withReadOnly(true)`](https://javadoc.pyranid.com/com/pyranid/TransactionOptions.html#withReadOnly(java.lang.Boolean)) maps to JDBC [`Connection::setReadOnly(true)`](https://docs.oracle.com/en/java/javase/26/docs/api/java.sql/java/sql/Connection.html#setReadOnly(boolean)) for the physical transaction connection. Pyranid restores the connection's original read-only setting before returning it to the pool.

**Warning: Read-Only Is a JDBC/DBMS Signal**
Read-only behavior is enforced, optimized, routed, or ignored by the JDBC driver and DBMS. SQL Server, Oracle, and SQLite drivers may treat it as advisory or ignore it for normal transactions; DuckDB's driver rejects per-connection read-only changes outright (read-only mode is a database-open option there). Pyranid does not parse SQL to reject writes itself.

## Post-Transaction Operations

It is useful to be able to schedule code to run after a transaction has completed.  Often, transaction management happens at a higher layer of code than business logic (e.g. a transaction-per-web-request pattern), so it is helpful to have a mechanism to "warp" local logic out to the higher layer.

Without this, you might run into subtle bugs like

1. Write to database
2. Send out notifications of system state change
3. (Sometime later) transaction is rolled back
4. Notification consumers are in an inconsistent state because they were notified of a change that was reversed by the rollback

```java
// Business logic
class EmployeeService {
  public void giveEveryoneRaises() {
    database.query("UPDATE employee SET salary = salary * 2")
      .execute();
    payrollSystem.startLengthyWarmupProcess();

    // Only send emails after the current transaction ends
    database.currentTransaction().get().addPostTransactionOperation((transactionResult) -> {
      if(transactionResult == TransactionResult.COMMITTED) {
        // Successful commit? email everyone with the good news
        for(Employee employee : findAllEmployees())
          sendCongratulationsEmail(employee);
      } else if(transactionResult == TransactionResult.ROLLED_BACK) {
        // Rolled back? We can clean up
        payrollSystem.cancelLengthyWarmupProcess();
      } else if(transactionResult == TransactionResult.IN_DOUBT) {
        // The final database outcome is unknown.
        // Avoid commit-only side effects and reconcile separately.
        payrollSystem.queueReconciliation();
      }
    });
  }

  // Rest of implementation elided
}

// Servlet filter which wraps requests in transactions
class DatabaseTransactionFilter implements Filter {
  @Override
  public void doFilter(ServletRequest servletRequest,
                       ServletResponse servletResponse,
                       FilterChain filterChain) throws IOException, ServletException {
    database.transaction(() -> {
      // Above business logic would happen somewhere down the filter chain
      filterChain.doFilter(servletRequest, servletResponse);

      // Business logic has completed at this point but post-transaction
      // operations will not run until the closure exits
    });

    // By this point, post-transaction operations will have been run
  }

  // Rest of implementation elided
}
```

### Transaction Result

Post-transaction callbacks receive a [`TransactionResult`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html) enum value indicating how the transaction finished:

* [`COMMITTED`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#COMMITTED) (successful commit)
* [`ROLLED_BACK`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#ROLLED_BACK) (Pyranid can prove that no application transaction work committed. This includes a successful rollback before commit, a commit-phase serialization failure followed by a successful rollback, and a failed physical-transaction begin whose candidate connection Pyranid discarded before application transaction work could execute. This result does not necessarily mean Pyranid invoked JDBC `rollback()`.)
* [`IN_DOUBT`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#IN_DOUBT) (Pyranid cannot prove the final database outcome because commit failed without a recognized serialization failure followed by successful rollback, or because rollback failed after application transaction work could have executed. A rollback failure while cleaning up a failed physical-transaction begin is not `IN_DOUBT` when application transaction work could not have executed; Pyranid discards the candidate connection and reports `ROLLED_BACK`.)

Post-transaction callbacks are fail-fast. If a callback throws, Pyranid wraps the failure in a [`PostTransactionOperationException`](https://javadoc.pyranid.com/com/pyranid/PostTransactionOperationException.html). When there is no other transaction or cleanup failure, [`Database::transaction(TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.TransactionalOperation)) throws this exception as the primary failure. When the transaction operation, commit, rollback, or cleanup already failed, Pyranid suppresses it onto the primary exception. Check [`PostTransactionOperationException::getTransactionResult()`](https://javadoc.pyranid.com/com/pyranid/PostTransactionOperationException.html#getTransactionResult()) to distinguish a successful commit followed by callback failure from a failed or in-doubt transaction.

Connection reuse is conservative and separate from the transaction result. When the final database outcome is unknown, Pyranid skips mutable-state restoration and attempts to abort and close the connection. If transaction-isolation, read-only, or auto-commit restoration fails after an otherwise definite commit or rollback, Pyranid stops further restoration and discards that connection as well rather than returning it to the pool with uncertain state.

## Retrying Serialization Failures

Use [`Database::transactionWithRetry(RetryPolicy, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transactionWithRetry(com.pyranid.RetryPolicy,com.pyranid.TransactionalOperation)) when concurrent database transactions may safely be re-run after the database rolls one of them back, such as serialization conflicts or deadlocks.

Retry behavior is explicit: you choose total attempts, the retry condition, and the backoff strategy.

```java
RetryPolicy retryPolicy = RetryPolicy.ofMaxAttempts(
  3,
  RetryPolicy.Backoff.fixed(Duration.ofMillis(25)),
  RetryPolicy.Condition.serializationFailureOrDeadlock());

TransactionRetryResult<Void> retryResult = database.transactionWithRetry(retryPolicy, () -> {
  database.query("""
      UPDATE account
      SET balance = balance - :amount
      WHERE account_id = :accountId
      """)
    .bind("accountId", accountId)
    .bind("amount", amount)
    .execute();
});
```

[`RetryPolicy::ofMaxAttempts(Integer, RetryPolicy.Backoff, RetryPolicy.Condition)`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.html#ofMaxAttempts(java.lang.Integer,com.pyranid.RetryPolicy.Backoff,com.pyranid.RetryPolicy.Condition)) means three total attempts in this example: the initial transaction plus up to two retries. Pyranid starts a fresh physical transaction for each attempt. The whole closure may run more than once, so keep non-idempotent external side effects outside the retried closure.

[`Database::transactionWithRetry(...)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transactionWithRetry(com.pyranid.RetryPolicy,com.pyranid.TransactionalOperation)) must own the complete transaction boundary and fails fast when called inside an active transaction for the same [`Database`](https://javadoc.pyranid.com/com/pyranid/Database.html). Pyranid consults the configured retry condition only after the failed attempt has a definite rolled-back outcome. Body-level serialization failures and deadlocks remain eligible after a successful rollback; a commit-phase serialization failure is eligible only when Pyranid's follow-up rollback succeeds. A failure before a physical transaction becomes available to application work is also eligible for policy evaluation, even if best-effort rollback of its begin candidate fails, because Pyranid discards the candidate and no application transaction work could execute. Other commit failures, rollback failures after application transaction work could have executed, and failures after a successful commit—including connection cleanup and post-transaction callback failures—are returned to the caller and are never replayed.

Unlike [`Database::transaction(ReturningTransactionalOperation<T>)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transaction(com.pyranid.ReturningTransactionalOperation)), [`Database::transactionWithRetry(RetryPolicy, TransactionalOperation)`](https://javadoc.pyranid.com/com/pyranid/Database.html#transactionWithRetry(com.pyranid.RetryPolicy,com.pyranid.TransactionalOperation)) returns a [`TransactionRetryResult`](https://javadoc.pyranid.com/com/pyranid/TransactionRetryResult.html) so callers can inspect failures recovered before success. [`TransactionRetryResult::getValue()`](https://javadoc.pyranid.com/com/pyranid/TransactionRetryResult.html#getValue()) contains the successful transaction value, and [`TransactionRetryResult::getFailures()`](https://javadoc.pyranid.com/com/pyranid/TransactionRetryResult.html#getFailures()) contains failed attempts that Pyranid retried before the transaction eventually succeeded.

```java
// Risky: the email may send more than once
database.transactionWithRetry(retryPolicy, () -> {
  database.query("UPDATE orders SET status = 'PAID' WHERE order_id = :orderId")
    .bind("orderId", orderId)
    .execute();

  emailClient.sendReceipt(orderId);
});
```

Instead, return enough information to run the side effect after the retry helper succeeds:

```java
TransactionRetryResult<ReceiptEmail> retryResult = database.transactionWithRetry(retryPolicy, () -> {
  database.query("UPDATE orders SET status = 'PAID' WHERE order_id = :orderId")
    .bind("orderId", orderId)
    .execute();

  return Optional.of(new ReceiptEmail(orderId));
});

retryResult.getValue().ifPresent(emailClient::sendReceipt);

for (DatabaseException failure : retryResult.getFailures()) {
  logger.info("Recovered transaction failure", failure);
}
```

For durable side effects, prefer the outbox pattern: write an outbox row inside the retried transaction and let a separate worker deliver it after commit.

[`RetryPolicy.Backoff::fixed(Duration)`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.Backoff.html#fixed(java.time.Duration)) and [`RetryPolicy.Backoff::exponential(Duration, Duration)`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.Backoff.html#exponential(java.time.Duration,java.time.Duration)) backoff strategies are built in:

```java
RetryPolicy exponentialRetryPolicy = RetryPolicy.ofMaxAttempts(
  5,
  RetryPolicy.Backoff.exponential(
    Duration.ofMillis(25),
    Duration.ofSeconds(1)),
  RetryPolicy.Condition.serializationFailureOrDeadlock());
```

Timeouts can be classified with [`DatabaseException::isTimeout()`](https://javadoc.pyranid.com/com/pyranid/DatabaseException.html#isTimeout()) and opted into with [`RetryPolicy.Condition::timeout()`](https://javadoc.pyranid.com/com/pyranid/RetryPolicy.Condition.html#timeout()), but Pyranid does not treat them as automatically safe to retry. A timeout may surface after the database has already performed part or all of the work.

Post-transaction operations registered with [`Transaction::addPostTransactionOperation(Consumer<TransactionResult>)`](https://javadoc.pyranid.com/com/pyranid/Transaction.html#addPostTransactionOperation(java.util.function.Consumer)) remain per-attempt hooks. A failed attempt with a determinate rolled-back outcome runs its hook with [`ROLLED_BACK`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#ROLLED_BACK) and may be retried if the policy permits. This includes a commit-phase serialization failure followed by successful rollback and a failed physical-transaction begin discarded before application transaction work could execute, even when no JDBC rollback completed. An attempt with an indeterminate outcome runs its hook with [`IN_DOUBT`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#IN_DOUBT) and terminates without replay; the successful attempt runs its hook with [`COMMITTED`](https://javadoc.pyranid.com/com/pyranid/TransactionResult.html#COMMITTED).