The SQLite extension: the shortest path to persistence

Overview

32.1 Intro

Server applications almost always need persistence, and under the single-file philosophy "installing another database service" breaks the whole story. SQLite is the natural answer: embedded, single-file, zero-ops — paired with XServer, "one exe + one directory" genuinely covers stateful business. XServer makes it an optional extension (deployments that skip it carry no baggage); what compiles in is the full native API with no second wrapper.

32.2 Building in & opening

/* prerequisite: the build.bat sqlite variant; in the script */
#include <sqlite3.h>   /* in the VFS, present only in the sqlite variant */

static sqlite3* G_DB;   /* generation state: reopened per generation (see 32.4) */

static void DB_Init(void)
{
    char sDBFile[512];
    snprintf(sDBFile, sizeof(sDBFile), "%s/db/main.db", xsAppPath());
    iResult = sqlite3_open_v2(sDBFile, &G_DB,
        SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE   /* read/write, create if absent */
        | SQLITE_OPEN_FULLMUTEX,                     /* multi-worker safe */
        NULL);
    if ( iResult != SQLITE_OK ) {
        printf("[db] open failed: %s\n", sqlite3_errmsg(G_DB));
        G_DB = NULL;
        return;
    }
    (void)sqlite3_exec(G_DB,
        "CREATE TABLE IF NOT EXISTS item ("
        "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "  name TEXT NOT NULL, created INTEGER)",
        NULL, NULL, NULL);
}
static void DB_Unit(void) { if ( G_DB ) { sqlite3_close(G_DB); G_DB = NULL; } }

What just happened: the boot log's [demo] database opened is exactly this code; FULLMUTEX lets a single handle serve 12 workers concurrently; the db/ directory sits inside the app directory — data files are naturally cross-generation (ch. 05's layout promise fulfilled: reopen the handle per swap, data intact).

32.3 The five statement steps in practice

/* insert: bind indices start at 1; TRANSIENT = SQLite copies the parameter */
static bool DB_InsertItem(const char* sName)
{
    sqlite3_stmt* p = NULL;
    bool bOK = sqlite3_prepare_v2(G_DB,
            "INSERT INTO item(name, created) VALUES(?1, ?2)",
            -1, &p, NULL) == SQLITE_OK;
    if ( bOK ) {
        sqlite3_bind_text(p, 1, sName, -1, SQLITE_TRANSIENT);
        sqlite3_bind_int64(p, 2, (int64)time(NULL));
        bOK = sqlite3_step(p) == SQLITE_DONE;
    }
    sqlite3_finalize(p);          /* every prepare gets a finalize */
    return bOK;
}

/* query: step advances a row, column reads by index (columns start at 0) */
while ( sqlite3_step(p) == SQLITE_ROW ) {
    int64 iId   = sqlite3_column_int64(p, 0);
    const unsigned char* sName = sqlite3_column_text(p, 1);
    /* ... fill an xvalue array → ReplyJSON ... */
}

Parameter binding (?N placeholders) is not a style preference but a security boundary: concatenating SQL strings is injection. The demo binds everywhere — also why routebench could safely pour in 2,000 bench-write rows.

32.4 Transactions & multiple workers

• Batch writes must group: the demo's bench-write wraps bulk inserts in a transaction — a thousand rows go from "one fsync each" to "one per group", a one-to-two-order-of-magnitude difference
• FULLMUTEX's boundary: handle-level mutual exclusion guarantees "no crash", not "fast" — hot write paths still deserve application-level queuing (a single write queue or per-business databases)
• Statement objects don't cross threads: keep prepare-step-finalize closed within one callback; reusing statements across requests means reset (never re-stepping a half-run statement)
• The generation decision (ch. 28's two postures): the demo chooses "reopen per generation" — zero-cost and naturally safe; a global singleton handle suits "reopening is expensive + reloads are frequent", at the price of managing cross-generation references yourself
• Backup: sqlite3_backup_* is inside the exported surface — online hot-backup scripts can be written directly

32.5 Contract

• Variant: sqlite symbols exist only in the sqlite variant; a script failing to compile under default is expected behavior.
• Security: parameter binding always; concatenated SQL is an open door.
• Lifecycle: the handle is generation state (reopen) or an explicit global singleton — pick one and write it into the module comment.

Common mistakes

Exercises