Tern

pg

The pg package is a PostgreSQL client for tasks: a pool of connections that any number of tasks share, parameters sent separately from the SQL (no injection), rows decoded straight into your structs, and transactions that belong to the task that started them. It speaks the PostgreSQL protocol directly (no libpq), logs in with SCRAM-SHA-256, and can use TLS. Add it with tern add pg.

Compiles (not run here)
import json
import pg

struct User:
    id: int
    name: str
    email: str

fn main():
    db = pg.pool("postgres://app:secret@localhost:5432/app", 16)
    if not db.ok():
        print("can't connect:", pg.error())
        return
    db.exec("CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY, name text, email text)", [])
    db.exec("INSERT INTO users (name, email) VALUES ($1, $2)", ["Ada", "ada@example.com"])
    users: [User] = []
    json.decode(db.query_json("SELECT id, name, email FROM users ORDER BY id", []), &users)
    for u in users:
        print(u.id, u.name)

Connecting

fn pool(url: str, size: int) Pool
fn connect(url: str) Pool            pool(url, 1)
fn ok(p: Pool) bool
fn stats(p: Pool) PoolStats
fn prune(p: Pool)
fn close(p: Pool)
fn error() str

The URL is postgres://user:password@host:port/database, with ?sslmode=require (TLS) or ?sslmode=verify-full (TLS, and the server's certificate must match the host) if the program also does import tls. Characters like @ in a password are written %40. ?application_name=NAME names the connections in pg_stat_activity (default tern).

A pool holds up to size connections. The first one opens right away, so p.ok() tells you whether the URL and password work (and pg.error() why not); the rest open when enough tasks need them at once. A task that finds every connection busy waits (only that task) until one is free, for up to pool_timeout; after that the statement fails and pg.error() says the pool was exhausted. A Pool value is a handle: copy it into as many tasks and closures as you like.

Before a connection is handed out, the pool checks that it is still good: one the server closed (a restart, pg_terminate_backend, a proxy's idle timeout) is noticed and replaced before a statement is sent on it, and one that is too old or has idled too long is closed and reopened. Now and then, as connections come back, the pool closes the ones idle longer than max_idle, so it shrinks after a busy spell; prune does that right away. close closes every idle connection (the pool reopens them when needed).

URL parameters for the pool (durations are 500ms, 5s, 30m, 1h, or seconds; 0 turns the limit off):

parameterdefault
pool_timeout30show long a statement waits for a free connection
max_lifetime1ha connection is replaced after this long (spread out by up to 9%, so they don't all reconnect at once)
max_idle30ma connection idle this long is closed
statement_cacheonoff: don't keep prepared statements (for PgBouncer in transaction mode)
application_nameternthe connections' name in pg_stat_activity

p.stats() returns what the pool is doing, for a health endpoint or a metric:

struct PoolStats:
    size: int          the most connections it opens
    open: int          connections open now
    idle: int          ... of them not in use
    in_use: int        ... running a statement, or held by a task's transaction
    waiting: int       tasks waiting for a connection now
    waits: int         times a task had to wait (every connection was in use)
    wait_ms: int       ... and the milliseconds it waited, added up
    timeouts: int      waits that gave up after pool_timeout
    opened: int        connections opened, ever
    closed: int        ... and closed (broken, too old, idle too long, p.close())

A statement that finds a free connection takes no lock: only waiting, opening, closing, and transactions touch the pool's shared counters.

The client is written in Tern: wire.tn speaks the PostgreSQL protocol (startup, SCRAM-SHA-256, TLS, the extended query protocol with prepared statements) over net, and the pool is pg.tn. fetch hands the server's rows, as they came, to the standard library's sql module, whose decoder the compiler writes for each struct.

Queries

fn exec(p: Pool, sql: str, params: [str]) int
fn query(p: Pool, sql: str, params: [str]) [{str: str}]
fn query_json(p: Pool, sql: str, params: [str]) str
fn query_row_json(p: Pool, sql: str, params: [str]) str

Parameters are $1, $2, ... in the SQL and strings in params; PostgreSQL converts each to its column's type ("42" to an int, "true" to a bool), and pg.NULL is SQL NULL. Every statement is prepared the first time a connection runs it and reused after that, so a repeated query costs one round trip.

  • exec returns the number of rows the statement changed (or returned, for a SELECT), or -1 if it failed.
  • query returns the rows as maps with every value as text; NULL is "".
  • query_json returns the rows as a JSON array with typed values: integers, floats and numerics as numbers, booleans as true/false, NULL as null, everything else as a string. json.decode then fills an array of structs, matching columns to fields by name (use AS "fieldName" for a field whose name has capitals, since PostgreSQL folds unquoted names to lowercase). A NULL leaves the field at its zero value.
  • query_row_json returns just the first row as a JSON object, or "null" if there is none (then json.decode returns false).

When a statement fails, exec returns -1, the query functions return no rows, and pg.error() has the server's message with its SQLSTATE code, like ERROR 23505: duplicate key value violates unique constraint "users_pkey" (Key (id)=(1) already exists.). After a success, pg.error() is "".

Typed rows with Result

fn fetch[T](p: Pool, sql: str, params: [str]) Result[[T], str]
fn fetch_one[T](p: Pool, sql: str, params: [str]) Result[Option[T], str]
fn run(p: Pool, sql: str, params: [str]) Result[int, str]          rows affected
fn run_script(p: Pool, sql: str) Result[int, str]                   several statements

fetch decodes the rows straight into your struct type, with code the compiler writes for it (no JSON in between, so it's the fastest way to read rows). Columns are matched to fields by name; other columns are ignored, and a field without a column is zero (None for an Option). A NULL is None in an Option field and zero in any other. int, float, and bool fields read the column's text as a number or t / f (a float field also reads a timestamptz, timestamp or date as seconds since 1970, UTC), an enum field (one without payloads) reads a text or enum column by variant name, loosely (in_progress fills InProgress), a str field takes any column as text, [int], [float], [bool], and [str] fields read PostgreSQL arrays (int8[], text[], ...; one dimension, no NULL elements), and a field of another type (a struct, a map) reads a json / jsonb column. Assigned with ? to a variable of a known type, it needs no type argument; elsewhere, write it: db.fetch[User](sql, params). fetch_one gives None when no row matches. Errors carry the server's message, or say which value didn't fit: "pg: query failed: ERROR 42P01: relation \"users\" does not exist", "pg: query failed: the value \"seven\" doesn't fit n: int".

Arrays go the other way as one parameter, in PostgreSQL's text form:

fn array_int(xs: [int]) str        array_float, array_bool, array_str too
Compiles (not run here)
import pg

struct Post:
    id: int
    title: str
    tags: [str]

fn main():
    db = pg.pool("postgres://app:secret@localhost/app", 4)
    ids = [3, 7, 9]
    posts = db.fetch[Post]("SELECT id, title, tags FROM posts WHERE id = ANY($1)", [pg.array_int(ids)])
    db.exec("UPDATE posts SET tags = $1 WHERE id = $2", [pg.array_str(["tern", "sql"]), "3"])
    db.exec("UPDATE posts SET title = $1 WHERE id = $2", [pg.NULL, "9"])      # pg.NULL: SQL NULL
    print(posts)

run_script runs a whole script (a schema file, a migration) in one round trip, with PostgreSQL's simple query protocol: the statements run in order, in one transaction unless the script has its own BEGIN / COMMIT, and the first error stops the rest. It takes no $1 parameters. It returns the rows affected, added up. For versioned schema changes, see migrate.

Compiles (not run here)
import pg

struct User:
    id: int
    name: str

fn team(db: pg.Pool, name: str) Result[[User], str]:
    users: [User] = db.fetch("SELECT id, name FROM users WHERE team = $1 ORDER BY id", [name])?
    return Ok(users)

fn rename(db: pg.Pool, id: int, name: str) Result[bool, str]:
    n = db.run("UPDATE users SET name = $1 WHERE id = $2", [name, str(id)])?
    return Ok(n == 1)

fn main():
    db = pg.pool("postgres://app:secret@localhost/app", 8)
    match team(db, "core"):
        Ok(users):
            print(len(users), "users")
        Err(e):
            print(e)
    u = db.fetch_one[User]("SELECT id, name FROM users WHERE id = $1", ["7"])
    if u is Ok(found):
        print(found.is_some())

Describing a statement

fn describe(p: Pool, sql: str) Result[Statement, str]

What the server knows about a statement without running it (a Parse and a Describe): the types of its parameters (params, like ["int8", "text"]) and of its result columns (columns: name, type_name such as "int8", "text", "_int4" for int4[], type_oid, kind ("b" base, "e" enum, "d" domain), base_type for a domain, table, and not_null, true for a table column declared NOT NULL). A mistake in the SQL is an Err with the server's message. sqlgen uses it to generate typed functions from your queries.

Compiles (not run here)
import pg

fn main():
    db = pg.pool("postgres://app:secret@localhost/app", 4)
    match db.describe("SELECT id, email FROM users WHERE id = $1"):
        Ok(s):
            print(s.params)                                   # ["int8"]
            for c in s.columns:
                print(c.name, c.type_name, c.table, c.not_null)   # id int8 users true ...
        Err(e):
            print(e)

Rows decode into structs with Option fields: a NULL column is None.

Transactions

fn begin(p: Pool) bool
fn commit(p: Pool) bool
fn rollback(p: Pool) bool

A transaction belongs to the task that starts it. From begin (or any statement that leaves the connection inside a transaction, such as exec("BEGIN", [])) until commit or rollback, that task's statements all run on the same connection, and no other task can use it, even while the task waits on a channel or a socket in the middle. Other tasks keep using the rest of the pool.

Compiles (not run here)
import pg

fn transfer(db: pg.Pool, src: str, dst: str, amount: str) bool:
    db.begin()
    a = db.exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1", [amount, src])
    if a != 1:
        db.rollback()
        return false
    db.exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, dst])
    return db.commit()

fn main():
    db = pg.pool("postgres://app:secret@localhost/app", 8)
    print(transfer(db, "1", "2", "100"))

Performance

One client thread, 256 tasks sharing 16 connections, against PostgreSQL 17 on the same machine, TechEmpower-style (see bench/std/results.md for the method and machine load):

requests/sTern pgRust tokio-postgresGo pgxRust sqlx
one query165,732153,343131,98350,958
20 queries8,1327,5276,4022,518
20 queries + 20 updates3,4243,0692,9171,234
client CPU per statement6.2 us6.7 us7.9 us19.8 us
100 rows into structs (fetch)101,18061,69729,83920,892

fetch decodes rows with code made for the struct, and once a statement is prepared on a connection it asks for numbers and booleans in binary, so neither side formats or parses them as text (a str field still gets them as text).

These numbers are from when the protocol was C in the runtime. Rewritten in Tern, it runs single statements with about 10% more instructions than that C did, still fewer than tokio-postgres (8.05 G against 8.55 G for the same run), and fetch with fewer than before (1.25 G against 2.88 G for tokio-postgres); see bench/std/results.md.

Source: docs/packages/pg.md. Every example on this page is compiled and run by tern x test docs.