xi-sqlite

SQLite bindings for Xi apps — pure Xi, no glue C.

The library binds the system sqlite3 directly through Xi's extern "C" FFI (link "sqlite3") and exposes everything through interfaces resolved by Xi's DI container: depend on sqlite.SQLite (connections and queries), sqlite.RowReader (typed access to result rows), or sqlite.ColumnDecoder (how columns become values), and the bundled implementations are injected automatically — or rebind any of them in your module App / module Test. Every fallible call returns T!, never aborts.

It also ships SqliteQueryProvider, a full QueryProvider for Xi's std/query — reads (run) and writes (insert/remove). That means std/data's CrudRepository works over SQLite out of the box: a repository declares its provider and source, and inherits findAll/findById/save/delete. See Repositories and Queries.

Install

As a dependency (recommended)

Add the release archive to your module's dependencies and run xi install — it fetches the library into ./modules, where it's auto-compiled in. Don't import it by path; just use it by namespace:

module App {
id = "my-app"
dependencies = ["https://github.com/code-by-sia/xi-sqlite/archive/refs/tags/v0.3.0.tar.gz"]
}

xi install my-app.xi # -> ./modules/sqlite.xi + ./modules/sqlite/*

Then sqlite.SQLite, SqliteQueryProvider, etc. are available with no import line.

Or vendor the source

Copy the src/ contents — sqlite.xi plus the sqlite/ folder — into your project and import the umbrella file (always that one; it loads the parts in the required order):

import "vendor/sqlite.xi" // wherever you placed it

Either way, the host needs the SQLite library (preinstalled on macOS; libsqlite3-dev on Debian/Ubuntu).

Usage

Keep SQL out of your entry: put it in a store class behind a repository interface, and let the entry depend on the abstraction only. examples/demo.xi is the full version of this sketch:

import "std/log.xi"
import "std/json.xi"
import "std/query.xi"
import "vendor/sqlite.xi"
type Note = { id: Integer, title: String, stars: Number }
type Notes = { items: List<Note> }
type NoteSession = { db: sqlite.Database }
interface NoteRepository {
producer open(path: String) -> NoteSession!
producer add(s: NoteSession, title: String, stars: Number) -> Integer!
producer list(s: NoteSession) -> Notes!
producer close(s: NoteSession) -> Bool!
}
// persistence lives here; the rest of the app never sees SQL
class NoteSQLiteStore implements NoteRepository {
deps { sql: sqlite.SQLite, binder: DatabaseBinder, provider: QueryProvider }
producer open(path: String) -> NoteSession! {
let db = sql.open(path)?
let made = sql.exec(db, "create table if not exists notes (id integer primary key, title text not null, stars real not null)")
if isErr(made) { return err(made.err) }
binder.useDatabase(db) // let query chains run here
return ok(NoteSession { db: db })
}
producer add(s: NoteSession, title: String, stars: Number) -> Integer! {
let params = json.array()
params = json.push(params, json.str(title)) // bound — no escaping
params = json.push(params, json.num(stars))
let saved = sql.execBound(s.db, "insert into notes (title, stars) values (?, ?)", params)
if isErr(saved) { return err(saved.err) }
return ok(sql.lastInsertId(s.db))
}
// a typed query chain instead of SQL + manual mapping
producer list(s: NoteSession) -> Notes! {
let notes = query.from<Note>("notes").sortedBy { it.id }.collect(provider)
return ok(Notes { items: notes })
}
// ... close elided
}
async entry (logger: Logger, notes: NoteRepository) main(args: String[]) -> Integer {
let opened = notes.open("app.db")
if isErr(opened) { logger.error(opened.err) return 1 }
let session = opened.value
let added = notes.add(session, "hi", 5.0)
if isErr(added) { logger.error(added.err) return 1 }
let all = notes.list(session)
if isErr(all) { logger.error(all.err) return 1 }
for n in all.value.items { logger.info("#" + n.id + " " + n.title) }
let closed = notes.close(session)
if isErr(closed) { logger.error(closed.err) return 1 }
return 0
}
module App {
bind QueryProvider -> SqliteQueryProvider as singleton
bind DatabaseBinder -> SqliteQueryProvider as singleton
}

Overriding: every seam is an interface, so swapping behavior is one bind — fake the whole database in tests (module Test { bind sqlite.SQLite -> FakeSQLite }), swap persistence (bind NoteRepository -> InMemoryNotes), or change column decoding for every query (bind sqlite.ColumnDecoder -> MyDecoder). Tests can also inject the real thing per test block: test "queries" (sql: sqlite.SQLite, reader: sqlite.RowReader) { ... }, as tests/sqlite_test.xi does.

The lower-level sql.query(db, "select ...") + sqlite.RowReader path is still there when you want hand-written SQL — but for reads that map onto a type, the query chain is the tidier default.

API

interface sqlite.SQLite — connections and queries (impl: SystemSQLite)

Method	Returns	Notes
open(path)	Database!	creates the file if missing; ":memory:" for in-memory
close(db)	Bool!	errs if statements are still open
exec(db, sql)	Bool!	one or more ;-separated statements, no result rows
execBound(db, sql, params)	Bool!	one statement with ? placeholders bound from a Json array
query(db, sql)	Rows!	typed rows; iterate rows.items
queryBound(db, sql, params)	Rows!	like query, with ? placeholders bound from a Json array
queryJson(db, sql)	String!	rows as a JSON array of objects
lastInsertId(db)	Integer	rowid of the last insert
changes(db)	Integer	rows affected by the last statement

interface sqlite.RowReader — typed row access (impl: TypedRowReader)

Each Row maps column names to typed Values (IntValue, RealValue, TextValue, NullValue). Missing columns and type mismatches yield the fallback you pass:

reader.intAt(row, "id", 0) // Integer
reader.numberAt(row, "price", 0.0) // Number (integers widen)
reader.textAt(row, "title", "") // String
reader.isNull(row, "deleted_at") // Bool
reader.hasColumn(row, "id") // Bool

interface sqlite.ColumnDecoder — column → Value → Json (impl: TypedColumnDecoder)

SystemSQLite runs every result column through this; rebind it to change how all queries decode. BLOB columns are surfaced as text (sqlite's cast of the raw bytes); select hex(col) in SQL when you need a stable binary encoding.

Repositories (std/data)

Because the provider implements the write contract too, a repository is just two lines of wiring — CrudRepository supplies findAll, findById, save, delete and deleteById as defaults:

import "std/data.xi"
type Note = { id: Integer, title: String, stars: Number }
class NoteRepository implements CrudRepository<Integer, Note, Note> {
deps { db: QueryProvider }
producer getProvider() -> QueryProvider => db
mapper source() -> String => "notes"
}

Inject it by its interface and the whole CRUD surface is available:

async entry (logger: Logger, sql: sqlite.SQLite, binder: DatabaseBinder,
notes: CrudRepository<Integer, Note, Note>) main(args: String[]) -> Integer {
let db = sql.open("app.db")
if isErr(db) { logger.error(db.err) return 1 }
let made = sql.exec(db.value, "create table if not exists notes (id integer primary key, title text not null, stars real not null)")
if isErr(made) { logger.error(made.err) return 1 }
binder.useDatabase(db.value)
notes.save(Note { id: 1, title: "hello", stars: 4.5 }) // upsert by id
notes.deleteById(2)
let good = notes.findAll() // composable Query
.filter { it.stars >= 4.0 }
.sortedByDescending { it.stars }
.toList() // -> List<Note>
if let one = notes.findById(1) { logger.info(one.title) }
return 0
}
module App {
bind QueryProvider -> SqliteQueryProvider as singleton
bind DatabaseBinder -> SqliteQueryProvider as singleton
}

save() upserts (it removes by key, then inserts), and findAll() binds this repository's provider to the query so the chain runs against it. examples/repository_demo.xi is the runnable version (./scripts/run-repository-demo.sh).

Note (Xi 0.1.12): an if let … { } else { } else-branch runs even when the optional is present — the body is correct, so prefer if let without else.

Queries (std/query)

Instead of SQL strings you can write a typed std/query chain. SqliteQueryProvider renders the reified plan to parameterized SQL (bundled SqliteDialect), runs it, and decodes the rows back into your type — captured values become bound parameters, so it's injection-safe.

SqliteQueryProvider implements the whole QueryProvider contract:

Method	Role
name()	returns "sqlite" — lets where-selected code pick a backend
run(plan)	read: renders the plan to SQL and returns rows
insert(source, row)	write: insert into <source> (…) values (?, …)
remove(source, key, id)	write: delete from <source> where <key> = ?

It implements two interfaces over one instance (mirroring std's MemorySource): QueryProvider runs plans, DatabaseBinder attaches the open database. Bind both to it as singleton:

import "std/query.xi"
type Note = { id: Integer, title: String, stars: Number }
async entry (logger: Logger, sql: sqlite.SQLite,
binder: DatabaseBinder, provider: QueryProvider) main(args: String[]) -> Integer {
let db = sql.open("app.db")
if isErr(db) { logger.error(db.err) return 1 }
binder.useDatabase(db.value) // attach the connection
let minStars = 4.0
let top = query.from<Note>("notes") // "notes" = table name
.filter { it.stars >= minStars } // minStars -> bound param
.sortedByDescending { it.stars }
.take(10)
.collect(provider) // -> List<Note>, run as SQL
for n in top { logger.info("#" + n.id + " " + n.title) }
return 0
}
module App {
bind QueryProvider -> SqliteQueryProvider as singleton
bind DatabaseBinder -> SqliteQueryProvider as singleton
}

examples/query_demo.xi is the runnable version (./scripts/run-query-demo.sh). Swap in std's in-memory reference provider for tests by binding both QueryProvider and RowStore to MemorySource.

Packaging

Build a release archive with xi pack (via scripts/pack.sh):

xi pack src/library.xi # -> dist/xi-sqlite-<version>.tar.gz

src/library.xi is the library { } manifest (id, version, which files to ship). Host the resulting archive (e.g. a GitHub release) and others depend on its URL as shown in Install.

Layout

• src/sqlite.xi — umbrella file; the one apps import
• src/sqlite/api.xi — namespace sqlite: the data types (Database, Value, Row, Rows), the interfaces (SQLite, RowReader, ColumnDecoder), and the Value-handling defaults (TypedRowReader, TypedColumnDecoder)
• src/sqlite/system.xi — SystemSQLite, the stock implementation over libsqlite3
• src/sqlite/query.xi — SqliteQueryProvider (run + insert + remove) and DatabaseBinder; backs std/query and std/data
• src/sqlite/ffi.xi — raw extern "C" binding (internal)
• src/library.xi — xi pack manifest
• examples/demo.xi — layered demo app: ./scripts/run-demo.sh
• examples/query_demo.xi — std/query demo: ./scripts/run-query-demo.sh
• examples/repository_demo.xi — std/data CRUD repository: ./scripts/run-repository-demo.sh
• tests/sqlite_test.xi — test suite: ./scripts/run-tests.sh

Types, interfaces, and the Value-matching classes share api.xi because a Xi namespace cannot span files; the parts deliberately don't import each other (Xi resolves imports by literal path and would load a file reached via two spellings twice), so always import the umbrella sqlite.xi.

Develop

./scripts/run-demo.sh # builds with xc, runs build/sqlite-demo
./scripts/run-query-demo.sh # the std/query provider demo
./scripts/run-repository-demo.sh # the std/data CRUD repository demo
./scripts/run-tests.sh # xi test tests/sqlite_test.xi
./scripts/pack.sh # xi pack -> dist/xi-sqlite-<version>.tar.gz

Expected demo output:

[info] inserted up to id 2
[info] #1 hello from Xi (4.5 stars)
[info] #2 no C bridge needed (5 stars)