// STRYKE-POSTGRES — POSTGRESQL CLIENT FOR STRYKE // OPT-IN PACKAGE

Install

# from a release (no rustc on the consumer machine)
s pkg install -g github.com/MenkeTechnologies/stryke-postgres

# from a local checkout
cd ~/projects/stryke-postgres
cargo build --release          # produces target/release/libstryke_postgres.{dylib,so}
s pkg install -g .             # cdylib lands in ~/.stryke/store/postgres@<version>/

# one-liner
make install

The cdylib is dlopened in-process on first use Postgres — no helper-binary fork per call. A postgres::Client cache keyed by connection URL is held in OnceCell, so there is no fresh TCP+TLS+auth handshake per call. Honors DATABASE_URL and POSTGRES_URL env vars. Any stryke script that declares use Postgres resolves the package automatically.

Quick start: use Postgres

A full runnable script — set $DATABASE_URL once, then call the surface:

use Postgres

# Set $DATABASE_URL once, omit the named arg everywhere.
$ENV{DATABASE_URL} = "postgres://wizard@127.0.0.1:5432/app"

# Single scalar.
p Postgres::query_scalar "SELECT COUNT(*) FROM users"

# Rows with positional placeholders ($1, $2, …).
val @rows = Postgres::query "SELECT id, name FROM users WHERE created_at > \$1",
                           bind => ["2025-01-01"]
@rows |> ep

# Streaming variant — no full-result buffering.
Postgres::query_stream "SELECT * FROM big_table",
    callback => fn { process _ }

# Write paths return { affected }.
val $r = Postgres::execute "UPDATE users SET name = \$1 WHERE id = \$2",
                          bind => ["alice", 42]
p "updated $r->{affected}"

# Bulk insert (columns inferred from the first row's keys).
Postgres::insert_many "users",
    [{ name => "x", score => 1, meta => { color => "blue" } },
     { name => "y", score => 2, meta => { color => "red"  } }]

# With RETURNING — get the generated rows back instead of a count.
val @rows = Postgres::insert_many "users",
    [{ name => "z", score => 3 }],
    returning => "id"
p $rows[0]->{id}

# Schema introspection.
p to_json Postgres::schema "users"
p Postgres::tables |> ep

Connection sources

Every connecting call accepts the same %opts connection keys; resolution order is:

1. url => "postgres://user:pass@host:port/db" named arg
2. Individual named args: host, port, user, password, database
3. $ENV{DATABASE_URL} (or $ENV{POSTGRES_URL}) when neither url nor host is given

All statements issued with the same %opts run on the same cached backend connection (connection affinity), which is what lets transactions, savepoints, and LISTEN/NOTIFY ride the same socket with no extra FFI handshake.

API reference: read paths

%opts keys for read paths: url, host, port, user, password, database, bind, limit (dump only), callback (query_stream only). bind is an arrayref of positional values ($1, $2, …).

Postgres::query        $sql, %opts → @rows
Postgres::query_stream $sql, %opts → $count    # callback per row, no buffering
Postgres::query_one    $sql, %opts → \%row | undef
Postgres::query_col    $sql, %opts → @values   # first column of each row
Postgres::query_scalar $sql, %opts → $value | undef
Postgres::dump         $table, %opts → @rows   # %opts limit => N

API reference: write paths

Postgres::execute     $sql, %opts → { affected }
Postgres::exec_file   $path, %opts → per-script result
Postgres::insert_many $table, $rows_aref, %opts → $inserted_count | @rows
Postgres::upsert      $table, $row_href, %opts → $affected | @rows
Postgres::update      $table, $set_href, $where?, %opts → $affected
Postgres::delete      $table, $where?, %opts → $affected
Postgres::truncate    $table, %opts → 1   # %opts restart_identity => 1

insert_many infers columns from the first row's keys and returns the inserted-row count, or the generated rows when returning => "col,…" | "*" is set (each returning token is validated, so it's injection-safe). upsert issues INSERT … ON CONFLICT DO UPDATE: conflict => \@cols is required; update => \@cols defaults to every non-conflict column (an empty list makes it DO NOTHING); returning works the same as on insert_many. update binds the SET values and interpolates $where (pass trusted values; for a parameterized condition use execute); delete interpolates $where; both omit $where to affect every row. Table, conflict, and column names are identifier-validated.

Postgres::update "users", { status => "active", seen => 1 }, "id = 42"
Postgres::delete "sessions", "expired_at < now()"
Postgres::upsert "kv", { id => 1, name => "a", hits => 1 }, conflict => ["id"]
Postgres::upsert "kv", { id => 1, name => "x", hits => 9 },
                 conflict => ["id"], update => ["hits"]
val @r = Postgres::upsert "kv", { id => 2, name => "b" },
                         conflict => ["id"], returning => "*"

API reference: bulk transfer (COPY)

Postgres::copy_in   $sql, $data, %opts → $row_count   # COPY … FROM STDIN
Postgres::copy_out  $sql, %opts → $data               # COPY … TO STDOUT

copy_in streams $data (text/CSV/TSV lines, per the COPY statement's FORMAT) straight into the table — Postgres's fastest bulk-load path. copy_out returns the server's COPY payload as a string.

API reference: LISTEN / NOTIFY

Postgres::notify  $channel, %opts → { ok }            # %opts payload => "..."
Postgres::listen  $channels, %opts → @notifications   # name or aref; %opts timeout_ms (default 1000)

notify sends via pg_notify($1,$2) (bound, not interpolated). listen issues LISTEN on each channel (identifier-quoted) and drains pending notifications for timeout_ms, returning { channel, payload, pid } rows.

API reference: transactions

All statements sharing the same %opts run on the same cached connection, so these ride on connection affinity with no extra FFI.

Postgres::begin       %opts → 1
Postgres::commit      %opts → 1
Postgres::rollback    %opts → 1
Postgres::transaction $code, %opts → $code_result   # BEGIN; $code->(); COMMIT — or ROLLBACK + re-raise on die
Postgres::savepoint         $name, %opts → 1
Postgres::release_savepoint $name, %opts → 1
Postgres::rollback_to       $name, %opts → 1

Savepoints are nested rollback points inside an open transaction; the name is identifier-quoted (it can't be a bound parameter). rollback_to undoes work back to the savepoint while leaving the outer transaction open.

Postgres::begin %c
Postgres::execute "INSERT INTO t VALUES (1)", %c
Postgres::savepoint "a", %c
Postgres::execute "INSERT INTO t VALUES (2)", %c
Postgres::rollback_to "a", %c    # row (2) undone, row (1) kept
Postgres::commit %c

Postgres::transaction fn {
    Postgres::execute "INSERT INTO accounts (id, cents) VALUES (1, 100)", %c
    Postgres::execute "UPDATE accounts SET cents = cents - 100 WHERE id = 2", %c
}, %c

API reference: metadata

Postgres::ping         %opts → 1 | 0
Postgres::tables       %opts → @names
Postgres::databases    %opts → @names
Postgres::schema       $table, %opts → column metadata (name/type/nullable)
Postgres::count        $table, $where?, %opts → $row_count
Postgres::exists       $table, $where?, %opts → 1 | 0   # SQL EXISTS — short-circuits
Postgres::table_exists $name, %opts → 1 | 0             # $name must be a plain identifier
Postgres::views        %opts → @names                   # user views as schema.view
Postgres::functions    %opts → @names                   # user functions as schema.fn
Postgres::indexes      %opts → @{ {name, def} }         # opt: table => "t"
Postgres::roles        %opts → @{ {name, superuser, can_login} }
Postgres::explain      $sql, %opts → @plan_lines        # opt: analyze => 1, params
Postgres::db_size      %opts → { bytes, pretty }
Postgres::table_size   $table, %opts → { table, bytes, pretty }
Postgres::activity     %opts → @{ {pid, user, state, wait_event_type, age_seconds, query} }
Postgres::locks        %opts → @{ {pid, locktype, mode, granted, relation} }
Postgres::sequences    %opts → @names
Postgres::extensions   %opts → @{ {name, version} }
Postgres::triggers     %opts → @{ {name, table, event, timing} }
Postgres::cancel_backend $pid, %opts → { pid, ok }      # opt: terminate => 1 (hard kill)

count and exists interpolate the table name and $where; pass binds in %opts{bind} (e.g. exists "t", "id = $1", bind => [42]). exists uses SQL EXISTS, which stops at the first matching row — prefer it over count(…) > 0 when you only need a yes/no.

API reference: schema discovery

Postgres::describe        $sql, %opts → { params, columns }   # prepare-only; type-checks without running
Postgres::current         %opts → { database, user, schema, pid }
Postgres::schemas         %opts → @names                      # non-system namespaces
Postgres::matviews        %opts → @names                      # materialized views as schema.matview
Postgres::server_settings %opts → @{ {name, setting, description} }   # pg_settings (SHOW ALL)
Postgres::server_encoding %opts → { server_encoding, client_encoding, timezone }
Postgres::constraints     $table, %opts → @{ {name, type, definition} }
Postgres::foreign_keys    $table, %opts → @{ {name, column, references_table, references_column} }
Postgres::primary_key     $table, %opts → @columns            # PK columns in key order; empty when none
Postgres::column_defaults $table, %opts → @{ {column, default} }
Postgres::table_stats     $table, %opts → { table, live_tuples, dead_tuples, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del }

describe prepares the statement via the extended protocol and reports the bind-parameter types ($1, $2, … in order) and the result-column shape WITHOUT executing it — the analyze-only counterpart of explain. constraints/foreign_keys/primary_key/column_defaults complement schema. The $table arg is bound via $1::regclass, so a bare or schema-qualified name resolves safely.

API reference: server admin / maintenance

Postgres::uptime          %opts → { started_at, uptime_seconds }
Postgres::replication     %opts → @{ {pid, user, application, client_addr, state, sync_state, write_lag_bytes, replay_lag_bytes} }
Postgres::current_setting $name, %opts → $setting | undef   # one GUC; opt: missing_ok => 1
Postgres::set_config      $name, $value, %opts → $setting   # set one GUC; opt: local => 1 (SET LOCAL)
Postgres::analyze         $table?, %opts → { ok, table }    # ANALYZE [VERBOSE]; whole DB when omitted
Postgres::vacuum          $table?, %opts → { ok, table }    # VACUUM [FULL] [ANALYZE] [VERBOSE]
Postgres::reindex         $name, %opts → { ok, target, name }   # opt target => index|table|schema|database|system (default index)

current_setting reads a single configuration parameter — the per-GUC complement of server_settings, which dumps them all; set_config writes one with the value bound (no interpolation). analyze/vacuum/reindex interpolate the identifier-validated table/index name (these statements take no bound parameters). vacuum cannot run inside a transaction block — do not call it between begin and commit.

API reference: pure helpers (no socket)

Connection-string and quoting utilities that open no connection — safe to call without a reachable server.

Postgres::parse_dsn($dsn)              → { scheme, user, password, host, port, dbname, params }
Postgres::parse_keyword_dsn($dsn)     → { user, password, host, port, dbname, params }   # libpq keyword/value form
Postgres::build_keyword_dsn(%opts)    → $dsn   # parts → libpq keyword/value DSN
Postgres::build_dsn(%opts)            → $dsn   # parts → URI DSN; inverse of parse_dsn
Postgres::quote_ident($name)          → $quoted        # "weird""col"
Postgres::unquote_ident($quoted)      → $name          # inverse of quote_ident
Postgres::quote_qualified_ident($name)  → $quoted      # public.my table → "public"."my table"
Postgres::parse_qualified_ident($name)  → \@parts      # inverse of quote_qualified_ident
Postgres::quote_literal($val)         → $quoted        # 'O''Brien'
Postgres::unquote_literal($lit)       → $val           # inverse of quote_literal (standard mode)
Postgres::quote_nullable($val)        → $quoted        # like quote_literal, but undef → NULL
Postgres::escape_like($val)           → $escaped       # 100% → 100\%, a_b → a\_b, \ → \\
Postgres::unescape_like($pat)         → $literal       # inverse; rejects unescaped wildcard / dangling backslash
Postgres::dollar_quote($val, $tag?)   → $quoted        # $tag$val$tag$ — auto-picks a non-colliding tag
Postgres::unquote_dollar($quoted)     → $val           # inverse of dollar_quote
Postgres::format_array(\@elems)       → $literal       # ["a,b","c"] → {"a,b",c}
Postgres::parse_array($literal)       → \@elems        # inverse of format_array (1-D)
Postgres::format_in_list(\@values)    → $operand       # [1,"a",undef] → (1, 'a', NULL) for `col IN (...)`
Postgres::parse_in_list($list)        → \@values       # inverse of format_in_list
Postgres::parse_range($literal)       → \%{ empty, lower, upper, lower_inclusive, upper_inclusive }
Postgres::split_statements($sql)      → \@statements   # split a SQL script on top-level `;`
Postgres::version()                   → package version string
Postgres::server_version(%opts)       → Postgres version() build string

split_statements respects strings, dollar-quotes, and line/block comments; blank statements are dropped. build_dsn/build_keyword_dsn are the inverses of the parsers (well-known keys first, params sorted), so output re-parses cleanly.

Type encoding (Postgres → JSON)

Bind parameters

PostgreSQL placeholders are positional $1, $2, … Pass binds as an arrayref. The cdylib's json_to_param maps JSON binds to wire values: null → NULL, bool → bool, int → i64, float → f64, string → text, array/object → jsonb. Add explicit casts ($1::int, $2::text) when type inference is ambiguous — Postgres is stricter than MySQL here.

Postgres::query  "SELECT * FROM t WHERE id = \$1::int",  bind => [42]
Postgres::query  'SELECT $1::text || $2::text AS r',     bind => ["a", "b"]
Postgres::execute 'INSERT INTO t (data) VALUES ($1)',    bind => [{x => 1}]   # jsonb

Examples

Runnable .stk scripts ship in examples/; each skips cleanly when no connection URL is set.

Tests & dev workflow

cargo test                          # unit + contract tests, no live calls
DATABASE_URL='postgres://…' s test t/   # end-to-end against live Postgres

make             # release build
make test        # cargo test + s test t/
make install     # release + pkg install -g .
make clean

The end-to-end suite skips cleanly when none of $DATABASE_URL, $POSTGRES_URL, $POSTGRES_DSN points at a reachable server.

Why a package, not a builtin

A real database client pulls in 100+ transitive crates (TLS, async runtime, type encoders). Most stryke one-liners never touch Postgres; for the ones that do, opt in with this package.

The stryke side is a thin .stk wrapper that calls pg__* FFI symbols on the cdylib; the heavy code lives in libstryke_postgres.{dylib,so}, dlopened in-process on first use Postgres. Core stryke is never linked against this package's deps.

FFI layer

Each Postgres::* wrapper builds a JSON args dict and calls a sibling pg__* symbol resolved out of libstryke_postgres.{dylib,so}. The cdylib is dlopened in-process on first use Postgres; a postgres::Client cache keyed by connection URL is held in OnceCell — no fork-per-call, no fresh TCP+TLS+auth handshake. Responses are JSON; errors come back as {"error": "<msg>"} and the wrapper dies with the message.

Layout

stryke-postgres/
├── Cargo.toml             # cdylib crate manifest (crate-type = ["cdylib"], publish = false)
├── src/
│   └── lib.rs            # cdylib — pg__* extern "C" exports + client cache
├── lib/                   # stryke .stk wrapper — `use Postgres`
├── stryke.toml            # stryke package manifest ([ffi] exports table)
├── t/                     # zunit-style tests
├── examples/              # runnable .stk examples
├── Makefile               # `make install` builds + installs
└── docs/                  # this site (GitHub Pages)

Sibling packages

Part of the stryke package family. Browse the others via the MenkeTechnologiesMeta umbrella repo:

• stryke-arrow — Apache Arrow / Parquet / Feather
• stryke-aws — S3, DynamoDB, SQS, Lambda, STS
• stryke-azure — Blob, Queues, Cosmos DB, Key Vault, Entra
• stryke-clickhouse — ClickHouse
• stryke-demo — live demos for every connector
• stryke-docker — Docker daemon API
• stryke-duckdb — embedded DuckDB SQL engine
• stryke-email — email + campaign client
• stryke-fleet — parallel expect/PTY automation
• stryke-gcp — Cloud Storage, Pub/Sub, Secret Manager, BigQuery, Firestore
• stryke-grpc — reflection-based gRPC client
• stryke-gui — mouse / keyboard / screen / clipboard automation
• stryke-k8s — Kubernetes
• stryke-kafka — Apache Kafka
• stryke-mcpd — MCP servers without a runtime
• stryke-mongo — MongoDB
• stryke-mssql — Microsoft SQL Server
• stryke-mysql — MySQL / MariaDB
• stryke-neo4j — Neo4j graph (Bolt)
• stryke-office — Office docs / PDF / images
• stryke-parquet — Parquet file inspector
• stryke-polars — Polars + ndarray + linalg + FFT
• stryke-redis — Redis / Valkey
• stryke-scrape — web scraping / crawling
• stryke-scylla — ScyllaDB / Cassandra
• stryke-search — Elasticsearch / OpenSearch
• stryke-selenium — browser automation (WebDriver)
• stryke-spark — Spark Connect (no JVM)
• stryke-utils — pure-stryke utility belt
• stryke-zmq — ZeroMQ messaging