pg

import pg from "pg";
import net from "net";
import os from "os";

var s: net.TcpStream = net.connect_host("127.0.0.1", 5432, time.seconds(10)) or { return 1; };
var nonce: [18]u8;
os.random_bytes(nonce[..]) or { return 1; };
let none: [0]pg.Param = [];
var c: pg.Conn = pg.connect("app", "appdb", password, nonce[..], none[..], 64 << 20);
defer c.free();

let ps: [1]pg.Value = [pg.Value.Int { v: 42; }];
var asked: bool = false;
var buf: [16384]u8;
while (true) {
    if (c.ready() && !asked) {                       // logged in: ask
        c.query("SELECT name, created FROM users WHERE id = $1", ps[..]);
        asked = true;
    }
    s.write_all(c.output()) or { break; };          // the StartupMessage, the password, the query
    c.advance_output(c.output().len);
    let n: i64 = s.read(buf[..]) or { break; };
    if (n == 0) { c.end_of_input(); break; }
    c.feed(buf[0..n]) or { io.println("{err}"); break; };
    var done: bool = false;
    while (true) {
        let e: ?pg.Event = c.next_event();
        if (e == null) { break; }
        match (e!) {
            pg.Event.Row { values } => { /* values.get_ref(0) is a pg.Value.Text */ }
            pg.Event.Error { err } => { io.println(err.describe()); }
            pg.Event.Ready { status } => { if (asked) { done = true; } }
            _ => {}
        }
    }
    if (done) { break; }
}
c.terminate();
s.write_all(c.output()) or {};

pg is PostgreSQL’s frontend/backend protocol (version 3.0, the one every server since 7.4 speaks) without the I/O: you feed it what the socket received, send what it queues, and take what the server said as events. The socket, the pool, the threads and the SQL are yours. It logs in with SCRAM-SHA-256, MD5 or a cleartext password, runs queries through the simple protocol (several statements in one text) and the extended one ($n parameters, prepared statements), and decodes the values of the common types from either format.

The connection

• database may be empty (the server takes the user’s name). params are more startup parameters, in order: application_name, client_encoding, options ("-c search_path=app"), …
• nonce is 18 or more random bytes from you (os.random_bytes): SCRAM’s client nonce. pg takes no randomness itself, so it stays pure; an empty nonce stops the program with a trap.
• The password is used for whichever method the server asks (SCRAM-SHA-256, MD5 or cleartext) and wiped as soon as the login ends. With SCRAM the server must prove it knows the password too: a wrong proof, or a login that skips it, fails with AuthFailed. An error from the server during the login comes as an Error event, then feed returns AuthFailed. Kerberos, GSSAPI and SSPI are Unsupported.
• max_message limits one message from the server (a big row, a long error); past it the connection ends with TooLarge. 64 MiB suits most programs.
• A connection error (Protocol, AuthFailed, Unsupported, TooLarge) is returned by feed once and kept in error(); afterwards feed ignores its input. What is queued may still be sent; then close the socket.

Queries

• query does what a driver does for a parameterized query: it sends Parse and Describe, and when the server has described the statement it sends Bind, Execute and Sync itself, with every parameter in the type the statement declares and the results in binary for the types pg decodes. You see only Columns (when the statement returns rows), Rows, Complete and Ready, or an Error and Ready. It costs one round trip more than a bare Bind; prepare once and query_prepared after saves it.
• One query, prepare or query_prepared at a time: wait for its Ready. simple and the bare messages can be queued behind it, and their replies come as their own events.
• The bare messages are each one message of the extended protocol, for a program that knows its types: compose a cycle and end it with sync. They and the query functions stop the program with a trap before the login ends or after a connection error.
• A parameter is sent with its type’s OID (Int as int8, Text as text, …), which PostgreSQL casts on assignment where it can. A Text is text, so it doesn’t go into a json, numeric or enum column; for those pass Raw { oid: 0; format: 0; data: "..." } and the server reads the text as the column’s type.
• ServerError.describe() gives "ERROR 23505: duplicate key value violates unique constraint ... (Key (id)=(1) already exists.)" for a log line.

Events

pub tagged Event {
    Ready { status: TxStatus; }                    // a cycle ended
    Columns { columns: *const Vec_Column; }        // the Rows after it are decoded by these
    Row { values: *const Vec_Value; }
    Complete { tag: []const u8; rows: i64; }       // "INSERT 0 1" and 1; "BEGIN" and -1
    Error { err: *const ServerError; }             // the cycle ends at the next Ready
    Notice { notice: *const ServerError; }         // at any time
    Notification { pid: i64; channel: []const u8; payload: []const u8; }   // NOTIFY, at any time
    Parameter { name: []const u8; value: []const u8; }                    // at any time
    ParseComplete; BindComplete; CloseComplete; NoData; EmptyQuery; PortalSuspended;
    Params { oids: *const collections.Vec_i64; }   // a statement's parameter types
}

• An event’s views are valid until the next next_event. A Row’s values are its own: keep one by copying the Value.
• ServerError has every field PostgreSQL sends: severity, code (the SQLSTATE), message, detail, hint, position, internal_position, internal_query, where_, schema, table, column, data_type, constraint, file, line, routine; empty or 0 when absent.
• A Column is name, table_oid, attr_num, type_oid, type_size, type_mod and format (0 text, 1 binary).
• LISTEN/NOTIFY: simple("LISTEN jobs") once, then Notification events arrive whenever the server sends them, between the replies to anything else. A connection that only listens still has to feed what arrives.

Values

pub tagged Value {
    Null;
    Bool { v: bool; }
    Int { v: i64; }               // int2, int4, int8, oid
    Float { v: f64; }             // float4, float8
    Text { v: string; }           // text, varchar, name, bpchar, json
    Bytes { v: string; }          // bytea
    Uuid { v: [16]u8; }
    Timestamp { micros: i64; }    // microseconds since 1970-01-01 00:00 UTC
    Date { days: i64; }           // days since 1970-01-01
    Json { v: string; }           // jsonb
    Inet { addr: [16]u8; v6: bool; prefix: i64; cidr: bool; }
    Array { elem_oid: i64; items: Vec_Value; }
    Raw { oid: i64; format: i64; data: string; }
}

• A timestamp without a time zone is taken as UTC. infinity and -infinity are the largest and smallest i64 (for a date, the largest and smallest i32), kept as they are.
• Text forms are read as PostgreSQL writes them with its default settings: DateStyle ISO and bytea_output hex. NaN and Infinity floats, BC dates and arrays quoted with backslashes are read.
• In a row, a value of a type pg doesn’t know, or one not in its type’s form, is Raw: one odd value doesn’t lose the row. numeric is one of them (its text: Raw { oid: 1700; format: 0; data: "1.50" }).
• An Array’s NULL items are Null. An Array of Text is sent in binary, element by element, so nothing needs quoting.

Not covered

COPY (a COPY ... FROM STDIN or TO STDOUT ends the connection with Unsupported), large objects, the replication protocol, numeric as a number, ranges, composite types, arrays of more than one dimension, Kerberos/GSSAPI/SSPI logins and SCRAM-SHA-256-PLUS (channel binding). TLS is not built in: send the SSLRequest yourself, read the server’s one byte, and run the engine over a net TLS stream. Pooling, timeouts, reconnecting and statement caches are the program’s.

Last modified: 2026-10-27 09:54:20 UTC