Part of magmalake β data lake building blocks in Mojo.
A PostgreSQL client for Mojo over libpq:
connections, parameterized statements, typed results, transactions, COPY,
and a connection pool for services that check connections out from
several threads β with errors that carry their SQLSTATE. Text format on the
wire, no ORM and no async β a client, not a framework.
This repository is a fork of
dvirarad/mojo-postgres by Dvir
Arad, Apache-2.0, and it stays Apache-2.0 with his copyright (see NOTICE).
The package layout, the ConnectionConfig builder and the first libpq symbol
inventory are his. Thank you.
What changed here, and why. Upstream targets Mojo 0.25.7, where FFI and
pointer APIs have since moved, and it reaches libpq through PQexec plus
PQescapeLiteral β values are escaped into SQL text, so there is no way to
bind a parameter. This fork is a rewrite on the 1.x toolchain that keeps the
shape and replaces the substance: PQexecParams and
prepared statements instead of escaping, a process-wide libpq handle instead
of external_call, SQLSTATE on every error, transactions, COPY, and a test
suite that runs against a real server. Upstream's open issues
#4,
#5 and
#6 are what the
milestones below implement. The design is written up at
magmalake.org/issues/1.
pixi shelf add postgres-mojoWorking with a coding agent? npx skills add mojoshelf/mojoshelf --skill mojoshelf-consume --yes teaches it to find and install tins itself β it installs the shelf CLI too.
That resolves the tin from mojoshelf and adds it β along with the tins it depends on β as pixi git source dependencies. magmalake tins are not published to a conda channel, so pixi add postgres-mojo will not find them.
libpq comes from conda-forge as a declared dependency; the conda-forge build
links OpenSSL, so sslmode=require works without anything else installed.
libpq is the reference PostgreSQL client library β every other client
(psycopg included) either wraps it or reimplements its wire protocol, so
binding it directly gives connections, COPY, and error reporting that match
what the server actually does. This tin sends parameters and reads results as
text, not libpq's binary format: one codec (postgres.text) is both
directions of the wire, and there is nothing to keep in sync between an
encoder and a separate binary decoder. It is deliberately not an ORM, has
no async, and one Connection is one PGconn, used from one thread, with
the caller writing the SQL. Pooling is opt-in and lives in one module of its
own β see Pooling. The motivating use is a
Postgres-backed Iceberg catalog (PyIceberg's SqlCatalog) for
iceberg.mojo, which needs exactly
this: connect, run parameterized SQL, read typed rows, and nothing more.
Every snippet below is real code, compiled against a throwaway test server β
see examples/ for the full, runnable files.
Connect, query by name or index (from examples/query_basic.mojo):
# By column name...
var res = conn.query("SELECT id, name, note FROM qb_people ORDER BY id")
for row in res:
var note = "NULL" if row.is_null("note") else row.text("note")
print(row.int64("id"), row.text("name"), note)
# ... and by 0-based index, which works the same on a `Row` obtained
# either way -- both ways resolve through the same column metadata.
var first = res.row(0)
print("by index:", first.int64(0), first.text(1))Params, a prepared statement, and NULL handling (from
examples/params_and_statements.mojo):
# Preparing pays for parsing and planning once; each `execute` after
# that sends only the name and the new parameter values.
var ins = conn.prepare(
"ins_event", "INSERT INTO ps_events VALUES ($1, $2, $3)"
)
for i in range(3):
_ = ins.execute(
Params()
.int64(Int64(i))
.float64(Float64(i) * 1.5)
.text("row-" + String(i))
)
# A NULL weight, so the read side has something to distinguish from 0.0.
_ = ins.execute(Params().int64(3).null().text("row-3"))
var res = conn.query("SELECT id, weight, tag FROM ps_events ORDER BY id")
for row in res:
# `is_null` first, then the typed accessor -- calling `float64` on a
# NULL cell raises rather than inventing a zero.
if row.is_null("weight"):
print(row.int64("id"), "weight=NULL", row.text("tag"))
else:
print(
row.int64("id"),
"weight=",
row.float64("weight"),
row.text("tag"),
)
# `opt_text` is the other way to read a nullable column: `None` for
# NULL, the text otherwise, no `is_null` check needed first.
var last = res.row(3)
var opt = last.opt_text("weight")
print("opt_text on the NULL row:", opt.value() if opt else "(NULL)")A transaction with a savepoint β leaving a with block without calling
commit() rolls back; there is no implicit commit anywhere in Transaction
(from examples/transactions.mojo):
# A savepoint is how a statement that fails inside a block is survived --
# PostgreSQL otherwise refuses everything until the block ends.
var tx = conn.begin()
_ = tx.execute("INSERT INTO tx_accounts VALUES ($1)", Params().int64(1))
tx.savepoint("maybe")
try:
_ = tx.execute("INSERT INTO tx_accounts VALUES ($1)", Params().int64(1))
except:
tx.rollback_to("maybe") # the duplicate is undone; row 1 stays
_ = tx.execute("INSERT INTO tx_accounts VALUES ($1)", Params().int64(2))
tx.commit()
# Leaving a `with` block WITHOUT calling commit() rolls back -- there is
# no implicit commit anywhere in `Transaction`.
with conn.begin() as forgotten:
_ = forgotten.execute(
"INSERT INTO tx_accounts VALUES ($1)", Params().int64(3)
)
# no commit() hereCOPY in with CopyEncoder, COPY out with rows() + decode_row
(from examples/copy_roundtrip.mojo):
var cp = conn.copy_in("COPY cp_rows (id, label) FROM STDIN")
var enc = CopyEncoder()
for i in range(1000):
enc.field(String(i))
enc.field("row-" + String(i))
enc.end_row()
if enc.size() > 1 << 14:
cp.write_rows(enc) # flush whole rows as they accumulate
cp.write_rows(enc)
print(cp.finish(), "rows loaded") # 1000
var out = conn.copy_out("COPY cp_rows TO STDOUT")
var lines = out.rows()
print(len(lines), "rows read back") # 1000
var first = decode_row(lines[0], COPY_TEXT, "\t", "\\N")
print("first row:", first[0].value(), first[1].value())Error handling β every SQLSTATE without parsing the message by hand
(from examples/errors.mojo):
try:
_ = conn.execute(
"INSERT INTO err_people VALUES ($1)", Params().int64(1)
)
except e:
# The SQLSTATE travels inside the message; sqlstate_of reads it back.
print("sqlstate_of(e) =", sqlstate_of(e))
if sqlstate_of(e) == "23505":
print("that id is already there")
# `last_error()` keeps the structured `PostgresError` -- severity,
# SQLSTATE, message, detail, hint, statement -- with predicates like
# `is_unique_violation` so no string parsing is needed.
if conn.last_error().is_unique_violation():
print("last_error confirms a unique violation:")
print(" ", conn.last_error().detail)| Type / function | Purpose |
|---|---|
Connection |
One PGconn. execute, query, prepare, begin, copy_in/copy_out, close. |
Statement |
A server-prepared statement from Connection.prepare; execute/query with new params. |
Transaction |
The guard from Connection.begin; rolls back on drop unless commit() ran. |
pool.ConnectionPool / pool.Lease |
The connection pool, for a threaded service. Not re-exported; from postgres.pool import .... |
CopyIn / CopyOut |
The COPY ... FROM/TO STDIN/STDOUT stream handles, from copy_in/copy_out. |
Result / Row |
A query's rows; Row is a snapshot that outlives the Result that produced it. |
Params |
A chainable builder for $1-style parameters, one typed method per type below. |
ConnectionConfig |
Typed connection fields, for callers who would rather not build a URI by hand. |
CopyEncoder / CopyDecoder / decode_row / split_rows |
The COPY text/CSV row codec, independent of libpq. |
PostgresError / sqlstate_of / sqlstate_class |
The structured error and the code constants (UNIQUE_VIOLATION, ...). |
is_unique_violation, is_deadlock, is_retryable, ... |
SQLSTATE predicates, as free functions and as PostgresError methods. |
Row's typed accessors do not check the column's OID β they parse the cell's
text as whatever type you asked for, and a mismatch surfaces as the codec's
own error. Params' builders bind the matching OID so the server needs no
cast in the SQL.
| PostgreSQL type | OID | Row accessor |
Params builder |
Mojo type |
|---|---|---|---|---|
bool |
16 | .bool(col) |
.bool(v) |
Bool |
int2 |
21 | .int16(col) |
.int16(v) |
Int16 |
int4 |
23 | .int32(col) |
.int32(v) |
Int32 |
int8 |
20 | .int64(col) |
.int64(v) |
Int64 |
float4 |
700 | .float32(col) |
.float32(v) |
Float32 |
float8 |
701 | .float64(col) |
.float64(v) |
Float64 |
numeric |
1700 | .numeric(col) |
.numeric(v) |
String |
text |
25 | .text(col) |
.text(v) |
String |
varchar |
1043 | .text(col) |
.text(v) |
String |
bpchar |
1042 | .text(col) |
.text(v) |
String |
bytea |
17 | .bytea(col) |
.bytea(v) |
List[UInt8] |
date |
1082 | .date_days(col) |
.date_days(v) |
Int32 (days since 1970-01-01) |
time |
1083 | .time_micros(col) |
.time_micros(v) |
Int64 (Β΅s since midnight) |
timestamp |
1114 | .timestamp_micros(col) |
.timestamp_micros(v) |
Int64 (Β΅s since epoch, no zone) |
timestamptz |
1184 | .timestamptz_micros(col) |
.timestamptz_micros(v) |
Int64 (UTC Β΅s since epoch) |
uuid |
2950 | .uuid(col) |
.uuid(v) |
String (canonical lower-case) |
json |
114 | .json(col) |
.json(v) |
String (stored verbatim) |
jsonb |
3802 | .json(col) |
.jsonb(v) |
String (server-normalized text) |
Notes:
numericstays aString. Coercing arbitrary-precision decimals toFloat64would silently lose digits;.numeric()hands back exactly the digits the server sent, trailing zeros of the column's declared scale included.- Dates and timestamps are epoch-relative integers, decoded with a
proleptic-Gregorian calendar (Howard Hinnant's
days_from_civil). Aninfinity/-infinitydate or timestamp, or aBCyear, has no encoding in that scheme and raises β readRow.text()for those instead. timestamptzis normalized to UTC. The server renders it in the session'sTimeZonewith an offset appended; the offset is applied on decode, so the result is the same instant regardless of session timezone.NaN/Infinity/-Infinitydecode to and encode from the corresponding IEEE-754 float values, case-insensitively on decode.
Every server error is raised as an Error formatted by PostgresError:
postgres [SQLSTATE 23505] duplicate key value violates unique constraint "people_pkey"
DETAIL: Key (id)=(1) already exists.
SQL: INSERT INTO people VALUES ($1, $2, $3)
Mojo 1.x can only raise a string, so the SQLSTATE travels inside the
message; sqlstate_of reads it back out, and Connection.last_error() keeps
the structured PostgresError β severity, SQLSTATE, message, detail, hint,
statement β with its is_unique_violation-style predicates. Two SQLSTATEs are
synthesized rather than sent by the server: 08001 for a Connection that
never came up, 08006 for one that was open and then closed or lost β both
match what class 08 means to every other PostgreSQL client. Committing a
transaction block that has already failed raises 25P02 rather than quietly
rolling back the way PostgreSQL itself would: Transaction.commit issues the
ROLLBACK itself first, so the connection is left clean, but the outcome is
reported instead of hidden.
A Statement, Transaction, CopyIn and CopyOut co-own the connection
they were made from β they hold a share of the same PGconn, not a borrow of
the Connection value β so the session stays open for as long as any of them
lives, even after the Connection that made them has gone out of scope
(Mojo destroys a value after its last use, so this is the ordinary case, not
an exotic one). Connection.close() is the exception: it ends the session
immediately, whoever else is still holding it, and every handle then raises
SQLSTATE 08006 on its next call rather than reaching a freed PGconn.
postgres.pool is a connection pool for a long-lived service: one pool for
the process lifetime, several threads leasing from it, and connections recycled
underneath so a failover is recovered from rather than held through. It is not
re-exported from postgres, because it pulls in
threads.mojo for its mutex and
condition variable and a single-threaded caller should not carry that.
from postgres import Params
from postgres.pool import ConnectionPool, PoolConfig
var pool = ConnectionPool(
"postgresql://localhost/app?connect_timeout=5",
PoolConfig(max_size=8, min_idle=1, acquire_timeout_ms=2000),
)
with pool.lease() as lease:
var res = lease.connection().query(
"SELECT id, name FROM t WHERE id = $1", Params().int64(1)
)
for row in res:
print(row.int64("id"), row.text("name"))
print(pool.stats()) # idle, busy, opened, recycled, discarded, escaped, waits, timeoutsA worker thread reaches the pool through pool.ref(), which is copyable and
travels in the context struct threads' parallel_for or TypedPool hands
around; every ref and every outstanding lease keeps the pool's state alive.
Read Ownership first: a Statement, Transaction, CopyIn or
CopyOut co-owns the PGconn, deliberately, so it can outlive the
Connection value. Pool that connection while such a handle is still alive and
it becomes two threads on one PGconn β which libpq forbids, and which
corrupts the protocol stream silently rather than crashing.
So the lease enforces the invariant rather than documenting it. When the with
block ends, Lease asks the connection how many things are holding it. One
means nothing escaped, and the connection is cleaned and pooled. More means a
handle outlived the block, and the connection is closed instead of pooled
and counted in stats().escaped; the escapee gets SQLSTATE 08006 from its
next call. The failure mode of losing track is "you lost a connection", never
"two threads share one" β the same shape as Transaction's promise that
losing track means "nothing happened", never "half of it happened".
That also settles transactions: a Transaction alive at the end of a lease
is a share, so a transaction cannot span two checkouts. It is enforced, not
assumed.
A Result is the one thing that may freely outlive its lease. It owns its
PGresult outright rather than sharing the connection, and libpq lets a result
outlive the connection it came from.
On return: ROLLBACK if PQtransactionStatus says a block is open β so a
lease cannot leak one into the next caller β and DISCARD ALL as well if
on_return asks for it. DISCARD ALL is opt-in because it is safer and
throws away the prepared statements a service reissuing the same queries is
paying to keep.
On checkout: most-recently-returned first, then aged out against
max_lifetime_ms and max_idle_ms and checked with Connection.is_alive().
A connection that fails is recycled in place with PQreset β same handle, new
session β and only closed outright if that fails too.
is_alive() is a non-blocking read of what the server has already sent: no
round trip, unlike ping(). It is deliberately not is_open(), which
cannot see this β PQstatus reports a cached opinion, and a backend killed
by pg_terminate_backend still reads CONNECTION_OK until a command fails on
it. No local check can be certain; what the pool guarantees is that a
known-dead connection is never handed out, and that one which died in a
caller's hands is discarded on return rather than pooled.
Idle connections age out when they are next picked up, and pool.reap() does
the same sweep on demand β closing anything past max_idle_ms, never below
min_idle, and reopening back up to min_idle. There is no reaper thread: a
thread inside a library owes its caller a lifecycle, and a detached thread
waiting on a condition variable that is about to be destroyed is precisely the
use-after-free this module exists to rule out. A service that must not pin
backends overnight already has a timer; reap() is one call on it.
Four benchmarks over (id int8, price numeric(12,4), qty int4, label text),
through the shared harness (bench.mojo):
| Benchmark | Rate | What it measures |
|---|---|---|
COPY ... FROM STDIN, 100k rows |
5.46 M rows/s (β167 MB/s) | The bulk load path |
SELECT, 100k rows scanned |
2.29 M rows/s | Decoding int8 + numeric + text per row |
Prepared INSERT, 10k round trips |
54.8 K rows/s | One parse/plan, many single-row round trips |
SELECT ... WHERE id = $1, 10k lookups |
34.0 K rows/s | The same round trip plus a parse and a plan each time |
pixi run -e bench benchMeasured on an Apple M4, PostgreSQL 18.4 on loopback with fsync=off and
synchronous_commit=off (scripts/pg-server.sh's throwaway cluster) β best
case for the client, since there is no network and no disk flush. These are
the baseline a future binary-format decision (spec Β§9) will be measured
against: text format's known cost is exactly what a numeric column in the
scan benchmark pays for, arriving as digits and staying a String.
pixi run test # unit + server + crosscheck + pool (Mojo 1.3.0.dev2026111805)
pixi run pool # just the pool suite and its negative control
pixi run examples # every examples/*.mojo file, against a live server-
Unit tests (
pixi run unit, no server) β config rendering, SQLSTATE parsing, the text-format codec (including a 4,321-day date sweep and everytimestamptzoffset spelling), theParamsbuilder, theCOPYencoder and decoder, and aPQlibVersion()call proving libpq loads, from$CONDA_PREFIXand again with it unset (through the binary's rpath, as a program run from a plain shell finds it). -
Server tests (
pixi run server, starts a throwaway cluster) β every type round-tripped throughParamsand decoded from a server-side cast, NULL vs. empty string, transactions and savepoints,COPYin both directions and both sub-formats, every error path, and ownership: aTransaction,StatementandCopyOuteach outliving theConnectionthey came from, andConnection.close()invalidating every handle type with SQLSTATE08006. -
The psql/psycopg cross-check (
tests/crosscheck.sh, part ofserver) β the same eighteen-column fixture (every type in the table above, a NULL row, an empty/zero/epoch row, and two edge rows covering int min/max,NaN/Infinity/-Infinity,0001-01-01/9999-12-31, non-UTCtimestamptzoffsets, and non-ASCII/tab/newline/quote/backslash text) written by this tin (both theParamspath and theCopyInpath) and read back cell-for-cell by psycopg 3; and the mirror image, written by psycopg with native Python types and read back byRow's typed accessors, cross-checked a third way againstpsql ... COPY ... TO STDOUTof the same table. -
Pool tests (
pixi run pool) β concurrent checkout from twelve threads over a four-connection pool, each lease asserting that the session marker it set is still its own when it reads it back (a sharedPGconnfails that deterministically); exhaustion raising53300onacquire_timeout_msand recovering when a lease is released; a pooled backend killed withpg_terminate_backendand the pool recycling it in place rather than handing back a dead socket; a lease that leaves a transaction open being rolled back before the next checkout sees it; and escape detection for both aStatementand aTransactionoutliving their lease; and a lease outliving the pool value β the ordinary case, since Mojo destroys a value at its last use β run a hundred times over, because what it guards against is a silent few-byte write into freed heap that surfaces as a crash somewhere else entirely. Plus a negative control:tests/run_pool.shrebuilds the escape assertion against a copy of the pool with the refcount check replaced by a constant, and fails unless that build fails the assertion. An escape that goes undetected is a silent race rather than a visible error, so the one check standing between the pool and that outcome is shown to have teeth rather than merely to be green.
The server suite, the cross-check and the pool suite each start a throwaway
PostgreSQL cluster from the conda postgresql package on a free port and stop
it on exit. No Docker.
ThreadSanitizer is deliberately not used on the pool suite: the Mojo 1.0.0
runtime allocator is invisible to TSan's intercepts, so any allocating threaded
Mojo program reports races that are not there. test_repeated_concurrent_bursts_stay_correct
β the same contended workload run five times over one pool β is what stands in
for it.
In scope: connections, $n parameters, prepared statements, typed text-format
results, transactions with savepoints, COPY (text and CSV), and SQLSTATE on
every error.
Also in scope, in postgres.pool and nowhere else: connection pooling for a
threaded service β see Pooling.
Out of scope: an ORM or query builder, async,
LISTEN/NOTIFY, and binary-format results or parameters (Β§9's open
question β the Performance numbers above are the baseline it
will be measured against). A NOTICE/WARNING the server raises is printed
to stderr by libpq's default handler, which this tin installs no override
for; pass options=-c client_min_messages=warning in the conninfo to quiet
it.
- M1 connect and query:
Connection, text results by index and name, errors with SQLSTATE - M2 parameters and statements:
PQexecParams, prepared statements, theParamsbuilder, the full type table - M3 transactions and
COPY:Transactionwith savepoints and rollback on drop,CopyIn/CopyOut - M4 a Postgres-backed Iceberg catalog, in iceberg.mojo
Apache-2.0 β see LICENSE and NOTICE. libpq itself is under the PostgreSQL License and is loaded at runtime, not bundled.