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 QueryProvider for Xi's std/query: write a typed query.from<T>(...) chain and run it as parameterized SQL — see 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/releases/download/v0.2.0/xi-sqlite-0.2.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 "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!
}

// all SQL lives here; the rest of the app never sees it
class NoteSQLiteStore implements NoteRepository {
    deps { sql: sqlite.SQLite, reader: sqlite.RowReader }

    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) }
        return ok(NoteSession { db: db })
    }

    producer list(s: NoteSession) -> Notes! {
        let rows = sql.query(s.db, "select id, title, stars from notes order by id")
        if isErr(rows) { return err(rows.err) }
        let notes = empty List<Note>
        for row in rows.value.items {
            notes.push(Note {
                id:    reader.intAt(row, "id", 0),
                title: reader.textAt(row, "title", ""),
                stars: reader.numberAt(row, "stars", 0.0)
            })
        }
        return ok(Notes { items: notes })
    }
    // ... add/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 {}

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.

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.

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.

The provider 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 + DatabaseBinder, running std/query plans as SQL
• 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
• 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-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)