Workers and Promises (a.k.a. Worker1 and Promiser)

The sqlite3 JS API can be loaded in either the main thread or a Worker thread and used the same from both. Another alternative is to load sqlite3 in its own dedicated Worker, making the sqlite3 API completely inaccessible to client code except via the relatively limited APIs described in this document.

Please be sure to see the gotcha regarding relative URIs when loading sqlite3.js via a Worker.

Before we start, let's look briefly at some of the limitations and pain points of a Worker-style asynchronous interface, compared to a synchronous interface:

Worker1

The Worker1 API provides a basic Worker-based interface into sqlite3, for cases where the client application code lives in one thread and wants sqlite3 to operate in its own thread.

It is based off of the OO1 API and supports multiple database connections within the Worker thread. Before we start, it seems appropriate to point out that the Promise-based wrapper for this API is significantly simpler to use, as it does not require dealing with postMessage() and it gives the user additional control over the flow of the requests and responses.

In order to permit this API to be loaded in Worker threads without automatically registering onmessage handlers, initializing the worker API requires calling sqlite3.initWorker1API(). If this function is called from a non-worker thread then it throws an exception. It must only be called once per Worker.

The simplest way to get the Worker running is:

const W = new Worker('sqlite3-worker1.js'); // or equivalent
W.onmessage = function(event){
  event = event.data;
  switch(event.type){
    case 'sqlite3-api':
      if('worker1-ready' === event.result){
        // The worker is now ready to accept messages
      }
    ...
  }
};

sqlite3-worker1.js is a thin wrapper which loads sqlite3.js, its WASM module, and then calls sqlite3.initWorker1API(), which triggers a Worker message which the client should listen for:

{type:'sqlite3-api', result:'worker1-ready'}

That lets the client know that it has been initialized.

Note that the worker-based interface can be slightly quirky because of its async nature. In particular, any number of messages may be posted to the worker before it starts handling any of them. If, e.g., an "open" operation fails, any subsequent messages will fail. The Promise-based wrapper for this API is more comfortable to use in that regard and gives the client more control over the order and rate which messages are posted.

Worker1 Message Format

Each message posted to the worker has an operation-independent envelope and operation-dependent arguments:

{
  type: string, // one of: 'open', 'close', 'exec', 'config-get'

  messageId: OPTIONAL arbitrary value. The worker will copy it as-is
  into response messages to assist in client-side dispatching.

  dbId: a db identifier string (returned by 'open') which tells the
  operation which database instance to work on. If not provided, the
  first-opened db is used. This is an "opaque" value, with no
  inherently useful syntax or information. Its value is subject to
  change with any given build of this API and cannot be used as a
  basis for anything useful beyond its one intended purpose.

  args: ...operation-dependent arguments...

  // the framework may add other properties for testing or debugging
  // purposes.
}

Response messages, posted back to the Worker-invoking thread, look like:

{
  type: Same as the inbound message except for error responses,
  which have the type 'error',

  messageId: same value, if any, provided by the inbound message

  dbId: the id of the db which was operated on, if any, as returned
  by the corresponding 'open' operation.

  result: ...operation-dependent result...
}

Error responses are reported messages in an operation-independent format:

{
  type: "error",

  messageId: ...as above...,

  dbId: ...as above...

  result: {

    operation: type of the triggering operation: 'open', 'close', ...

    message: ...error message text...

    errorClass: string. The ErrorClass.name property from the thrown exception.

    input: the message object which triggered the error.

    stack: _if available_, a stack trace array.

  }
}

Worker1 Methods

The available message types are listed below in alphabetical order.

close

A close message closes a database.

Message format:

{
  type: "close",
  messageId: ...as above...
  dbId: ...as above...
  args: OPTIONAL {unlink: boolean}
}

If the dbId does not refer to an opened ID, this is a no-op. If the args object contains a truthy unlink value then the database will be unlinked (deleted) after closing it. The inability to close a db (because it's not opened) or delete its file does not trigger an error.

Response:

{
  type: "close",
  messageId: ...as above...,
  result: {
    filename: filename of closed db, or undefined if no db was closed
  }
}

config-get

This operation fetches the serializable parts of the sqlite3 API configuration.

Message format:

{
  type: "config-get",
  messageId: ...as above...,
  args: currently ignored and may be elided.
}

Response:

{
  type: "config-get",
  messageId: ...as above...,
  result: {

    version: sqlite3.version object

    bigIntEnabled: bool. True if BigInt support is enabled.

    vfsList: result of sqlite3.capi.sqlite3_js_vfs_list()
 }
}

exec

exec is the interface for running arbitrary SQL. It is a wrapper around the oo1.DB.exec() method and supports most of its features.

All SQL execution is processed through the exec operation. It offers most of the features of the oo1.DB.exec() method, with a few limitations imposed by the state having to cross thread boundaries.

Message format:

{
  type: "exec",
  messageId: ...as above...
  dbId: ...as above...
  args: string (SQL) or {... see below ...}
}

Response:

{
  type: "exec",
  messageId: ...as above...,
  dbId: ...as above...
  result: {
    input arguments, possibly modified. See below.
  }
}

The arguments are in the same form accepted by oo1.DB.exec() with the exceptions noted below.

A function-type args.callback property cannot cross the window/Worker boundary, so is not useful here. If args.callback is a string then it is assumed to be a message type key, in which case a callback function will be applied which posts each row result via:

postMessage({
  type: thatKeyType,
  rowNumber: 1-based-#,
  row: theRow,
  columnNames: anArray
})

The row property contains the row result in the form implied by the rowMode option (defaulting to 'array'). The rowNumber is a 1-based integer value incremented by 1 on each call into th callback. The columnNames array contains the column name(s) for the result row column(s).

At the end of the result set (whether or not any result rows were produced), it will post an identical message with (row=undefined, rowNumber=null) to alert the caller than the result set is completed. Note that a row value of null is a legal row result for certain arg.rowMode values.

(Design note: we don't use (row=undefined, rowNumber=undefined) to indicate end-of-results because fetching those would be indistinguishable from fetching from an empty object unless the client used hasOwnProperty() (or similar) to distinguish "missing property" from "property with the undefined value". Similarly, null is a legal value for row in some case , whereas the db layer won't emit a result value of undefined.)

The callback proxy must not recurse into this interface. An exec() call will tie up the Worker thread, causing any recursion attempt to wait until the first exec() is completed.

If the countChanges arguments property1 is truthy then the result property contained by the returned object will have a changeCount property which holds the number of changes made by the provided SQL. Because the SQL may contain an arbitrary number of statements, the changeCount is calculated by calling sqlite3_total_changes() before and after the SQL is evaluated. If the value of countChanges is 64 then the changeCount property will be returned as a 64-bit integer in the form of a BigInt (noting that that will trigger an exception if used in a BigInt-incapable build). In the latter case, the number of changes is calculated by calling sqlite3_total_changes64() before and after the SQL is evaluated.

The response is the input options object (or a synthesized one if passed only a string), possibly modified. options.resultRows and options.columnNames may be populated by the call to DB.exec(), and options.changeCount may be set as described above.

open

The open message directs the worker to open a database.

Message format:

{
  type: "open",
  messageId: ...as above...,
  args:{

    filename [=":memory:" or "" (unspecified)]: the db filename.
      See the sqlite3.oo1.DB constructor for peculiarities and
      transformations

    vfs: sqlite3_vfs name. Ignored if filename is ":memory:" or "".
      This may change how the given filename is resolved. The VFS may
      optionally be provided via a URL-style filename argument:
      filename: "file:foo.db?vfs=...". If both this argument and a
      URI-style argument are provided, which one has precedence is
      unspecified.

  }
}

For the filename, URL-style names may be used and those may include a VFS name, which enables them to use (e.g.) OPFS support: file:foo.db?vfs=opfs

Response:

{
    type: "open",
    messageId: ...as above...,
    result: {
      filename: db filename, possibly differing from the input.

      dbId: see below,

      persistent: true if the given filename resides in the
         known-persistent storage, else false.

      vfs: name of the underlying VFS
    }
}

The dbId is an opaque ID value which should be passed in the message envelope to other calls in this API to tell them which db to use. If it is not provided to future calls, they will default to operating on the first-opened db. This property is, for API consistency's sake, also part of the containing message envelope. Only the open operation includes it in the result property.

Achtung: because postMessage() events are queued up for execution in a queue which the application can neither see nor manipulate, it is possible that the client may queue up any number of messages before an open request is actually processed. If the open fails, all messages after it are likely to fail as well, but there is no way for the client code or worker to cancel them. The promiser API can work around that by enabling the client to "await" on the open response before continuing.

Promise-based Wrapper (a.k.a. Worker1 Promiser)

The Promise-based wrapper around the Worker1 API provides a considerably more user-friendly interface than postMessage(). Like the Worker1 API, this interface loads the main sqlite3 API in its own dedicated Worker thread, separate from all client code. Instead of accessing it via postMessage(), however, it is accessed via a Promise-based interface. Under the hood, it uses postMessage(), but the Promise interface provides the client with far more control in the timing of db operations.

To load it:

<script src="path/to/sqlite3-worker1-promiser.js"></script>

Noting that sqlite3-worker1-promiser.js is part of the sqlite3 JS/WASM distribution and must live in the same directory as the rest of the sqlite3 JS/WASM parts.

Promiser Configuration and Instantiation

It requires sqlite3-worker1.js and sqlite3.js, noting that it is possible to configure it to use a different script to load the sqlite3 worker.

That script will install a global-scope function named sqlite3Worker1Promiser() which acts as a factory for creating promiser instances. It has three call forms:

The defaults for the 2nd and 3rd call forms may be configured before calling sqlite3Worker1Promiser() by modifying the sqlite3Worker1Promiser.defaultConfig object.

The config object is technically optional but its onready property is effectively required because it's the only way to be notified when the asynchronous loading and initialization of the sqlite3 module is complete.

Design note: the irony of using an onready callback, instead of a returning a Promise, is not lost on the developers. Returning a Promise for sqlite3Worker1Promiser() turns out to be clumsier to use than an onready callback.

The config object may have any of the following properties, all of which have usable defaults except for onready:

With the config object in place, a promiser is instantiated like:

const promiser = self.sqlite3Worker1Promiser(config);

Promiser Methods

A Promiser object is a function with two call signatures:

where the type is always a string and the args value is message-type specific. It always returns a Promise object which resolves to an object:

{
  type: messageType,
  result: type-specific result value,
  ... possibly other metadata ...
}

Each message type corresponds to one API method, all of which correspond to methods from the Worker1 API and have the same arguments and results except where explicitly described below.

Errors are reported in the same way as the Worker1 API, but the error response causes the Promise to be rejected. Thus clients listen for these by adding catch() handlers to their Promises. For example:

promiser('open', {'filename':...}).then((msg)=>{
  ...
}).catch((e)=>{
  // Note that the error state is _not_ an Error object, but an
  // object in the same form the Worker1 API reports errors in.
  // That behavior is potentially subject to change in the future,
  // such that catch() always gets an Error object.
  console.error(e);
})

close

Functions like the Worker1 close method but will also clear the internal default dbId if it closes that specific database.

config-get

This is the only method, aside from open, which does not require a database connection.

exec

The method works almost identically to its Worker1 counterpart, with the following differences:

exec's {callback: STRING} option does not work via this interface (it triggers an exception), but {callback: function} does and works exactly like the STRING form does in the Worker: the callback is called one time for each row of the result set, passed the same worker message format as the worker API emits:

{
  type: typeString,
  row: VALUE,
  rowNumber: 1-based-#,
  columnNames: anArray
}

Where typeString is an internally-synthesized message type string used temporarily for worker message dispatching. It can be ignored by all client code except that which tests this API.

At the end of the result set, the same event is fired with (row=undefined, rowNumber=null) to indicate that the end of the result set has been reached. Note that the rows arrive via worker-posted messages, with all the implications of that.

open

Functions like the Worker1 open method but will also internally record the dbId from the response if this is the first db that was opened so that it can use that database ID for future operations which do not provide one.

  1. ^ countChanges was added in version 3.43