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.
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() strThe 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):
| parameter | default | |
|---|---|---|
pool_timeout | 30s | how long a statement waits for a free connection |
max_lifetime | 1h | a connection is replaced after this long (spread out by up to 9%, so they don't all reconnect at once) |
max_idle | 30m | a connection idle this long is closed |
statement_cache | on | off: don't keep prepared statements (for PgBouncer in transaction mode) |
application_name | tern | the 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]) strParameters 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.
execreturns the number of rows the statement changed (or returned, for a SELECT), or -1 if it failed.queryreturns the rows as maps with every value as text; NULL is"".query_jsonreturns the rows as a JSON array with typed values: integers, floats and numerics as numbers, booleans astrue/false, NULL asnull, everything else as a string.json.decodethen fills an array of structs, matching columns to fields by name (useAS "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_jsonreturns just the first row as a JSON object, or"null"if there is none (thenjson.decodereturns 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 statementsfetch 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 tooimport 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.
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.
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) boolA 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.
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/s | Tern pg | Rust tokio-postgres | Go pgx | Rust sqlx |
|---|---|---|---|---|
| one query | 165,732 | 153,343 | 131,983 | 50,958 |
| 20 queries | 8,132 | 7,527 | 6,402 | 2,518 |
| 20 queries + 20 updates | 3,424 | 3,069 | 2,917 | 1,234 |
| client CPU per statement | 6.2 us | 6.7 us | 7.9 us | 19.8 us |
100 rows into structs (fetch) | 101,180 | 61,697 | 29,839 | 20,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.