sqlite

The SQLite extension provides embedded database access with parameterised queries, transactions, and full CRUD support.

API

Opening a Database

const db = sqlite.open("/var/www/data/app.db");

Creates the file if it doesn't exist.

Creating Tables

db.exec(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
)`);

Inserting Data

db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
const id = db.lastInsertId(); // 1

Querying Data

// All results
const users = db.query("SELECT * FROM users");
// [{ id: 1, name: "Alice", email: "alice@example.com", created_at: "..." }]

// Single result
const user = db.queryOne("SELECT * FROM users WHERE id = ?", [1]);
// { id: 1, name: "Alice", ... } or null

// With conditions
const recent = db.query("SELECT * FROM users WHERE created_at > ? LIMIT ?", ["2026-01-01", 10]);

Updating and Deleting

db.exec("UPDATE users SET name = ? WHERE id = ?", ["Bob", 1]);
const updated = db.changes(); // 1

db.exec("DELETE FROM users WHERE id = ?", [1]);
const deleted = db.changes(); // 1

Transactions

db.beginTransaction();
try {
    db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
    db.exec("INSERT INTO users (name, email) VALUES (?, ?)", ["Bob", "bob@example.com"]);
    db.commit();
} catch (e) {
    db.rollback();
}

Checking Tables

if (!db.tableExists("users")) {
    db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
}

Parameterised Queries

Always use ? placeholders for user input. This prevents SQL injection:

// Safe
db.query("SELECT * FROM users WHERE name = ?", [request.query.name]);

// UNSAFE - never do this
db.query("SELECT * FROM users WHERE name = '" + request.query.name + "'");

Type Mapping