A user asked for a PostgreSQL tin. This is the spec.
**Recommendation: build it…, on libpq via FFI**, following the `zstd.mojo` shim recipe. The one existing Mojo attempt is pre-1.0 and cannot express parameterized queries, so there is no shortcut worth taking. Scope it to a *client*: connections, parameterized statements, typed results, transactions and COPY. No ORM, no async, no pooling in v1.
> Also published as a formatted page: https://claude.ai/code/artifact/68772e43-8a81-4172-a4a5-9f8f9bc2433f
---
## 1. Why this tin
Two independent reasons, and only the first is a user request.
**Someone asked for it.** Postgres is the default database of most data teams, and Mojo currently has no way to talk to one that works on the 1.x toolchain. That is reason enough.
**It completes the Iceberg SQL catalog.** `iceberg.mojo` is gaining a SQL catalog on top of `sqlite.mojo` for local development and PyIceberg test parity. The same catalog schema — `iceberg_tables` and `iceberg_namespace_properties` — is what Java's `JdbcCatalog` and PyIceberg's `SqlCatalog` use in *production*, where the backing store is Postgres, not SQLite. A Postgres tin turns a development convenience into a deployable catalog.
What it is **not**: required to connect to Iceberg. REST catalogs cover the managed world, and `iceberg.mojo` already speaks REST.
## 2. Prior art
[dvirarad/mojo-postgres](https://github.com/dvirarad/mojo-postgres) (Apache-2.0, 609 lines) already binds libpq. It deserves credit and a read, but cannot be adopted as-is:
| Aspect | Prior art | Consequence |
|---|---|---|
| Toolchain | `mojo == 0.25.7.0` | Pre-1.0. Will not build on stable 1.0.0 or nightly; the FFI and pointer APIs have all moved. |
| Query interface | `PQexec` + `PQescapeLiteral` | **No parameterized queries.** Values are escaped into SQL text — the API shape that invites injection and forbids binary binds. |
| Coverage | 22 libpq symbols | No `PQexecParams`, `PQprepare`, COPY, or SQLSTATE access. |
| Packaging | No `shelf.toml` | Not a tin; not installable from the registry. |
So: a fresh implementation, crediting the prior art in the README and borrowing its symbol list as a starting inventory. **If its author is interested, upstreaming would be better than a second binding — worth an issue before starting.**
## 3. Scope
**In, for v1**
- Connect by URI or keyword string; TLS through libpq's own `sslmode`
- Parameterized queries (`PQexecParams`) and named prepared statements
- Typed result access by column, with NULL distinct from empty
- Explicit transactions, plus savepoints
- `COPY` in and out — bulk load is the reason a data stack wants Postgres
- Errors carrying SQLSTATE, not just a message
**Out, and why**
- **ORM / reflection** — same call as `sqlite.mojo`: drags in a dependency and a design argument
- **Async / non-blocking** — libpq supports it, Mojo has no async runtime to drive it
- **Connection pooling** — revisit once `threads.mojo` proves out under load
- **LISTEN/NOTIFY** — needs a polling loop; defer until something wants it
- **Binary result format** — see §5; text first, measure, then decide
## 4. Architecture
Identical in shape to `zstd.mojo`, `lz4.mojo` and `objectstore.mojo`, because that recipe is now proven three times:
- **conda `libpq`** (18.6 is current in conda-forge) as an ordinary dependency — no system Postgres required, and it brings its own OpenSSL.
- **A thin C shim** built by `pixi-build-cmake` into `$CONDA_PREFIX/lib/libpostgresmojo.{so,dylib}`. It exists mainly for `PQexecParams`, whose signature takes four parallel arrays (`paramValues`, `paramLengths`, `paramFormats`, `paramTypes`); marshalling those from Mojo is far cleaner behind one fixed-arity call than inline.
- **Opened once, process-wide.** A `_Global` handle, never a `dlopen` per call — that mistake cost `zstd.mojo` roughly 450 µs per call and stayed invisible until a compressed workload multiplied it. The shim self-pins with `RTLD_NODELETE` so Mojo's per-call handle teardown cannot unload it.
## 5. Types, and the format decision
libpq returns every value either as text or in a binary wire format, chosen per query. **v1 should use text format**, and the spec says so out loud rather than leaving it implicit.
Text is a null-terminated string per cell; the tin parses it against the column's type OID from `PQftype`. That is one code path, no endianness, no per-version wire layouts, and it is what `psql` itself displays. Binary avoids a parse but requires a decoder per OID in network byte order, and Postgres reserves the right to change those layouts between major versions. Ship text, benchmark a wide numeric scan, and only then decide whether binary earns its complexity.
| Postgres | OID | Mojo | Note |
|---|---|---|---|
| `bool` | 16 | `Bool` | text is `t` / `f` |
| `int2` / `int4` / `int8` | 21 / 23 / 20 | `Int16` / `Int32` / `Int64` | |
| `float4` / `float8` | 700 / 701 | `Float32` / `Float64` | watch `NaN`, `Infinity` spellings |
| `numeric` | 1700 | `String` | arbitrary precision; **do not silently coerce to float** |
| `text` / `varchar` / `bpchar` | 25 / 1043 / 1042 | `String` | |
| `bytea` | 17 | `List[UInt8]` | text format is hex-escaped; use `PQunescapeBytea` |
| `date` / `timestamp` / `timestamptz` | 1082 / 1114 / 1184 | `Int64` or `String` | expose the raw ISO string too; epoch conversion is lossy for infinity |
| `uuid` | 2950 | `String` | |
| `json` / `jsonb` | 114 / 3802 | `String` | parsing belongs to the caller |
## 6. API sketch
Shaped to match `sqlite.mojo` where the concepts line up, so someone who has used one can read the other.
```mojo
from postgres import Connection, Params
var conn = Connection.connect("postgresql://user@localhost/app") # or keyword string
conn.ping() # PQstatus
conn.server_version() # PQserverVersion
# One-shot, parameterized. $1-style placeholders, never string interpolation.
var rows = conn.query("SELECT id, email FROM users WHERE tier = $1 AND active",
Params().text("gold"))
for r in rows:
var id = r.int64("id") # by name or index
var mail = r.text("email")
if r.is_null("email"): ...
# Statements you run repeatedly
var stmt = conn.prepare("insert_user", "INSERT INTO users(email, tier) VALUES ($1, $2)")
stmt.execute(Params().text(addr).text("free"))
# Transactions
var tx = conn.begin()
tx.execute("UPDATE accounts SET balance = balance - $1 WHERE id = $2",
Params().int64(100).int64(7))
tx.commit() # or tx.rollback(); dropping without commit rolls back
# Bulk load -- the reason a data stack reaches for Postgres
var copy = conn.copy_in("COPY events (ts, kind, payload) FROM STDIN WITH (FORMAT csv)")
copy.write(chunk) # PQputCopyData, chunked
copy.finish() # PQputCopyEnd; raises with SQLSTATE on failure
```
Errors raise with the message, the SQLSTATE code from `PQresultErrorField`, and the failing SQL. **SQLSTATE matters**: callers need to distinguish `23505` unique-violation from `40001` serialization-failure to retry correctly, and a message string cannot be matched on safely.
## 7. Testing against a real server
The interesting problem, and the one that decides whether CI is trustworthy. Conda-forge ships the full `postgresql` package (18.6), server binaries included, so CI needs no Docker and no service container — which matters because GitHub's macOS runners do not support service containers at all.
```bash
initdb -D "$WORK/pg" --auth=trust --username=postgres
pg_ctl -D "$WORK/pg" -o "-p 55432 -k $WORK/pg -c listen_addresses=" -l "$WORK/pg.log" start
# ... run tests against the unix socket in $WORK/pg
pg_ctl -D "$WORK/pg" stop
```
Same pattern `objectstore.mojo` uses for MinIO, and it runs identically on both runner families. Coverage should include: every type in §5 round-tripping including NULL and empty-vs-null; a unique violation surfacing SQLSTATE `23505`; a transaction rolled back and observably absent; a prepared statement reused; `COPY` of 100k rows with a throughput number for the README; a connection failure raising promptly rather than hanging.
**Cross-check against real clients**, the way every other magmalake tin is gated: write rows with this tin and read them with `psql` and with Python's `psycopg`; then write with `psycopg` and read back here. Values must match cell for cell.
## 8. Verification gates
- [ ] Tests green on **stable 1.0.0 and nightly**, ubuntu-latest and macos-latest — four legs, `pixi-version: v0.71.1` pinned
- [ ] `pixi build` succeeds and emits a real `.mojopkg`
- [ ] **In a throwaway project, `pixi shelf add postgres-mojo` then `pixi install` both succeed.** Stricter than `pixi build`, and what the registry's daily smoke test runs — two separate packaging defects reached the registry before this check was adopted
- [ ] Any git-rev pin matches what `shelf info` publishes, never local HEAD
## 9. Milestones
- [ ] **M1 — Connect and query.** Shim, `_Global` handle, `Connection`, `PQexec`, text results by index and name, errors with SQLSTATE. Reaches parity with the prior art, on a toolchain that exists.
- [ ] **M2 — Parameters and statements.** `PQexecParams`, `PQprepare`/`PQexecPrepared`, the `Params` builder, the full §5 type table. This is the milestone that makes the tin safe to hand to anyone.
- [ ] **M3 — Transactions and COPY.** Explicit transactions with savepoints; `COPY` in and out with chunked IO and a published throughput figure.
- [ ] **M4 — Postgres-backed Iceberg catalog.** Lands in `iceberg.mojo`, not here: point the existing SQL catalog at Postgres, and verify a catalog written by PyIceberg's `SqlCatalog` over Postgres loads identically.
## 10. Risks and open questions
- **Is conda's libpq built with SSL?** Check before promising TLS. If not, `sslmode=require` fails at runtime and the fix is a different package or a build feature flag.
- **Server startup in CI is the flaky part**, not the binding. Budget a readiness loop on `pg_isready` rather than a fixed sleep, and cap it.
- **Text format may disappoint on wide numeric scans.** That is fine — but measure it in M3 rather than assuming, and record the number so the binary-format decision has evidence behind it.
- **Talk to the prior author first.** An issue on `dvirarad/mojo-postgres` offering to help modernise it may be a better outcome for the ecosystem than a second binding, and costs one message to find out.
