Virtual Tables and Table-valued Functions

See also: C structs in JS

Virtual tables are one of sqlite's most powerful features, providing clients with a way to access near-arbitrary data through SQL. With great power, however, comes non-trivial complexity.

The virtual table (a.k.a. vtab) API is an advanced feature of the library with a great deal of documentation written about it and example programs demonstrating it. These docs do not endeavor to teach users about how to write their own virtual tables, and instead focus only on the JS-specific components of doing so.

The JS API for virtual tables is close to a one-to-one mapping of the C API, differing only in the addition of some helper routines for accessing and manipulating the C-side state used by the APIs. The APIs described here are not necessary for defining virtual tables in JS, but are provided solely to simplify the process. Virtual tables can be implemented using only the C-style API if clients prefer.

Managing Pointer-to-Struct Lifetimes

The sqlite3_module interface has clear lifetime rules for sqlite3_vtab and sqlite3_vtab_cursor objects which work like this:

JS code cannot do very much with the raw pointers and requires wrapper objects in order to access and manipulate them, as described in the C structs docs. Similarly, JS-side instances cannot "extend" those types in the way C code would, but they can attach any implementation-specific state to the JS instances (so, in effect, have an easier way to customize their state than their C counterparts do).

The sqlite3.vtab object provides APIs to simplify the lifetime management and pointer-mapping of these objects in JS. These APIs work identically for sqlite3_vtab and sqlite3_vtab_cursor but are used at different points in the sqlite3_module interface.

The API objects are named vtab.xVtab and vtab.xCursor, for sqlite3_vtab and sqlite3_vtab_cursor types, respectively, and their methods are described below.

The main sqlite3 JS unit tests have example virtual tables which demonstrate the use of these APIs.


Passed the output pointer argument from an appropriate sqlite3_module method (see below), this creates a new struct instance, writes its pointer value to the given output pointer, and returns that object. It will throw if allocation of the struct instance fails.



Passed a struct pointer from an appropriate sqlite3_module method (see below), this returns the same struct instance wrapper which was created for that pointer and returned by create().


Rule to remember: never call dispose() on an instance returned by this function. Delay that until calling...

unget() and dispose()

unget() is identical to get() but also disconnects the mapping between the given pointer and the returned struct object, such that future calls to this function or get() with the same pointer will return the undefined value. It is up to the caller to call theStruct.dispose() on the returned object before the enclosing function returns.

dispose() works like unget() but calls theStruct.dispose() instead of returning the object. Calling unget() obligates the caller to call dispose() on the returned object when they're done with it.


sqlite3_module Methods

The sqlite3_module class inherits from the core C struct type and extends the hierarchy with...


sqlite3_module setupModule(options)

Sets up this module object.

A helper to initialize and set up an sqlite3_module() object for later installation into individual databases using sqlite3_create_module(). Requires an object with the following properties:

Certain methods may refer to the same implementation. To simplify the definition of such methods:

This is to facilitate creation of those methods inline in the passed-in object without requiring the client to explicitly get a reference to one of them in order to assign it to the other one.

The catchExceptions-installed handlers will account for identical references to the above functions and will install the same wrapper function for both so that the underlying library sees the same pointer value for both.

The given methods are expected to return integer values, as expected by the C API. If catchExceptions is truthy, the return value of the wrapped function will be used as-is and will be translated to 0 if the function returns a falsy value (e.g. if it does not have an explicit return). If catchExceptions is not active, the method implementations must explicitly return integer values. Failing to do so will lead to failures, in particular in error cases because those will go unreported.

Throws on error. On success, returns a sqlite3_module object: this or opt.struct or a new sqlite3_module instance, depending on how it's called.


sqlite3_index_info objects are used for conveying information between sqlite3_module::xBestIndex() implementations and the sqlite3 engine. JS code does not manage lifetimes of these and must not hold on to pointers to them for longer than a function call which receives the pointer as an argument. JS code needs to use capi.sqlite3_index_info wrapper objects to interact with the C-level struct state.


const xBestIndex = function(pVtab, pIdxInfo){
  const vtab = sqlite3.vtab.xVtab.get(pVtab); // documented above
  const sii = new sqlite3.capi.sqlite3_index_info(pIdxInfo);
  sii.dispose(); // see notes below
  return 0;

When C struct wrappers are constructed with a pointer argument, as shown above, they do not allocate new instances, but instead provide access to existing instances. In such cases, it is not strictly necessary to call their dispose() method, but it is generally a good habit to be in to avoid memory leaks for the cases where it is important.

In additional to the struct-related APIs available to all JS-bound C structs, sqlite3_index_info has the following helper methods installed in its prototype, available to all instances:

These are all demonstrated in the main unit test script.