ScriptWeaver View source

Database (SQLite)

Built-in SQLite, embedded in the Player — no external database, driver, or network. The JS API mirrors better-sqlite3 (the popular Node binding), so it should feel familiar. All operations are synchronous — no callbacks or promises.

const db = new Database(':memory:');
db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');

const insert = db.prepare('INSERT INTO users (name) VALUES (?)');
insert.run('Alice');
insert.run('Bob');

const row = db.prepare('SELECT * FROM users WHERE id = ?').get(1);
console.log(row); // { id: 1, name: 'Alice' }

db.close();

Opening & closing

const mem = new Database(':memory:'); // in-memory, discarded on close
const file = new Database('/path/to/app.db'); // on disk (created if absent)

Connections are not garbage-collected — always close() when done:

db.close();

Running statements

exec(sql) runs one or more statements with no parameters and no result — ideal for schema setup. It returns the Database, so calls chain:

db.exec(`
  CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
  CREATE INDEX notes_body ON notes (body);
`);

For one-off parameterised statements, run / get / all prepare, execute, and finalise in a single call:

db.run('INSERT INTO notes (body) VALUES (?)', 'hello'); // → { changes, lastInsertRowid }
db.get('SELECT * FROM notes WHERE id = ?', 1); // → first row, or undefined
db.all('SELECT * FROM notes'); // → array of rows

For anything you run more than once, prepare it once and reuse the statement.

Prepared statements

prepare(sql) returns a reusable Statement:

const byId = db.prepare('SELECT * FROM users WHERE id = ?');
const a = byId.get(1);
const b = byId.get(2); // reuses the compiled statement

byId.finalize(); // release it when done
Method For Returns
run(...params) INSERT / UPDATE / DELETE { changes, lastInsertRowid }
get(...params) SELECT the first row as { column: value }, or undefined
all(...params) SELECT an array of all rows
iterate(...params) SELECT a generator that yields rows lazily
finalize() — releases the statement (no GC cleanup)

iterate is the memory-friendly choice for large result sets — it streams rather than materialising every row:

for (const row of db.prepare('SELECT * FROM big').iterate()) {
  if (done(row)) break; // the statement is reset even on early break
}

Binding parameters

Positional — ? placeholders, filled left to right:

db.prepare('INSERT INTO users (name, age) VALUES (?, ?)').run('Alice', 30);

Named — pass a single object; its keys are matched against :key, $key, and @key placeholders:

const ins = db.prepare('INSERT INTO users (name, age) VALUES (:name, :age)');
ins.run({ name: 'Bob', age: 42 });

Transactions

transaction(fn) wraps a function in BEGIN / COMMIT, rolling back automatically if it throws. It returns a function that forwards its arguments to fn and returns fn's result:

const insertMany = db.transaction((rows) => {
  const stmt = db.prepare('INSERT INTO users (name) VALUES (?)');
  for (const r of rows) stmt.run(r.name);
  stmt.finalize();
  return rows.length;
});

const n = insertMany([{ name: 'Cara' }, { name: 'Dan' }]); // commits, or rolls back on throw

API reference

Database

Member Description
new Database(path) Open path (or ':memory:').
exec(sql) Run statement(s) with no params/results; returns the Database.
prepare(sql) Compile sql into a reusable Statement.
run(sql, ...params) One-shot prepare + run; { changes, lastInsertRowid }.
get(sql, ...params) One-shot prepare + get; first row or undefined.
all(sql, ...params) One-shot prepare + all; array of rows.
transaction(fn) Wrap fn in BEGIN/COMMIT (ROLLBACK on throw).
changes Rows changed by the most recent write.
lastInsertRowid Rowid of the most recent insert.
close() Close the connection.

Statement (from db.prepare)

Member Description
run(...params) Execute; { changes, lastInsertRowid }.
get(...params) First row, or undefined.
all(...params) All rows.
iterate(...params) Generator yielding rows lazily.
columnCount Number of result columns.
columnName(i) Name of column i.
columnDeclType(i) Declared type of column i.
finalize() Release the statement.

Lifetime — there is no automatic cleanup. Three garbage collectors run under the hood, but the SQLite handles aren't tied to any of them. Release every prepare()d statement with finalize() and every connection with close(). One-shot db.run/get/all finalise their statement for you.

See also