# SQLite Deft ships SQLite built in — no driver to install, no shared library to load. The `sqlite/*` stdlib functions work identically in the scripting binary, the ANSI binary, and the full TUI. SQLite is always available; you don't need a build flag. ## Opening and Closing

def db [sqlite/open ":memory:"]               # in-memory database
# ... or ...
def db [sqlite/open "data.db"]                # file-backed

sqlite/close $db

The handle is a `native_resource`. Closing it releases the underlying SQLite handle; using a closed handle is an error. ## Exec — DDL and DML `sqlite/exec` runs SQL that doesn't return rows. Returns the number of rows changed.

sqlite/exec $db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)"

sqlite/exec $db "INSERT INTO users (name, age) VALUES ('Alice', 30)"

### Parameterised exec Pass parameters as a list after the SQL string. Placeholders are `?`:

sqlite/exec $db "INSERT INTO users (name, age) VALUES (?, ?)" @{ "Alice" 30 }
sqlite/exec $db "INSERT INTO users (name, age) VALUES (?, ?)" @{ "Bob"   25 }

Numbers, floats, strings, and nil all bind correctly. Invalid SQL returns a `%Err` — check with `err?`:

set r [sqlite/exec $db "INSERT INTO bogus_table VALUES (1)"]
if [err? $r] { echo "failed: $r~message" }

## Query — SELECT `sqlite/query` returns a list of maps keyed by column name:

def rows [sqlite/query $db "SELECT * FROM users"]
for row $rows {
    echo "$row~name (age $row~age)"
}
# Alice (age 30)
# Bob (age 25)

With parameters:

def adults [sqlite/query $db "SELECT * FROM users WHERE age >= ?" @{ 18 }]

NULL cells come through as `nil`. ### `query-one` — single row `sqlite/query-one` returns the first matching row, or `nil`:

def alice [sqlite/query-one $db "SELECT * FROM users WHERE name = ?" @{ "Alice" }]
if $alice { echo "found: $alice~name" }

### `scalar` — single value `sqlite/scalar` returns the first column of the first row, or `nil`:

def count [sqlite/scalar $db "SELECT COUNT(*) FROM users"]
echo "user count: $count"

## Introspection

sqlite/tables $db                              # => list of table names
sqlite/columns $db "users"                     # => column metadata list

`sqlite/columns` returns rows from `PRAGMA table_info(...)`. Each row has fields like `:name`, `:type`, `:notnull`, `:pk`. ## User-Defined Functions Register a Deft callable as a SQL function on the connection — SQL can then call it like any builtin (the equivalent of TCL's `db function`):

sqlite/register-function $db "shout" {|s| [upper $s]}

sqlite/scalar $db "SELECT shout('hello')"      # => "HELLO"

sqlite/unregister-function $db "shout" 1

The second argument is the SQL name (letters, digits, underscores). Arity is inferred from the function's parameters; pass it explicitly before the function when needed, and use `-1` for variadic — an `@rest` parameter collects the extra arguments:

sqlite/register-function $db "deft_add" 2 {|a b| ($a + $b)}
sqlite/register-function $db "deft_join" -1 {|@xs| [join $xs "-"]}

sqlite/scalar $db "SELECT deft_add(3, 4), deft_join('a', 'b', 'c')"

Values map both ways like query parameters: `nil` ↔ NULL, booleans become 0/1, integral numbers stay INTEGER, strings ↔ TEXT, and BLOB arguments arrive as binary strings. Results that aren't scalars (vectors, maps, keywords) are stringified with `[str]`-style formatting. Errors abort the SQL statement: raising inside the function, or returning `%Err{:message "..."}`, fails the statement with that message; `%Ok{:value x}` unwraps to `x`. A trailing options map configures the registration:

sqlite/register-function $db "deft_twice" {|x| ($x * 2)} %{ :deterministic true }

sqlite/exec $db "CREATE INDEX idx ON t (deft_twice(n))"   # needs :deterministic true

- `:deterministic` — always the same result for the same arguments; required to use the function in expression indexes, generated columns, and CHECK constraints. - `:direct-only` — refuse calls from triggers, views, and schema contexts; recommended for side-effecting functions. - `:innocuous` — assert the function is safe to run from schema contexts when `trusted_schema` is on. Functions capture their environment normally, can query the same database themselves, and stay registered (GC-safe) until replaced by a new `register-function` with the same name and arity, removed with `unregister-function`, or the handle is closed. UDFs are sync-only. Registration on a handle in async mode returns `%Err`, and an async query that reaches a Deft function fails with a clear error, because `*-async` workers are not allowed to call back into the VM. ## Transactions

sqlite/begin $db
try {
    sqlite/exec $db "INSERT INTO users (name, age) VALUES (?, ?)" @{ "Carol" 28 }
    sqlite/exec $db "UPDATE counters SET value = value + 1 WHERE name = 'users'"
    sqlite/commit $db
} catch e {
    sqlite/rollback $db
    echo "rolled back: $e~message"
}

Rollback discards everything since the matching `begin`:

sqlite/begin $db
sqlite/exec $db "INSERT INTO users (name, age) VALUES ('Temp', 0)"
sqlite/rollback $db
# The 'Temp' row is gone.

## Pipelines and Transducers Query results are ordinary lists, so all collection operations work without ceremony:

# Filter and map results
def adults $rows
    |> filter { ($it~age >= 18) }
    |> map { $it~name }
    |> collect

# Aggregate
set total-age $rows |> map { $it~age } |> reduce 0 { $acc + $it }

# Index by name
def by-name [from-entries [map $rows {|r| @{ $r~name $r }}]]
echo $by-name~Alice~age                              # => 30

For reusable transforms, factor them into a transducer:

def xf [comp
    [filter { ($it~age >= 18) }]
    [map { $r~name }]]

[into @{} xf $rows]

See Collections and Transducers. ## `runtime/on-shutdown` for Cleanup Long-running scripts and TUI apps should register a shutdown hook to close database handles:

def db [sqlite/open "data.db"]

runtime/on-shutdown {
    try { sqlite/close $db } catch _e { nil }
}

This pattern is used in `packages/chat/src/chat.dft` and `packages/stdext/src/semantic.dft` for production DB persistence. ## A Complete Example

#!/usr/bin/env deft

def db [sqlite/open ":memory:"]

sqlite/exec $db """
    CREATE TABLE posts (
        id    INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        body  TEXT
    )
"""

sqlite/exec $db "INSERT INTO posts (title, body) VALUES (?, ?)" @{
    "First Post"
    "This is my first post."
}
sqlite/exec $db "INSERT INTO posts (title, body) VALUES (?, ?)" @{
    "Second Post"
    "More content here."
}

def count [sqlite/scalar $db "SELECT COUNT(*) FROM posts"]
echo "Total posts: $count"

def posts [sqlite/query $db "SELECT * FROM posts ORDER BY id"]

# Render via pipeline
$posts
    |> map { " - $it~title" }
    |> each { echo $it }

sqlite/close $db

## Typed Persistence Writing `CREATE TABLE` statements and row↔value codecs by hand gets old fast. Schema-First Persistence covers `deft/persist`, which derives the storage layout and typed CRUD accessors from your `deftype` schemas — with the same `schema/validate` rules applied before every write and the built-in crosstab virtual table driving declarative pivots. Reach for `sqlite/*` directly when you want the raw API. ## Concurrency Notes SQLite handles are not safe to share across OS threads. Within a single runtime, this isn't a problem — only one thread touches the handle. For cross-runtime access, either: - Open a separate handle per runtime against the same file (SQLite handles concurrent file access via WAL mode), or - Use `runtime/eval` to dispatch DB work to a single owning runtime. ## `sqlite/*` Quick Reference | Function | Returns | Purpose | |---|---|---| | `[sqlite/open ":memory:" \| path]` | handle | Open | | `[sqlite/close $db]` | nil | Close | | `[sqlite/exec $db sql ?params]` | number | DDL/DML; rows changed | | `[sqlite/query $db sql ?params]` | list of maps | SELECT | | `[sqlite/query-one $db sql ?params]` | map or nil | First row | | `[sqlite/scalar $db sql ?params]` | value or nil | First column of first row | | `[sqlite/tables $db]` | list | All table names | | `[sqlite/columns $db "table"]` | list | Column metadata | | `[sqlite/register-function $db "name" ?arity fn ?opts]` | bool | Register a Deft function as a SQL UDF | | `[sqlite/unregister-function $db "name" arity]` | bool | Remove a UDF (-1 = variadic) | | `[sqlite/begin $db]` | nil | Start transaction | | `[sqlite/commit $db]` | nil | Commit | | `[sqlite/rollback $db]` | nil | Rollback |

Documentation