Luau Bindings — db
The require("db") script API — raw DBFS/SQLite access. This is the layer beneath fs: fs gives you files, metadata, tags, and the safe fs.query catalogue search; db gives you arbitrary SQL against the same SQLite database — for custom application tables and queries fs.query can't express. Every function is [antos]. Design & native core: antos_db.
local db = require("db")
local n = db.scalar("SELECT count(*) FROM files WHERE kind = ?", { "dir" })
db operates on the active DBFS (sys.db on D:) by default. All calls are D:-only — off DBFS they return nil, err. Always parameterise — bind values as ? (positional, from an array) or :name (named, from a table); never concatenate values into SQL.
Querying
| Function |
Behaviour |
db.query(sql [, params]) |
Run a SELECT; returns an array of row tables, or nil, err. |
db.rows(sql [, params]) |
Iterator over result rows — streams, for large result sets. |
db.get(sql [, params]) |
First row as a table, or nil. |
db.scalar(sql [, params]) |
First column of the first row (a single value), or nil. |
db.exec(sql [, params]) |
Run INSERT/UPDATE/DELETE/DDL; returns { changes, last_rowid }, or nil, err. |
local db = require("db")
-- a custom application table (prefix keeps it clear of system tables)
db.exec("CREATE TABLE IF NOT EXISTS app_notes_items (id INTEGER PRIMARY KEY, body TEXT, done INTEGER DEFAULT 0)")
db.exec("INSERT INTO app_notes_items (body) VALUES (?)", { "buy milk" })
for row in db.rows("SELECT id, body FROM app_notes_items WHERE done = 0") do
print(row.id, row.body)
end
Prepared statements
| Function |
Behaviour |
db.prepare(sql) |
Compile once → a statement handle for repeated execution. |
Handle methods: stmt:query(params), stmt:rows(params), stmt:get(params), stmt:scalar(params), stmt:exec(params), stmt:close().
local ins = db.prepare("INSERT INTO app_log_lines (ts, msg) VALUES (?, ?)")
for _, m in ipairs(messages) do ins:exec({ os.time(), m }) end
ins:close()
Transactions
| Function |
Behaviour |
db.transaction(fn) |
Run fn inside a transaction — commits if it returns normally, rolls back if it errors or returns false. Returns fn's result. |
db.begin() · db.commit() · db.rollback() |
Manual control, when the scoped form doesn't fit. |
db.transaction(function()
db.exec("UPDATE app_bank SET balance = balance - ? WHERE id = ?", { 10, 1 })
db.exec("UPDATE app_bank SET balance = balance + ? WHERE id = ?", { 10, 2 })
end) -- both apply, or neither
Schema & introspection
| Function |
Behaviour |
db.tables() |
Array of table names. |
db.columns(table) |
Column info for a table. |
db.schema_version() (alias db.schemaVersion()) |
The DBFS schema version. |
Other databases
| Function |
Behaviour |
db.open(path) |
Open another DBFS / SQLite file → a db handle with the same methods; handle:close(). Default target is the active sys.db. |
Notes
db and fs are two views of one database. Prefer fs for files, metadata, and tags, and fs.query for catalogue search; reach for db when you need custom tables or SQL fs.query can't express.- Name custom tables to avoid collisions with system tables (convention:
app_<name>_…). Writing directly to the system catalogue tables is unsupported — use fs for files and tags.
db writes go through the same DBFS path as everything else, so they are transactional and covered by the D!: shadow backup — a db insert is as durable as a file write.
Related