14. Database (SQLite)

STencil has a built-in SQLite database — real, persistent storage in a single file.

Open a database

db_open("app.db");   // creates the file if it doesn't exist; returns bool

There is one active database connection at a time.

Run statements: db_exec

For CREATE, INSERT, UPDATE, DELETE. Returns the number of affected rows (or a string starting with HATA: on error).

db_exec("CREATE TABLE IF NOT EXISTS person (id INTEGER PRIMARY KEY, name TEXT, age INT)");
db_exec("INSERT INTO person (name, age) VALUES ('Ember', 20)");
db_exec("INSERT INTO person (name, age) VALUES ('Ada', 30)");

Query rows: db_query

Returns a list of dicts (one dict per row, keyed by column name).

let rows = db_query("SELECT * FROM person ORDER BY age");
print(len(rows), "rows");
for r in rows {
    print(r["name"], "is", r["age"]);
}
// Ember is 20
// Ada is 30

A tiny persistent example

db_open("notes.db");
db_exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, text TEXT)");
db_exec("INSERT INTO notes (text) VALUES ('buy milk')");

let all = db_query("SELECT * FROM notes");
for n in all {
    print("#" + n["id"] + " " + n["text"]);
}

Because it writes to a file, the data is still there next time you run the program.

Notes

• Column values come back as int / float / string / null automatically.
• For user-supplied values prefer building SQL carefully; current db_exec takes a full SQL string (no parameter binding helper yet) — sanitize untrusted input.

Next: Compiling & Distributing