C-style API

See also: WASM utilities, Gotchas

The C-style API actually has two flavors:

In both cases, the canonical C API documentation applies, with the exception that some APIs in the latter category have extended semantics in JavaScript, as covered throughout this document.

It is not expected that most JS clients will want or need to deal with most of these APIs because doing so can, for some commonly-used features, require careful attention to detail with regards to passing certain data between JS and WASM (notably pointer-to-pointer "output arguments" in the C APIs).

Instead, it is expected that clients will primarily use the oo1 or Worker 1 APIs, possibly falling back to the C-style APIs for innocuous routines like sqlite3_libversion() (noting that the C-style API is not available to clients who "remote-control" database connections via Worker messages).

The C-style API, for the most part, functions like its C counterpart. Most of the APIs behave identically to their native C variants and their JS/WASM bindings are not explicitly documented because the C documentation already serves that purpose. A small handful of exceptions exist, where differences in JS-style and C-style argument handling necessitate slightly different semantics which warrant explicit documention. Such cases are documented below.

Symbolic Constants (SQLITE_...)

The sqlite3 API makes heavy use of symbolic constants for function result codes and flags-style function arguments. All such symbols which are potentially required by WASM-bound C functions are defined in the sqlite3.capi namespace object. For example, sqlite3.capi.SQLITE_ERROR. The entire list of C-side constants is at:

www:/c3ref/constlist.html

noting that only those relevant to WASM-bound APIs are made available to JS.

The JS API extends that list with:

For result code constants, the function sqlite3_js_rc_str() accepts an integer argument and returns the JS string form of its corresponding symbolic name, or returns undefined if no match is found.

Even though the SQLITE_... constant values are exceedingly unlikely to ever change, due to backwards compatibility constraints, JS code should avoid using any such numeric values directly in code. The symbolic names should always be preferred.

JavaScript Exceptions

With only a single exception (as it were) the C-level APIs never throw, but some of the higher-level C-style APIs do and the object-oriented APIs use exceptions exclusively to report errors. The one exception is sqlite3.wasm.alloc(), which will throw an sqlite3.WasmAllocError if it cannot allocate memory (which can normally be considered a fatal error).

Note that there's a distinction between "C-level" and "C-level-adjacent." The C-level API is specifically the public sqlite3 API functions which are exported directly from WASM to JavaScript. Some of the utility code intended to work with the C-level API does indeed throw, but it does so in ways which will, when properly used, never allow an exception to propagate back through the JS/WASM boundary, as doing so would be disastrous to the C-level code.

SQLite3Error

An Error subclass specifically for reporting DB-level errors and enabling clients to unambiguously identify such exceptions. Its constructor behaves differently depending on its arguments:

Achtung: Allocators

In C it is always important that dynamically-allocated memory is freed in a manner which corresponds to how it was allocated.

By way of example, either of these two are legal:

void * m = malloc(100);
free(m);
m = sqlite3_malloc(100);
sqlite3_free(m);

but neither of these are legal:

void * m = malloc(100);
sqlite3_free(m); // NO!
m = sqlite3_malloc(100);
free(m); // NO!

Depending on how sqlite3_malloc() and sqlite3_free() are defined, which may differ from one build of sqlite3 to the next, they might or might not be simply proxies for malloc() resp. free(). If they are, then the second example would work... until the definition of the former pair changed, in which case the second example leads to undefined behavior.

Depending on how sqlite3.js is built, sqlite3_malloc() might be the module's default allocator. This project's canonical builds all use sqlite3's allocator as the module-wide default since 2022-11-30, and guaranty to do so in all future versions.

However, clients creating custom builds for different WASM environments may change that, in which case any JavaScript code written for such builds needs to be careful about how it uses dynamically-allocated memory, to ensure that different memory management APIs are never intermixed. That is, that calls to malloc(), free(), and realloc() must never intermixed with sqlite3_malloc(), sqlite3_free(), and sqlite3_realloc(). Mixing them will lead to undefined results.

For a real-world example of an sqlite3 API where this distinction is critical, see sqlite3_deserialize().

Argument and Result Type Conversion in the C-style API

See also: api-wasm.md#wasm-type-conversion

Many of the sqlite3.capi.sqlite3_...() routines may perform some type of JS-to-WASM conversion on their arguments or return values. This section covers those.

64-bit Integers (BigInt)

In short, when built with BigInt support, the JS API transparently (or mostly so) treats C-level int64 values as JS BigInt values. There are cases where clients need to be aware that they're using BigInt values, as not all numeric operations support a mix of Number and BigInt values, e.g. bitwise operations.

See the build notes for details.

String Conversion

The JS bindings of the C APIs which live in the sqlite3.capi namespace are configured to do automatic conversion of string arguments to and from WASM/JS. Thus:

const v = capi.sqlite3_vfs_find("foo");

will convert "foo" to a C-style string before calling the WASM-bound function and will free the associated memory before returning. Similarly, result values of C-style strings will be converted to JS:

console.log(capi.sqlite3_libversion());

Where such APIs accept C-string arguments, they may be passed either a JS string or a WASM-side string pointer, noting that such uses must be NUL-terminated or results are undefined. The JS/WASM compatibility glue will treat numeric arguments to string parameters as WASM pointers. When used that way, it is up to the caller to ensure that they are passing valid pointers. Unlike C compilers, which have at least a moderate degree of type safety when passing pointers around, WASM's only notion of pointers is equivalent to a void* in C. That is, pointers completely lack any type information, making it easy to "shoot oneself in the foot," as the saying goes.

UTF-8 Only!

C-style strings in arguments and results are always assumed to be UTF-8 encoded and results are undefined if any WASM-bound sqlite3 API is passed, or returns, a string which has a different encoding. That differs from JS strings, which use JS's native 16-bit encoding (which is either UTF-16 or UCS-2 (see also)).

Though the sqlite3 C API has routines for working with UTF-16, this API targets only the web and UTF-8 is the encoding of the web. There are no current plans to expose the UTF-16 APIs via the JS interface.

"Flexible Strings"

Certain WASM-bound APIs, where explicitly noted, have additional string-type argument conversions colloquially known as "flexible strings." Such string arguments may be in any of these forms:

This support is generally reserved for arguments which expect SQL strings, as such strings are often large and frequently come from external sources, e.g. byte arrays loaded from local files, over XHR requests, or using fetch(). Functions which take filename strings, and simlilar "small" strings, do not use this feature.

Function Pointers

Many sqlite3 APIs take function pointers as arguments. Though such pointers can be created and managed from JS code, it is often tedious to do so, so most such bindings will automatically convert Function-type values to function pointers for purposes of such APIs (for example, sqlite3_exec()).

Such conversions are convenient but come with some caveats:

To be clear: all such leaks do not reflect any flaw in the core sqlite3 library, but are side effects of automated conversions from JS-native types to WASM/C-level equivalents.

When passed pointers, such APIs do no conversion - they pass the pointers through as-is to the C APIs. They only "convert" JS functions to WASM by installing a WASM-visible function pointer associated with the JS function.

Functions which take one-time-use callbacks (like sqlite3_exec()) will install such conversions only for the duration of the call, and will then uninstall them before returning.

Longer-lived bindings, e.g. custom SQL functions created using one of the sqlite3_create_function() variants or collations installed using sqlite3_create_collation(), are bound in such a way that the binding can be tracked and cleaned up when either (A) they are replaced or uninstalled by a subsequent call to the function which installed them or (B) the database handle is closed using the JS binding of sqlite3_close_v2().

Minor caveat: such tracking only happens via the JS bindings of those functions installed in the sqlite3.capi namespace. Such tracking is bypassed if the lower-level sqlite3.wasm.exports bindings are called.

If clients want to perform their own handling of JS-to-WASM function conversion, client code needs to:

  1. Use wasm.installFunction() to create a WASM-bound function which acts as a proxy to a given JS function. This step includes assignment of a WASM-side function pointer for the function.
  2. Pass that pointer around to the appropriate C APIs.
  3. If necessary, eventually pass the pointer from the first step to wasm.uninstallFunction() to remove it from the WASM environment. If this is not done, the function binding effectively leaks.

If that sounds tedious, that's because it is! The good news is that the provided bindings take care of that for all common cases, so low-level care-taking of such details should generally not be necessary from client code.

There are a small handful of cases where such automatic conversions cannot be cleaned up by the JS bindings because they're handled deep in the C API, far out of view of the JS bindings. The currently-known "problem cases" are:

Struct-type Pointers

How certain C-level structs are exposed to JavaScript, and how client code can interact with and manipulate them, is covered in detail in its own document. The following sections summarize automatic type conversions which may happen for function arguments.

No automated conversions are performed on struct pointer result values. They are always returned to JS as WASM pointers.

sqlite3*

Functions which are declared as taking a sqlite3* argument may perform the following argument-type conversions:

sqlite3_stmt*

Functions which are declared as taking a sqlite3_stmt* argument may perform the following argument-type conversions:

sqlite3_vfs*

Functions which are declared as taking a sqlite3_vfs* argument may perform the following argument-type conversions:

WASM APIs Which Differ from Their C Counterparts

Every effort is made to keep the WASM bindings of the C APIs working as closely to their C counterparts as is feasible for the sake of consistency and the ability to use the C-level documentation with the WASM API. Some APIs, however, benefit greatly from the addition of custom wrappers which simplify particular cases which are unduly onerous to handle at the client-code level in JS.

This section describes sqlite3.capi member APIs which have semantics which, depending on how they are used, may differ from their C counterparts.

Unless specifically noted otherwise, for consistency with the C API, all JS-customized bindings of the C APIs require the same number of arguments as their C counterparts and will return capi.SQLITE_MISUSE if passed any other argument count.

In all cases, when passed arguments which are not accounted for in JS-specific customization, they pass their arguments on as-is to the underlying C APIs and behave as the C documentation says they do. When passed certain JS types, however, they may behave differently, either gaining or losing features, depending on the context.

The affected APIs are listed below.

Certain more general aspects of these APIs, such as how to deal with output pointers from JS, are detailed in the WASM utilities docs.

sqlite3_bind_blob()

C reference: www:/c3ref/bind_blob.html

sqlite3_bind_blob() works exactly like its C counterpart unless its 3rd argument is one of:

In all of those cases, the final argument (text destructor) is ignored and SQLITE_WASM_DEALLOC is assumed.

A 3rd argument of null is treated as if it were a WASM pointer of 0.

If the 3rd argument is neither a WASM pointer nor one of the above-described types, capi.SQLITE_MISUSE is returned.

sqlite3_bind_text()

C reference: www:/c3ref/bind_blob.html

sqlite3_bind_text() works exactly like its C counterpart unless its 3rd argument is one of:

In each of those cases, the final argument (text destructor) is ignored and SQLITE_WASM_DEALLOC is assumed.

A 3rd argument of null is treated as if it were a WASM pointer of 0.

If the 3rd argument is neither a WASM pointer nor one of the above-described types, capi.SQLITE_MISUSE is returned.

If client code needs to bind partial strings, it needs to either parcel the string up before passing it in here or it must pass in a WASM pointer for the 3rd argument and a valid 4th-argument value, taking care not to pass a value which truncates a multi-byte UTF-8 character. When passing WASM-format strings, it is important that the final argument be valid or unexpected content can result, or even a crash if the application reads past the WASM heap bounds.

sqlite3_close_v2()

sqlite3_close_v2() differs from its native counterpart only in that it attempts to perform certain JS-binding-related cleanup before it calls the native implementation. In short, it makes an effort to clean up any WASM function table entries which were automatatically installed on behalf of the being-closed database handle.

The following db-bound bindings are cleaned up by sqlite3_close_v2():

For details about why these cleanups are useful, see the section on automated function pointer conversions.

sqlite3_config()

The JS binding of sqlite3_config() wraps only a small subset of configuration operations, eliding any which either do not make sense for this environment or are considered too low-level for this environment. Only the call forms listed below are are accepted. Any other config options will return capi.SQLITE_NOTFOUND. Passing in fewer than 2 arguments will trigger a return of capi.SQLITE_MISUSE.

Significant usage caveat: sqlite3_config() cannot be legally called after the library has been "started," returning SQLITE_MISUSE if it is. Initialization of the JS bindings necessarily requires using the library in such a way that makes later calls to sqlite3_config() illegal. Clients can use sqlite3_shutdown() to "uninitialize" the library in order to make use of sqlite3_config() legal again, but doing so may cause certain JS API features to disappear, such as JS-registered VFSes.

sqlite3_config(X, int):

sqlite3_config(X, int, int):

sqlite3_config(X, int64):

Noting that the latter requires BigInt support.

sqlite3_create_collation() and friends

C reference: www:/c3ref/create_collation.html

sqlite3_create_collation() and sqlite3_create_collation_v2() work exactly like their C-side equivalents except that:

  1. They return capi.SQLITE_FORMAT if the 3rd argument contains any encoding-related value other than capi.SQLITE_UTF8. No other encodings are supported. As a special case, if the bottom 4 bits of that argument are 0, SQLITE_UTF8 is assumed.

  2. They accept JS functions for their function-pointer arguments, for which they will install WASM-bound proxies. The proxies are uninstalled automatically if a subsequent call to this function replaces them (either with NULL or a different function) or when containing database is closed.

Returns 0 on success, non-0 on error, in which case the error state of its sqlite3* argument may contain more information.

sqlite3_create_function() and friends

C reference: www:/c3ref/create_function.html

The argument names used below refer directly to the argument names from the native C documentation:

int sqlite3_create_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function_v2(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*),
  void(*xDestroy)(void*)
);
int sqlite3_create_window_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*),
  void (*xValue)(sqlite3_context*),
  void (*xInverse)(sqlite3_context*,int,sqlite3_value**),
  void(*xDestroy)(void*)
);

sqlite3_create_function(), sqlite3_create_function_v2(), and sqlite3_create_window_function() differ from their native counterpart only in the following ways:

  1. The fourth argument (eTextRep) argument must not specify any encoding other than sqlite3.capi.SQLITE_UTF8. The JS API does not currently support any other encoding and likely never will. If any other encoding flag is provided, sqlite3.capi.SQLITE_FORMAT is returned. To simplify usage, any falsy value is interpreted as SQLITE_UTF8.

  2. Any of the function pointer arguments may be either WASM pointers (assumed to be function pointers) or JS Functions. In the latter case, each gets bound to WASM using sqlite3.wasm.installFunction() and that wrapper is passed on to the native implementation.

The semantics of generated JS wrappers are:

Note that:

For xFunc(), xStep(), xInverse(), and xFinal():

If any JS-side bound functions throw, those exceptions are intercepted and converted to database-side errors with the exception of xDestroy(): any exception from it is ignored, possibly generating a console.error() message. Destructors must not throw.

Once installed, there is currently no way to uninstall the automatically-converted WASM-bound JS functions from WASM. They can be uninstalled from the database as documented in the C API, but this wrapper currently has no infrastructure in place to also free the WASM-bound JS wrappers, effectively resulting in a memory leak if the client uninstalls the UDF. Removing client-installed UDFs is rare in practice. If this factor is relevant for a given client, they can create WASM-bound JS functions themselves, hold on to their pointers, and pass the pointers in to here. Later on, they can free those pointers (using wasm.uninstallFunction() or equivalent).

Clients who wish more control over their bindings can create their own WASM-bound JS functions using sqlite3.wasm.installFunction(). The helper methods described in the following subsections can assist with that and are exposed as properties of all 3 of the create-function functions to assist such implementations.

Iincluding the pCtx argument to the JS-bound wrappers was a painful decision but it boiled down to API consistency. By and large, that argument is irrelevant for JS bindings, so is just noise for most client-side UDFs. If it were elided, however, more advanced uses of UDFs would be much more painful to write, forcing the users to write their own WASM-bound wrappers to get at that argument. Including it in the JS callback interface ensures that the interface is more consistent with the C callback interface (sans the way SQL-side arguments are provided to them) and enables Power User use cases to be developed. It is expected, however, that folks will often forget to add it, which will cause their UDF argument lists to be "off by one." It is hoped that having to add that seemingly extraneous argument to client-side UDF signatures will soon seem like second nature to folks writing UDFs, in the same way that Python programmers have to add an explicit self argument to their object-oriented methods (whereas C++, JavaScript, and most other languages make that argument implicit).

udfConvertArgs()

This function is a deprecated alias for sqlite3_values_to_js() and predates that function's addition to the API.

udfSetError()

This function is a deprecated alias for sqlite3_result_error_js() and predates that function's addition to the API.

udfSetResult()

This function is a deprecated alias for sqlite3_result_js() and predates that function's addition to the API.

sqlite3_db_config()

sqlite3_db_config() differs from its C counterpart only in a small technicality: the C function is variadic, and such functions cannot be bound directly to WASM, but the hand-written JS-side wrapper for it supports all of the C APIs combinations of arguments, as documented at at www:/c3ref/c_dbconfig_defensive.html.

sqlite3_deserialize()

C reference: www:/c3ref/deserialize.html

sqlite3_deserialize() does not materially differ from its C-side counterpart but does have subtle caveats regarding memory allocation.

The documentation regarding allocators covers this issue in more abstract terms, but sqlite3_deserialize() provides a concrete example of a case where it is critical that one be aware the definition of the environment's C-level memory allocator.

Depending on how sqlite3_deserialize() is used, the memory passed to it may be required to come from sqlite3_malloc(), sqlite3_realloc(), or their 64-bit counterparts.

The sqlite3.capi.SQLITE_DESERIALIZE_RESIZEABLE flag can be passed to this function to enable automatic growth of the new in-memory databases on demand but the memory passed to sqlite3_deserialize() in that case must come from sqlite3_malloc() or sqlite3_realloc() (or their 64-bit counterparts) or else results are undefined.

For example, let's consider this snippet which downloads a remote database and deserializes it:

// Do not copy/paste without reading the docs below!
const db = new sqlite3.oo1.DB();
await fetch('my.db')
  .then(response => response.arrayBuffer())
  .then(arrayBuffer => {
    const bytes = new Uint8Array(arrayBuffer);
    const p = sqlite3.wasm.allocFromTypedArray(bytes);
    db.onclose = {after: function(){sqlite3.wasm.dealloc(p)}};
    const rc = sqlite3.capi.sqlite3_deserialize(
      db.pointer, 'main', p, bytes.length, bytes.length,
      0
    );
    db.checkRc(rc);
  });

That use of sqlite3_deserialize() is legal, but only because it defines a fixed-sized memory region of memory not managed by sqlite3. In this scenario, sqlite3 is a read-only user of memory owned by the client application.

If the SQLITE_DESERIALIZE_FREEONCLOSE and/or SQLITE_DESERIALIZE_RESIZEABLE flags were used in the final argument to the deserialize call above, the result would, abstractly speaking, be undefined behavior (but see below for why it may not be).

Abstractly speaking, sqlite3.wasm.allocFromTypedArray() uses the system-level allocator, and it is not semantically legal to pass memory alloced from sqlite3.wasm.alloc() (i.e. from C's malloc()) to sqlite3_free() or sqlite3_realloc(). In order to support the above-mentioned flags in the example above, the call to allocFromTypedArray() would need to be replaced by an equivalent function which allocates the memory using sqlite3_alloc(). However, as of 2022-11-30, this project's canonical builds use the sqlite3_malloc() family of functions as the module-wide allocator, meaning that this is a non-issue in canonical builds but might be an issue in custom builds, depending on how they're constructed.

sqlite3_exec()

C reference: www:/c3ref/exec.html

sqlite3_exec(), if called with a non-Function object as its 3rd argument:

sqlite3_exec(myDbPtr, "drop table x", 0, 0, 0);

works exactly like all other WASM-bound functions, including the automatic conversion of its 2nd argument (SQL), which supports flexible string conversion.

If, however, it is called with a JS function as its 3rd argument then it performs the following translations...

For the duration of the sqlite3_exec() call, it installs a WASM-side function which hooks the provided callback into WASM. That binding is removed immediately before sqlite3_exec() returns.

The callback's C signature is:

callback(void *, int colCount, char** pColValues, char** pColNames)

but the callback's WASM-bound proxy calls it like:

callback(array colValues, array colNames)

i.e. it converts the C-style string arrays to JS arrays of strings. The initial void pointer argument is elided because a JS closure can bind such state into the callback, if needed, and the column count is available via the length property of the two array arguments.

If the callback throws or returns any non-0 value, the proxy catches that and converts it to a C result, which the native sqlite3_exec() implementation will translate to a SQLITE_ABORT result. If it does not throw, its return value is coerced to be a 32-bit integer. Thus an implicit return (the undefined value) equates to a result code of 0 (i.e. success).

Example:

const db = new sqlite3.oo1.DB().exec(
  "create table t(a); insert into t(a) values(3),(6),(9)"
);
sqlite3.capi.sqlite3_exec(
  db, // resolves to db.pointer, the underlying (sqlite3*)
  "select a from t",
  (vals,names)=>{
    console.log(names[0],vals[0]);
  },
  0, 0
);

Will output:

a 3
a 6
a 9

Note that sqlite3_exec(), for consistency with the C API, requires all 5 arguments, though the final two are almost always 0.

If a specific use case genuinely needs the C-style signature for the callback, that can be achieved by manually installing the callback into WASM (using the signature string "i(pipp)") and then passing its pointer to sqlite3_exec(), as demonstrated here:

const pCb = wasm.installFunction('i(pipp)', function(pVoid,nCols,aVals,aCols){
  // wasm.cArgvToJs() can be used to convert aVals and aCols to arrays:
  const vals = wasm.cArgvToJs(nCols, aVals);
  // Noting that the aCols list is constant across each sqlite3_exec()
  // invocation and can be cached _if_ this function will only ever
  // be used with a single specific query.

  // Alternately, individual name and value columns can be converted
  // to JS strings like:
  const strAt = (ndx)=>{
    return ndx<nCols
      ? wasm.cstrToJs(wasm.peekPtr(aVals + (ndx * wasm.ptrSizeof)))
      : undefined;
  };
  const val0 = strAt(0), val1 = strAt(1), val2 = strAt(2);
  // To coerce them to numbers, simply prefix the JS strings with a plus
  // sign, e.g. +strAt(...).
  return 0;
});
try {
  let rc = capi.sqlite3_exec(db, "select a, a*2 from foo", pCb, 0, 0);
  ...
}finally{
  // If the callback will be used repeated, skip this part:
  wasm.uninstallFunction(pCb);
}

sqlite3_prepare_v2() and sqlite3_prepare_v3()

C reference: www:/c3ref/prepare.html

Like in the C API, sqlite3_prepare_v2() is simply a proxy for sqlite3_prepare_v3() which passes on a default set of flags to that function. sqlite3_prepare_v3(), due to disparities between JS and C, has two distinctly different usages

As a point of reference, the native signatures of these functions are:

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_prepare_v3(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  unsigned int prepFlags, /* Zero or more SQLITE_PREPARE_ flags */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

The sqlite3_prepare_v3() binding handles two different uses with differing JS/WASM semantics:

  1. For the cases that the SQL argument is a JS string:
    int sqlite3_prepare_v3(pDb, sqlString, -1, prepFlags, ppStmt , null)
    The SQL length argument (the 3rd argument) must always be negative because it must be a byte length and that value is expensive to calculate from JS (where only the character length of strings is readily available). It is retained in this API's interface for code/documentation compatibility reasons but is currently always ignored except as noted below.

  2. For the case that the SQL argument is a WASM pointer to a C-string.
    int sqlite3_prepare_v3(pDb, sqlPointer, sqlByteLen, prepFlags, ppStmt, sqlPointerToPointer)
    The 3rd argument is used as-is but it is still critical that the C-style input string (2nd argument) be terminated with a 0 byte.

In usage (1), if the second the 2nd argument must be a flexible string other than a WASM pointer. If it is, sqlite3_prepare_v3() assumes case (1) and calls the underyling C function with the equivalent of:

(pDb, sqlAsString, -1, prepFlags, ppStmt, null)

The final argument, pzTail, is ignored in this case because its result is meaningless when a string-type value is passed through: the string goes through another level of internal conversion for WASM's sake and the result pointer would refer to that transient conversion's memory, not the passed-in string.

If the SQL is a Uint8Array or Int8Array then it is assumed to hold a UTF-8 string, is converted to a C-string, and the byte array's length is used as the sqlByteLen argument.

If the SQL argument is not a string or one of the above-mentioned array types, it must be a pointer to a NUL-terminated string which was allocated in WASM memory (e.g. using wasm.alloc(), from the C stack, or equivalent). In that case, the final argument may be 0/null/undefined or must be a pointer to which the "tail" of the compiled SQL is written, as documented for the C-side sqlite3_prepare_v3(). In case (2), the underlying C function is called with the equivalent of:

(pDb, sqlAsPointer, sqlByteLen, prepFlags, ppStmt, pzTail)

It returns its result and compiled statement as documented in the C API.

Fetching the output pointers (5th and 6th parameters) requires using wasm.peek() (or equivalent - see below) and the pzTail will point to an address relative to the sqlAsPointer value.

If passed an invalid 2nd argument type, this function will return SQLITE_MISUSE and capi.sqlite3_errmsg() will contain a string describing the problem.

Side-note: if given an empty string, or one which contains only comments or an empty SQL expression, 0 is returned but the result output pointer will be NULL.

sqlite3_randomness()

C reference: www:/c3ref/randomness.html

Usages:

If passed a single argument which appears to be a byte-oriented TypedArray (Int8Array or Uint8Array), this function treats that TypedArray as an output target, fetches theArray.byteLength bytes of randomness, and populates the whole array with it. As a special case, if the array's length is 0, this function behaves as if it were passed (0,0). When called this way, it returns its argument, else it returns the undefined value.

If called with any other arguments, they are passed on as-is to the C API. Results are undefined if passed any incompatible values.

sqlite3_set_authorizer()

C reference: www:/c3ref/set_authorizer.html

sqlite3_set_authorizer() works just like in the C API unless it is passed a JS function as its second argument, in which case it installs a proxy which will convert the C-string (pointer) arguments to JS strings before passing them on to the client-provided function. That is, the C-native call has this signature:

int (*xAuth)(void*,int,const char*,const char*,const char*,const char*)

And this function wraps that up in a JS call in this form:

int func(void*, int, string, string, string, string)

If the JS function returns a falsy value (e.g. via an implicit return of the undefined value), it is coerced to integer 0. If the JS function throws, it is caught and translated to a db-level error.

If passed a non-function second argument, e.g. a WASM pointer or null, it is passed on as-is to the underlying binding. Clients who want to receive C-style string arguments in their JS authorizer callback can install their callback using wasm.installFunction() and pass the function pointer returned from that function into this one, in which case no additional layer of type-conversion/exception-handling proxy is installed. In such cases it is important that such proxies not throw/propagate exceptions, noting that any failed memory allocation may trigger an sqlite3.WasmAllocError.

Though it may seem superfluous to have database authorizer functions in client-side JS applications, some applications make use of them to help ensure that the application itself does not misuse the database, only enabling certain features on an as-needed basis.

Auto-extension APIs

C reference: www:/c3ref/auto_extension.html

The auto-extension API behaves mostly like its C counterpart, with only the following minor differences:

These differences, for practical purposes, mean the following:

Session and Changeset APIs

The session API bindings differ from their C counterparts only in that:

  1. They optionally accept JS functions for their function-pointer arguments. They will automatically convert JS-function-type arguments to function pointers, as described elsewhere in this document.
  2. sqlite3session_delete() will clean up any auto-installed function pointers added via sqlite3session_table_filter().

Most of the function-pointer argument types take only WASM-compatible values which are passed on as-is to the JS function. However, any with the following native signature undergo argument conversion before calling the provided JS function:

int (*)(void *, const char *)

That applies to xFilter callback argument of the following functions:

The only argument conversion which happens there is that the 2nd argument is converted to a JS string before passing it to the JS function. If, for some unsual case, the client code instead requires the C-string pointers, than can be achieved by manually installing the callback function and then passing its pointer (provided by the installation process) to the sqlite3 API instead of passing a JS function. That inhibits any automatic conversion. For the installation, use the signature string "i(ps)".

The JS-installed proxies are installed only for the duration of the call except for sqlite3session_table_filter(), which is installed associated with the sqlite3_session object passed to that function. sqlite3session_delete() will clean up any such proxies installed by sqlite3session_table_filter().

Session API Extensions

These are thin wrappers around sqlite3changeset_new() and sqlite3changeset_old(), respectively, which fetch the sqlite3_value* from the column specified by the 2nd argument and then return the result of passing it to sqlite3_value_to_js(). Both of these functions throw on error, including when the underlying functions return non-0. If sqlite3changeset_new() returns 0 but sets the output value to NULL, this function returns the undefined value, which is never a valid conversion from an sqlite3_value, so is unambiguous.

Hook APIs

C references:

The sqlite3_commit_hook(), sqlite3_rollback_hook(), sqlite3_update_hook() functions, and the sqlite3_preupdate_hook() family of functions, do not fundmantally differ from their C counterparts, but sqlite3_update_hook() and sqlite3_preupdate_hook() take part in automatic JS-to-WASM function conversion and such conversions are subject to cleanup in sqlite3_close_v2(). Note that sqlite3_update_hook() and sqlite3_preupdate_hook() requires BigInt support.

If sqlite3_update_hook() or sqlite3_preupdate_hook() are passed a JS function, it undergoes argument conversion for the two C-string arguments. That is, the native signature of those callback are:

// sqlite3_update_hook() callback:
void (*)(void *, int, char const *, char const *, sqlite3_int64)
// sqlite3_preupdate_hook() callback:
void (*)(void *, sqlite3*, int, char const *, char const *, sqlite3_int64, sqlite3_int64)

and the JS signatures are:

// sqlite3_update_hook() callback:
void (void *, int, string, string, BigInt)
// sqlite3_preupdate_hook() callback:
void (void *, sqlite3*, int, string, string, BigInt, BigInt)

The hook callbacks must not propagate any exceptions. They are not caught automatically by this layer because there is no way to report an error via sqlite3 from the rollback and (pre)update hooks.

Pre-update Hook Extensions

These are thin wrappers around sqlite3_preupdate_new() and sqlite3_preupdate_old(), respectively, which fetch the sqlite3_value* from the column specified by the 2nd argument and then return the result of passing it to sqlite3_value_to_js(). Both of these functions throw on error, including when the underlying functions return non-0.

WASM-Specific C-style Functions

This section documents C-style functions which live in the sqlite3.capi namespace but are specific to the WASM/JS APIs. Note that the sqlite3.wasm API has some C-like functions which are not documented here because they are not part of the public API and are not intended for use outside of this project's own code.

sqlite3_column_js()

mixed sqlite3_column_js(pStmt, iCol [,throwIfCannotConvert=true])

Returns the result of passing the result of sqlite3_column_value(pStmt,iCol) to sqlite3_value_to_js(). The 3rd argument of this function is ignored by this function except to pass it on as the second argument of sqlite3_value_to_js(). If the sqlite3_column_value() returns NULL (e.g. because the column index is out of range), this function returns undefined, regardless of the 3rd argument. If the 3rd argument is falsy and conversion fails, undefined will be returned.

sqlite3_js_aggregate_context()

pointer sqlite3_js_aggregate_context(pCtx, n)

A thin wrapper around sqlite3.capi.sqlite3_aggregate_context() which behaves the same except that if that function returns 0 and n is truthy, it throws a WasmAllocError. If n is falsy, it simply returns 0 if that function returns 0. That behavior is intended to assist in developing xFinal() implementations.

Example, using the OO1 API:

const capi = sqlite3.capi,
  wasm = sqlite3.wasm,
  sjac = capi.sqlite3_js_aggregate_context;
db.createFunction({
  name: 'summer',
  xStep: (pCtx, n)=>{
    const ac = sjac(pCtx, 4);
    wasm.poke32(ac, wasm.peek32(ac) + Number(n));
  },
  xFinal: (pCtx)=>{
    const ac = sjac(pCtx, 0);
    return ac ? wasm.peek32(ac) : 0;
  }
});

When a given aggregate is run multiple times in one SQL statement, the aggregate context pointer will (in contrast to the pCtx pointer) remain stable across calls, enabling users to keep the state of each call separate from each other. For example, if it's called twice in one statement, one set of calls to the aggregate will have an aggregate context of X and the other will have an aggregate context of Y. That pointer can be used as a key in a client-side lookup table in order to map arbitrarily complex data to the aggregate, but the client must be sure to remove the mapping in their xFinal() implementation.

sqlite3_js_db_export()

Uint8Array sqlite3_js_db_export(pDb [, schema=0])

A convenience wrapper around sqlite3_serialize() which serializes the given sqlite3* pointer or sqlite3.oo1.DB instance to a Uint8Array.

On success it returns a Uint8Array. If the schema is empty, an empty array is returned.

schema is the schema to serialize. It may be a WASM C-string pointer or a JS string. If it is falsy, it defaults to "main".

On error it throws with a description of the problem.

Note that this operation is restricted by the library's build-time max-single-allocation limit (SQLITE_MAX_ALLOCATION_SIZE) and cannot export databases larger than approximately that size. Prior to version 3.44 that was approximately 536mb. As of 3.44 the core library's default limit of 2gb is used.

sqlite3_js_db_uses_vfs()

boolean sqlite3_js_db_uses_vfs(pDb, vfsName, dbName)

Given an sqlite3*, an sqlite3_vfs name, and an optional db name (defaults to "main"), returns a truthy value (see below) if that db handle uses that VFS, else returns false. If pDb is falsy then the 3rd argument is ignored and this function returns a truthy value if the default VFS name matches that of the 2nd argument. Results are undefined if pDb is truthy but refers to an invalid pointer. The 3rd argument specifies the database name of the given database connection to check, defaulting to the main db.

The 2nd and 3rd arguments may either be a JS string or a WASM C-string. If the 2nd argument is a NULL WASM pointer, the default VFS is assumed.

The truthy value it returns is a pointer to the sqlite3_vfs object.

To permit safe use of this function from APIs which may be called via the C stack (like SQL UDFs), this function does not throw: if bad arguments cause a conversion error when passing into wasm-space, false is returned.

sqlite3_js_db_vfs()

pointer sqlite3_js_db_vfs(dbPointer, dbName=0)

Given a sqlite3* and a database name (JS string or WASM C-string pointer, which may be 0), returns a pointer to the sqlite3_vfs responsible for it. If the given db name is null/0, or not provided, then "main" is assumed.

sqlite3_js_kvvfs_...()

See the KVVFS docs for details of the KVVFS-specific functions.

sqlite3_js_db_uses_vfs()

boolean sqlite3_js_db_uses_vfs(pDb,vfsName,dbName=0)

Given an sqlite3*, an sqlite3_vfs name, and an optional db name (defaulting to "main"), returns a truthy value (see below) if that db uses that VFS, else returns false. If pDb is falsy then the 3rd argument is ignored and this function returns a truthy value if the default VFS name matches that of the 2nd argument. Results are undefined if pDb is truthy but refers to an invalid pointer. The 3rd argument specifies the database name of the given database connection to check, defaulting to the main db.

The 2nd and 3rd arguments may either be a JS string or a WASM C-string. If the 2nd argument is a NULL WASM pointer, the default VFS is assumed. If the 3rd is a NULL WASM pointer, "main" is assumed.

The truthy value it returns is a pointer to the sqlite3_vfs object.

To permit safe use of this function from APIs which may be called via the C stack (like SQL UDFs), this function does not throw: if bad arguments cause a conversion error when passing into wasm-space, false is returned.

sqlite3_js_posix_create_file()

(Added in version 3.43 as an alternative to the deprecated sqlite3_js_vfs_create_file().)

int sqlite3_js_posix_create_file(fiename, data[, dataLen = data.byteLength])

If the current environment supports the POSIX file APIs, this routine creates (or overwrites) the given file using those APIs. This is primarily intended for use in Emscripten-based builds where the POSIX APIs are transparently proxied by an in-memory virtual filesystem. It may behave differently in other environments.

The first argument must be either a JS string or WASM C-string holding the filename. Note that this routine does not create intermediary directories if the filename has a directory part.

The 2nd argument may either a valid WASM memory pointer, an ArrayBuffer, or a Uint8Array. The 3rd must be the length, in bytes, of the data array to copy. If the 2nd argument is an ArrayBuffer or Uint8Array and the 3rd is not a positive integer then the 3rd defaults to the array's byteLength value.

Results are undefined if data is a WASM pointer and dataLen is exceeds data's bounds.

Throws if any arguments are invalid or if creating or writing to the file fails.

sqlite3_js_rc_str()

string sqlite3_js_rc_str(int)

Given one of the SQLITE_... constant values, this function returns the string form of that constant, or undefined if no match is found, noting that some constants available in the C API are not exported to WASM because they simply are not used there. Example:

sqlite3_js_rc_str(sqlite3.capi.SQLITE_ERROR); // ==> "SQLITE_ERROR"

sqlite3_js_sql_to_string()

Added in 3.44.

string sqlite3_js_sql_to_string(v)

Converts SQL input from a variety of convenient formats to plain strings.

If v is a string, it is returned as-is. If it is-a Array, its join("") result is returned. If is is a Uint8Array, Int8Array, or ArrayBuffer, it is assumed to hold UTF-8-encoded text and is decoded to a string. If it looks like a WASM pointer, wasm.cstrToJs(v) is returned. Else undefined is returned.

sqlite3_js_vfs_create_file()

Achtung: this approach does not work properly in debug builds of sqlite3 because its out-of-scope use of the sqlite3_vfs API triggers assertions in the core library. That was unfortunately not discovered until 2023-08-11. This function is now deprecated and should not be used in new code.

Alternative options:

Usages:

Creates (or overwrites) a file using the storage appropriate for the given sqlite3_vfs. The first argument may be a VFS name (JS string only, NOT a WASM C-string), WASM-managed sqlite3_vfs*, or a capi.sqlite3_vfs instance. Pass 0 (a NULL pointer) to use the default VFS. If passed a string which does not resolve using sqlite3_vfs_find(), an exception is thrown. (Note that a WASM C-string is not accepted because it is impossible to distinguish from a C-level sqlite3_vfs*.)

The second argument, the filename, must be a JS or WASM C-string.

The 3rd may either be falsy, a valid WASM memory pointer, an ArrayBuffer, or Uint8Array. The 4th must be the length, in bytes, of the data array to copy. If the 3rd argument is a Uint8Array or ArrayBuffer and the 4th is not a positive integer then the 4th defaults to the array's byteLength value.

If data is falsy then a file is created with dataLen bytes filled with uninitialized data (whatever truncate() leaves there). If data is not falsy then a file is created or truncated and it is filled with the first dataLen bytes of the data source.

Throws if any arguments are invalid or if creating or writing to the file fails.

Note that most VFSes do not automatically create directory parts of filenames, nor do all VFSes have a concept of directories. If the given filename is not valid for the given VFS, an exception will be thrown. This function exists primarily to assist in implementing file-upload capability, with the caveat that clients must have some idea of the VFS into which they want to upload and that VFS must support the operation.

VFS-specific notes:

sqlite3_js_vfs_list()

array sqlite3_js_vfs_list()

Returns an array of the names of all currently-registered sqlite3 VFSes.

sqlite3_result_error_js()

void sqlite3_result_error_js(sqlite3_context * pCtx, Error e)

Calls either sqlite3_result_error_nomem(), if e is-a WasmAllocError, or sqlite3_result_error(). In the latter case, the second arugment is coerced to a string to create the error message.

Does not throw.

sqlite3_result_js()

void sqlite3_result_js(pCtx, value)

This acts as a proxy for one of the other sqlite3_result_...() routines, depending on the type of its 2nd argument:

On error, it calls sqlite3_result_error() with a description of the problem.

The first argument to this function is a (sqlite3_context*).

Does not throw.

sqlite3_value_to_js()

mixed sqlite3_value_to_js(sqlite3_value* v, throwIfCannotConvert=true)

Given a (sqlite3_value*), this function attempts to convert it to an equivalent JS value with as much fidelity as feasible and return it.

By default it throws if it cannot determine any sensible conversion. If passed a falsy second argument, it instead returns undefined if no suitable conversion is found. Note that there is no conversion from SQL to JS which results in the undefined value. It always throws a WasmAllocError if allocating memory for a conversion fails.

sqlite3_values_to_js()

array sqlite3_values_to_js(int argc, sqlite3_value* pArgV, throwIfCannotConvert=true)

Requires a C-style array of sqlite3_value* objects and the number of entries in that array. Returns a JS array containing the results of passing each C array entry to sqlite3_value_to_js(). The 3rd argument to this function is passed on as the 2nd argument to that one.