db — database (SQLite)

The connection is automatic: it is read from the $DATABASE_URL environment variable (default sqlite:fluxon.db). You write no connection code.

use db

# Query — the result is a list of maps
rows = db.q "select * from products where owner=$1" [owner_id]

# A single row (or nil)
user = db.one "select * from users where id=$1" [id]

# Insert — returns the inserted row
row = db.ins "orders" {cust:5 total:0 status::new}

# Update — db.up "table" {changes} {condition}
db.up "orders" {total:1500} {id:order_id}

# Delete — db.del "table" {condition}
db.del "cart_items" {id:item_id}

# UPSERT — db.put "table" {changes} {key}
# updates if it exists by key, inserts if not (atomic)
db.put "agent_memory" {val:v} {agent:aid key:k}

Transactions — db.tx

If a multi-step mutation must be atomic (for example checkout: order + line items + decrementing stock), wrap it in a db.tx block. If an error (fail or !) occurs inside the block, all changes are rolled back — the DB never stays in a half-finished state:

db.tx \->
  ord = db.ins "orders" {cust:c.id total:total}
  each it in items
    db.ins "order_items" {ord:ord.id prod:it.id qty:it.qty price:it.price}
    db.up "products" {stock:it.stock - it.qty} {id:it.id}
  db.up "carts" {status::converted} {id:cart.id}
  # if it reaches the end of the block — commit. If a fail happens midway — all cancelled.

db.tx can also return a value (via ret):

ord = db.tx \->
  o = db.ins "orders" {...}
  ret o            # the block value goes outside

Concurrency (parallel requests) guarantee

db.tx takes the write lock up front (BEGIN IMMEDIATE) and a contending transaction waits (up to the busy_timeout) instead of racing. This means the "read → check → modify" pattern is safe. For example, two parallel withdrawals from one account — both cannot go through at once, and there is no overdraft:

db.tx \->
  acc = db.one "select * from accounts where id=$1" [aid]
  if acc.balance < amt
    fail 422 "insufficient balance"
  db.up "accounts" {balance:acc.balance - amt} {id:aid}   # race-safe

Idempotency — not performing the same operation twice

In places like money transfers, a client may resend a request. Protect it with a unique key (a uniq column): first check whether it exists, then write the key inside a transaction — if it is a duplicate, the uniq error → tx rollback:

old = db.one "select * from transactions where ikey=$1" [key]
old ?? (ret old)              # already done → return the old result
db.tx \->
  db.ins "transactions" {ikey:key amount:amt ...}   # duplicate → uniq → rollback
  # ... transfer the money

• Parameters via $1, $2..., values passed as a list [...].

• In db.ins/db.up, the map keys are column names.

• A query without parameters does not need a list: db.q "select * from links".

• An aggregate (count/sum) can return nil on an empty table — protect it with ?? 0:

  r = db.one "select count(*) c, sum(clicks) s from links"
  log "links: ${r.c}, clicks: ${r.s ?? 0}"

Schema declaration — tbl

You declare tables in Fluxon itself:

tbl products
  id     serial pk
  owner  int ref:users.id
  name   str
  price  money
  status sym index|uniq      # multiple modifiers on one column → pipe `|`
  ts     now

  index(owner status)        # multi-column index (space-separated, no commas)
  uniq(owner price)          # multi-column unique

Type keywords: serial int flt str bool json now sym money. Modifiers: pk (primary key), uniq, index, null, ref:table.column (foreign key).

Indexes and uniqueness

For a single column, append a word modifier: index, uniq. To put both on one column the canonical form is | (pipe): status sym index|uniq. The spaced form (index uniq) is also accepted. For multi-column, use a separate parenthesized line: index(a b), uniq(a b) — space-separated by default (no commas, to save tokens); a comma is also accepted: index(a, b). Index names are automatic (idx_<table>_<cols> / uniq_<...>) — you never invent a name. A name that is too long (DB limit is 63 bytes) is automatically shortened (with a deterministic hash suffix); your code never breaks.

Declarative migration — tbl is the single source of truth

You only write the latest shape of the tbl; Fluxon diffs it against the current DB and runs the necessary DDL itself:

• new column → ADD COLUMN;
• column removed from tbl → DROP COLUMN (the table is first backed up to _fluxon_bak_*);
• a tbl removed entirely → DROP TABLE (with backup; only Fluxon-managed tables — a manually created table is never touched);
• index added/removed → CREATE/DROP INDEX.

Migration is idempotent — re-deploying the same tbl is safe, nothing breaks. No migration SQL needed for schema changes. Type changes and renames are not automatic — do those manually with db.q "ALTER TABLE ...", and Fluxon syncs the rest afterward.

Special column types

A json column — when read it automatically becomes a map/list (not a string, no need for json.dec); when written, a map/list is automatically encoded.

The money type — for money. Money should NEVER be a flt (float) — float rounding errors corrupt money. money is a whole number of minor units (tiyin, cents): 15000 = 150.00 so'm. All money math uses money/int (int is 64-bit):

tbl accounts
  id      serial pk
  balance money       # in tiyin, e.g. 15000 = 150.00
total = price * qty   # int math, not float

The sym type — for enums. If a column is sym: the DB stores text, but when Fluxon reads it, it automatically returns a symbol. On writing and filtering, a symbol is automatically converted to text. Then match works directly:

tbl tickets
  category sym         # DB: text ("billing"), Fluxon: symbol (:billing)
  status   sym

# Writing: you give a symbol, the DB stores text
db.ins "tickets" {category::billing status::new}

# Reading: if the schema says sym, Fluxon returns a symbol
t = db.one "select * from tickets where id=$1" [id]
match t.category       # t.category is a symbol, so match works
  :billing -> log "billing matter"
  :technical -> log "technical"
  _ -> log "other"

# Filtering: a symbol is passed, automatically converted to text
db.q "select * from tickets where category=$1" [:billing]

One rule: a sym column — text in the DB, a symbol in Fluxon, conversion automatic.