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 ERROR: 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

Parameter binding — use this for any user-supplied value

Both db_exec and db_query accept a ? placeholder in the SQL plus a list of values. The values are bound as data, never interpreted as SQL — this is how you avoid SQL injection when a value comes from user input:

db_exec("INSERT INTO users (name) VALUES (?)", [userInput]);
db_query("SELECT * FROM users WHERE age > ?", [18]);

Prefer binding over building SQL strings by hand whenever the value isn't a constant you wrote yourself.

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.
• Always bind user-supplied values with ? placeholders (see above) instead of concatenating them into the SQL string.

Next: Compiling & Distributing