pgxcrown

nimble install pgxcrown

pgxtool init
pgxtool create-project my_extension

import pgxcrown
import std/[options, json]

type
  User* = object
    id*: int32
    username*: string
    score*: float64
    active*: bool
    profile*: JsonNode

# Pure calculation: IMMUTABLE & PARALLEL SAFE
proc make_user*(id: int32, name: string, score: float64): User {.immutable, parallelSafe.} =
  return User(
    id: id,
    username: name,
    score: score,
    active: true,
    profile: %*{"tier": "gold", "verified": true}
  )

# In-database query: STABLE
proc get_top_users*(minScore: float64): seq[User] {.stable.} =
  let u = table("users", "u")
  let q = Select(u.id, u.username, u.score, u.active, u.profile)
    .From(u)
    .Where(u.active == true and u.score >= minScore)
    .OrderBy(u.score.desc)
    .Limit(50)
  return q.fetch(User)

# Test against isolated container sandbox with zero host database mutation
pgxtool test my_extension

# Or test across the full multi-version matrix (14, 15, 16, 17)
pgxtool test my_extension --all

# Compiles binary, audits symbols, and generates .control and .sql files
pgxtool build-extension my_extension

# Install into PostgreSQL system directories
sudo ./my_extension/src/install.sh

CREATE EXTENSION my_extension;

-- 1. Call function returning a named composite type
SELECT * FROM make_user(1, 'Luis', 98.5);

-- 2. Query table functions with SETOF streaming
SELECT * FROM get_top_users(90.0);

graph LR
    A["Nim Source Code"] --> B["Macro Engine & AST Analysis"]
    B --> C["Automatic Panic Shield"]
    B --> D["Effect System Verification"]
    B --> E["Type DDL & SQL Generator"]
    E --> F["pgxtool Binary Auditor"]
    F --> G["Native .so Shared Library"]
    G --> H["PostgreSQL Engine"]

import pgxcrown
import std/[options, json]

type
  Person* = object
    id*: int32
    name*: string
    age*: int16
    score*: float64
    active*: bool
    metadata*: JsonNode

# Receives a composite type, modifies it, and returns it
proc bump_score*(p: Person, bonus: float64): Person {.immutable, parallelSafe.} =
  result = p
  result.score = p.score + bonus
  result.metadata = p.metadata.copy()
  result.metadata["bumped"] = %true

# Returns Option[Person] (translates to Person or SQL NULL)
proc find_person*(id: int32): Option[Person] {.stable.} =
  if id == 1:
    return some(Person(id: 1, name: "Alice", age: 30'i16, score: 95.5, active: true, metadata: %*{"role": "admin"}))
  return none(Person)

CREATE TYPE "Person" AS (
  "id" int4,
  "name" Text,
  "age" int2,
  "score" float8,
  "active" boolean,
  "metadata" jsonb
);

CREATE OR REPLACE FUNCTION bump_score("Person", float8) returns "Person" as
'my_extension', 'pgx_bump_score'
language c IMMUTABLE PARALLEL SAFE STRICT;

CREATE OR REPLACE FUNCTION find_person(int4) returns "Person" as
'my_extension', 'pgx_find_person'
language c STABLE;

import pgxcrown, std/json

proc process_config*(cfg: JsonNode): JsonNode {.immutable.} =
  result = cfg.copy()
  result["processed"] = %true
  result["timestamp"] = %1700000000

proc default_settings*(): JsonNode {.immutable.} =
  return %*{"theme": "dark", "notifications": true, "max_connections": 100}

-- Pass JSONB literals directly into UDFs:
SELECT process_config('{"theme": "dark", "retries": 3}'::jsonb);

proc bad_stable_function*(): bool {.stable.} =
  discard spiInsertFrom(User(id: 1), "users") # Compile-time error!

Error: spiInsertFrom(User(), "users") has an illegal effect: DbWriteEffect

import pgxcrown

proc get_department_leaders*(minSalary: int = 80000): string =
  let e = table("employees", "e")
  let d = table("departments", "d")

  # CTE definition
  let deptStats = Select(e.dept_id, avg(e.salary) as "avg_sal")
    .From(e)
    .GroupBy(e.dept_id)

  # Main Query
  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 "dept_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. Automatic table creation from Nim types
discard spiCreateTableFrom[User]("users", ifNotExists = true, primaryKey = "id")

# 2. Direct entity insertion
discard spiInsertFrom(User(id: 1, username: "luis_dev", score: 98.5), "users")

# 3. Typed entity fetching (.fetch[T] / .fetchOne[T])
let users = Select(u.id, u.username, u.score).From(u).fetch(User)
let singleUser = Select(u.id, u.username).From(u).Where(u.id == 1).fetchOne(User)

-- Integer Overflow -> Caught cleanly
SELECT proof_integer_overflow(2147483647, 1);
-- Result: ERROR: Extension Defect [OverflowDefect]: over- or underflow

-- Index Out-of-Bounds -> Caught cleanly
SELECT proof_index_out_of_bounds(5);
-- Result: ERROR: Extension Defect [IndexDefect]: index 5 not in 0 .. 2