pg (PostgreSQL)

On this page:

spider.pg: PostgreSQL: init, query, queryOne, queryExecute, begin. Opt-in with -Dpg=true.

`spider.pg`

PostgreSQL for an app (spider.pg, opt-in with -Dpg=true): one connection pool per process, opened by init, then query, queryOne, queryExecute, begin and transaction from anywhere. Rows come back as structs whose fields are matched to columns by name.

`spider.pg.ArrayParameter`

zig

pub fn ArrayParameter(comptime T: type) type

What array returns: a list of values to pass as one query parameter.

Fields

values: []const T
type_name: []const u8

`spider.pg.array`

zig

pub fn array(comptime T: type, values: []const T) ArrayParameter(T)

A list of values as one parameter, for = ANY($1). values is not copied: it must stay valid until the query call returns. A plain slice passed as the parameter does the same.

zig

const ids = [_]i32{ 2, 4, 9 };
const posts = try spider.pg.query(
    Post,
    c.arena,
    "SELECT id, title FROM posts WHERE id = ANY($1)",
    .{spider.pg.array(i32, &ids)},
);

`spider.pg.DbConfig`

zig

pub const DbConfig = struct

The options of init. A field left null comes from the environment (the .env files included), then from a built-in default.

Fields

host: ?[]const u8 = null: Server address. null: PG_HOST, or "localhost".
port: ?u16 = null: Server port. null: PG_PORT, or 5432.
database: ?[]const u8 = null: Database name. null: PG_DB, or "spider_db".
user: ?[]const u8 = null: null: PG_USER, or "spider".
password: ?[]const u8 = null: null: PG_PASSWORD, or "spider".
pool_size: ?usize = null: Connections kept open. null: 10 (no variable is read).
mapping: MappingMode = .fail: What typed mapping does with a column missing from the result or a NULL in a non-optional field (fields with a declared default use it either way). .fail returns error.ColumnMissing / error.UnexpectedNull; .warn logs it (once per field) and keeps the old zero value (0, "", false) — a migration aid for code written against the old silent behavior. Under .warn a value that cannot be decoded into its field (error.TypeMismatch, error.IntegerOverflow, error.InvalidEnumValue) is logged and becomes the zero value too.

`spider.pg.MappingMode`

zig

pub const MappingMode = enum { fail, warn }

The values of DbConfig.mapping: see that field.

`spider.pg.init`

zig

pub fn init(allocator: std.mem.Allocator, io: std.Io, overrides: DbConfig) !void

Opens the connection pool. Call it once at startup, before any query, and deinit() at exit. allocator and io must outlive the pool.

A connection that can't be opened is tried five times in all, waiting 1, 2, 4 and 8 seconds, then the error is returned. An answer from Postgres itself (wrong password, unknown database) is logged and returned at once as error.PG.

zig

try spider.pg.init(allocator, io, .{});
defer spider.pg.deinit();

`spider.pg.deinit`

zig

pub fn deinit() void

Closes the pool. No query may run after it.

`spider.pg.QueryResult`

zig

pub fn QueryResult(comptime T: type) type

What query(T, ...) returns: nothing for void, one value for i32 and i64, a slice []T for a struct.

`spider.pg.DbError`

zig

pub const DbError = error

Postgres failures, by SQLSTATE class. error.PG remains for codes not listed here. Use lastError() in the catch for message/detail/constraint.

Errors

UniqueViolation
ForeignKeyViolation
NotNullViolation
CheckViolation
ExclusionViolation
IntegrityConstraintViolation
InvalidTextRepresentation
StringDataRightTruncation
NumericValueOutOfRange
InvalidDatetimeFormat
DatetimeFieldOverflow
DivisionByZero
DataException
SerializationFailure
DeadlockDetected
LockNotAvailable
QueryCanceled
UndefinedTable
UndefinedColumn
UndefinedFunction
SqlSyntaxError
InsufficientPrivilege
RaisedException
PG

`spider.pg.MappingError`

zig

pub const MappingError = error{ ColumnMissing, UnexpectedNull, TypeMismatch, IntegerOverflow }

Typed-mapping failures: what a row can fail to give a struct. error.ColumnMissing: no column with the field's name. error.UnexpectedNull: a NULL where the field is neither optional nor has a default. error.TypeMismatch: the column's type cannot become the field's type (a timestamp into an integer, 1.5 into an integer, a date before year 1 into a string). error.IntegerOverflow: the number does not fit the field. See DbConfig.mapping. An unknown enum label is error.InvalidEnumValue, which is not part of this set.

`spider.pg.isDbError`

zig

pub fn isDbError(err: anyerror) bool

True when err is one of the errors of DbError: a failure reported by Postgres itself, as opposed to a mapping error or a lost connection.

`spider.pg.ErrorInfo`

zig

pub const ErrorInfo = struct

Details of the last Postgres error raised on this thread, as lastError returns them. The strings live in a per-thread buffer of 2048 bytes (longer texts are cut) that the next Postgres error on the thread overwrites: copy what must be kept.

Fields

code: []const u8: The SQLSTATE, e.g. "23505".
message: []const u8
detail: ?[]const u8: Can contain row values: not for end users.
constraint: ?[]const u8
table: ?[]const u8
column: ?[]const u8

`spider.pg.lastError`

zig

pub fn lastError() ?ErrorInfo

Details (SQLSTATE, message, detail, constraint, table, column) of the Postgres error that the pg.* call which just failed returned. Read it in the catch, before any other I/O: it is per-thread and the next failing call on this thread replaces it. detail can contain row values (e.g. an email in a unique violation) — don't show it to end users.

null while no Postgres error happened on this thread. It is only set by a DbError, and a call that succeeds does not clear it: after a mapping error or a connection error it still holds the previous Postgres error.

`spider.pg.query`

zig

pub fn query(
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !QueryResult(T)

Runs one statement with $1, $2... parameters on a connection of the pool and gives back what T asks for:

a struct: every row as a []T allocated in arena. A field takes the column of the same name. Fields may be []const u8, bool, integers (i8 to i64, u8 to u64), f32, f64, enums (the label of a Postgres enum or a text), optionals of those, and slices of those for an array column: tags: []const []const u8 takes a TEXT[], ids: []const i64 a BIGINT[] (one dimension; a NULL element needs an optional element type). A []const u8 field accepts any column: bool, integers, floats, numeric, uuid, date, time ("07:05:09", fractions dropped), timestamp and jsonb come as text ("2026-09-26T18:40:33.183Z" for a timestamptz, in UTC); any other type comes as the bytes Postgres sent, which is the text itself for text, varchar, json and enums.
i32 or i64: the first column of the first row; 0 when there is no row or the value is NULL.
void: nothing; for INSERT, UPDATE and DELETE.

Fails with:

a DbError when Postgres refuses the statement (lastError() has the details), e.g. error.UniqueViolation;
a MappingError when a row does not fit T: error.ColumnMissing and error.UnexpectedNull (a field that is neither optional nor has a default; see DbConfig.mapping), error.TypeMismatch, error.IntegerOverflow; error.InvalidEnumValue for an unknown enum label;
error.UseBeginForTransactions for BEGIN, COMMIT, ROLLBACK or SAVEPOINT: each call may run on a different connection. Use begin().

zig

const posts = try spider.pg.query(Post, c.arena, "SELECT id, title FROM posts WHERE author_id = $1", .{author_id});
const total = try spider.pg.query(i64, c.arena, "SELECT count(*) FROM posts", .{});
try spider.pg.query(void, c.arena, "DELETE FROM posts WHERE id = $1", .{id});

`spider.pg.queryOne`

zig

pub fn queryOne(
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !?T

The first row of a statement, or null when it returns no row. T is a struct, mapped as in query, or i32/i64 for the first column (null when that value is NULL). Further rows are discarded. Fails as query.

zig

const post = try spider.pg.queryOne(Post, c.arena, "SELECT id, title FROM posts WHERE id = $1", .{id}) orelse return error.NotFound;

`spider.pg.queryExecute`

zig

pub fn queryExecute(
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
) !QueryResult(T)

Execute raw SQL without parameters. Supports multiple statements separated by ';'.

With void as T the script (DDL, migrations) runs statement by statement on one connection and stops at the first that fails; the ones before it stay applied unless the script has its own BEGIN; ...; COMMIT, which is allowed here when balanced: a failure inside it rolls the transaction back. A ; inside a string, a comment or a $$ ... $$ body does not split. With another T it is query for one statement without parameters.

zig

try spider.pg.queryExecute(void, arena, "CREATE TABLE IF NOT EXISTS posts (id serial PRIMARY KEY, title text NOT NULL)");

`spider.pg.queryOneExecute`

zig

pub fn queryOneExecute(
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
) !?T

The first row of one statement without parameters: queryOne(T, arena, sql, .{}).

`spider.pg.begin`

zig

pub fn begin() !Transaction

Starts a transaction: takes a connection from the pool, sends BEGIN and keeps the connection for the Transaction until commit() or rollback(). Always defer tx.rollback(): it does nothing after a commit and returns the connection in every other case.

zig

var tx = try spider.pg.begin();
defer tx.rollback();
try tx.query(void, c.arena, "INSERT INTO users (name) VALUES ($1)", .{name});
try tx.commit();

`spider.pg.transaction`

zig

pub fn transaction(
    comptime R: type,
    context: anytype,
    comptime body: fn (*Transaction, @TypeOf(context)) anyerror!R,
) !R

Runs body(&tx, context) inside one transaction on one pinned connection: commits when it returns normally, rolls back when it returns an error (the error is passed through). Returns what body returned; when the COMMIT itself fails, the transaction is rolled back and that error is returned.

zig

const NewPost = struct { arena: std.mem.Allocator, title: []const u8 };
const input: NewPost = .{ .arena = c.arena, .title = title };
const id = try spider.pg.transaction(i64, input, struct {
    fn run(tx: *spider.pg.Transaction, in: NewPost) !i64 {
        const sql = "INSERT INTO posts (title) VALUES ($1) RETURNING id";
        return tx.query(i64, in.arena, sql, .{in.title});
    }
}.run);

`spider.pg.Transaction`

zig

pub const Transaction = struct

Database transaction on ONE pinned connection. Use begin() (or transaction()) to create one.

zig

var tx = try pg.begin();
defer tx.rollback();
try tx.query(void, arena, "INSERT INTO users (name) VALUES ($1)", .{"Alice"});
try tx.commit();

After Postgres refuses a statement it aborts the transaction: further statements (and commit) return error.TransactionAborted until rollback().

Fields

conn: *pg_lib.Conn: The connection the transaction is pinned to. Set by begin().
committed: bool = false: True once commit() succeeded.
rolled_back: bool = false: True once rollback() ran.

`pg.Transaction.exec`

zig

pub fn exec(self: *Transaction, sql: []const u8, params: anytype) !void

Deprecated: use tx.query(void, arena, sql, params) instead.

`pg.Transaction.query`

zig

pub fn query(
    self: *Transaction,
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !QueryResult(T)

The module's query, on the transaction's connection. Also fails with error.TransactionAborted after a statement of this transaction failed, and with error.TransactionAlreadyFinished after commit() or rollback().

`pg.Transaction.queryOne`

zig

pub fn queryOne(
    self: *Transaction,
    comptime T: type,
    arena: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !?T

The module's queryOne, on the transaction's connection. Fails as Transaction.query.

`pg.Transaction.commit`

zig

pub fn commit(self: *Transaction) !void

Sends COMMIT and returns the connection to the pool. Fails with error.TransactionAborted when a statement failed before, and with error.TransactionAlreadyFinished on a second call.

On failure the transaction stays open; the usual defer tx.rollback() then cleans it up and returns the connection.

`pg.Transaction.rollback`

zig

pub fn rollback(self: *Transaction) void

Undoes the transaction and returns the connection to the pool. Does nothing after commit() or a first rollback().

Safe to call at any point (also from defer), including after a failed statement: it uses the driver's rollback, which works on an aborted transaction, so the connection goes back to the pool clean instead of being torn down and reopened.

`spider.pg.exec`

zig

pub fn exec(sql: []const u8, params: anytype) !void

Deprecated: use query(void, arena, sql, params) instead.

`spider.pg.execRaw`

zig

pub fn execRaw(sql: []const u8) !void

Deprecated: use queryExecute(void, arena, sql) instead.

`spider.pg.queryWith`

zig

pub fn queryWith(sql: []const u8, params: anytype) !Result

Deprecated: use query(T, arena, sql, params) instead.

`spider.pg.queryRaw`

zig

pub fn queryRaw(sql: []const u8, params: anytype) !Result

Deprecated: use query(T, arena, sql, params) instead.

`spider.pg.queryRow`

zig

pub fn queryRow(sql: []const u8, params: anytype) !Result

Deprecated: use query(T, arena, sql, params) instead.

`spider.pg.queryAs`

zig

pub fn queryAs(
    comptime T: type,
    allocator: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !MappedRows(T)

Deprecated: use query(T, arena, sql, params) instead.

`spider.pg.queryOneAs`

zig

pub fn queryOneAs(
    comptime T: type,
    allocator: std.mem.Allocator,
    sql: []const u8,
    params: anytype,
) !?MappedRows(T)

Deprecated: use queryOne(T, arena, sql, params) instead.

`spider.pg.Result`

zig

pub const Result = struct

Deprecated: use query(T, arena, sql, params), which returns typed rows. The untyped result of queryWith, queryRaw and queryRow: every value as text, with NULL and the empty string both read as "".

`pg.Result.deinit`

zig

pub fn deinit(self: *Result) void

Deprecated: use query (see Result). Frees the result.

`pg.Result.rows`

zig

pub fn rows(self: *const Result) usize

Deprecated: use query (see Result). Number of rows.

`pg.Result.columns`

zig

pub fn columns(self: *const Result) usize

Deprecated: use query (see Result). Number of columns.

`pg.Result.columnName`

zig

pub fn columnName(self: *const Result, col: usize) []const u8

Deprecated: use query (see Result). Name of column col; "" when out of range.

`pg.Result.columnTypeOid`

zig

pub fn columnTypeOid(_: *const Result, _: usize) i32

Deprecated: use query (see Result). Always 0: the type is not kept.

`pg.Result.affectedRows`

zig

pub fn affectedRows(self: *const Result) usize

Deprecated: use query (see Result). The number of rows returned, the same as rows(); not the count of rows an UPDATE or DELETE changed.

`pg.Result.getValue`

zig

pub fn getValue(self: *const Result, row: usize, col: usize) []const u8

Deprecated: use query (see Result). The value at row, col as text; "" for NULL or out of range.

`pg.Result.get`

zig

pub fn get(self: *const Result, row: usize, comptime name: []const u8) []const u8

Deprecated: use query (see Result). The value of column name in row; "" for NULL or an unknown column.

`pg.Result.isNull`

zig

pub fn isNull(self: *const Result, row: usize, col: usize) bool

Deprecated: use query (see Result). True for NULL and for an empty string.

`pg.Result.mapAll`

zig

pub fn mapAll(self: *Result, comptime T: type, alloc: std.mem.Allocator) ![]T

Deprecated: use query (see Result). Every row as a T, allocated with alloc. A number that does not parse becomes 0.

`pg.Result.mapOne`

zig

pub fn mapOne(self: *Result, comptime T: type, alloc: std.mem.Allocator) !?T

Deprecated: use query (see Result). The first row as a T, or null without rows.