Pgxcrown is a modern framework and toolchain for building compiled, native PostgreSQL C dynamic libraries (.so / .dll) using Nim. It combines Nim's expressive syntax, deterministic ARC/ORC memory management, and zero-overhead C code generation with PostgreSQL's low-level engine internals (postgres.h, fmgr.h, executor/spi.h).
- Zero-Copy Memory-Mapped Views: Direct pointer-slice
PgText(zero allocation, zerostrlen) and SIMD-alignedPgVector[T]for high-throughput vector and array processing. - PostgreSQL-Integrated Allocator (
0 Leaks on Abort): Dynamic allocations (seq,string,ref object) route transparently to PostgreSQL'sMemoryContext(palloc). If a query is aborted or cancelled (siglongjmp), 100% of memory is reclaimed instantly. - Fail-Safe Panic Shield (
0 SIGABRTs): Compiles automaticPG_TRYboundaries into every UDF—intercepting panics, overflows, and defects to safely abort transactions without crashing the PostgreSQL backend process. - Advanced SQLSTATE Error Mapping: First-class typed exceptions (
PgErrorandPostgresError) with automatic mapping to standard PostgreSQL error codes (ERRCODE_DATA_EXCEPTION,ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE, etc.). - Fluent In-Database SPI Engine: Ergonomic, left-to-right query execution (
.scalar(),.first(),.all(),.rows(),.run()) with multi-columnRETURNINGsupport and zerodiscardboilerplate. - Universal Objects & Composite Types: Pure Nim
objecttypes automatically generateCREATE TYPE "Name" AS (...)DDL with bidirectional binary marshaling (tupleHeaderToObject,objectToDatum). - Declarative Custom Base Types: Declarative pragmas (
{.pgxType.},{.pgxInput.},{.pgxOutput.}) supporting 15 flat scalar types with type-specific parsing and automatic DDL generation. - Smart Incremental Toolchain (
pgxtool): Sub-15ms incremental builds via SHA-256 source manifests, isolated.pgx_build/nimcache, and ephemeral Docker container testing across PostgreSQL 14–17 with golden snapshot diffing (--bless). - Autonomous Background Workers (
pgxWorker): In-database daemons with sub-1ms dynamic launch (bgw_notify_pid), zero-lock live observability (workerActivityinpg_stat_activity), nested subtransactions (workerSavepoint), automatic catalog tracking (pgxcrown_workers), and graceful signal shutdown (onShutdown). - Compile-Time Effect Verification: Static enforcement of SQL volatility (
{.immutable.},{.stable.},{.volatile.}) preventing illegal database writes at compile time.
Option A — Docker-only standalone binary (no Nim or host PostgreSQL headers needed):
# Linux x86_64 (static binary)
mkdir -p ~/.local/bin
curl -fL https://github.com/luisacosta828/pgxcrown/releases/latest/download/pgxtool-linux-amd64 -o ~/.local/bin/pgxtool
chmod +x ~/.local/bin/pgxtool
# macOS Apple Silicon (arm64)
# curl -fL https://github.com/luisacosta828/pgxcrown/releases/latest/download/pgxtool-darwin-arm64 -o /usr/local/bin/pgxtool && chmod +x /usr/local/bin/pgxtoolOption B — Full host toolchain via Nimble (for local build-extension + Docker):
nimble install -y pgxcrownpgxtool init
pgxtool create-project analytics
pgxtool test analytics --pg 17 # compiles inside GHCR & runs SQL regression tests!import pgxcrown
import std/[options, json]
type
UserStats* = object
userId*: int32
username*: string
score*: float64
verified*: bool
# Pure calculation: Zero-copy text view, IMMUTABLE & PARALLEL SAFE
proc format_badge*(name: PgText, score: float64): string {.immutable, parallelSafe.} =
if score >= 90.0:
return "⭐ Gold: " & $name
return "Bronze: " & $name
# In-database SPI query: STABLE
proc get_top_stats*(minScore: float64): seq[UserStats] {.stable.} =
let u = table("users", "u")
let q = Select(u.id as "userId", u.username, u.score, u.active as "verified")
.From(u)
.Where(u.score >= minScore and u.active == true)
.OrderBy(u.score.desc)
.Limit(25)
return q.fetch(UserStats)# Run regression tests in an ephemeral PostgreSQL 16 sandbox (or pick 14/15/17)
pgxtool test analytics
pgxtool test analytics --pg 17
# Run directly inside the extension directory (CWD mode) or via explicit path
(cd analytics && pgxtool test --pg 14)
pgxtool test ./analytics --pg 15 --bless
# Or test across the entire multi-version matrix (14, 15, 16, 17)
pgxtool test analytics --all
# Force pulling/using the official GHCR image repository explicitly
PGXCROWN_IMAGE=ghcr.io/luisacosta828/pgxcrown pgxtool test analytics --pg 17Note: When installed via
nimble install -y pgxcrownoutside a git checkout,pgxtool testautomatically pullsghcr.io/luisacosta828/pgxcrown:<pg>. Inside a repository checkout, it reuses localpgxcrown:<pg>/ GHCR images or buildsdocker/Dockerfileautomatically if missing.
# Compile native .so shared library with sub-15ms incremental cache
pgxtool build-extension analytics
# Install into PostgreSQL system directories
sudo ./analytics/src/install.shTraditional C UDFs frequently allocate memory or invoke strlen to construct Nim strings from PostgreSQL varlena text headers. PgText maps PostgreSQL text, varchar, and bpchar data directly as a zero-copy pointer slice:
import pgxcrown
import std/strutils
proc fast_contains*(haystack: PgText, needle: string): bool {.immutable, parallelSafe.} =
# Zero-copy slicing, zero allocations, direct memcmp:
return haystack.contains(needle)
proc clean_slug*(input: PgText): string {.immutable, parallelSafe.} =
# Seamlessly interoperates with std/strutils via implicit converter:
return input.strip().toLowerAscii().replace(" ", "-")- Zero Memory Allocations: Direct access to PostgreSQL's detoasted varlena payload.
- Standard Library Interoperability: Implicit conversion to
stringandtoOpenArraycompatibility withstd/strutils,std/hashes, and tables. - Fast Return Path: Returning
PgTextconverts directly back to PostgreSQLDatumvia zero-copy varlena wrappers.
PgVector[T] provides direct, zero-copy read access to PostgreSQL 1D arrays (int4[], float8[], text[], etc.) without copying elements into intermediate heap sequences:
import pgxcrown
proc vector_dot_product*(a, b: PgVector[float64]): float64 {.immutable, parallelSafe.} =
if a.len != b.len:
raisePgError("ERRCODE_CARDINALITY_VIOLATION", "Vector dimensions must match")
var sum = 0.0
for i in 0 ..< a.len:
sum += a[i] * b[i]
return sum- Zero Allocation: Operates directly over PostgreSQL's contiguous array buffer.
- Bound-Checked Indexing: Safe indexing (
[]), slicing ([a..b]), and iterators (items,pairs). openArrayInterop: Direct compatibility with high-performance linear algebra routines.
Define standard Nim object types, and Pgxcrown automatically produces the corresponding CREATE TYPE "Name" AS (...) DDL with bidirectional binary serialization:
import pgxcrown
import std/[options, json]
type
Person* = object
id*: int32
name*: string
score*: float64
active*: bool
metadata*: JsonNode
# Receives a composite type, modifies it, and returns it
proc update_score*(p: Person, bonus: float64): Person {.immutable, parallelSafe.} =
result = p
result.score = p.score + bonusAuto-Generated PostgreSQL DDL:
CREATE TYPE "Person" AS (
"id" int4,
"name" Text,
"score" float8,
"active" boolean,
"metadata" jsonb
);
CREATE OR REPLACE FUNCTION update_score("Person", float8) RETURNS "Person"
AS 'analytics', 'pgx_update_score'
LANGUAGE c IMMUTABLE PARALLEL SAFE STRICT;Pgxcrown wraps all UDFs with an automated PG_TRY / PG_CATCH safety boundary. Unhandled defects and panics (integer overflows, nil dereferences, out-of-bounds access) are intercepted and converted into clean PostgreSQL transaction aborts (0 SIGABRTs):
import pgxcrown
proc transfer_credits*(userId: int32, amount: int64): bool {.volatile.} =
if amount <= 0:
raisePgError(ERRCODE_INVALID_PARAMETER_VALUE, "Credit transfer amount must be strictly positive")
if amount > 1_000_000:
raisePgError(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE, "Credit limit exceeded for single transaction")
# Business logic...
return true| Exception Type | PostgreSQL SQLSTATE | SQL Error Name |
|---|---|---|
OverflowDefect |
22003 |
ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE |
DivByZeroDefect |
22012 |
ERRCODE_DIVISION_BY_ZERO |
IndexDefect |
2202E |
ERRCODE_ARRAY_SUBSCRIPT_ERROR |
ValueError |
22023 |
ERRCODE_INVALID_PARAMETER_VALUE |
KeyError |
P0002 |
ERRCODE_NO_DATA_FOUND |
PgError |
User Defined | Custom SQLSTATE (e.g. 23505, 22001) |
Pgxcrown seamlessly couples Nim's ARC/ORC runtime with PostgreSQL's hierarchical MemoryContext system via GNU ld linker wrapping (--wrap=malloc,free,...). All standard heap allocations (string, seq[T], ref object) born inside an extension transparently allocate inside PostgreSQL's active CurrentMemoryContext.
When PostgreSQL aborts a query (via statement_timeout, client disconnection, or pg_cancel_backend()), it executes a hardware-level siglongjmp back to the main query dispatcher. This skips standard language destructors. With Pgxcrown's custom allocator, PostgreSQL's AtError_Memory() automatically sweeps and reclaims 100% of Nim's memory in
Write standard, idiomatic Nim code. You don't manage pointers, call palloc, or manually free anything:
import pgxcrown
proc parse_tags*(raw: PgText): seq[string] =
# All allocations (seq, strings, split) born in CurrentMemoryContext
var tags = newSeq[string]()
for item in raw.toString().split(','):
tags.add("tag: " & item.strip())
return tags
# Upon query completion or abort, memory is reclaimed automatically by PostgreSQL.Per-tuple or per-query memory contexts are reset after each row or statement. To store lookup tables, ML models, or configurations that must survive across queries for the entire duration of the client connection, use withTopMemoryContext:
import pgxcrown
import pgxcrown/memcontext
import std/tables
var gConfigCache {.global.}: Table[string, string]
var gInitialized {.global.} = false
proc get_setting*(key: PgText): PgText =
if not gInitialized:
# Memory allocated within this block lives in TopMemoryContext:
withTopMemoryContext:
gConfigCache = initTable[string, string]()
gConfigCache["timeout_ms"] = "5000"
gConfigCache["region"] = "us-east-1"
gInitialized = true
return returnPgText(gConfigCache.getOrDefault(key.toString(), "not_found"))When processing large payloads (e.g. parsing a 100MB JSON document), allocating millions of temporary strings inside the query's main context can cause transient memory spikes. Use withSubContext to isolate the scratchpad:
import pgxcrown
import pgxcrown/memcontext
proc extract_summary*(hugePayload: PgText): int32 =
var resultCount = 0
# Creates an isolated child memory context, executes body, and deletes it in finally:
withSubContext("bulk_parser_ctx"):
let parsedNodes = parseHeavyData(hugePayload.toString())
resultCount = parsedNodes.len
# Child context is destroyed here—RAM returns to baseline BEFORE query finishes!
return resultCount.int32Execute in-database SQL operations via PostgreSQL's Server Programming Interface (SPI) with zero network latency and fluent left-to-right method chaining:
import pgxcrown
proc get_department_summary*(minSalary: int32 = 60000): string =
let e = table("employees", "e")
let d = table("departments", "d")
let deptStats = Select(e.dept_id, avg(e.salary) as "avg_sal")
.From(e)
.GroupBy(e.dept_id)
let q = WithCte("dept_stats", deptStats)
.Select(
e.id as "emp_id",
e.name as "emp_name",
d.name as "dept_name",
caseWhen(e.salary >= 100000).then("Senior").elseEnd("Associate") as "tier",
rowNumber().over(partitionBy = e.dept_id, orderBy = e.salary.desc) as "rank"
)
.From(e)
.InnerJoin(d).On(e.dept_id == d.id)
.Where(e.status == "active" and e.salary >= minSalary)
.OrderBy(e.salary.desc.nullsLast)
.Limit(25)
return $q# 1. Clean DML Execution (No 'discard' required!)
InsertInto("users", "name", "score").Values("'luis'", "98.5").run()
# 2. DML with RETURNING (Scalar or Entity)
let newId = InsertInto("users", "name").Values("'ada'").Returning("id").scalar(int32)
# 3. Typed Reading (.scalar, .first, .all, .rows)
let userCount = Select(count(u.id)).From(u).scalar(int)
let topUsers = Select(u.id, u.username, u.score).From(u).OrderBy(u.score.desc).all(UserStats)Deploy persistent or dynamic background processes directly inside PostgreSQL using pure Nim—with zero Datum leaks, instant startup, real-time DBA observablity, and failure isolation:
- Declarative DSL (
bgWorker): Configure entrypoints, target databases, and restart policies without handling raw C structs. - Instant (~1ms) Dynamic Startup: Module-level
MyProcPidbinding ensures instantSIGUSR1wake-up from the Postmaster. - Zero-Lock Live Observability (
workerActivity): Reports real-time status intopg_stat_activity(queryandstatecolumns) without table locks or performance degradation. - Subtransactions & Savepoints (
workerSavepoint): Isolate per-record failures during batch runs without aborting the parent transaction. - Catalog Tracking & Lifecycle SQL: Automatically registers PID and status in
pgxcrown_workersand exposesstart_<name>_worker(),stop_<name>_worker(), and<name>_worker_status()functions to SQL. - Graceful Shutdown (
onShutdown): Cleanly interceptsSIGTERMand auto-reconciles the catalog before process exit.
import pgxcrown
type
OrderRecord* = object
id*: int
customer*: string
amount*: float
status*: string
# Background Worker Daemon Entrypoint
proc order_archiver_worker*() {.pgxWorker.} =
workerInit("postgres")
let orders = table("orders")
let vencidos = table("vencidos")
runWorkerLoop(intervalMs = 5000):
workerActivity("Checking expired orders"):
workerTransaction:
# Type-safe CTE pipeline
let expired = WithCte("vencidos",
Select(orders.id, orders.customer, orders.amount, orders.status)
.From(orders)
.Where(orders.status == "vencido")
)
.Select(vencidos.id, vencidos.customer, vencidos.amount, vencidos.status)
.From(vencidos)
.all(OrderRecord)
if expired.len > 0:
workerActivity("Archiving " & $expired.len & " expired orders"):
for exp in expired:
let order = exp
# Subtransaction savepoint: errors in one record do not fail the entire batch!
workerSavepoint:
InsertInto("orders_history", "id", "customer", "amount", "status")
.Values($order.id, quoteLiteral(order.customer), $order.amount, quoteLiteral(order.status))
.run()
DeleteFrom("orders").Where(orders.id == order.id).run()
do:
pgWarning("Failed to archive order " & $order.id & ": " & err.msg)
do:
pgNotice("OrderArchiver worker cleanly shut down.")
# SQL Launcher Function (dynamically starts worker)
proc start_order_archiver_worker*(): int32 {.volatile.} =
let cfg = bgWorker("order_archiver"):
database "postgres"
restart 30
let handle = registerDynamicWorker(cfg)
if handle.isSome:
let (status, pid) = handle.get.waitForStartup()
if status == bgwStarted:
trackWorkerStarted("order_archiver", pid)
return pid.int32
return -1'i32
# SQL Graceful Stop Function
proc stop_order_archiver_worker*(): bool {.volatile.} =
return stopWorkerByName("order_archiver")
# SQL Live Status Function
proc order_archiver_worker_status*(): string {.volatile.} =
return getWorkerStatusByName("order_archiver")-- Start worker and retrieve PID:
SELECT start_order_archiver_worker();
-- Live observability in pg_stat_activity:
SELECT pid, state, query, wait_event
FROM pg_stat_activity
WHERE backend_type = 'order_archiver';
-- Query catalog status:
SELECT * FROM pgxcrown_workers;
SELECT order_archiver_worker_status(); -- Returns 'running' or 'stopped'
-- Stop worker cleanly via SQL:
SELECT stop_order_archiver_worker();pgxtool automates the entire PostgreSQL extension lifecycle:
| Command | Usage | Description |
|---|---|---|
init |
pgxtool init |
Initializes the local extension workspace directory (~/.pgxtool). |
create-project |
pgxtool create-project <name> |
Scaffolds a new extension project directory, main.nim, and test templates. |
create-worker |
pgxtool create-worker <name> |
Scaffolds an autonomous background worker daemon with dynamic launcher and lifecycle functions. |
build-extension |
pgxtool build-extension <name> [options] |
Compiles Nim to .so shared library, runs security audit, and generates DDL. |
--clean |
Cleans previous build artifacts and cache before compiling. | |
--force (-f) |
Ignores incremental cache and forces complete recompilation. | |
--verbose (-v) |
Displays verbose compiler invocations and compilation flags. | |
--debug (-d) |
Enables pgxDebug compile-time AST and DDL debug output. |
|
clean |
pgxtool clean <name> |
Removes build cache (.pgx_build) and generated artifacts (.so, .sql, .control). |
install |
pgxtool install <name> |
Automatically copies .so, .control, and .sql to PostgreSQL directories. |
create-type |
pgxtool create-type <name> --base-type <type> |
Generates a custom distinct base type with dynamic parsing for 15 scalar types. |
create-hook |
pgxtool create-hook <name> |
Scaffolds a Postgres kernel hook template (emit_log, post_parse_analyze). |
test |
pgxtool test [project|path] [--pg <v>] [--all] [--local] [--bless] [--debug] |
Runs SQL regression tests in Docker/GHCR sandboxes (PG 14–17) or natively inside a container (--local). |
version |
pgxtool --version |
Prints the installed pgxtool / pgxcrown version. |
| Nim Type | PostgreSQL Type | Generated SQL DDL | Zero-Copy Support |
|---|---|---|---|
PgText |
TEXT / VARCHAR |
Text |
✅ Yes |
PgVector[T] |
T[] |
int4[], float8[], text[] |
✅ Yes |
string / cstring |
TEXT |
Text / cstring |
Transparent |
int32 / int |
INTEGER |
int4 |
Native |
int64 |
BIGINT |
int8 |
Native |
int16 |
SMALLINT |
int2 |
Native |
float64 / float |
DOUBLE PRECISION |
float8 |
Native |
float32 |
REAL |
float4 |
Native |
bool |
BOOLEAN |
boolean |
Native |
JsonNode |
JSONB |
jsonb |
Native Binary FFI |
Option[T] |
Nullable Type | type DEFAULT NULL |
Native |
seq[T] (Return) |
SETOF T |
RETURNS SETOF <type> |
Streamed |
type T = distinct Base |
Domain Type (pgxType) |
CREATE TYPE "T" (...) |
Native |
type T = object |
Composite Type | CREATE TYPE "T" AS (...) |
Binary Serialized |
type T = enum |
ENUM Type | CREATE TYPE "T" AS ENUM (...) |
Native |
Prebuilt all-in-one container images are published to GitHub Container Registry (ghcr.io/luisacosta828/pgxcrown, linux/amd64). Each image bundles official postgres:<ver>, postgresql-server-dev-<ver>, gcc, Nim 2.4.0 + Nimble, and pgxcrown (pgxtool) so engineers and CI runners can compile, install, and test C/Nim PostgreSQL extensions with zero host PostgreSQL setup.
| PostgreSQL Version | Rolling Major Tag | Pinned Release Tag |
|---|---|---|
| PostgreSQL 17 | ghcr.io/luisacosta828/pgxcrown:17 / :latest |
ghcr.io/luisacosta828/pgxcrown:17-0.27.2 |
| PostgreSQL 16 | ghcr.io/luisacosta828/pgxcrown:16 |
ghcr.io/luisacosta828/pgxcrown:16-0.27.2 |
| PostgreSQL 15 | ghcr.io/luisacosta828/pgxcrown:15 |
ghcr.io/luisacosta828/pgxcrown:15-0.27.2 |
| PostgreSQL 14 | ghcr.io/luisacosta828/pgxcrown:14 |
ghcr.io/luisacosta828/pgxcrown:14-0.27.2 |
# 1) Pull any version directly from GHCR
docker pull ghcr.io/luisacosta828/pgxcrown:17
# 2) Compile inside an ephemeral GHCR container (zero host PG/Nim toolchain needed)
docker run --rm -v "$PWD":/workspace -w /workspace \
ghcr.io/luisacosta828/pgxcrown:17 pgxtool build-extension analytics
# 3) Start a PostgreSQL 17 dev container and compile + install + run SQL tests (--local)
docker run -d --name pgxcrown-17 -e POSTGRES_PASSWORD=postgres -p 5417:5432 \
-v "$PWD":/workspace -w /workspace ghcr.io/luisacosta828/pgxcrown:17
docker exec -it pgxcrown-17 pgxtool test analytics --local
# 4) Refresh pgxcrown inside an existing container image when a newer release ships
nimble refresh && nimble install -y pgxcrown- If
PGXCROWN_IMAGEis set (e.g.PGXCROWN_IMAGE=ghcr.io/luisacosta828/pgxcrown),pgxtool testpulls and runs<PGXCROWN_IMAGE>:<pg>. - Otherwise, if local
pgxcrown:<pg>or cachedghcr.io/luisacosta828/pgxcrown:<pg>already exists locally, it reuses it immediately. - Inside a repository checkout containing
docker/Dockerfile, missing images are built locally aspgxcrown:<pg>; outside a checkout (e.g.nimble install -y pgxcrown),pgxtoolautomatically pullsghcr.io/luisacosta828/pgxcrown:<pg>.
You can also build images locally with ./docker/build.sh or orchestrate all four PostgreSQL versions via docker/compose.yaml. See docker/README.md for the complete container reference.
-
Operating System: Linux (
x86_64,aarch64), macOS. (Windows: use WSL2 or Docker). -
Docker-Only Mode (Zero Host Setup): Only Docker + standalone
pgxtoolbinary from Releases (pgxtool init/create-project/testautomatically pullghcr.io/luisacosta828/pgxcrown:{14,15,16,17}). -
Host Native Compilation (Optional): PostgreSQL
14–17(postgresql-server-dev-*/pg_config), Nim Compiler$\ge 2.0.0$ , and GCC/Clang.
MIT License © 2026 Luis Acosta