pg

PostgreSQL driver for bnl — pure bnl, no native deps. Implements the v3 frontend/backend protocol directly over net + tls + crypto, with SCRAM-SHA-256 auth. Runs anywhere bnl runs.

Install

bpm install pg

Use

import "pg" as pg;

function main() {
    var db = wait pg.connect("postgresql://app:secret@127.0.0.1:5432/myapp");

    wait db.exec(
        "CREATE TABLE IF NOT EXISTS users ("
        + " id   SERIAL PRIMARY KEY,"
        + " name VARCHAR(64))");

    var inserted = wait pg.insert(db, "users", {name: "Alice"});
    print("id:", inserted.id);

    var rows = wait db.query("SELECT * FROM users WHERE id > $1", [0]);
    var i = 0;
    while (i < rows.length) {
        print(rows[i].id, rows[i].name);
        i = i + 1;
    }

    db.close();
}

main();

Every method returns a Future — pair with wait.

API

pg.connect(uri_or_opts) → Future<db>

Accepts either a postgresql:// URI string or an opts map.

// URI
var db = wait pg.connect("postgresql://app:secret@host:5432/myapp?sslmode=require");

// Opts (equivalent)
var db = wait pg.connect({
    host:     "host",
    port:     5432,
    user:     "app",
    password: "secret",
    database: "myapp",
    ssl:      "require"
});

Opt	Type	Default	Notes
host	string	"127.0.0.1"
port	number	5432
user	string	—	required
password	string	none	required when server asks for SASL
database	string	none	defaults server-side to the user name
application_name	string	"bnl-pg"	shown in pg_stat_activity
client_encoding	string	"UTF8"
ssl	bool / "require"	false	TLS handshake before startup
ssl_verify	bool	true	verify the server cert

URI query params recognised: sslmode (disable / prefer / require / verify-ca / verify-full), application_name, client_encoding.

postgres:// is accepted as an alias of postgresql://.

db.exec(sql, params?) → Future<null>

DDL / DML statement. $1, $2, ... placeholders are substituted left-to-right with params. After resolution:

• db.changes() — affected-row count parsed from the CommandComplete tag.
• db.last_tag() — full tag string ("INSERT 0 3", "UPDATE 5", …).

db.query(sql, params?) → Future<list of map>

Any rows-returning statement (SELECT, INSERT ... RETURNING, etc.).

db.query_one(sql, params?) → Future<map | null>

First matching row, or null if none. Still fetches the whole result set — add LIMIT 1 yourself for large tables.

db.tx_status() → string

Single-character transaction state from the last ReadyForQuery: "I" idle, "T" in transaction, "E" failed transaction (commit will roll back).

db.parameters() → map

Server parameter values that arrived in startup (server_version, client_encoding, DateStyle, …) plus anything updated by later SET commands.

db.close()

Sends Terminate and closes the socket. Idempotent.

pg.transaction(db, fn) → Future<value>

Runs fn(db) inside BEGIN / COMMIT, rolling back on throw or rejected Future. Resolves to whatever fn resolves to.

pg.insert(db, table, row) → Future<map>

Builds INSERT INTO <table> (...) VALUES (...) RETURNING * and resolves to the returned row map. Use this in place of MySQL's last_insert_id pattern.

pg.migrate(db, statements) → Future<count>

Runs the list of statements inside a transaction.

pg.pool(opts) → pool

Same opts as connect, plus max (default 10).

var pool = pg.pool({host: "...", user: "...", password: "...", max: 20});

wait pool.run(function (db) {
    return db.query("SELECT 1 AS one");
});

pool.size();  pool.idle_size();  pool.close();

pg.version() → string

Driver version, e.g. "0.1.0".

Parameter substitution & escaping

Placeholders are Postgres-style $N (1-indexed). Each $N may appear multiple times in the SQL; the driver substitutes the same value each time.

bnl value	SQL form
null	NULL
true / false	TRUE / FALSE
integer-valued number	123
non-integer number	123.45
string (no backslash)	'…' with '' doubling of '
string (with backslash)	E'…' form with \\ / \' / \n / \r escaping

Errors

Server errors reject with a structured map:

try {
    wait db.exec("INSERT INTO users (id) VALUES (1)");
} catch (e) {
    if (type(e) == "map" and e.sqlstate == "23505") {
        print("duplicate key:", e.message);
    } else {
        throw e;
    }
}

Field	Notes
code	SQLSTATE — same as sqlstate. Kept for symmetry with the mysql package.
sqlstate	5-char SQLSTATE (e.g. "23505" for unique_violation)
message	Server's primary message

Client-side errors (TCP failure, protocol violation, bad arguments) reject as plain strings prefixed "pg: ".

Type decoding

Text protocol throughout. The driver converts integers / floats / bools / NULL to native bnl values; everything else stays a string so the caller can parse dates / json / arrays / numeric-with-precision themselves.

Postgres type	Decodes as
bool	true / false
int2 / int4 / int8	number (string when out of safe-integer range)
float4 / float8	number
numeric	string (preserves precision)
text / varchar / bytea / date / timestamp / json / …	string

License

MIT.