# 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`. ## 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](../01-Language/collections) and [Transducers](../01-Language/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

## 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/begin $db]` | nil | Start transaction | | `[sqlite/commit $db]` | nil | Commit | | `[sqlite/rollback $db]` | nil | Rollback |

Documentation