app.database - SQLite Database API

SQLite database operations.

Connection Management

app.database.open(path)

Open or create a database.

Parameters:

• path (string) - Database file path (supports ~ expansion)

Returns: number, error - Database handle; returns nil, error on failure

local db, err = app.database.open("~/data/my.db")
if db then
    -- Use the database
    app.database.close(db)
end

app.database.close(handle)

Close a database connection.

Parameters:

• handle (number) - Database handle

Returns: boolean, error - Whether successful; returns false, error on failure

local ok, err = app.database.close(db)

SQL Execution

app.database.execute(handle, sql, params?)

Execute a SQL statement (INSERT/UPDATE/DELETE/CREATE, etc.).

Parameters:

• handle (number) - Database handle
• sql (string) - SQL statement
• params (array, optional) - Parameter array

Returns: boolean, error - Whether successful; returns false, error on failure

-- Create a table
app.database.execute(db, [[
    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'))
    )
]])

-- Insert data (positional parameters)
app.database.execute(db, "INSERT INTO users (name, email) VALUES (?, ?)",
    {"John", "john@example.com"})

app.database.query(handle, sql, params?)

Query data.

Parameters:

• handle (number) - Database handle
• sql (string) - SQL query statement
• params (array, optional) - Parameter array

Returns: array, error - Array of result rows (each row is a key-value table); returns nil, error on failure

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

-- Query with conditions
local results = app.database.query(db, "SELECT * FROM users WHERE id = ?", {1})

Transactions

app.database.begin(handle)

Begin a transaction.

Returns: boolean, error - Whether successful; returns false, error on failure

app.database.begin(db)

app.database.commit(handle)

Commit a transaction.

Returns: boolean, error - Whether successful; returns false, error on failure

app.database.commit(db)

app.database.rollback(handle)

Roll back a transaction.

Returns: boolean, error - Whether successful; returns false, error on failure

app.database.rollback(db)

Transaction example:

app.database.begin(db)
local ok1 = app.database.execute(db, "INSERT INTO users (name) VALUES (?)", {"User1"})
local ok2 = app.database.execute(db, "INSERT INTO users (name) VALUES (?)", {"User2"})

if ok1 and ok2 then
    app.database.commit(db)
else
    app.database.rollback(db)
end

Helper Methods

app.database.changes(handle)

Get the number of rows affected by the last operation.

Returns: number

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

app.database.lastInsertId(handle)

Get the last inserted row ID.

Returns: number

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

app.database.tableExists(handle, tableName)

Check if a table exists.

Parameters:

• handle (number) - Database handle
• tableName (string) - Table name

Returns: boolean, error - Returns false, error on failure

if not app.database.tableExists(db, "users") then
    -- Create the table
end

app.database.tables(handle)

List all table names.

Returns: array, error - Array of table names; returns nil, error on failure

local tables = app.database.tables(db)
if tables then
    for _, name in ipairs(tables) do
        app.log.info("Table: " .. name)
    end
end

app.database.columns(handle, tableName)

Get column information of a table.

Parameters:

• handle (number) - Database handle
• tableName (string) - Table name

Returns: array, error - Array of column info; returns nil, error on failure

local columns = app.database.columns(db, "users")
if columns then
    for _, col in ipairs(columns) do
        app.log.info(col.name .. " - " .. col.type)
        -- col.notnull: boolean
        -- col.defaultValue: any
        -- col.primaryKey: boolean
    end
end

Examples

File Index Database

function MyPlugin:init()
    local pluginDir = app.context.pluginDirectory()
    local dbPath = app.path.join(pluginDir, "index.db")
    self.db = app.database.open(dbPath)

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

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

function MyPlugin:indexFile(path)
    local size = app.file.size(path)
    local modified = app.file.modificationDate(path)
    local hash = app.crypto.md5File(path)

    app.database.execute(self.db, [[
        INSERT OR REPLACE INTO files (path, name, size, modified, hash, indexed_at)
        VALUES (?, ?, ?, ?, ?, ?)
    ]], {
        path,
        app.path.basename(path),
        size,
        modified,
        hash,
        app.date.now()
    })
end

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

function MyPlugin:cleanup()
    app.database.close(self.db)
end