# Persistence `deft/persist` turns a [`deftype`](../01-Language/types-and-protocols) schema into a storage layout and typed accessors. You declare the fields once; the package derives the `CREATE TABLE` statements and hands back a repository whose reads are returned as typed values and whose writes are validated through [`schema/validate`](../02-Stdlib/schema) before they reach the database. The same field specs generate both the app-side validation and the database's own `NOT NULL` / `CHECK` / `UNIQUE` constraints, so the two can never drift apart. The package is pure Deft on top of the native [`sqlite/*`](sqlite) functions — no build flag, works in every host, and the v1 backend is SQLite. The API is deliberately store-neutral in naming: functions speak of types, records, and storage rather than tables and columns. Only the raw escape hatches say "SQL". The API is also async-first: pure functions (`persist/sql`, `persist/name-of`, `persist/repo`) are synchronous, while every store operation returns a `Future`. Awaiting suspends only the calling coroutine — a TUI stays responsive while the store works — and a remote backend (Postgres, …) fits the same surface naturally, since every operation funnels through one async driver seam.

import "deft/persist" as persist

## Quick Start

deftype Sale %{
    :id     %{:type :number :primary-key true}
    :region %{:type :string  :required true :index true}
    :month  %{:type :string  :required true}
    :amount %{:type :number  :default 0}
}

set store [sqlite/open ".deft/app.db"]

[await [persist/ensure $store :Sale]]            # idempotent layout create
set sales [persist/repo $store :Sale]            # typed accessor (sync)

[await [persist/insert $sales %Sale{:region "West" :month "jan" :amount 4}]]
# => %Sale{:id 1 :region "West" :month "jan" :amount 4}

[await [persist/get $sales 1]]                   # → %Sale{...} or nil
[await [persist/update $sales %{:id 1 :amount 9}]]   # plain map = partial patch
[await [persist/delete $sales 1]]                # → rows changed

Everything is receiver-first, so await mid-pipeline and keep going:

set rows ([persist/find $sales %{:where %{:region "West"} :order %{:month :asc}}]
    |> await
    |> filter {|s| ($s~amount > 2)}
    |> map {|s| "$s~month: $s~amount"}
    |> collect)

# Fan out across stores/queries — multi-await, or await-all for a list:
[await [persist/count $sales] [persist/count $sales %{:where %{:month "jan"}}]]

## Results Awaiting resolves to plain success values — vectors of typed records, single records, numbers, `nil` — so they flow straight into `|>` pipelines. Failure resolves to a `%Err{ :message … :path? }`; check with `err?` after the await. Validation failures keep the path-annotated shape of `schema/validate`, so you can point the user at the offending field:

set r [await [persist/insert $sales %{:region "West" :bogus 1}]]
if [err? $r] {
    echo "rejected:" $r~message          # unknown field 'bogus'
}

## Functions Store operations return a `Future` (await them); `persist/sql`, `persist/name-of`, and `persist/repo` are synchronous. | Function | Purpose | |---|---| | `persist/sql :Type ?opts` | The backend definition SQL — table DDL or `CREATE VIEW` (pure, printable) | | `persist/name-of :Type` | Storage name (`:HTTPServer` → `"http_server"`) | | `persist/repo $store :Type ?opts` | Build the typed accessor (pure binding) | | `persist/view $store $view` | Read-only typed accessor over a declarative view | | `persist/ensure $store refs ?opts` | Create the layout if missing — tables and/or views (idempotent) | | `persist/migrate $store refs ?opts` | Align layout + preflight data + create/recreate views | | `persist/check $store refs ?opts` | Report rows the schema rejects (tables and views) | | `persist/all $repo ?spec` | All rows as typed values | | `persist/find $repo ?spec` | Filtered / sorted / paged rows | | `persist/get $repo id` | One row by primary key, or nil | | `persist/insert $repo value` | Validate + INSERT → stored value | | `persist/insert-many $repo values` | All-or-nothing batch insert → vector of stored values | | `persist/update $repo value` | Validate + UPDATE → stored value | | `persist/upsert $repo value` | INSERT … ON CONFLICT(pk) DO UPDATE | | `persist/delete $repo id` | DELETE by primary key → rows changed | | `persist/count $repo ?spec` | Row count, with an optional `:where` | | `persist/query $repo sql ?params` | Raw SQL → typed rows | | `persist/pivot $store $ref spec` | Cross-tabulation over a type or declarative view | | `persist/foreign-keys $store ?on` | Toggle FK enforcement on the connection (default ON) | | `persist/meta-read $store ?scope ?version` | Read a stored schema blob | | `persist/meta-history $store scope` | Retained versions for a scope | | `persist/meta-check-text $store scope version text` | Compare a raw blob without parsing | | `persist/meta-write-text $store text opts` | Write a raw schema blob | | `persist/meta-record-text $store text scope version mode` | Record with `:if-missing`/`:auto`/`:strict` | The `types` argument accepts a single type, a vector of types, or a `%{type opts}` map for per-type overrides. `persist/repo` returns a `%PersistRepo{}` implementing the `Persistable` protocol, so postfix dispatch works too — the result is still a Future: `[await [$sales(all)]]`, `[await [$sales(get 1)]]`, `[await [$sales(insert-many $batch)]]`. ## Async, Transactions, and Backends - One handle, one mode. The first persist call flips its SQLite handle to async-only; raw `sqlite/*` sync functions on that handle then return `%Err`. Drive outer transactions with the async verbs (`sqlite/begin-async` / `commit-async` / `rollback-async`), use `persist/query` for raw reads, or open a second handle. - Ordering. Every statement for a handle is FIFO on its executor thread, so multi-statement sequences (transactions, savepoints) stay ordered; `insert-many`'s savepoint nests inside an outer async transaction. - Concurrency. Different handles run in parallel — await several futures at once with `[await $f1 $f2]` (`await-all` from `deft/stdext/async` wraps a list), and a TUI coroutine awaiting a store keeps painting. - Backends. Every store operation funnels through the backend's `io-*` driver (`src/sqlite.dft`): `io-exec`, `io-query`, `io-query-one`, `io-scalar`, `io-query-cols`, all `@async`. A remote backend implements the same five with the same semantics and neither the facade nor any caller changes. Keep dialect-specific raw SQL behind `persist/query`. ## Declaring Storage Core mapping derives from the ordinary field-spec vocabulary — `:type`, `:required`, `:default`, `:enum` — plus a handful of storage-specific keys. The field specs stay readable as schema; the storage keys refine how they land on disk: | Field spec key | Effect | |---|---| | `:primary-key true` | `PRIMARY KEY`; the key for get/update/upsert/delete | | `:unique true` | `UNIQUE` column constraint | | `:index true` | Single-column index | | `:sql-type "INTEGER"` | Raw column-type override | | `:sql-default "datetime('now')"` | Raw expression default | | `:references %{:type :User :field :id :on-delete :cascade}` | `REFERENCES` clause; the child-side column is auto-indexed (SQLite does not index FK columns itself) | No `:primary-key` field ⇒ an implicit `id INTEGER PRIMARY KEY` AUTOINCREMENT` column is added, unless `:id false` is passed in the` options. Options common to the layout functions: | Option | Meaning | |---|---| | `:name "…"` | Storage name override | | `:id true/false` | Implicit id column (default true) | | `:indexes @{…}` | Composite index specs `%{:fields @{…} :name :unique}` | | `:append @{sql…}` | Extra raw statements | | `:sample n` / `:all true` | Preflight scan size (default 1000) | | `:force true` | Apply CHECK-bearing columns despite invalid rows | | `:check true` | Run the migrate preflight (default true) | ### Type mapping | Deft type | Column | Notes | |---|---|---| | `:string` | TEXT | | | `:number` | REAL | f64 — for money store INTEGER cents or TEXT via `:sql-type` | | `:boolean` | INTEGER | 1/0, decoded back to booleans | | `:keyword` | TEXT | bare name (`:open` → `"open"`) | | `:list` / `:set` / `:map` / `:any` | TEXT | `deft/stringify`, decoded via `deft/parse` | | nested `deftype` | TEXT | stringified tagged map, decoded recursively | | `:many true` fields | TEXT | stored stringified as a vector | Rows decode through `schema/from-value` with `:coerce true`; extra database columns — including an implicit `id` on a type that does not declare one — are preserved as extra keys, so `$row~id` works regardless. ## Referential Integrity SQLite enforces `REFERENCES` clauses only when `PRAGMA foreign_keys` is ON — per-connection and OFF by default. `persist/ensure` and `persist/migrate` switch it on before doing anything, so writes through a repo afterwards are checked: a missing parent reference or a delete of a still-referenced parent resolves to `%Err` (`FOREIGN KEY` constraint failed`), and `:on-delete :cascade` / `:set-null` fire as` declared. Stores whose tables were created out of band call `[await [persist/foreign-keys $store]]` once (or set the PRAGMA via `sqlite/exec-async`). `persist` also auto-indexes the child side of every `:references` field (`CREATE INDEX IF NOT EXISTS "idx_<table>_<col>"`), on create and on `persist/migrate` — SQLite itself only indexes the parent side, so without this every parent delete/update and FK check would scan the child table. One migration caveat: with FKs enabled, `ALTER TABLE ADD` COLUMN … REFERENCES` requires a NULL default, so such columns are` reported as blocked rather than applied. ## Writing - `insert` validates strictly (unknown fields are errors), applies `:default` values and coercion, omits nil fields so database defaults apply, and returns the stored record with its generated key. - `insert-many` takes a vector of values (or a single value) and inserts the batch in one transaction — implemented with a savepoint, so it also nests inside an outer `sqlite/begin`. Every row is validated and encoded before the store is touched; any failure rolls the whole batch back and returns the `%Err` with the failing row's 0-based `:index` (the original error is under `:error`). On success it returns the vector of stored records. - `update` accepts two shapes: - a plain map is a partial patch — the provided keys are merged onto the current row first, so omitted fields and required fields keep their stored values; - a `%Type{…}` value is the full new state — the defaults materialized by the tagged literal are part of the write. - `upsert` inserts when the primary key is absent, otherwise `ON CONFLICT DO UPDATE`s every non-key column. - All write verbs are async like the reads — `[await [persist/insert …]]`. Multi-statement work wraps in `sqlite/begin-async` / `commit-async`; updates are read-modify-write by key (optimistic locking is on the roadmap, not in v1). ## Finding `persist/find` takes a spec map, all keys optional:

[await [persist/find $sales %{
    :where  %{:region "West"}               # equality; nil → IS NULL
    :order  %{:month :desc}                 # one key
    :limit  10
    :offset 20}]]

Field names and order directions are checked against the type — typos resolve to `%Err` with a `:path`. For anything richer (joins, `LIKE`, aggregates) use `persist/query`, which runs parameterized SQL and decodes each row through the same schema:

[await [persist/query $sales "SELECT * FROM sale WHERE region LIKE ?" @{"W%"}]]

## Evolving a Schema `persist/migrate` aligns existing storage with the type, additively and non-destructively: 1. missing tables are created from the same plan as `persist/ensure`; 2. missing columns are added via `ALTER TABLE` where SQLite allows it — `PRIMARY KEY`, `UNIQUE`, NOT-NULL-without-default and expression defaults are blocked and reported with a reason; 3. indexes are created (`IF NOT EXISTS`); 4. a preflight decodes a sample of existing rows (default 1000, `:all` true` for all) and reports rejected ones under `:invalid`; when any` exist, CHECK-bearing new columns are blocked unless `:force true`; 5. extra database columns and affinity drift are reported under `:missing` / `:changed` — never dropped or rewritten.

set rep [await [persist/migrate $store :Sale]]
# => %{"Sale" %{:created false
#               :added   @{"amount"}          # applied
#               :blocked %{"due" "…reason"}   # reported only
#               :missing @{"legacy"}          # in DB, not in schema
#               :changed @{%{:column "priority" :db-type "INTEGER" :schema-type "REAL"}}
#               :invalid @{…}                 # rows the schema rejects
#               :scanned 1000}}

`persist/check $store types` runs the same scan without migrating — an audit after out-of-band edits. Schema removals and type changes are diagnostics; apply them by hand. For a complete app-owned schema and last-session walkthrough, see Persisting App State. ## Schema Metadata Every persist database carries a reserved `_persist_meta_` table. It stores one canonical schema-descriptor blob per `(scope, version)`; `is_current` marks the version that produced the live data. The blob contains the full schema — types, fields/specs, views, libraries, and (for generated models) the relation/order/info graph — as plain Deft data, never executable source. ``` _persist_meta_(scope, version, meta_version, persist_version, deft_version, schema_hash, is_current, types, created_at, updated_at) ```` - `ensure` records/merges the descriptor for direct persist use; schema-builder-generated `persist.dft` and generated models record the full schema blob; `migrate` updates it after a clean additive pass. - Same version with a different `schema_hash` is rejected. A higher version inserts a row and keeps the old version with `is_current=0`. - `meta_version` is the hard format gate; `persist_version` is checked by major version. - Raw text checks (`meta-check-text`) compare hashes without parsing the stored blob, which keeps large schemas cheap and avoids loading a descriptor just to reject a mismatch. - The storage name `_persist_meta_` is reserved for user types. The blob verifies and documents a dataset; loading still requires the matching compiled `deftype`/model modules. It can also be used by tools to regenerate schema sources from a database. ## Declarative Views A view combines tables relationally — but not as a throwaway query: it is a real `CREATE VIEW` persisted in the store's schema, declared from the `deftype` schemas themselves, and it becomes a first-class relation for typed reads and crosstabs. The shape is a `deftype` (so decoding and validation reuse the normal codec). Joins are declared from the schema's own `:references` via `:via` paths on shape fields:

deftype User %{
    :id   %{:type :number :primary-key true}
    :name %{:type :string}
    :org  %{:type :number :references %{:type :Org :field :id}} }

deftype Sale %{
    :id     %{:type :number :primary-key true}
    :region %{:type :string}
    :amount %{:type :number}
    :buyer  %{:type :number :references %{:type :User :field :id}} }

deftype SaleView %{
    :id     %{:type :number :primary-key true}   # base column
    :region %{}                                   # base column
    :amount %{}                                   # base column
    :buyer  %{:type :string :via @{:buyer :name}}         # 1 hop → User.name
    :org    %{:type :string :via @{:buyer :org :label}} } # multi-hop → Org.label

def sales-by-buyer %PersistView{ :from :Sale :shape :SaleView }

[await [persist/ensure $store @{:Org :User :Sale $sales-by-buyer}]]

generates (deterministic aliases: base, then `t1`, `t2`… per path prefix — shared prefixes dedupe, self-joins stay distinct): ```sql CREATE VIEW IF NOT EXISTS "sale_view" ("id", "region", "amount", "buyer", "org") AS SELECT "sale"."id", "sale"."region", "sale"."amount", "t1"."name", "t2"."label" FROM "sale" "sale" LEFT JOIN "user" "t1" ON "t1"."id" = "sale"."buyer" LEFT JOIN "org" "t2" ON "t2"."id" = "t1"."org" ```` Then use it like any relation:

set buyers [persist/view $store $sales-by-buyer]     # read-only repo
set rows [await [persist/all $buyers]]               # → @{%SaleView{…} …}
[await [persist/find $buyers %{:where %{:buyer "Ada"}}]]

# Crosstabs over the view — rows/cols/value are shape fields:
[await [persist/pivot $store $sales-by-buyer %{
    :rows @{:region} :cols @{:buyer} :value :amount :agg :sum }]]

Spec: `%PersistView{ :from :shape :name? :join? :as? :joins? :where? }` — `:join` is the default kind (`:left`, or `:inner`), `:as` renames the base alias, `:where` is a raw SQL predicate. Shape field keys: `:via` reference path, `:from @{alias field}` for explicitly joined columns, `:join` for a per-field kind override (conflicting kinds on a shared path are an error). Schemas without `:references` use the explicit escape hatch:

def flat-view %PersistView{
    :from :Sale :shape :SaleFlat
    :joins @{ %JoinSpec{
        :alias :u :type :User :kind :inner
        :on @{ %{:left @{:sale :buyer} :right @{:u :id}} } } } }

Semantics: views are read-only through persist (writes return `%Err`); `persist/migrate` reuses unchanged definitions (comparing the stored `sqlite_master.sql`), drops changed ones in reverse order and recreates them in declaration order — views hold no data, so recreation is safe; `persist/check` scans view rows for decode failures too. v1 projects columns + joins only: for aggregates or computed columns, use `persist/query`, or declare more `:joins`. ## Cross Tabulations `persist/pivot` compiles a declarative pivot into the built-in SQLite `crosstab` virtual table — over a type or a declarative view. Rows and columns are named by field, the column set is discovered with `SELECT DISTINCT`, and the aggregate is chosen from `sum` (default), `avg`, `min`, `max`, `count`, `first`:

[await [persist/pivot $store :Sale %{
    :rows  @{:region}         # 1+ row-key fields
    :cols  @{:year :month}    # 1+ column-key levels
    :value :amount
    :agg   :sum
    :where "amount > 0"       # raw SQL filter, applied to both axes
    :col-order :asc}]]
# => %{:name "persist_pivot"
#      :cols @{"region" "2024.0 / jan" "2024.0 / feb" …}
#      :rows @{%{…} …}}

Multi-level column names are the rendered key levels joined with `" / "`. Values stay raw (pivot columns are dynamic); row keys are returned with keyword keys. The spec is validated strictly — unknown fields and bad aggregates come back as `%Err` with paths. ## A Complete Example

import "deft/persist" as persist

deftype Expense %{
    :id       %{:type :number :primary-key true}
    :category %{:type :string  :required true :index true}
    :month    %{:type :string  :required true}
    :amount   %{:type :number  :required true :min 0}
    :recurring %{:type :boolean :default false}
}

set store [sqlite/open ":memory:"]
[await [persist/ensure $store :Expense]]
set expenses [persist/repo $store :Expense]

# One atomic batch (validation happens before the store is touched):
[await [persist/insert-many $expenses @{
    %{:category "rent" :month "jan" :amount 1200 :recurring true}
    %{:category "food" :month "jan" :amount 400}
    %{:category "food" :month "feb" :amount 380} }]]

set jan [await [persist/count $expenses %{:where %{:month "jan"}}]]
echo "january entries: $jan"

echo "by category:"
set matrix [await [persist/pivot $store :Expense %{
    :rows  @{:category}
    :cols  @{:month}
    :value :amount
    :agg   :sum}]]
$matrix~rows
    |> each {|r| echo "$r~category jan=$r~jan feb=$r~feb"}

## See Also - Types and Protocols — `deftype` and the field-spec vocabulary the storage layer extends - Schema — `schema/validate` / `schema/from-value`, the validators persistence writes through - SQLite — the underlying `sqlite/*` API - Packages — how `deft/persist` is mounted and imported