pg.nu

packages/psql/src/pg.nu — a PostgreSQL client in pure NURL. No libpq: the wire protocol (pgwire.nu), the authentication (trust / cleartext / MD5 / SCRAM-SHA-256) and the optional TLS upgrade (PostgreSQL's SSLRequest, then the pure-NURL TLS client) are all implemented here, so a secure connection works on a host with nothing installed.

( pg_connect host port user password database sslmode ) → !PgConn PgErr ( pg_query conn sql ) → !PgResult PgErr simple protocol: any number of statements, text cells ( pg_query_params conn sql params ) → !PgResult PgErr extended protocol: $1 … $n bound to typed PgVal parameters, binary cells where exact, the statement prepared once per connection (the statement cache) ( pg_pool_new host port user password database sslmode cfg ) → ( Pool PgConn PgErr ) ( pg_pool_get pool ) → !( Pooled PgConn PgErr ) PgErr ( pg_close conn ) → v early release (optional)

A PgConn is a handle: every copy is the same connection, and the last owner closes it (Terminate, then the TLS session or the socket). A request checks the connection's state out of its Locked for the round trip, so copies on several threads or fibers take turns. PgResult is a plain value (pgwire.nu: its typed accessors pg_row_i / pg_row_f / pg_row_b / pg_row_s / pg_row_bytes / pg_row_ts / pg_row_date). Nothing here is released by hand.

sslmode: 0 disable · 1 prefer (TLS if the server offers it, no certificate check; should it decline, a password is sent only through SCRAM — a server asking for it in the clear or as MD5 is refused with PgInsecureAuth, since anyone in the path can ask) · 2 require (TLS mandatory, no certificate check) · 3 verify-full (TLS, the chain checked against the system trust store and the host name against the certificate — what a connection over a network wants).

API

: | PgErr

: | PgErr {
    PgConnFail  // could not open the TCP socket, or the connection broke
    PgTls  // TLS upgrade failed (refused, or handshake/cert error)
    PgProtocol  // malformed/unexpected backend message
    PgAuth  // authentication failed or unsupported method
    PgServerError  // backend ErrorResponse — see ( pg_conn_lasterr conn )
    PgQuery  // query-time failure — see ( pg_conn_lasterr conn )
    PgNeedPassword  // server asked for a password but none was supplied
    PgInsecureAuth  // sslmode=prefer fell back to plaintext and the server asked for the password in the clear or as MD5
    PgPoolTimeout  // pg_pool_get waited its wait_ms and no connection came back
    PgPoolClosed  // the pool was closed
}

@ pg_err_name PgErr e → s

: PgStmt

: PgStmt {
    String sql
    i hash  // of sql, compared before the text
    String name
    ( Vec i ) param_oids
    ( Vec PgCol ) cols  // fmt = the format asked for (pgw_result_fmt)
    ( Vec i ) rfmts  // Bind's result-format codes for cols
    i used  // the connection's clock when last executed (LRU)
}

A statement the connection prepared: what Describe said of it, and the formats its results are asked in.

: i PG_STMT_CACHE 64

How many prepared statements a connection keeps by default.

: PgConnImpl

: PgConnImpl {
    TcpConn conn  // the transport: plaintext, or TLS once tcp_starttls ran
    i tls  // 0 plaintext, 1 TLS
    ( Vec u ) rxbuf  // backend bytes; messages from rpos on are not read yet
    i rpos
    ( Vec u ) txbuf  // the frontend messages of the request being sent
    String lasterr  // last ErrorResponse (`[SQLSTATE] message`) / failure text
    PgNotice err  // …in full
    i be_pid
    i be_key
    String srv_ver  // server_version from ParameterStatus (for the banner)
    String db_name  // connection identity, for the banner and \conninfo
    String user_name
    String host_name
    i started  // 1 once the StartupMessage went out: say Terminate on close
    b broken  // an I/O or protocol failure: the stream is lost
    i txn  // the last ReadyForQuery status: 73 idle, 84 in a transaction, 69 failed one
    ( Vec PgStmt ) stmts  // the statement cache
    i stmt_cap
    i stmt_seq  // names the next statement
    i clock  // ticks per execution, for the LRU
}

% Drop PgConnImpl

% Drop PgConnImpl {
    @ drop PgConnImpl c → v {
        ? & == . c started 1 ! . c broken {
            : ( Vec u ) term ( vec_with_cap [u] 5 )
            ( pgw_terminate term )
            ?? ( tcp_write_all . c conn term ) { T _ → {} F _ → {} }
        } {}
    }
}

Its last owner says goodbye — a best-effort Terminate, once the server was spoken to — and the TcpConn then closes the TLS session or the socket with the rest of the fields.

: PgConn { ( Locked PgConnImpl ) st }

A PgConn is a handle on its state in a Locked (stdlib/std/thread.nu): every copy is the same connection, the last owner closes it, and a request checks the state out (locked_take) for its round trip — copies on several threads take turns.

@ PgConn_share PgConn h → PgConn

@ PgConn_drop sink PgConn h → v

@ pg_conn_lasterr PgConn h → String

The connection's text fields, copied out. lasterr is the last ErrorResponse ([SQLSTATE] message), empty if none.

@ pg_conn_sqlstate PgConn h → String

The last error's SQLSTATE (23505 a unique violation, …), empty if none.

@ pg_conn_server_version PgConn h → String

@ pg_conn_db_name PgConn h → String

@ pg_conn_user_name PgConn h → String

@ pg_conn_host_name PgConn h → String

@ pg_conn_tls PgConn h → i

1 when the connection runs over TLS, 0 when it is plaintext.

@ pg_conn_ok PgConn h → b

F once an I/O or protocol failure lost the stream: every request then fails with PgConnFail, and a pool drops the connection.

@ pg_conn_txn_status PgConn h → i

The transaction status the server last reported: 73 'I' idle, 84 'T' inside a transaction block, 69 'E' inside a failed one.

@ pg_conn_set_stmt_cache PgConn h i n → v

How many prepared statements pg_query_params keeps (default PG_STMT_CACHE); 0 prepares each execution unnamed, keeping none. A smaller bound closes the least recently used ones on the next prepare.

@ pg_conn_stmt_count PgConn h → i

The statements in the cache now.

: PgMsg

: PgMsg {
    i mtype
    ( Vec u ) payload
}

The next whole message during startup, its payload copied out.

@ pg_connect s host i port s user s password s database i sslmode → !PgConn PgErr

@ pg_query PgConn h s sql → !PgResult PgErr

── simple query ────────────────────────────────────────────────── Any number of statements (a script); text cells. The result is the last statement's.

@ pg_query_params PgConn h s sql ( Vec PgVal ) params → !PgResult PgErr

── extended query: parameters, binary cells, the statement cache ── sql with $1 … $n placeholders, params their values in order. The statement is prepared once per connection — Parse + Describe, the server inferring every parameter's type — and kept under its SQL text (the least recently used closed past the cache's bound); each run is one round trip: Bind (each value in the format its type takes best, pgw_param_fmt), Execute, Sync. Cells of the fixed-width types come binary, the rest as text.

@ pg_pool_new s host i port s user s password s database i sslmode PoolCfg cfg → ( Pool PgConn PgErr )

── connection pool ─────────────────────────────────────────────── Connections made on demand with these settings and lent one caller at a time (stdlib/std/pool.nu). A connection coming back that lost its stream is dropped; one left inside a transaction is rolled back first — and dropped if that fails — so the next borrower starts clean.

@ pg_pool_get ( Pool PgConn PgErr ) pool → !( Pooled PgConn PgErr ) PgErr

@ pg_pooled PgConn h → PgConn

The connection a pool checkout holds, lent while the guard lives.

@ pg_conn_reset PgConn h → b

Ready the connection for its next user: T when it is usable and idle — rolled back first when it was left inside a transaction block.

@ pg_result_cell PgResult r i row i col → String

── result compatibility ────────────────────────────────────────── The cell as text, empty for NULL (pg_row_s, with NULL folded away).

@ pg_result_is_null PgResult r i row i col → b

@ pg_result_free sink PgResult r → v

A plain value: let go of r now rather than at the end of its owner's scope.

@ pg_close sink PgConn c → v

── teardown ────────────────────────────────────────────────────── Let go of c now rather than at the end of its owner's scope (the last owner closes the connection).

---

scram.nu

packages/psql/src/scram.nu — SCRAM-SHA-256 client (RFC 5802 / RFC 7677), the SASL mechanism PostgreSQL uses for password authentication.

Pure NURL on top of the stdlib crypto (SHA-256 / HMAC / PBKDF2 / base64). Channel binding is not used ("n,," / "c=biws"), matching libpq's default SCRAM-SHA-256 (not the -PLUS variant).

( scram_client_first_bare nonce ) → String "n=,r=<nonce>" ( scram_compute password cfb server_first ) → ScramResult

password is the raw password bytes (SASLprep is the identity map for the ASCII passwords overwhelmingly used in practice). cfb is the client-first-bare returned above; server_first is the server's SASLContinue payload. The result carries the client-final message to send and the base64 server signature to check against the SASLFinal.

API

: ScramResult

: ScramResult {
    b ok
    String client_final
    String server_sig
}

@ scram_client_first_bare s nonce → String

client-first-bare: PostgreSQL ignores the SCRAM username (it uses the startup username), so we send an empty n=.

@ scram_compute ( Vec u ) password String cfb String server_first → ScramResult

---

main.nu

packages/psql/src/main.nu — the psql command: a pure-NURL PostgreSQL client. Connects over the v3 wire protocol with optional TLS (pure-NURL, no libpq / no OpenSSL needed on the target), authenticates with trust / cleartext / MD5 / SCRAM-SHA-256, and behaves like psql for everyday use: aligned result tables, backslash meta-commands, a multi-line REPL terminated by ';', and one-shot -c.

psql [flags] ["postgres://user:pass@host:port/db?sslmode=..."] -h HOST host (default $PGHOST or localhost) -p PORT port (default $PGPORT or 5432) -U USER user (default $PGUSER or postgres) -d DB database (default $PGDATABASE or the user) -c SQL run one command and exit; otherwise read from stdin --sslmode M disable | prefer | require | verify-full (default $PGSSLMODE, else prefer — libpq's default) Password is taken from $PGPASSWORD.

Meta-commands (when no statement is pending): \dt list tables \l list databases \d TABLE describe a table \du list roles \dn list schemas \conninfo connection info \? this help \q quit

API

: ConnInfo

: ConnInfo {
    String host
    i port
    String user
    String password
    String database
    i sslmode
    b has_url
}

postgres://[user[:pass]@]host[:port][/db][?sslmode=M]

@ main → i

---

pgwire.nu

packages/psql/src/pgwire.nu — the PostgreSQL v3 protocol as messages.

The codec, with no socket and no lock in it: frontend messages are appended to a byte buffer, backend messages are framed and decoded out of one, and a collector folds the messages answering one request (up to its ReadyForQuery) into a result. pg.nu runs it over a connection; a writer that coalesces several requests into one write and a reader that hands each Sync-delimited answer to the request that sent it can run the same functions.

frontend pgw_query pgw_parse pgw_describe pgw_bind pgw_execute pgw_close pgw_sync pgw_terminate → append to a ( Vec u ) framing ( pgw_frame buf off ) → i bytes of the complete message at off, 0 = need more, -1 = bad answer ( pgw_collect_new ) ( pgw_collect_feed c type buf off end ) → PgCollect: the result, the server's error, the transaction status, done at ReadyForQuery values PgVal (a typed parameter) → binary or text per the type the server inferred; a result cell ← text or binary per column

Results are a flat table: every DataRow's payload is appended to one byte buffer as it arrived, and each cell is an (offset, length) span into it (length -1 = NULL) — one copy per row, nothing per cell. The typed accessors (pg_row_i, pg_row_f, pg_row_b, pg_row_s, …) decode a span in the column's format; a NULL, or a value the accessor's type does not hold, is F.

API

: i PG_BOOL 16

── type OIDs (pg_type.oid) ───────────────────────────────────────

: i PG_BYTEA 17

: i PG_INT8 20

: i PG_INT2 21

: i PG_INT4 23

: i PG_TEXT 25

: i PG_OID 26

: i PG_FLOAT4 700

: i PG_FLOAT8 701

: i PG_VARCHAR 1043

: i PG_DATE 1082

: i PG_TIME 1083

: i PG_TIMESTAMP 1114

: i PG_TIMESTAMPTZ 1184

: i PG_NUMERIC 1700

: i PG_UUID 2950

: i PG_EPOCH_US 946684800000000

Microseconds and days between the Unix epoch and PostgreSQL's (2000-01-01), the origin of its binary date and timestamp.

: i PG_EPOCH_DAYS 10957

: | PgVal

: | PgVal {
    PgNull
    PgInt i
    PgFloat f
    PgBool b
    PgText String
    PgBytes ( Vec u )  // bytea; or 16 bytes for a uuid
    PgTimestamp i  // microseconds since 1970-01-01 00:00:00 UTC
    PgDate i  // days since 1970-01-01
}

A typed parameter. pg_query_params sends each in the format the type the server inferred for its $n takes best: binary where the representation is exact (integers, floats, bool, bytea, uuid, timestamps, date), the text form otherwise — which the server then parses as that type, so PgText serves any type and PgInt a numeric. Never interpolated into the SQL.

: PgCol

: PgCol {
    String name
    i oid
    i fmt
}

A result column: its name, its type, and the format its cells came in (0 text, 1 binary).

: PgResult

: PgResult {
    i ncols
    i nrows
    ( Vec PgCol ) cols
    ( Vec u ) data
    ( Vec i ) spans
    String tag  // CommandComplete (e.g. "SELECT 3", "INSERT 0 1")
}

Row-major result table. Cell (r, c) is the span at index 2(rncols+c) in spans: an offset into data and a length, -1 for NULL.

: PgNotice

: PgNotice {
    String severity  // ERROR, FATAL, …
    String code  // SQLSTATE
    String message
    String detail
    String hint
}

An ErrorResponse / NoticeResponse, the fields a caller acts on.

@ pg_result_empty → PgResult

@ pgw_notice_empty → PgNotice

@ pgw_put16 ( Vec u ) v i n → v

── byte helpers ──────────────────────────────────────────────────

@ pgw_put32 ( Vec u ) v i n → v

@ pgw_put64 ( Vec u ) v i n → v

@ pgw_put_slice ( Vec u ) v ( Slice u ) b → v

@ pgw_put_raw ( Vec u ) v s raw → v

@ pgw_put_cstr ( Vec u ) v s raw → v

@ pgw_u16 ( Slice u ) b i off → i

Big-endian reads off a view; 0 past its end, like slice_byte.

@ pgw_u32 ( Slice u ) b i off → i

@ pgw_i16 ( Slice u ) b i off → i

@ pgw_i32 ( Slice u ) b i off → i

@ pgw_i64 ( Slice u ) b i off → i

@ pgw_begin ( Vec u ) out i mtype → i

── frontend messages ───────────────────────────────────────────── A message is its type byte, a length word counting itself and the body, and the body: begin writes the first two (the length a placeholder), end patches the length once the body is in.

@ pgw_end ( Vec u ) out i at → v

@ pgw_query ( Vec u ) out s sql → v

Query: the simple protocol — any number of statements, text results.

@ pgw_parse ( Vec u ) out s name s sql → v

Parse a named statement ("" = the unnamed one), letting the server infer every parameter's type (no OIDs given).

@ pgw_describe ( Vec u ) out i kind s name → v

Describe (kind 83 'S' a statement: ParameterDescription + RowDescription or NoData; kind 80 'P' a portal: RowDescription or NoData).

@ pgw_close ( Vec u ) out i kind s name → v

Close a statement (83 'S') or a portal (80 'P').

@ pgw_execute ( Vec u ) out s portal i max_rows → v

@ pgw_sync ( Vec u ) out → v

@ pgw_terminate ( Vec u ) out → v

@ pgw_bind ( Vec u ) out s portal s stmt ( Vec PgVal ) params ( Vec i ) oids ( Vec i ) rfmts → v

Bind params to statement stmt as portal portal. oids are the parameter types the server inferred (ParameterDescription); each value goes in the format pgw_param_fmt picks for it. rfmts are the result columns' formats: none = all text, one = all that format, else one per column.

@ pgw_param_fmt PgVal v i oid → i

── parameter encoding ──────────────────────────────────────────── 1 (binary) where the value's own representation is exactly what the type's binary form holds, 0 (its text form, which the server parses as the inferred type) everywhere else.

@ pgw_put_param ( Vec u ) out PgVal v i oid → v

The value's length word and bytes, in the format pgw_param_fmt picked.

: i PGW_MAX_MSG 1090519040

── framing ─────────────────────────────────────────────────────── The largest backend message accepted: a 1 GiB field (the most a PostgreSQL value holds) plus room for its row.

@ pgw_frame ( Vec u ) buf i off → i

How many bytes the backend message starting at off takes, once all of it is in buf: 0 while it is not, -1 when its length word is not a length (fewer than 4, or past PGW_MAX_MSG — the stream is lost).

@ pgw_notice ( Vec u ) buf i off i end → PgNotice

── backend message bodies ──────────────────────────────────────── ErrorResponse / NoticeResponse: (code byte, C string)* then a 0.

@ pgw_notice_text PgNotice n → String

"[SQLSTATE] message" — the one-line form lasterr keeps.

@ pgw_row_desc ( Vec u ) buf i off i end → ( Vec PgCol )

RowDescription: the columns, with the format each will arrive in.

@ pgw_param_desc ( Vec u ) buf i off i end → ( Vec i )

ParameterDescription: the type the server inferred for each $n.

: PgCollect

: PgCollect {
    PgResult res
    ( Vec i ) param_oids  // ParameterDescription (a Describe of a statement)
    b described  // a RowDescription or NoData answered a Describe
    i rc
    PgNotice err
    i txn  // ReadyForQuery's status: 73 'I' idle, 84 'T' in a transaction, 69 'E' failed one
    b done
}

── the answer to one request ───────────────────────────────────── What the backend says between a request and its ReadyForQuery, folded as it arrives. rc: 0 ok, 3 the server reported an error (err), 1 the stream broke (a message out of place, a row of the wrong width).

@ pgw_collect_new → PgCollect

@ pgw_collect_preset inout PgCollect c ( Vec PgCol ) cols → v

The result's columns are already known (a cached statement, executed without a Describe): rows arrive in fmts (one per column).

@ pgw_collect_feed inout PgCollect c i mtype ( Vec u ) buf i off i end → v

One backend message, type mtype, body [off, end) of buf.

@ pg_result_ncols PgResult r → i

── result accessors ──────────────────────────────────────────────

@ pg_result_nrows PgResult r → i

@ pg_result_tag PgResult r → String

CommandComplete's tag ("SELECT 3", "UPDATE 2", …).

@ pg_result_affected PgResult r → i

The rows a command touched or returned — the tag's last number, 0 when it has none (CREATE TABLE).

@ pg_result_col PgResult r i col → String

@ pg_result_col_type PgResult r i col → i

The column's type OID (PG_INT4, PG_TEXT, …), 0 out of range.

@ pg_result_col_index PgResult r s name → ?i

The index of the column called name, F when there is none.

@ pg_row_is_null PgResult r i row i col → b

T for a NULL — and for a cell outside the table.

@ pg_row_view PgResult r i row i col → ?( Slice u )

The cell's raw bytes, a view of the result (text, or the type's binary form — pg_result_col_type says which type, and pg_row_s renders either). F for NULL.

@ pg_row_i PgResult r i row i col → ?i

An integer: int2 / int4 / int8 / oid (bool as 0 / 1), or a text cell that is one.

@ pg_row_f PgResult r i row i col → ?f

A float: float4 / float8, an integer column, or a text cell that is a number (numeric included — to the nearest double).

@ pg_row_b PgResult r i row i col → ?b

A bool: bool, or a text cell t / f (true / false).

@ pg_row_s PgResult r i row i col → ?String

The cell as text: a text cell as it came, a binary one rendered as PostgreSQL writes its type (a timestamptz in UTC, +00).

@ pg_row_bytes PgResult r i row i col → ?( Vec u )

The cell's bytes: a bytea's value (binary, or decoded from its \x text form), a uuid's 16 bytes, any other cell's text.

@ pg_row_ts PgResult r i row i col → ?i

A timestamp / timestamptz as microseconds since 1970-01-01 00:00 UTC (a date as its midnight), binary or text (YYYY-MM-DD[ HH:MM:SS[.f]][±HH[:MM]]).

@ pg_row_date PgResult r i row i col → ?i

A date as days since 1970-01-01 (a timestamp's day), binary or text.

@ pgw_parse_int ( Slice u ) b → ?i

── text forms ──────────────────────────────────────────────────── A whole view that is a decimal integer (an optional sign, digits).

@ pgw_parse_float ( Slice u ) b → ?f

A whole view that is a number: digits with an optional point and exponent, or NaN / Infinity / -Infinity (PostgreSQL's spellings).

: PgCivil { i y i m i d }

The civil date of days since 2000-01-01 (H. Hinnant's algorithm, proleptic Gregorian): year, month, day.

@ pgw_civil i days → PgCivil

@ pgw_days_from_civil i y0 i m i d → i

Days since 2000-01-01 of a civil date.

@ pgw_push_date String out i pgdays → v

YYYY-MM-DD (a year before 1 as PostgreSQL writes it: … BC).

@ pgw_push_time String out i us0 → v

HH:MM:SS[.ffffff] (trailing zeros of the fraction dropped).

@ pgw_push_timestamp String out i us b tz → v

A timestamp, us since 2000-01-01: date and time, +00 for a timestamptz; ±infinity as PostgreSQL writes them.

@ pgw_parse_timestamp ( Slice u ) b → ?i

YYYY-MM-DD[( |T)HH:MM[:SS[.ffffff]]][Z|±HH[:MM]] → µs since 1970 UTC.

@ pgw_float_text f x → String

A double as PostgreSQL writes one: the shortest digits that round-trip, NaN / Infinity / -Infinity spelled its way.

@ pgw_render i oid ( Slice u ) b → String

A binary cell of type oid in PostgreSQL's text form.

@ pgw_result_fmt i oid → i

The result format to ask for a column of type oid: binary for the fixed-width types decoded here, text for the rest.