# PostgreSQL
Deft ships a native PostgreSQL client — no `libpq`, no driver to
install. The `pg/*` stdlib functions speak the PostgreSQL wire
protocol (v3) directly and work identically in the scripting binary,
the ANSI binary, and the full TUI. Auth is SCRAM-SHA-256 by default,
with MD5 and cleartext fallbacks; TLS upgrades in-band via the same
mbedTLS stack the HTTP client uses.
Like `keyval/*`, every query returns a **Future** — `[await ...]`
suspends the coroutine until the reply arrives, so a whole pipeline
can run on one connection without blocking the event loop.
NOTE: The postgres Zig code was lifted from Bun 3.14.
## Connecting
Three connection forms, all equivalent:
# URL (bun/libpq style) def db [pg/connect "postgres://user:pass@localhost:5432/mydb?sslmode=require"] # libpq kv-string def db [pg/connect "host=localhost port=5432 user=deft password=secret dbname=mydb sslmode=disable"] # Positional + opts map def db [pg/connect "localhost" 5432 %{ :user "deft" :password "secret" :database "mydb" :sslmode "disable" }]
The connection is dialed **lazily** — the first command triggers the
TCP connect, so `pg/connect` itself never blocks. `pg/info` before
any command reports `:state :disconnected`.
### Opts map
| Opt | Meaning |
|---|---|
| `:host` | Server hostname or IP |
| `:port` | Server port (default 5432) |
| `:user` / `:username` | Role name |
| `:password` | Password |
| `:database` / `:db` | Database name (defaults to the user) |
| `:sslmode` | `"disable"` `"prefer"` `"require"` `"verify-ca"` `"verify-full"` |
| `:ssl` | Bool shorthand — `true` = `"require"`, `false` = `"disable"` |
| `:application-name` | Reported to the server in `pg_stat_activity` |
| `:timeout` | Command deadline in ms (default 60000; `0` disables) |
Env fallbacks apply for anything unset: `PGHOST`, `PGPORT`, `PGUSER`,
`PGPASSWORD`, `PGDATABASE`, `PGSSLMODE`, `PGAPPNAME`.
### SSL modes
| Mode | Behaviour |
|---|---|
| `disable` | Plaintext only |
| `prefer` | Try TLS; fall back to plaintext if the server refuses or the handshake fails |
| `require` | TLS required, certificate not verified (encrypt-only, libpq semantics) |
| `verify-ca` | TLS + chain verification |
| `verify-full` | TLS + chain + hostname verification |
## Exec — DDL and DML
`pg/exec` runs SQL that doesn't return rows. **Without parameters it
uses the simple query protocol, so multi-statement strings work** —
handy for schema migrations. With parameters it switches to the
extended protocol (`$1..$n` placeholders).
set r [await [pg/exec $db ` CREATE TABLE IF NOT EXISTS items ( id serial PRIMARY KEY, name text, price numeric ) `]] # %{:tag "CREATE TABLE" :command "CREATE" :rows 0} set r [await [pg/exec $db "INSERT INTO items (name, price) VALUES ($1, $2)" "widget" 9.5]] # %{:tag "INSERT 0 1" :command "INSERT" :rows 1}
The result map carries the command tag verbatim (`:tag`), the
command word (`:command`) and the affected row count (`:rows`).
## Query — SELECT
def rows [await [pg/query $db "SELECT id, name, price FROM items ORDER BY id"]] for row $rows { echo "$row~name (price $row~price)" } def one [await [pg/query-one $db "SELECT * FROM items WHERE name = $1" "widget"]] # first row as a map, or nil def n [await [pg/scalar $db "SELECT count(*) FROM items"]] # first column of the first row, or nil
Rows are maps keyed by **keyword** column name (`$row~name`). Column
order is available via `pg/columns` (row maps are hash-ordered).
### Lazy sequences
`pg/query-seq` returns a cursor-backed lazy sequence — row bytes are
decoded on iteration, so `for`, `map`, `filter` and pipelines run
without materializing the whole result first:
set seq [await [pg/query-seq $db "SELECT id, name FROM items ORDER BY id"]] set names $seq |> map {|row| $row~name} |> collect for row in $seq { echo $row~name }
A seq may only be iterated once before it is realized; re-iteration
afterwards replays the realized cache.
## Parameters
Parameters are variadic or one vector, matching `$1..$n` in order:
[await [pg/exec $db "INSERT INTO items VALUES ($1, $2, $3)" "a" 1 @{ "x" "y" }]]
| Deft value | Sent as |
|---|---|
| `nil` | SQL NULL |
| `true` / `false` | `t` / `f` |
| number | decimal text (integers without a `.0`) |
| string | text |
| keyword | its name |
| vector | array literal `{...}` |
| anything else | error at call time |
## Type mapping
Rows arrive as text; decoding is driven by the column's type OID:
| PostgreSQL type | Deft value |
|---|---|
| `int2` `int4` `int8` `oid` `xid` `cid` | number (f64 — values beyond ±2^53 lose precision, same tradeoff as `sqlite`) |
| `float4` `float8` `numeric` | number |
| `bool` | bool |
| `text` `varchar` `char` `name` `json` `xml` `uuid` `inet` `cidr` `macaddr` `money` `bit` `varbit` | string |
| `date` `time` `timestamp` `timestamptz` `timetz` `interval` | string (server's ISO text form) |
| `bytea` | raw byte string (Deft's binary convention — NUL-safe, same as `crypto/*`) |
| arrays (`int[]`, `text[]`, …) | vector (nested arrays too; `NULL` elements → nil) |
| NULL | nil |
| unknown OIDs | string as-is |
`bytea` parameters are sent as `\x` hex text: a raw byte string is
passed through verbatim, so `"\x414243"` becomes bytes `ABC`.
## Transactions
[await [pg/begin $db]] [await [pg/exec $db "INSERT INTO items (name, price) VALUES ($1, $2)" "kept" 1]] [await [pg/commit $db]] # or [await [pg/begin $db]] [await [pg/rollback $db]]
An error inside a transaction aborts it server-side: subsequent
statements fail until you issue `pg/rollback` (or `pg/commit`).
## LISTEN / NOTIFY
Register a callback and `LISTEN` via `pg/exec`; notifications arrive
as push events on the same connection:
[pg/on-notify $db {|ev| echo "channel $ev~channel: $ev~payload" # %{ :pid :channel :payload } }] [await [pg/exec $db "LISTEN items_changed"]] [await [pg/exec $db "NOTIFY items_changed, 'row 42 updated'"]]
Server warnings (`NOTICE`, `WARNING`, …) route to `pg/on-notice`
with `%{ :message :severity :code }` when set.
## Introspection
[await [pg/tables $db]] # @{ "items" "users" ... } — user tables only [await [pg/columns $db "items"]] # @{ %{ :name "id" :type "integer" :not_null true :default nil } ... }
## Errors
Query failures reject the future with a `%Err` carrying the SQLSTATE
code and the server's message fields:
set r [await [pg/query $db "SELECT * FROM missing_table"]] [err? $r] # true $r~message # relation "missing_table" does not exist $r~code # 42P01 (SQLSTATE) $r~severity # ERROR $r~detail # optional extra detail $r~hint # optional hint $r~position # optional character offset in the query
The connection stays usable after an error (the failed statement is
the only casualty — transactions excepted, see above).
## Connection state and shutdown
[pg/info $db] # %{ :state :host :port :user :database :sslmode :ssl # :txn :pending :pid :server-version } [pg/close $db] # sends Terminate, rejects pending requests → :ok
`pg/close` is idempotent; using a closed client is an error.
## Not supported (yet)
- `COPY` in/out — the client refuses with a clear error
- Unix-socket connections (TCP only)
- Binary-format parameters/results (text format always)
- Statement caching / connection pooling