app.database - SQLite Database API

SQLite database operations.

Permission: L2 Sensitive — declared; warns the user at install time ("database")

The module has a single entry point, app.database.open(path), which returns a database handle. Everything after that is a method on the handle, called with a colon:

local db = app.database.open("~/data/my.db")
db:execute("CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY)")
local rows = db:query("SELECT * FROM t")
db:close()

app.database.open(path)

Opens or creates a database.

Parameters:

• path (string) - Database file path (~ is expanded)

Returns: dbHandle; returns nil, error on failure

local db, err = app.database.open("~/data/my.db")
if not db then
    app.log.error("Could not open database: " .. tostring(err))
    return
end

Where to put the database. Not in app.context.pluginDirectory() — that is bundled content, and updating the plugin replaces the whole directory, taking the database with it. Use app.context.dataDirectory(), which survives updates:

local dbPath = app.path.join(app.context.dataDirectory(), "index.db")

Handle methods

db:execute(sql, params?)

Runs a statement (INSERT / UPDATE / DELETE / CREATE and so on).

Parameters:

• sql (string) - The statement
• params (array, optional) - Values bound to the ? placeholders

Returns: boolean; returns false, error on failure

db:execute([[
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        created_at INTEGER DEFAULT (strftime('%s', 'now'))
    )
]])

-- Bind values as parameters; never concatenate them into the SQL — that is injection
db:execute("INSERT INTO users (name, email) VALUES (?, ?)",
    {"Alice", "alice@example.com"})

db:query(sql, params?)

Runs a query.

Parameters:

• sql (string) - The query
• params (array, optional) - Values bound to the ? placeholders

Returns: array of rows, each a column-name-to-value table; nil, error on failure

local users = db:query("SELECT * FROM users")
if users then
    for _, user in ipairs(users) do
        app.log.info(user.name .. " - " .. user.email)
    end
end

local results = db:query("SELECT * FROM users WHERE id = ?", {1})

db:begin() / db:commit() / db:rollback()

Transaction control. All three return boolean, or false, error on failure.

db:begin()

local ok1 = db:execute("INSERT INTO users (name) VALUES (?)", {"User 1"})
local ok2 = db:execute("INSERT INTO users (name) VALUES (?)", {"User 2"})

if ok1 and ok2 then
    db:commit()
else
    db:rollback()
end

db:changes()

Rows affected by the last statement.

Returns: number

db:execute("UPDATE users SET name = ? WHERE id > ?", {"Updated", 10})
local affected = db:changes()

db:lastInsertId()

The row ID of the last insert.

Returns: number

db:execute("INSERT INTO users (name) VALUES (?)", {"Test"})
local id = db:lastInsertId()

db:tableExists(tableName)

Whether a table exists.

Returns: boolean; false, error on failure

if not db:tableExists("users") then
    db:execute("CREATE TABLE users (id INTEGER PRIMARY KEY)")
end

db:tables()

Lists all table names.

Returns: array; nil, error on failure

for _, name in ipairs(db:tables() or {}) do
    app.log.info("Table: " .. name)
end

db:columns(tableName)

Column information for a table.

Returns: array, each entry with name / type / notnull / defaultValue / primaryKey; nil, error on failure

for _, col in ipairs(db:columns("users") or {}) do
    app.log.info(col.name .. " - " .. col.type)
end

db:close()

Closes the connection.

db:close()

Examples

A file index database

function MyPlugin:init()
    -- In the data directory, not the plugin directory: the latter is replaced on update
    local dbPath = app.path.join(app.context.dataDirectory(), "index.db")

    local db, err = app.database.open(dbPath)
    if not db then
        app.log.error("Could not open the index: " .. tostring(err))
        return
    end
    self.db = db

    self.db:execute([[
        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY,
            path TEXT UNIQUE,
            name TEXT,
            size INTEGER,
            modified INTEGER,
            hash TEXT,
            indexed_at INTEGER
        )
    ]])

    self.db:execute("CREATE INDEX IF NOT EXISTS idx_files_name ON files(name)")
end

function MyPlugin:indexFile(path)
    self.db:execute([[
        INSERT OR REPLACE INTO files (path, name, size, modified, hash, indexed_at)
        VALUES (?, ?, ?, ?, ?, ?)
    ]], {
        path,
        app.path.basename(path),
        app.file.size(path),
        app.file.modificationDate(path),
        app.crypto.md5File(path),
        app.date.now()
    })
end

function MyPlugin:searchFiles(query)
    return self.db:query(
        "SELECT * FROM files WHERE name LIKE ? ORDER BY modified DESC",
        {"%" .. query .. "%"}
    )
end

function MyPlugin:cleanup()
    if self.db then self.db:close() end
end

Wrap bulk writes in a transaction

Without one, every execute is its own transaction, which gets noticeably slow at a few thousand rows. Wrapping them in one transaction means a single flush:

db:begin()
for _, row in ipairs(rows) do
    db:execute("INSERT INTO t (a, b) VALUES (?, ?)", {row.a, row.b})
end
db:commit()