jdbc.core for jolt

A SQLite and PostgreSQL database library for jolt (Clojure on Chez Scheme). It binds the system libsqlite3 and libpq directly through jolt.ffi — jolt's foreign-function interface — and runs the real clojure.jdbc on top of them, plus a small next.jdbc surface. No jolt built-in, no JVM: the native binding lives here, and the API is the published library rather than a copy of it.

jdbc.core is clojure.jdbc itself. This library supplies the java.sql surface it drives (db.jdbc-shim) over the native drivers, so its own documentation and semantics apply as written.

(require '[db.jdbc])                                       ; registers the shim, once
(require '[jdbc.core :as jdbc])
(with-open [conn (jdbc/connection "sqlite::memory:")]      ; or "postgres://user:pw@host/db"
  (jdbc/execute! conn "create table p (id integer primary key, name text)")
  (jdbc/insert! conn :p {:name "ada"})                     ; -> (1), one result per row
  (jdbc/fetch conn ["select * from p where name = ?" "ada"]))

Require db.jdbc once before jdbc.core, and before anything else that pulls it in. It has to be loaded first because clojure.jdbc's namespaces resolve the java.sql constants as they compile, and it is what points connection construction at the native drivers instead of DriverManager.

fetch/fetch-one, execute!, insert!/insert-multi!/update!/delete!, prepared-statement, and atomic (transactions with nested savepoints) are supported on both backends. Queries are strings or sqlvecs ([sql & params], JDBC ? placeholders — rewritten to $N for postgres).

Generated keys come back through RETURNING, since neither driver has a JDBC generated-keys channel. {:returning true} (or :all) asks for the whole row, which is what postgres' own driver gives; a sequence of column names asks for those. Without it there are no generated keys to report, so insert! falls back to the update count, exactly as clojure.jdbc does on a driver that has none.

Binary values

A byte array parameter binds as a SQLite blob / postgres bytea, and those columns read back as byte arrays. The bytes round-trip exactly, so embedded NULs, non-UTF-8 bytes, and empty payloads all survive.

(jdbc/execute! conn "create table doc (id integer primary key, body blob)")
(jdbc/insert! conn :doc {:body (byte-array [0 255 65])})
(:body (jdbc/fetch-one conn "select body from doc"))   ; -> byte array

On postgres a byte array is sent in binary with its type given as bytea, so it does not depend on the statement offering a bytea column for the server to infer one from. ["select ? as c" (byte-array [1 2])] binds a bytea and reads back as bytes rather than inferring text.

next.jdbc surface

The next.jdbc namespace carries the upstream calling conventions over the same drivers: get-datasource / get-connection, execute! / execute-one! (rows for a result set, {:next.jdbc/update-count n} otherwise), plan (a reducible over the rows), execute-batch! (one SQL across a seq of parameter groups, answering per-group update counts), and with-transaction with its options map (:isolation, :read-only, :rollback-only). Every operation takes a connection, a datasource, or a db-spec; a datasource or spec opens a connection owned by that call. Rows are unqualified lower-cased keyword maps — the drivers cannot see table names, so upstream's qualified default is not reproducible, and next.jdbc.result-set builder markers are accepted and ignored.

Transaction settings are fail-closed capabilities, not bookkeeping flags. SQLite applies :serializable / :read-uncommitted through PRAGMA read_uncommitted and enforces :read-only with PRAGMA query_only. PostgreSQL maps its accepted JDBC isolation levels and read-only/read-write modes to transaction/session SET statements. Settings are applied before the first body statement and restored after commit or rollback. A driver descriptor without :transaction-settings (including an older external DuckDB adapter) may still run ordinary transactions, but explicit :isolation or :read-only options are rejected before BEGIN, user code, or driver SQL. Nested savepoint transactions reject explicit settings because they cannot truthfully change an already-active outer transaction.

A datasource is db.datasource: an explicit open-datasource / acquire / release / close-datasource lifecycle over the drivers. It is a connection factory, not a pool — pooling can grow behind the same surface later, and this library deliberately does not emulate HikariCP.

PostgreSQL value model

Typed columns normalize to conventional values on the way out: uuid columns read as uuids, numeric as bigdec (it used to go through parse-double, losing precision), date/time/timestamp/timestamptz as java.time values (LocalDate/LocalTime/LocalDateTime/OffsetDateTime — a deliberate divergence from JDBC's java.sql.Timestamp, which jolt does not model), and arrays of the common element types as vectors (quoting, NULL elements and nesting honoured). A value the parser cannot read — postgres infinity, say — keeps its text form rather than throwing on a read path.

Parameters: uuids and temporal values bind as their text form and postgres infers the type; a vector binds as an array literal, which pairs with a cast at the use site (?::text[]). SQLite stores anything it does not know as text.

PostgreSQL map specs retain their complete remote connection configuration:

{:dbtype "postgresql"
 :host "db.internal" :port 5432
 :user "app" :password "..." :dbname "application"
 :sslmode "verify-full" :connect-timeout 5
 :pg/options {:channel-binding "require"}}

:pg/options (or a map-valued :options) carries arbitrary libpq URI query parameters. Common :sslmode, :connect-timeout, :application-name, :target-session-attrs, and :keepalives keys are accepted at top level. Credentials are URI-encoded for libpq and are never attached to connection errors or logged by the driver.

Errors

Database errors satisfy (catch java.sql.SQLException ...), so code written against the JDBC contract works unchanged. Migratus depends on this: its table-exists? probe catches SQLException to decide whether it still needs to create schema_migrations. Errors raised by the drivers themselves also carry :jdbc/sql-error true in their ex-data.

Layout

• db.sqlite / db.pg — the native bindings (jolt.ffi).
• db.driver.sqlite — the independently registered SQLite SPI adapter.
• db.driver — the explicit driver SPI and deterministic registry.
• db.builtin — bundled-driver loader and PostgreSQL SPI adapter.
• db.jdbc-shim — the java.sql surface clojure.jdbc drives, over those drivers.
• db.jdbc — the entry point: loads the shim, then clojure.jdbc on top of it.
• jdbc.core — clojure.jdbc itself, pulled in as a dependency.
• next.jdbc (+ .sql/.prepare/.result-set/.transaction) — the next.jdbc surface migratus and similar tools use.

External drivers

An external driver implements db.driver/Driver and calls db.driver/register! when its namespace is explicitly required. The SPI is deliberately narrow: open a handle, close it once, and execute one statement once, returning ordered labels/rows plus an affected-row count. Transactions and generated keys are declared as capabilities and enforced by the shared JDBC shim. Drivers that support transaction options additionally publish :transaction-settings: defaults plus JDBC isolation/read-only values mapped to transaction and session SQL (or adapter hooks). Absence means explicit settings are unsupported.

Registry aliases are exact for map specs and URI strings use the longest registered prefix. Conflicting aliases or prefixes are rejected. Requiring a driver never downloads native code; installers and library loading belong to the driver package.

Requirements

jolt v0.7.23 or newer on PATH; the system libsqlite3 (preinstalled on macOS and most Linux distros). PostgreSQL support additionally needs libpq at runtime.

The version floor is not cosmetic. In addition to the earlier host-shim fixes, the native ownership paths use Jolt's lexically scoped FFI allocation helpers and collect-safe blocking calls introduced by v0.7.23.

Test

jolt -M:test                                   # sqlite
JOLT_TEST_PG_URI=postgres://... jolt -M:test   # also runs the postgres suite

The default suite exercises PostgreSQL URI construction, handle ownership, serialization, failure cleanup, and lifecycle swarms with injected libpq calls; it does not pretend that those fakes prove a native client/server exchange. Set JOLT_TEST_PG_URI to a disposable real PostgreSQL database to enable the existing end-to-end libpq value, parameter, transaction, and bytea checks. The integration suite creates and drops tables prefixed jolt_ / nj_.