Pyranid Logo

Core Concepts

Database-Specific Recipes

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

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

Cross-Database Patterns

Configure The Database Type Behind Proxies

Pyranid can auto-detect supported databases from JDBC metadata. If a proxy, pool, or wrapper obscures that metadata, configure the DatabaseType explicitly so dialect-specific binding and mapping still apply.

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

Prefer IN-List Expansion To SQL ARRAY For Portable Filters

SQL ARRAY binding is not supported by every database or JDBC driver. For ordinary filters, Parameters::inList(...) is the portable shape.

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

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

SQL Server

Return Multiple Identity Values With OUTPUT

SQL Server's JDBC generated-key behavior is limited for multi-row identity inserts. Use SQL Server's OUTPUT clause and map the result set directly.

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

Use OUTPUT ... INTO For Trigger Tables

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

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

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

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

  // Use employeeIds here.
});

Return Upsert Actions From MERGE

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

record MergeResult(String mergeAction, Long employeeId, String name) {}

List<MergeResult> results = database.query("""
  MERGE employee AS target
  USING (VALUES (:email, :name)) AS source(email, name)
  ON target.email = source.email
  WHEN MATCHED THEN
    UPDATE SET name = source.name
  WHEN NOT MATCHED THEN
    INSERT (email, name) VALUES (source.email, source.name)
  OUTPUT $action AS merge_action, inserted.employee_id, inserted.name;
  """)
  .bind("email", "ada@example.com")
  .bind("name", "Ada")
  .executeForList(MergeResult.class);

Map datetimeoffset To Java Time Types

SQL Server datetimeoffset carries an offset. Pyranid maps it to OffsetDateTime and can also map it to the corresponding Instant.

OffsetDateTime eventAt = OffsetDateTime.parse("2020-11-01T01:30:15.123456700-04:00");

database.query("""
  INSERT INTO audit_event (audit_event_id, event_at)
  VALUES (:id, :eventAt)
  """)
  .bind("id", 1L)
  .bind("eventAt", eventAt)
  .execute();

OffsetDateTime stored = database.query("""
  SELECT event_at
  FROM audit_event
  WHERE audit_event_id = :id
  """)
  .bind("id", 1L)
  .fetchObject(OffsetDateTime.class)
  .orElseThrow();

Oracle

Request Generated Keys By Column Name

Oracle's default generated-key path can return a ROWID. Ask for the identity column explicitly.

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

Treat Empty Strings As Null

Oracle stores "" as NULL. Do not write cross-database code that expects an empty string to round-trip on Oracle.

database.query("""
  INSERT INTO employee_note (employee_id, note)
  VALUES (:employeeId, :note)
  """)
  .bind("employeeId", employeeId)
  .bind("note", "")
  .execute();

Boolean storedAsNull = database.query("""
  SELECT CASE WHEN note IS NULL THEN 1 ELSE 0 END
  FROM employee_note
  WHERE employee_id = :employeeId
  """)
  .bind("employeeId", employeeId)
  .fetchObject(Boolean.class)
  .orElseThrow();

Store UUIDs In RAW(16)

Pyranid binds Java UUID values as RFC-4122 bytes for Oracle, which is a good fit for RAW(16) columns.

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

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

MySQL And MariaDB

Use Generated Keys On MySQL

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

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

Use RETURNING On MariaDB

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

record EmployeeRow(Long employeeId, String name) {}

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

Keep MySQL Streams Self-Contained

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

List<Long> activeEmployeeIds = database.query("""
  SELECT employee_id
  FROM employee
  ORDER BY employee_id
  """)
  .fetchStream(Long.class, stream ->
    stream
      .filter(id -> id > 0)
      .limit(10_000)
      .toList());

Bind JSON As A JSON Parameter

Use Parameters::json(...) for JSON columns. Pyranid binds MySQL-family JSON as text, which avoids the character-set problems that can happen with generic binary-looking binds.

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

SQLite

Use RETURNING For Multi-Row Generated IDs

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

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

Store UUIDs As Text

SQLite has dynamic typing, so a TEXT UUID column is the most straightforward representation. Pyranid binds Java UUID values as strings for SQLite.

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

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

Store Exact Decimals As Text

SQLite numeric affinity can store decimal-looking values as floating point. If exact decimal precision matters, store the canonical string and map it back to BigDecimal.

BigDecimal amount = new BigDecimal("12345678901234567890.123456789012345678");

database.query("""
  INSERT INTO invoice (invoice_id, amount)
  VALUES (:invoiceId, :amount)
  """)
  .bind("invoiceId", 1L)
  .bind("amount", amount.toPlainString())
  .execute();

BigDecimal stored = database.query("""
  SELECT amount
  FROM invoice
  WHERE invoice_id = :invoiceId
  """)
  .bind("invoiceId", 1L)
  .fetchObject(BigDecimal.class)
  .orElseThrow();

Be Careful With In-Memory Databases

SQLite :memory: databases are scoped to a physical JDBC connection. A pool with multiple physical connections can produce multiple independent empty databases. For integration tests, a temporary file database is usually less surprising.

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

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

Database database = Database.withDataSource(dataSource)
  .databaseType(DatabaseType.SQLITE)
  .build();
Previous
ResultSet Mapping
Next
Error Handling