Options
All
  • Public
  • Public/Protected
  • All
Menu

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

Hierarchy

  • SQLiteAPI

Index

Methods

Methods

bind

  • 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

    Returns number

    SQLITE_OK (throws exception on error)

bind_blob

  • bind_blob(stmt: number, i: number, value: Uint8Array | number[]): number

  • Bind blob to prepared statement parameter

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      binding index

    • value: Uint8Array | number[]

    Returns number

    SQLITE_OK (throws exception on error)

bind_collection

  • 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 str = sqlite3.str_new(db, `
      INSERT INTO tbl VALUES (?, ?, ?);
    `);
    const prepared = await sqlite3.prepare_v2(db, sqlite3.str_value(str));
    sqlite3.bind_collection(prepared.stmt, [42, 'hello', null]);
    ...

    Object example using named parameters (':', '@', or '$' prefixes are allowed):

    const str = sqlite3.str_new(db, `
      INSERT INTO tbl VALUES (@foo, @bar, @baz);
    `);
    const prepared = await sqlite3.prepare_v2(db, sqlite3.str_value(str));
    sqlite3.bind_collection(prepared.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

    Returns number

    SQLITE_OK (throws exception on error)

bind_double

  • bind_double(stmt: number, i: number, value: number): number

  • Bind number to prepared statement parameter

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      binding index

    • value: number

    Returns number

    SQLITE_OK (throws exception on error)

bind_int

  • bind_int(stmt: number, i: number, value: number): number

  • Bind number to prepared statement parameter

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      binding index

    • value: number

    Returns number

    SQLITE_OK (throws exception on error)

bind_int64

  • bind_int64(stmt: number, i: number, value: bigint): number

  • Bind number to prepared statement parameter

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      binding index

    • value: bigint

    Returns number

    SQLITE_OK (throws exception on error)

bind_null

  • bind_null(stmt: number, i: number): number

  • Bind null to prepared statement

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

    Returns number

    SQLITE_OK (throws exception on error)

bind_parameter_count

  • bind_parameter_count(stmt: number): number

bind_parameter_name

  • bind_parameter_name(stmt: number, i: number): string

bind_text

  • bind_text(stmt: number, i: number, value: string): number

  • Bind string to prepared statement

    Note that binding indices begin with 1.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      binding index

    • value: string

    Returns number

    SQLITE_OK (throws exception on error)

changes

  • changes(db: any): number

close

  • close(db: any): Promise<number>

column

  • 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: number, i: number): 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.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      column index

    Returns Uint8Array

    column value

column_bytes

  • column_bytes(stmt: number, i: number): number

column_count

  • column_count(stmt: number): number

column_double

  • column_double(stmt: number, i: number): number

column_int

  • column_int(stmt: number, i: number): number

column_int64

  • column_int64(stmt: number, i: number): bigint

column_name

  • column_name(stmt: number, i: number): string

column_names

  • column_names(stmt: number): 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: number, i: number): string

column_type

  • column_type(stmt: number, i: number): number

  • Get column type for a prepared statement

    Note that this type may not match the type declared in CREATE TABLE.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    • i: number

      column index

    Returns number

    enumeration value for type

create_function

  • create_function(db: number, zFunctionName: string, nArg: number, eTextRep: number, pApp: number, xFunc?: (context: number, values: Uint32Array) => void, xStep?: (context: number, values: Uint32Array) => void, xFinal?: (context: number) => void): number

  • Create or redefine SQL functions

    see

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

    Parameters

    • db: number

      database pointer

    • zFunctionName: string
    • nArg: number

      number of function arguments

    • eTextRep: number

      text encoding (and other flags)

    • pApp: number

      application data

    • Optional xFunc: (context: number, values: Uint32Array) => void
        • (context: number, values: Uint32Array): void

        • Parameters

          • context: number
          • values: Uint32Array

          Returns void

    • Optional xStep: (context: number, values: Uint32Array) => void
        • (context: number, values: Uint32Array): void

        • Parameters

          • context: number
          • values: Uint32Array

          Returns void

    • Optional xFinal: (context: number) => void
        • (context: number): void

        • Parameters

          • context: number

          Returns void

    Returns number

    SQLITE_OK (throws exception on error)

create_module

  • create_module(db: number, zName: string, module: SQLiteModule, appData?: any): number

data_count

  • data_count(stmt: number): number

declare_vtab

  • declare_vtab(db: number, zSQL: string): number

exec

  • exec(db: number, zSQL: string, callback?: (row: SQLiteCompatibleType[], columns: string[]) => void): 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.

    see

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

    Parameters

    Returns Promise<number>

    Promise resolving to SQLITE_OK (rejects on error)

finalize

  • finalize(stmt: number): Promise<number>

  • Destroy a prepared statement object compiled with prepare_v2

    This function does not throw on error.

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    Returns Promise<number>

    Promise resolving to SQLITE_OK or error status

get_autocommit

  • get_autocommit(db: number): number

libversion

  • libversion(): string

libversion_number

  • libversion_number(): number

limit

  • limit(db: number, id: number, newVal: number): number

open_v2

  • open_v2(zFilename: string, iFlags?: number, zVfs?: string): 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).

    see

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

    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.

prepare_v2

  • prepare_v2(db: number, sql: number): Promise<{ sql: number; stmt: number }>

  • Compile an SQL statement

    SQL is provided as a pointer in WASM memory, so the utility functions str_new and str_value should be used. The returned Promise-wrapped object provides both the prepared statement and a pointer to the still uncompiled SQL that can be used with the next call to this function. A Promise containing null is returned when no statement remains.

    Each prepared statement should be destroyed with finalize after its usage is complete.

    Code using prepare_v2 generally looks like this:

    const str = sqlite3.str_new(db, sql);
    try {
      // Traverse and prepare the SQL, statement by statement.
      let prepared = { stmt: null, sql: sqlite3.str_value(str) };
      while ((prepared = await sqlite3.prepare_v2(db, prepared.sql))) {
        try {
          // Step through the rows produced by the statement.
          while (await sqlite3.step(prepared.stmt) === SQLite.SQLITE_ROW) {
            // Do something with the row data...
          }
        } finally {
          sqlite3.finalize(prepared.stmt);
        }
      }
    } finally {
      sqlite3.str_finish(str);
    }

    The statements convenience function can be used to avoid the boilerplate of calling prepare_v2 directly.

    see

    https://www.sqlite.org/c3ref/prepare.html

    Parameters

    • db: number

      database pointer

    • sql: number

      SQL pointer

    Returns Promise<{ sql: number; stmt: number }>

    Promise-wrapped object containing the prepared statement pointer and next SQL pointer, or a Promise containing null when no statement remains

progress_handler

  • progress_handler(db: number, nProgressOps: number, handler: (userData: any) => number, userData: any): any

  • Specify callback to be invoked between long-running queries

    Parameters

    • db: number

      database pointer

    • nProgressOps: number

      target number of database operations between handler invocations

    • handler: (userData: any) => number
        • (userData: any): number

        • Parameters

          • userData: any

          Returns number

    • userData: any

    Returns any

reset

  • reset(stmt: number): Promise<number>

  • Reset a prepared statement object

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    Returns Promise<number>

    Promise-wrapped SQLITE_OK (rejects on error)

result

  • Convenience function to call result_* based of the type of value

    Parameters

    Returns void

result_blob

  • result_blob(context: number, value: Uint8Array | number[]): void

result_double

  • result_double(context: number, value: number): void

result_int

  • result_int(context: number, value: number): void

result_int64

  • result_int64(context: number, value: bigint): void

result_null

  • result_null(context: number): void

result_text

  • result_text(context: number, value: string): void

row

  • 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: number, authFunction: (userData: any, iActionCode: number, param3: string, param4: string, param5: string, param6: string) => number, userData: any): number

  • Register a callback function that is invoked to authorize certain SQL statement actions.

    see

    https://www.sqlite.org/c3ref/set_authorizer.html

    Parameters

    • db: number

      database pointer

    • authFunction: (userData: any, iActionCode: number, param3: string, param4: string, param5: string, param6: string) => number
        • (userData: any, iActionCode: number, param3: string, param4: string, param5: string, param6: string): number

        • Parameters

          • userData: any
          • iActionCode: number
          • param3: string
          • param4: string
          • param5: string
          • param6: string

          Returns number

    • userData: any

    Returns number

sql

  • sql(stmt: number): string

statements

  • statements(db: number, sql: string): AsyncIterable<number>

  • SQL statement iterator

    This is a convenience function that manages statement compilation, replacing boilerplate code associated with calling prepare_v2 directly. 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.
    }

    finalize should not be called on a statement provided by the iterator; the statement resources will be released automatically at the end of each iteration. This also means that the statement is only valid within the scope of the loop - use prepare_v2 directly to compile a statement with an application-specified lifetime.

    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

    Returns AsyncIterable<number>

step

  • step(stmt: number): Promise<number>

  • Evaluate an SQL statement

    see

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

    Parameters

    • stmt: number

      prepared statement pointer

    Returns Promise<number>

    Promise resolving to SQLITE_ROW or SQLITE_DONE (rejects on error)

str_appendall

  • str_appendall(str: number, s: string): void

  • Add content to a sqlite3_str dynamic string

    Not recommended for building strings incrementally; prefer using Javascript and str_new with initialization.

    see

    https://www.sqlite.org/c3ref/str_append.html

    Parameters

    • str: number

      sqlite3_str pointer

    • s: string

      string to append

    Returns void

str_finish

  • str_finish(str: number): void

str_new

  • str_new(db: number, s?: string): number

  • Create a new sqlite3_str dynamic string instance

    The purpose for sqlite3_str is to transfer a SQL string in Javascript to WebAssembly memory for use with prepare_v2.

    An optional initialization argument has been added for convenience which is functionally equivalent to (but slightly more efficient):

    const str = sqlite3.str_new(db);
    sqlite3.str_appendall(str, s);

    A sqlite3_str instance should always be destroyed with str_finish after use to avoid a resource leak.

    see

    https://www.sqlite.org/c3ref/str_append.html

    Parameters

    • db: number

      database pointer

    • Optional s: string

      optional initialization string

    Returns number

    sqlite3_str pointer

str_value

  • str_value(str: number): number

  • Get pointer to sqlite3_str dynamic string data

    The returned pointer points to the UTF-8 encoded string in WebAssembly memory. Use as input with prepare_v2.

    see

    https://www.sqlite.org/c3ref/str_errcode.html

    Parameters

    • str: number

      sqlite3_str pointer

    Returns number

    pointer to string data

user_data

  • user_data(context: number): any

value

  • 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: number): 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.

    see

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

    Parameters

    • pValue: number

      sqlite3_value pointer

    Returns Uint8Array

    value

value_bytes

  • value_bytes(pValue: number): number

value_double

  • value_double(pValue: number): number

value_int

  • value_int(pValue: number): number

value_int64

  • value_int64(pValue: number): bigint

value_text

  • value_text(pValue: number): string

value_type

  • value_type(pValue: number): number

vfs_register

  • vfs_register(vfs: SQLiteVFS, makeDefault?: boolean): number

Generated using TypeDoc