bare-sqlite

bare-sqlite — SQLite bindings for Bare. It is a native addon.

npm i bare-sqlite

Usage

const { DatabaseSync } = require('bare-sqlite')

const db = new DatabaseSync(':memory:')

db.exec(`
  CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT);
  INSERT INTO books (title) VALUES ('Dune'), ('Foundation');
`)

for (const row of db.prepare('SELECT id, title FROM books').iterate()) {
  console.log(row)
}

db.close()

API

DatabaseSync

new DatabaseSync(location: string, opts?: SQLiteDatabaseSync.Options)

Open a SQLite database. location is a path to a database file, or ':memory:' for an in-memory database.

Parameters

close(): void

Close the database. Throws if not open. Prepared statements that are still reachable from JavaScript remain valid until they are finalized; the underlying connection is released once the last statement is gone.

Throws

• DATABASE_NOT_OPEN — the database is not open.

createTagStore(maxSize?: number): TagStore

Create an LRU cache of prepared statements keyed on the SQL string produced by a tagged template. maxSize defaults to 1000. The returned store exposes sql.all, sql.get, sql.iterate, and sql.run as tag functions; placeholder values are bound positionally.

Parameters

Returns TagStore — A TagStore exposing all, get, iterate, and run as tagged-template functions.

Throws

• DATABASE_NOT_OPEN — the database is not open.
• INVALID_ARGUMENT — maxSize is not a positive integer.

DatabaseSync.Options

interface Options {
  open?: boolean
  readOnly?: boolean
  enableForeignKeyConstraints?: boolean
  enableDoubleQuotedStringLiterals?: boolean
  allowExtension?: boolean
  timeout?: number
}

enableLoadExtension(allow: boolean): void

Toggle extension loading at runtime. Useful for enabling extension loading during setup and disabling it before running user-supplied SQL. Throws if allowExtension was not enabled at construction.

Parameters

Throws

• DATABASE_NOT_OPEN — the database is not open.
• LOAD_EXTENSION_DISABLED — allowExtension was not enabled at construction.

exec(sql: string): void

Execute one or more SQL statements without returning rows. sql may contain multiple statements separated by ;.

Parameters

Throws

• DATABASE_NOT_OPEN — the database is not open.

isOpen: boolean

true if the database is currently open, false otherwise.

loadExtension(path: string, entryPoint?: string | null): void

Load an SQLite extension from path. entryPoint is the C initialization function name; when omitted, SQLite derives it from the filename. Throws if allowExtension was not enabled at construction.

Parameters

Throws

• DATABASE_NOT_OPEN — the database is not open.
• LOAD_EXTENSION_DISABLED — allowExtension was not enabled at construction.

open(): void

Open the database. Throws if already open. Useful when the database was constructed with open: false.

Throws

• DATABASE_ALREADY_OPEN — the database is already open.

prepare(sql: string): StatementSync

Compile sql into a prepared statement. The returned StatementSync can be reused with different parameter values.

Parameters

Returns StatementSync — A StatementSync that can be reused with different parameter values.

Throws

• DATABASE_NOT_OPEN — the database is not open.

StatementSync

StatementSync.all

all<T extends SQLiteStatementSync.Row = SQLiteStatementSync.Row>(...params: SQLiteStatementSync.Parameters): T[]

Execute the statement and return all rows as an array of objects keyed by column name.

Parameters

Returns T[] — All result rows, as an array of objects keyed by column name.

columns(): SQLiteStatementSync.Column[]

Return an array describing the statement's result columns.

Returns SQLiteStatementSync.Column[] — An array describing the statement result columns.

expandedSQL: string | null

The SQL with bound parameter values substituted in, or null if SQLite couldn't expand it.

StatementSync.get

get<T extends SQLiteStatementSync.Row = SQLiteStatementSync.Row>(...params: SQLiteStatementSync.Parameters): T | undefined

Execute the statement and return the first row, or undefined if there are no rows.

Parameters

Returns T | undefined — The first result row, or undefined if the query produced no rows.

StatementSync.iterate

iterate<T extends SQLiteStatementSync.Row = SQLiteStatementSync.Row>(...params: SQLiteStatementSync.Parameters): IterableIterator<T>

Execute the statement and return an iterator that yields result rows one at a time as objects keyed by column name.

Parameters

Returns IterableIterator<T> — An iterator that yields result rows one at a time as objects keyed by column name.

run(...params: SQLiteStatementSync.Parameters): SQLiteStatementSync.RunResult

Execute the statement and return a result object with changes (the number of rows modified) and lastInsertRowid.

Parameters

Returns SQLiteStatementSync.RunResult — A result object with changes (the number of rows modified) and lastInsertRowid.

setAllowBareNamedParameters(allow: boolean): void

When true (the default), named-parameter lookup falls back to the bare key when the sigil-prefixed key (':foo') is not found. When false, only sigil-prefixed keys are considered.

Parameters

setAllowUnknownNamedParameters(allow: boolean): void

When false (the default), passing a named-parameters object with keys that don't correspond to any placeholder throws INVALID_ARGUMENT. When true, extras are silently ignored.

Parameters

setReadBigInts(enabled: boolean): void

When true, INTEGER columns are returned as BigInt rather than Number. changes and lastInsertRowid from stmt.run() are returned as BigInt too. Default is false.

Parameters

sourceSQL: string

The original SQL string that the statement was compiled from.

StatementSync.BindValue

type BindValue = null | undefined | number | bigint | string | ArrayBuffer | ArrayBufferView

StatementSync.Column

interface Column {
  column: string | null
  name: string | null
  database: string | null
  table: string | null
  type: string | null
}

StatementSync.NamedParameters

type NamedParameters = Record<string, BindValue>

StatementSync.Parameters

type Parameters = [NamedParameters, ...BindValue[]] | BindValue[]

StatementSync.Row

type Row = Record<string, Value>

StatementSync.RunResult

interface RunResult {
  changes: number | bigint
  lastInsertRowid: number | bigint
}

StatementSync.Value

type Value = null | number | bigint | string | Buffer

StatementSync.values

values<T extends SQLiteStatementSync.Value[] = SQLiteStatementSync.Value[]>(...params: SQLiteStatementSync.Parameters): T[]

Execute the statement and return all rows as an array of value tuples, one per row, in the order given by stmt.columns(). Cheaper than stmt.all() when column names aren't needed.

Parameters

Returns T[] — All result rows, as an array of value tuples ordered by columns().

TagStore

TagStore.all

all<T extends StatementSync.Row = StatementSync.Row>(strings: readonly string[], ...params: StatementSync.BindValue[]): T[]

Execute the statement and return all rows as an array of objects keyed by column name.

Parameters

Returns T[] — All result rows, as an array of objects keyed by column name.

capacity: number

The maximum number of statements the cache holds before evicting the least-recently-used entry.

clear(): void

Evict all cached prepared statements.

db: DatabaseSync

The DatabaseSync this store prepares statements against.

TagStore.get

get<T extends StatementSync.Row = StatementSync.Row>(strings: readonly string[], ...params: StatementSync.BindValue[]): T | undefined

Execute the statement and return the first row, or undefined if there are no rows.

Parameters

Returns T | undefined — The first result row, or undefined if the query produced no rows.

TagStore.iterate

iterate<T extends StatementSync.Row = StatementSync.Row>(strings: readonly string[], ...params: StatementSync.BindValue[]): IterableIterator<T>

Execute the statement and return an iterator that yields result rows one at a time as objects keyed by column name.

Parameters

Returns IterableIterator<T> — An iterator that yields result rows one at a time as objects keyed by column name.

run(strings: readonly string[], ...params: StatementSync.BindValue[]): StatementSync.RunResult

Execute the statement and return a result object with changes (the number of rows modified) and lastInsertRowid.

Parameters

Returns StatementSync.RunResult — A result object with changes (the number of rows modified) and lastInsertRowid.

size: number

The number of prepared statements currently cached.

TagStore.values

values<T extends StatementSync.Value[] = StatementSync.Value[]>(strings: readonly string[], ...params: StatementSync.BindValue[]): T[]

Execute the statement and return all rows as an array of value tuples, one per row, in the order given by stmt.columns(). Cheaper than stmt.all() when column names aren't needed.

Parameters

Returns T[] — All result rows, as an array of value tuples ordered by columns().

Classes

errors

class errors {
  cause: unknown
  code: string
  message: string
  name: string
  stack: string
}

The SQLiteError class. Errors thrown by the module are instances of it, with a code such as DATABASE_NOT_OPEN, DATABASE_ALREADY_OPEN, INVALID_ARGUMENT, or LOAD_EXTENSION_DISABLED, or an SQLite result code such as CANTOPEN, BUSY, or IOERR.

WebAssembly build

From 0.1.5, the package also ships a WebAssembly build, prebuilds/wasi-wasm32/bare-sqlite.wasm. On Bare 1.36.0 and later, the host import attribute loads it in place of the native build, sandboxed through WASI, so the database reaches only the directories its WASI provider grants:

const { DatabaseSync } = require('bare-sqlite', {
  with: { host: 'wasi-wasm32', wasi: './wasi.js' }
})

const db = new DatabaseSync('/books.db')

Database paths are paths inside the provider's preopens, so /books.db is books.db in the directory mounted at /. Without a provider, an in-memory database (':memory:') still works, and opening a file throws CANTOPEN. The WebAssembly build cannot load extensions: on a database opened with allowExtension: true, loadExtension() throws a SQLiteError with code ERROR and the message ERROR: extension loading is not supported. See WebAssembly addons for the provider and the rules for loading a package this way.

The WebAssembly build locks a database by creating and removing a <name>.lock directory next to it, so the provider's directory needs the create and remove rights as well as lookup and read, even for a database opened with readOnly: true. Writing also needs write and truncate. Without create, statements fail with IOERR. Without write, writes fail with CANTOPEN. Without truncate, which SQLite uses to shrink the file, VACUUM fails with IOERR, and a ROLLBACK of a transaction that grew the file can report success but leave a hot journal, after which statements fail with IOERR. A .lock directory left behind, because the process lacked remove or was killed while holding the lock, makes every later statement on the database fail with BUSY until the directory is deleted, though new DatabaseSync() still succeeds.

See also

• Run an addon sandboxed in WebAssembly — load the WebAssembly build with one directory granted.
• Bare modules — the full bare-* catalog.
• Bare runtime API — the runtime these modules extend.