new SQLite(urlOrPath, optsopt, optsopt)
SQLite main class.
Example
var { SQLite } = require("sigma/sqlite");
var db = new SQLite("file:./my.db", { timeoutMs: 2000, foreignKeys: true });
db.run("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, name TEXT)");
var res = db.run("INSERT INTO test(name) VALUES(?)", ["Alice"]);
console.log(res.changes, res.lastInsertId);
Parameters:
|
string
|
urlOrPath |
|
SQLite connection string or file path. |
|
Object
|
opts |
<optional>
|
Options: { readOnly?:boolean, timeoutMs?:number, foreignKeys?:boolean }
Examples:
- "jdbc:sqlite:/abs/path.db"
- "file:./test.db"
- "./test.db" |
|
SQLiteOptions
|
opts |
<optional>
|
|
all(sql, paramsopt) → {Array.<Object>}
Fetch all rows from a SELECT.
Example
var db = new SQLite("file:./demo.db");
db.exec("CREATE TABLE IF NOT EXISTS t(id INTEGER)");
db.runMany("INSERT INTO t(id) VALUES(?)", [[1],[2],[3]]);
var rows = db.all("SELECT * FROM t");
rows.forEach(r => print(r.id));
Parameters:
|
string
|
sql |
|
|
|
Array
|
Object
|
params |
<optional>
|
|
Returns:
|
Array.<Object>
|
close() → {void}
Close connection.
Example
var db = new SQLite("file:./demo.db");
// ... do stuff
db.close();
Returns:
|
void
|
enableWAL() → {string}
Enable Write-Ahead Logging mode.
Example
var db = new SQLite("file:./demo.db");
db.enableWAL(); // -> "WAL"
Returns:
|
string
|
New journal mode.
|
exec(sql) → {number}
Execute one or more SQL statements (no params).
NOTE: naive splitter; avoid semicolons in string literals.
Example
var db = new SQLite("file:./demo.db");
var count = db.exec("PRAGMA foreign_keys=ON; CREATE TABLE IF NOT EXISTS log(msg TEXT)");
print("exec count=" + count);
Parameters:
|
string
|
sql |
Multi-statement string |
Returns:
|
number
|
Total affected rows.
|
get(sql, paramsopt) → {Object|null}
Fetch the first row from a SELECT.
Example
var db = new SQLite("file:./demo.db");
var row = db.get("SELECT 42 AS answer");
print(JSON.stringify(row)); // {"answer":42}
Parameters:
|
string
|
sql |
|
|
|
Array
|
Object
|
params |
<optional>
|
|
Returns:
|
Object
|
null
|
migrate(steps) → {number}
Apply schema changes only once, tracked in _migrations table.
Example
var db = new SQLite("file:./demo.db");
var steps = [
{ key: "init", sql: "CREATE TABLE IF NOT EXISTS u(id INTEGER PRIMARY KEY, name TEXT)" },
{ key: "age", sql: "ALTER TABLE u ADD COLUMN age INTEGER DEFAULT 0" }
];
var applied = db.migrate(steps);
print("applied=" + applied);
Parameters:
|
Array.<{key: string, sql: string}>
|
steps |
|
Returns:
|
number
|
Number of applied steps.
|
open(urlOrPath) → {this}
Open a connection if not already open.
Example
var db = new SQLite("file:./demo.db");
db.open("file:./test.db"); // idempotent, safe to call again
Parameters:
|
string
|
urlOrPath |
Database file path or URI |
Returns:
|
this
|
run(sql, paramsopt) → {Object}
Execute INSERT/UPDATE/DELETE (returns { changes, lastInsertId }).
Example
var db = new SQLite("file:./demo.db");
db.exec("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT)");
var res = db.run("INSERT INTO users(name) VALUES(?)", ["Alice"]);
print("changes=" + res.changes + " lastId=" + res.lastInsertId);
Parameters:
|
string
|
sql |
|
SQL statement with placeholders. |
|
Array
|
Object
|
params |
<optional>
|
Parameters (positional or named). |
Returns:
|
Object
|
runMany(sql, rows, optionsopt) → {Object}
Execute the same statement for multiple parameter sets.
Example
var db = new SQLite("file:./demo.db");
db.exec("CREATE TABLE IF NOT EXISTS t(name TEXT, age INTEGER)");
var out = db.runMany("INSERT INTO t(name, age) VALUES(:name, :age)", [
{ name: "Tom", age: 20 },
{ name: "Jerry", age: 21 }
]);
print(JSON.stringify(out));
Parameters:
|
string
|
sql |
|
Statement with placeholders. |
|
Array.<Object>
|
Array.<Array>
|
rows |
|
Array of objects or arrays. |
|
Object
|
options |
<optional>
|
Properties
|
boolean
|
transactional |
<optional>
|
true
|
Wrap in a transaction. |
|
number
|
batchSize |
<optional>
|
500
|
Batch size for array rows. |
|
boolean
|
continueOnError |
<optional>
|
false
|
Skip failed rows. |
|
function
|
onError |
<optional>
|
|
Called with (row, index, error) on failure. |
|
Returns:
|
Object
|
setJournalMode(mode) → {string|null}
Set journal_mode and return the resulting mode reported by SQLite.
Example
var db = new SQLite("file:./demo.db");
var newMode = db.setJournalMode("WAL");
print(newMode); // "WAL"
Parameters:
|
"DELETE"
|
"TRUNCATE"
|
"PERSIST"
|
"MEMORY"
|
"WAL"
|
"OFF"
|
mode |
|
Returns:
|
string
|
null
|
effective journal mode
|
transaction(fn, modeopt) → {*}
Run a function inside a BEGIN/COMMIT block.
Example
var db = new SQLite("file:./demo.db");
db.exec("CREATE TABLE IF NOT EXISTS c(v INTEGER)");
db.transaction(conn => {
conn.run("INSERT INTO c(v) VALUES(?)", [1]);
conn.run("INSERT INTO c(v) VALUES(?)", [2]);
}, "immediate");
Parameters:
|
function
|
fn |
|
|
Function receiving this db instance. |
|
"deferred"
|
"immediate"
|
"exclusive"
|
mode |
<optional>
|
"deferred"
|
Transaction mode. |
Returns:
|
*
|
Value returned by fn.
|
(static) uri(fileOrPath, queryopt) → {string}
Build a JDBC SQLite URL with URI query options.
Example:
const url = SQLite.uri("file:./test.db", { mode: "ro", cache: "shared" });
// -> "jdbc:sqlite:file:./test.db?mode=ro&cache=shared"
(Fix) If fileOrPath already starts with "jdbc:sqlite:", do not prepend again.
Example
var url = SQLite.uri("./demo.db", { mode: "ro", cache: "shared" });
print(url); // jdbc:sqlite:file:./demo.db?mode=ro&cache=shared
Parameters:
|
string
|
fileOrPath |
|
e.g. "./test.db" | "file:./test.db" | "jdbc:sqlite:file:./test.db" |
|
object
|
query |
<optional>
|
Properties
|
"ro"
|
"rw"
|
"rwc"
|
"memory"
|
mode |
<optional>
|
|
|
"shared"
|
"private"
|
cache |
<optional>
|
|
|
boolean
|
immutable |
<optional>
|
|
|
string
|
vfs |
<optional>
|
|
|
boolean
|
nolock |
<optional>
|
|
|
boolean
|
psow |
<optional>
|
|
|
Returns:
|
string
|
JDBC connection URI.
|