nish/sqlite

nish/sqlite (the file std/sqlite.ts) — SQLite, the database in a file, from a Nish program.

import {
  Cursor,
  Database,
  OPEN_CREATE,
  OPEN_MEMORY,
  OPEN_NOFOLLOW,
  OPEN_READONLY,
  OPEN_READWRITE,
  OPEN_URI,
  SEEK_GE,
  SEEK_GT,
  SEEK_LE,
  SEEK_LT,
  SQLITE_ABORT,
  SQLITE_BLOB,
  SQLITE_BUSY,
  SQLITE_CANTOPEN,
  SQLITE_CONSTRAINT,
  SQLITE_CORRUPT,
  SQLITE_DONE,
  SQLITE_ERROR,
  SQLITE_FLOAT,
  SQLITE_FULL,
  SQLITE_INTEGER,
  SQLITE_INTERNAL,
  SQLITE_IOERR,
  SQLITE_LOCKED,
  SQLITE_MISMATCH,
  SQLITE_MISUSE,
  SQLITE_NOMEM,
  SQLITE_NOTADB,
  SQLITE_NULL,
  SQLITE_OK,
  SQLITE_PERM,
  SQLITE_RANGE,
  SQLITE_READONLY,
  SQLITE_ROW,
  SQLITE_SCHEMA,
  SQLITE_TEXT,
  SQLITE_TOOBIG,
  SqliteError,
  Statement,
  openDatabase,
  sqliteVersion,
} from "nish/sqlite";
import { openDatabase, OPEN_READWRITE, OPEN_CREATE } from "nish/sqlite"

const db = openDatabase("app.db", OPEN_READWRITE | OPEN_CREATE).expect("open")
db.exec("CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, name TEXT)").expect("create")
const insert = db.prepare("INSERT INTO t (name) VALUES (?1)").expect("prepare")
insert.bindText(1, "ada").expect("bind")
insert.step().expect("insert")
insert.reset().expect("reset")

const rows = db.prepare("SELECT id, name FROM t ORDER BY id").expect("prepare")
const buf = new Array<u8>(64)
while (rows.step().expect("step")) {
  const n = rows.columnInto(1, buf, 0).expect("name")
  // n > buf.length means nothing was copied: grow buf and read again
  console.log(`${rows.columnI64(0)} ${String.fromBytes(buf, 0, n)}`)
}
db.close().expect("close")  // finalizes `insert` and `rows` too

It is the SQLite C library, vendored in runtime/sqlite/ and linked into a program that imports this module (scripts/build.sh), with one small C adapter beside it (runtime/sqlite/nish-sqlite.c). A connection and a prepared statement are handles into the adapter's tables, so a Database and a Statement are an i32 each, and a handle used after close or finalize answers MISUSE instead of reaching freed memory.

The loop allocates nothing

Every call a row makes — bind, step, reset, columnI64, columnF64, columnInto — answers a bare scalar or a Result of two scalars, which travels as one machine word, and copies nothing but what it is asked for: columnInto copies a text or blob column into the caller's u8[] with one memcpy. columnText is the convenience that allocates a string. Building a SqliteError allocates, which is why the hot calls answer the bare result code and error(code) builds the message only for the caller who wants it.

Results are SQLite's own codes

Results are SQLite's own codes, extended (SQLITE_CONSTRAINT_UNIQUE rather than SQLITE_CONSTRAINT), so a program can compare them with the constants below or with SQLite's documentation. SqliteError.code is the primary code, the low byte. openDatabase and prepare answer a built SqliteError, since they run once and the caller nearly always wants the message; every other call answers Err with the bare extended code, and Database.error or Statement.error turns it into a SqliteError.

A cursor reads a table or index without SQL

Inside a read transaction (beginRead to endRead), openCursor checks the object's columns against the list the program names, and the Cursor then walks SQLite's B-tree directly: first, next, seekRowid, and for an index a seek key built with keyI64 and keyText and moved to with seek. Its column reads answer what the row stores and, like a statement's, allocate nothing. Every exec, commit, rollback and endRead, and every stepped statement but a plain read (a pragma_* SELECT included), closes every cursor first, so a cursor never reads a page that changed; its calls answer SQLITE_MISUSE.

db.beginRead().expect("begin")
const ix = db.openCursor("t_name", "name,rowid").expect("cursor")
ix.keyText("ada").expect("key")
let on = ix.seek(SEEK_GE).expect("seek")
while (on) {
  // ix.columnInto(0, buf, 0) is the name, ix.rowid() the row
  on = ix.next().expect("next")
}
db.endRead().expect("end")  // closes `ix`
Closing is explicit

close finalizes every statement the connection still has, so a statement never outlives its database; finalize frees one statement early. A connection and its statements belong to the thread that opened them (the handle tables are thread-local under --threads).

Calling C is the ffi capability (docs/LANGUAGE.md, "Capabilities"), so a program that imports this module is reported with ffi, and --deny ffi refuses it. The files SQLite opens are opened by C, beneath the fs capability's view.

Functions

openDatabase

Opens the database at path. flags holds one of OPEN_READONLY, OPEN_READWRITE or OPEN_READWRITE | OPEN_CREATE, with any of OPEN_URI, OPEN_MEMORY and OPEN_NOFOLLOW; anything else is SQLITE_MISUSE. The path :memory: is a database of its own that lives as long as the connection. A failed open leaves nothing open behind it.

const openDatabase: (
  path: string,
  flags: i32,
) => Result<Database, SqliteError>
pathstringrequired

The file to open, a file: URI under OPEN_URI, or :memory:.

flagsi32required

The OPEN_* bits, combined with |.

returnsResult<Database, SqliteError>

The connection, or a SqliteError with SQLite's code and message, such as SQLITE_CANTOPEN for a file that cannot be opened.

sqliteVersion

The version of the SQLite library linked into this program.

const sqliteVersion: () => string
returnsstring

The version string, such as 3.50.4.

Classes

Cursor

A B-tree cursor on a table or index, which reads rows without a statement. Columns count from 0 in the order openCursor named them. A call on a closed or invalidated cursor answers SQLITE_MISUSE (0 from rowid and columnI64).

class Cursor
Member
new Cursor(handle: i32) Wraps an adapter handle. A program gets a Cursor from Database.openCursor rather than building one.
close(): Result<void, i32> Closes the cursor; the handle names nothing after.
columnI64(c: i32): i64 Column c of the current row as an integer, converted as SQLite converts.
columnInto(c: i32, buf: u8[], at: i32): Result<i32, i32> Copies a text or blob column's stored bytes into buf at at, and answers their length, as Statement.columnInto does: it copies only when all of them fit, so a length larger than buf.length - at means nothing was written. It converts no number to text.
columnType(c: i32): Result<i32, i32> The type the current row stores for column c, as written: a REAL column holding a whole number stores an integer, where a query would answer a float.
error(code: i32): SqliteError The error for code, which this cursor's last call answered. Allocates the SqliteError.
first(): Result<boolean, i32> Moves to the first row, in the index's order for an index.
keyI64(v: i64): Result<void, i32> Appends an integer to the seek key, for the index's next key column.
keyReset(): Result<void, i32> Empties an index cursor's seek key, to build a new one with keyI64, keyText and keyTextBytes.
keyText(text: string): Result<void, i32> Appends text to the seek key, copied; the text ends at its first NUL.
keyTextBytes(buf: readonly u8[], off: i32, len: i32): Result<void, i32> Appends buf[off, off + len) to the seek key as text, copied; a window outside buf is SQLITE_MISUSE.
last(): Result<boolean, i32> Moves to the last row.
next(): Result<boolean, i32> Moves to the next row.
previous(): Result<boolean, i32> Moves to the row before.
rowid(): i64 The current row's rowid: a table row's key, an index entry's rowid.
seek(op: i32): Result<boolean, i32> Moves an index cursor by its seek key, compared with each entry's first key columns, so a key with fewer values than the index is a prefix: SEEK_GE and SEEK_GT land on the first entry at or after the key, SEEK_LE and SEEK_LT on the last at or before it, the E forms counting an equal entry. The key stays, for the next seek.
seekRowid(rowid: i64): Result<boolean, i32> Moves a table cursor to the row whose rowid is rowid.

Database

A connection. Close it with close, which finalizes its statements too.

class Database
Member
new Database(handle: i32) Wraps an adapter handle. A program gets a Database from openDatabase rather than building one. With a handle that names no connection, a call that can fail fails with SQLITE_MISUSE; lastInsertRowid and changes answer 0, and error answers a message.
begin(): Result<void, i32> Starts a transaction: exec("BEGIN").
beginRead(): Result<void, i32> Starts a read transaction: BEGIN, then a read of the schema, which takes the main database's read lock. Its snapshot is what every cursor of this connection reads until endRead, and openCursor needs one.
changes(): i64 How many rows the last INSERT, UPDATE or DELETE on this connection changed.
close(): Result<void, i32> Finalizes every statement of this connection and closes it; the handles name nothing after. A close that fails has still finalized the statements, but leaves the connection open, its message readable through error.
commit(): Result<void, i32> Commits the open transaction: exec("COMMIT").
endRead(): Result<void, i32> Closes every cursor of this connection and commits the transaction.
error(code: i32): SqliteError The error for code, which this connection's last call answered. Ask before the next call, since the next call replaces the connection's message. Allocates the SqliteError.
exec(sql: string): Result<void, i32> Runs every statement of sql in turn, discarding any rows. It stops at the first statement that fails, so the ones before it have run.
lastInsertRowid(): i64 The rowid of the last row an INSERT on this connection made.
openCursor(name: string, columns: string): Result<Cursor, SqliteError> Opens a cursor on name, a table or index of the main database, inside a read transaction (beginRead). columns is the schema the program reads, checked at open: a table's columns, or an index's key columns, each with DESC where the index has it, and then rowid, in order, joined by , with no space. A table that is WITHOUT ROWID, a view or virtual table, one with a generated column, an index on an expression or with a collation other than BINARY, and a database that is not UTF-8 are refused. close, endRead, closing the connection, any exec, and any stepped statement but a plain read (pragma_* SELECTs too) close it.
prepare(sql: string): Result<Statement, SqliteError> Compiles the first statement of sql, to be bound, stepped and reset as often as wanted; text after it is ignored. Text with no statement in it is SQLITE_MISUSE. The statement belongs to this connection, and close finalizes it.
rollback(): Result<void, i32> Undoes the open transaction: exec("ROLLBACK").

SqliteError

A failure, with SQLite's message for it. Built only when asked for, by error(code).

class SqliteError
Member
new SqliteError(extendedCode: i32, message: string) Builds the error from an extended code, keeping its low byte as code.
code: i32 The primary result code, such as SQLITE_CONSTRAINT.
extendedCode: i32 The extended code, such as SQLITE_CONSTRAINT_UNIQUE (2067).
message: string SQLite's message, such as UNIQUE constraint failed: t.id.

Statement

A prepared statement. Parameters count from 1 (?1, or the order of ?), columns from 0, as in SQLite.

class Statement
Member
new Statement(handle: i32) Wraps an adapter handle. A program gets a Statement from Database.prepare rather than building one. With a handle that names no statement, a call that can fail fails with SQLITE_MISUSE, and the other column readers answer as for a column that is not there: 0, SQLITE_NULL, or null.
bindBlob(i: i32, buf: readonly u8[], off: i32, len: i32): Result<void, i32> Binds buf[off, off + len) as a blob, copied, so buf may change after the call; a window outside buf is SQLITE_MISUSE.
bindF64(i: i32, v: f64): Result<void, i32> Binds a floating-point value to parameter i.
bindI64(i: i32, v: i64): Result<void, i32> Binds an integer to parameter i.
bindNull(i: i32): Result<void, i32> Binds SQL NULL to parameter i.
bindText(i: i32, text: string): Result<void, i32> Binds text to parameter i; SQLite copies it, so the string need not outlive the call. The text ends at its first NUL.
bindTextBytes(i: i32, buf: readonly u8[], off: i32, len: i32): Result<void, i32> Binds buf[off, off + len) as text, copied, so buf may change after the call; a window outside buf is SQLITE_MISUSE. The bytes should be UTF-8 and may hold a NUL.
clearBindings(): Result<void, i32> Clears every parameter back to NULL. reset keeps the bindings, so this is the call for a run that must not see the last one's values.
columnBytes(c: i32): i32 How many bytes columnInto would copy for column c: a blob's length, or anything else's length as text. Ask it to size a buffer before the copy.
columnCount(): i32 The number of columns a row of this statement has, known from prepare on, before the first step.
columnF64(c: i32): f64 Column c of the current row as a floating-point value, converted as SQLite converts.
columnI64(c: i32): i64 Column c of the current row as an integer, converted as SQLite converts (text "12abc" is 12, a float is truncated).
columnInto(c: i32, buf: u8[], at: i32): Result<i32, i32> Copies column c — a blob's bytes, anything else as text — into buf at at, and answers its length. It copies only when all of it fits, so a length larger than buf.length - at means nothing was written and says how much room is needed. SQLITE_RANGE for a column that is not there, SQLITE_MISUSE for an at outside 0..buf.length.
columnText(c: i32): string | null Column c of the current row as a new string, cut at its first NUL. Allocates on every call; columnInto is the call for a hot loop.
columnType(c: i32): i32 The type of column c in the current row.
error(code: i32): SqliteError The error for code, which this statement's last call answered. Ask before the next call, since the next call on the connection replaces its message. Allocates the SqliteError.
finalize(): Result<void, i32> Frees the statement early; its handle names nothing after. Without this, Database.close frees it.
reset(): Result<void, i32> Rewinds the statement to run again, keeping its bindings. A failure of the last step is not answered again here: step already answered it.
step(): Result<boolean, i32> Runs the statement to its next row. Call reset to run it again from the start, with the same bindings.

Constants

0x4

OPEN_CREATE: i32

With OPEN_READWRITE, create the file when it is not there.

0x80

OPEN_MEMORY: i32

An in-memory database, the path only naming it.

0x01000000

OPEN_NOFOLLOW: i32

Refuse a path whose last component is a symbolic link.

0x1

OPEN_READONLY: i32

Open for reading only. One of the three modes openDatabase needs.

0x2

OPEN_READWRITE: i32

Open for reading and writing; the file must exist unless OPEN_CREATE is given too.

0x40

OPEN_URI: i32

Read the path as a file: URI.

1

SEEK_GE: i32

Cursor.seek: the first index entry at or after the key.

2

SEEK_GT: i32

Cursor.seek: the first index entry after the key.

3

SEEK_LE: i32

Cursor.seek: the last index entry at or before the key.

4

SEEK_LT: i32

Cursor.seek: the last index entry before the key.

4

SQLITE_ABORT: i32

The operation was interrupted.

4

SQLITE_BLOB: i32

columnType of a blob.

5

SQLITE_BUSY: i32

Another connection holds the lock the operation needs.

14

SQLITE_CANTOPEN: i32

The file could not be opened.

19

SQLITE_CONSTRAINT: i32

A constraint (UNIQUE, NOT NULL, CHECK, a foreign key) failed.

11

SQLITE_CORRUPT: i32

The database file is malformed.

101

SQLITE_DONE: i32

step has finished.

1

SQLITE_ERROR: i32

A generic failure; error(code).message says which.

2

SQLITE_FLOAT: i32

columnType of a floating-point value.

13

SQLITE_FULL: i32

The disk is full.

1

SQLITE_INTEGER: i32

columnType of an integer.

2

SQLITE_INTERNAL: i32

An internal fault in SQLite.

10

SQLITE_IOERR: i32

The operating system failed a read or a write.

6

SQLITE_LOCKED: i32

A table is locked by this connection's own work.

20

SQLITE_MISMATCH: i32

A value of the wrong type for a STRICT column.

21

SQLITE_MISUSE: i32

A call out of order, or a handle that names nothing.

7

SQLITE_NOMEM: i32

SQLite could not allocate.

26

SQLITE_NOTADB: i32

The file is not a database.

5

SQLITE_NULL: i32

columnType of SQL NULL.

0

SQLITE_OK: i32

Success.

3

SQLITE_PERM: i32

The access mode the database was opened with does not allow the write.

25

SQLITE_RANGE: i32

A parameter or column index that is not there.

8

SQLITE_READONLY: i32

The database is read-only.

100

SQLITE_ROW: i32

step has a row.

17

SQLITE_SCHEMA: i32

The schema changed; prepare the statement again.

3

SQLITE_TEXT: i32

columnType of text.

18

SQLITE_TOOBIG: i32

A string or blob is larger than SQLite's limit.