Interface SQLiteAPI

Javascript wrappers for the SQLite C API (plus a few convenience functions)

Function signatures have been slightly modified to be more Javascript-friendly. For the C functions that return an error code, the corresponding Javascript wrapper will throw an exception with a code property on an error.

Note that a few functions return a Promise in order to accomodate either a synchronous or asynchronous SQLite build, generally those involved with opening/closing a database or executing a statement.

To create an instance of the API, follow these steps:

// Import an ES6 module factory function from one of the
// package builds, either 'wa-sqlite.mjs' (synchronous) or
// 'wa-sqlite-async.mjs' (asynchronous). You should only
// use the asynchronous build if you plan to use an
// asynchronous VFS or module.
import SQLiteESMFactory from 'wa-sqlite/dist/wa-sqlite.mjs';

// Import the Javascript API wrappers.
import * as SQLite from 'wa-sqlite';

// Use an async function to simplify Promise handling.
(async function() {
  // Invoke the ES6 module factory to create the SQLite
  // Emscripten module. This will fetch and compile the
  // .wasm file.
  const module = await SQLiteESMFactory();

  // Use the module to build the API instance.
  const sqlite3 = SQLite.Factory(module);

  // Use the API to open and access a database.
  const db = await sqlite3.open_v2('myDB');
  ...
})();

See

https://sqlite.org/c3ref/funclist.html

interface SQLiteAPI {
    bind(stmt, i, value): number;
    bind_blob(stmt, i, value): number;
    bind_collection(stmt, bindings): number;
    bind_double(stmt, i, value): number;
    bind_int(stmt, i, value): number;
    bind_int64(stmt, i, value): number;
    bind_null(stmt, i): number;
    bind_parameter_count(stmt): number;
    bind_parameter_name(stmt, i): string;
    bind_text(stmt, i, value): number;
    changes(db): number;
    clear_bindings(stmt): number;
    close(db): Promise<number>;
    column(stmt, i): SQLiteCompatibleType;
    column_blob(stmt, i): Uint8Array;
    column_bytes(stmt, i): number;
    column_count(stmt): number;
    column_double(stmt, i): number;
    column_int(stmt, i): number;
    column_int64(stmt, i): bigint;
    column_name(stmt, i): string;
    column_names(stmt): string[];
    column_text(stmt, i): string;
    column_type(stmt, i): number;
    create_function(db, zFunctionName, nArg, eTextRep, pApp, xFunc?, xStep?, xFinal?): number;
    data_count(stmt): number;
    exec(db, zSQL, callback?): Promise<number>;
    finalize(stmt): Promise<number>;
    get_autocommit(db): number;
    libversion(): string;
    libversion_number(): number;
    limit(db, id, newVal): number;
    open_v2(zFilename, iFlags?, zVfs?): Promise<number>;
    progress_handler(db, nProgressOps, handler, userData): any;
    reset(stmt): Promise<number>;
    result(context, value): void;
    result_blob(context, value): void;
    result_double(context, value): void;
    result_int(context, value): void;
    result_int64(context, value): void;
    result_null(context): void;
    result_text(context, value): void;
    row(stmt): SQLiteCompatibleType[];
    set_authorizer(db, authFunction, userData): number;
    sql(stmt): string;
    statements(db, sql, options?): AsyncIterable<number>;
    step(stmt): Promise<number>;
    update_hook(db, callback): void;
    value(pValue): SQLiteCompatibleType;
    value_blob(pValue): Uint8Array;
    value_bytes(pValue): number;
    value_double(pValue): number;
    value_int(pValue): number;
    value_int64(pValue): bigint;
    value_text(pValue): string;
    value_type(pValue): number;
    vfs_register(vfs, makeDefault?): number;
}

Methods

bind

bind(stmt, i, value): number
Bind value to prepared statement

This convenience function calls the appropriate bind_* function based on the type of value. Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: SQLiteCompatibleType
Returns number
SQLITE_OK (throws exception on error)

bind_blob

bind_blob(stmt, i, value): number
Bind blob to prepared statement parameter

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: number[] | Uint8Array
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

bind_collection

bind_collection(stmt, bindings): number
Bind a collection of values to a statement

This convenience function binds values from either an array or object to a prepared statement with placeholder parameters.

Array example using numbered parameters (numbering is implicit in this example):
```
const sql = 'INSERT INTO tbl VALUES (?, ?, ?)';
for await (const stmt of sqlite3.statements(db, sql) {
  sqlite3.bind_collection(stmt, [42, 'hello', null]);
  ...
}
```
Object example using named parameters (':', '@', or '$' prefixes are allowed):
```
const sql = 'INSERT INTO tbl VALUES (?, ?, ?)';
for await (const stmt of sqlite3.statements(db, sql) {
  sqlite3.bind_collection(stmt, {
    '@foo': 42,
    '@bar': 'hello',
    '@baz': null,
  });
  ...
}
```
Note that SQLite bindings are indexed beginning with 1, but when binding values from an array a the values begin with a[0].
Parameters
- stmt: number
  prepared statement pointer
- bindings: {
  [index: string]: SQLiteCompatibleType | null;
  } | SQLiteCompatibleType[]
Returns number
SQLITE_OK (throws exception on error)

bind_double

bind_double(stmt, i, value): number
Bind number to prepared statement parameter

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: number
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

bind_int

bind_int(stmt, i, value): number
Bind number to prepared statement parameter

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: number
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

bind_int64

bind_int64(stmt, i, value): number
Bind number to prepared statement parameter

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: bigint
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

bind_null

bind_null(stmt, i): number
Bind null to prepared statement

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

bind_parameter_count

bind_parameter_count(stmt): number
Get number of bound parameters
Parameters
- stmt: number
  prepared statement pointer
Returns number
number of statement binding locations

See
https://www.sqlite.org/c3ref/bind_parameter_count.html

bind_parameter_name

bind_parameter_name(stmt, i): string
Get name of bound parameter

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
Returns string
binding name

See
https://www.sqlite.org/c3ref/bind_parameter_name.html

bind_text

bind_text(stmt, i, value): number
Bind string to prepared statement

Note that binding indices begin with 1.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  binding index
- value: string
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/bind_blob.html

changes

changes(db): number
Get count of rows modified by last insert/update
Parameters
- db: any
  database pointer
Returns number
number of rows modified

See
https://www.sqlite.org/c3ref/changes.html

clear_bindings

clear_bindings(stmt): number
Reset all bindings on a prepared statement.
Parameters
- stmt: number
  prepared statement pointer
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/clear_bindings.html

close

close(db): Promise<number>
Close database connection
Parameters
- db: any
  database pointer
Returns Promise<number>
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/close.html

column

column(stmt, i): SQLiteCompatibleType
Call the appropriate column_* function based on the column type

The type is determined by calling column_type, which may not match the type declared in CREATE TABLE. Note that if the column value is a blob then as with column_blob the result may be invalid after the next SQLite call; copy if it needs to be retained.

Integer values are returned as Number if within the min/max safe integer bounds, otherwise they are returned as BigInt.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns SQLiteCompatibleType
column value

column_blob

column_blob(stmt, i): Uint8Array
Extract a column value from a row after a prepared statment step

The contents of the returned buffer may be invalid after the next SQLite call. Make a copy of the data (e.g. with .slice()) if longer retention is required.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns Uint8Array
column value

See
https://www.sqlite.org/c3ref/column_blob.html

column_bytes

column_bytes(stmt, i): number
Get storage size for column text or blob
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns number
number of bytes in column text or blob

See
https://www.sqlite.org/c3ref/column_blob.html

column_count

column_count(stmt): number
Get number of columns for a prepared statement
Parameters
- stmt: number
  prepared statement pointer
Returns number
number of columns

See
https://www.sqlite.org/c3ref/column_blob.html

column_double

column_double(stmt, i): number
Extract a column value from a row after a prepared statment step
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns number
column value

See
https://www.sqlite.org/c3ref/column_blob.html

column_int

column_int(stmt, i): number
Extract a column value from a row after a prepared statment step
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns number
column value

See
https://www.sqlite.org/c3ref/column_blob.html

column_int64

column_int64(stmt, i): bigint
Extract a column value from a row after a prepared statment step
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns bigint
column value

See
https://www.sqlite.org/c3ref/column_blob.html

column_name

column_name(stmt, i): string
Get a column name for a prepared statement
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns string
column name

See
https://www.sqlite.org/c3ref/column_blob.html

column_names

column_names(stmt): string[]
Get names for all columns of a prepared statement

This is a convenience function that calls column_count and column_name.
Parameters
- stmt: number
Returns string[]
array of column names

column_text

column_text(stmt, i): string
Extract a column value from a row after a prepared statment step
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns string
column value

See
https://www.sqlite.org/c3ref/column_blob.html

column_type

column_type(stmt, i): number
Get column type for a prepared statement

Note that this type may not match the type declared in CREATE TABLE.
Parameters
- stmt: number
  prepared statement pointer
- i: number
  column index
Returns number
enumeration value for type

See
https://www.sqlite.org/c3ref/column_blob.html

create_function

create_function(db, zFunctionName, nArg, eTextRep, pApp, xFunc?, xStep?, xFinal?): number
Create or redefine SQL functions

The application data passed is ignored. Use closures instead.

If any callback function returns a Promise, that function must be declared async, i.e. it must allow use of await.
Parameters
- db: number
  database pointer
- zFunctionName: string
- nArg: number
  number of function arguments
- eTextRep: number
  text encoding (and other flags)
- pApp: number
  application data (ignored)
- Optional xFunc: ((context, values) => void | Promise<void>)
  - - (context, values): void | Promise<void>
    - Parameters
      context: number
      values: Uint32Array
      Returns void | Promise<void>
- Optional xStep: ((context, values) => void | Promise<void>)
  - - (context, values): void | Promise<void>
    - Parameters
      context: number
      values: Uint32Array
      Returns void | Promise<void>
- Optional xFinal: ((context) => void | Promise<void>)
  - - (context): void | Promise<void>
    - Parameters
      context: number
      Returns void | Promise<void>
Returns number
SQLITE_OK (throws exception on error)

See
https://sqlite.org/c3ref/create_function.html

data_count

data_count(stmt): number
Get number of columns in current row of a prepared statement
Parameters
- stmt: number
  prepared statement pointer
Returns number
number of columns

See
https://www.sqlite.org/c3ref/data_count.html

exec

exec(db, zSQL, callback?): Promise<number>
One-step query execution interface

The implementation of this function uses row, which makes a copy of blobs and returns BigInt for integers outside the safe integer bounds for Number.
Parameters
- db: number
  database pointer
- zSQL: string
  queries
- Optional callback: ((row, columns) => void)
  called for each output row
  - - (row, columns): void
    - Parameters
      row: SQLiteCompatibleType[]
      columns: string[]
      Returns void
Returns Promise<number>
Promise resolving to SQLITE_OK (rejects on error)

See
https://www.sqlite.org/c3ref/exec.html

finalize

finalize(stmt): Promise<number>
Destroy a prepared statement object compiled by statements with the unscoped option set to true

This function does not throw on error.
Parameters
- stmt: number
  prepared statement pointer
Returns Promise<number>
Promise resolving to SQLITE_OK or error status

See
https://www.sqlite.org/c3ref/finalize.html

get_autocommit

get_autocommit(db): number
Test for autocommit mode
Parameters
- db: number
  database pointer
Returns number
Non-zero if autocommit mode is on, zero otherwise

See
https://sqlite.org/c3ref/get_autocommit.html

libversion

libversion(): string
Get SQLite library version

Returns string
version string, e.g. '3.35.5'

See
https://www.sqlite.org/c3ref/libversion.html

libversion_number

libversion_number(): number
Get SQLite library version

Returns number
version number, e.g. 3035005

See
https://www.sqlite.org/c3ref/libversion.html

limit

limit(db, id, newVal): number
Set a usage limit on a connection.
Parameters
- db: number
  database pointer
- id: number
  limit category
- newVal: number
Returns number
previous setting

See
https://www.sqlite.org/c3ref/limit.html

open_v2

open_v2(zFilename, iFlags?, zVfs?): Promise<number>
Opening a new database connection.

Note that this function differs from the C API in that it returns the Promise-wrapped database pointer (instead of a result code).
Parameters
- zFilename: string
- Optional iFlags: number
  SQLite.SQLITE_OPEN_CREATE | SQLite.SQLITE_OPEN_READWRITE (0x6) if omitted
- Optional zVfs: string
  VFS name
Returns Promise<number>
Promise-wrapped database pointer.

See
https://sqlite.org/c3ref/open.html

progress_handler

progress_handler(db, nProgressOps, handler, userData): any
Specify callback to be invoked between long-running queries

The application data passed is ignored. Use closures instead.

If any callback function returns a Promise, that function must be declared async, i.e. it must allow use of await.
Parameters
- db: number
  database pointer
- nProgressOps: number
  target number of database operations between handler invocations
- handler: ((userData) => number | Promise<number>)
  - - (userData): number | Promise<number>
    - Parameters
      userData: any
      Returns number | Promise<number>
- userData: any
Returns any

reset

reset(stmt): Promise<number>
Reset a prepared statement object
Parameters
- stmt: number
  prepared statement pointer
Returns Promise<number>
Promise-wrapped SQLITE_OK (rejects on error)

See
https://www.sqlite.org/c3ref/reset.html

result

result(context, value): void
Convenience function to call result_* based of the type of value
Parameters
- context: number
  context pointer
- value: SQLiteCompatibleType
Returns void

result_blob

result_blob(context, value): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
- value: number[] | Uint8Array
Returns void
See
https://sqlite.org/c3ref/result_blob.html

result_double

result_double(context, value): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
- value: number
Returns void
See
https://sqlite.org/c3ref/result_blob.html

result_int

result_int(context, value): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
- value: number
Returns void
See
https://sqlite.org/c3ref/result_blob.html

result_int64

result_int64(context, value): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
- value: bigint
Returns void
See
https://sqlite.org/c3ref/result_blob.html

result_null

result_null(context): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
Returns void
See
https://sqlite.org/c3ref/result_blob.html

result_text

result_text(context, value): void
Set the result of a function or vtable column
Parameters
- context: number
  context pointer
- value: string
Returns void
See
https://sqlite.org/c3ref/result_blob.html

row

row(stmt): SQLiteCompatibleType[]
Get all column data for a row from a prepared statement step

This convenience function will return a copy of any blob, unlike column_blob which returns a value referencing volatile WASM memory with short validity. Like column, it will return a BigInt for integers outside the safe integer bounds for Number.
Parameters
- stmt: number
  prepared statement pointer
Returns SQLiteCompatibleType[]
row data

set_authorizer

set_authorizer(db, authFunction, userData): number
Register a callback function that is invoked to authorize certain SQL statement actions.
Parameters
- db: number
  database pointer
- authFunction: ((userData, iActionCode, param3, param4, param5, param6) => number | Promise<number>)
  - - (userData, iActionCode, param3, param4, param5, param6): number | Promise<number>
    - Parameters
      userData: any
      iActionCode: number
      param3: string
      param4: string
      param5: string
      param6: string
      Returns number | Promise<number>
- userData: any
Returns number
See
https://www.sqlite.org/c3ref/set_authorizer.html

sql

sql(stmt): string
Get statement SQL
Parameters
- stmt: number
  prepared statement pointer
Returns string
SQL

See
https://www.sqlite.org/c3ref/expanded_sql.html

statements

statements(db, sql, options?): AsyncIterable<number>
SQL statement iterator

This function manages statement compilation by creating an async iterator that yields a prepared statement handle on each iteration. It is typically used with a for await loop (in an async function), like this:
```
// Compile one statement on each iteration of this loop.
for await (const stmt of sqlite3.statements(db, sql)) {
  // Bind parameters here if using SQLite placeholders.

  // Execute the statement with this loop.
  while (await sqlite3.step(stmt) === SQLite.SQLITE_ROW) {
    // Collect row data here.
  }

  // Change bindings, reset, and execute again if desired.
}
```
By default, the lifetime of a yielded prepared statement is managed automatically by the iterator, ending at the end of each iteration. finalize should not be called on a statement provided by the iterator unless the unscoped option is set to true (that option is provided for applications that wish to manage statement lifetimes manually).

If using the iterator manually, i.e. by calling its next method, be sure to call the return method if iteration is abandoned before completion (for await and other implicit traversals provided by Javascript do this automatically) to ensure that all allocated resources are released.
Parameters
- db: number
  database pointer
- sql: string
- Optional options: SQLitePrepareOptions
Returns AsyncIterable<number>
See
https://www.sqlite.org/c3ref/prepare.html

step

step(stmt): Promise<number>
Evaluate an SQL statement
Parameters
- stmt: number
  prepared statement pointer
Returns Promise<number>
Promise resolving to SQLITE_ROW or SQLITE_DONE (rejects on error)

See
https://www.sqlite.org/c3ref/step.html

update_hook

update_hook(db, callback): void
Register an update hook

The callback is invoked whenever a row is updated, inserted, or deleted in a rowid table on this connection.
Parameters
- db: number
  database pointer
- callback: ((updateType, dbName, tblName, rowid) => void)
  - - (updateType, dbName, tblName, rowid): void
    - Parameters
      updateType: number
      dbName: string
      tblName: string
      rowid: bigint
      Returns void
Returns void
See
- https://www.sqlite.org/c3ref/update_hook.html
updateType is one of:
- SQLITE_DELETE: 9
- SQLITE_INSERT: 18
- SQLITE_UPDATE: 23
- https://www.sqlite.org/c3ref/c_alter_table.html

value

value(pValue): SQLiteCompatibleType
Extract a value from sqlite3_value

This is a convenience function that calls the appropriate value_* function based on its type. Note that if the value is a blob then as with value_blob the result may be invalid after the next SQLite call.

Integer values are returned as Number if within the min/max safe integer bounds, otherwise they are returned as BigInt.
Parameters
- pValue: number
  sqlite3_value pointer
Returns SQLiteCompatibleType
value

value_blob

value_blob(pValue): Uint8Array
Extract a value from sqlite3_value

The contents of the returned buffer may be invalid after the next SQLite call. Make a copy of the data (e.g. with .slice()) if longer retention is required.
Parameters
- pValue: number
  sqlite3_value pointer
Returns Uint8Array
value

See
https://sqlite.org/c3ref/value_blob.html

value_bytes

value_bytes(pValue): number
Get blob or text size for value
Parameters
- pValue: number
  sqlite3_value pointer
Returns number
size

See
https://sqlite.org/c3ref/value_blob.html

value_double

value_double(pValue): number
Extract a value from sqlite3_value
Parameters
- pValue: number
  sqlite3_value pointer
Returns number
value

See
https://sqlite.org/c3ref/value_blob.html

value_int

value_int(pValue): number
Extract a value from sqlite3_value
Parameters
- pValue: number
  sqlite3_value pointer
Returns number
value

See
https://sqlite.org/c3ref/value_blob.html

value_int64

value_int64(pValue): bigint
Extract a value from sqlite3_value
Parameters
- pValue: number
  sqlite3_value pointer
Returns bigint
value

See
https://sqlite.org/c3ref/value_blob.html

value_text

value_text(pValue): string
Extract a value from sqlite3_value
Parameters
- pValue: number
  sqlite3_value pointer
Returns string
value

See
https://sqlite.org/c3ref/value_blob.html

value_type

value_type(pValue): number
Get type of sqlite3_value
Parameters
- pValue: number
  sqlite3_value pointer
Returns number
enumeration value for type

See
https://sqlite.org/c3ref/value_blob.html

vfs_register

vfs_register(vfs, makeDefault?): number
Register a new Virtual File System.
Parameters
- vfs: SQLiteVFS
  VFS object
- Optional makeDefault: boolean
Returns number
SQLITE_OK (throws exception on error)

See
https://www.sqlite.org/c3ref/vfs_find.html

Interface SQLiteAPI

See

Index

Methods

Methods

bind

Parameters

stmt: number

i: number

value: SQLiteCompatibleType

Returns number

bind_blob

Parameters

stmt: number

i: number

value: number[] | Uint8Array

Returns number

See

bind_collection

Parameters

stmt: number

bindings: { [index: string]: SQLiteCompatibleType | null; } | SQLiteCompatibleType[]

Returns number

bind_double

Parameters

stmt: number

i: number

value: number

Returns number

See

bind_int

Parameters

stmt: number

i: number

value: number

Returns number

See

bind_int64

Parameters

stmt: number

i: number

value: bigint

Returns number

See

bind_null

Parameters

stmt: number

i: number

Returns number

See

bind_parameter_count

Parameters

stmt: number

Returns number

See

bind_parameter_name

Parameters

stmt: number

i: number

Returns string

See

bind_text

Parameters

stmt: number

i: number

value: string

Returns number

See

changes

Parameters

db: any

Returns number

See

clear_bindings

Parameters

stmt: number

Returns number

See

close

Parameters

bindings: {
[index: string]: SQLiteCompatibleType | null;
} | SQLiteCompatibleType[]