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:
- Transactions can only work if the database has only a single user or if all transaction-enclosed SQL code is contained in the same Worker message. The latter is easy to do but may place severe restrictions on the flow of the transaction compared to a synchronous interface. With an async interface it is easy to interleave transactioned and non-transactioned code, which will only make a mess.
- Nested queries are not possible with an async interface. That is, it is not possible to fire off an async query and, while processing its results, run nested queries to perform work based on those results. A nested query gets queued up at the end of the event queue, so cannot run until the outer query completes. The only workaround for this is to collect all data for the outer query before running any inner queries. Depending on the size of the data, that may or may not be feasible.
- Delayed execution of
postMessage()
-submitted db requests means that a user may submit any number of requests before the JS environment can start resolving any of them. This can lead to situations such as one db operation failing but there still being 15 more in the queue behind it which will run after that failure. In synchronous code, we can stop at the failure and skip the rest of the operations, but we cannot do so via a Worker-style API.
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 usedhasOwnProperty()
(or similar) to distinguish "missing property" from "property with the undefined value". Similarly,null
is a legal value forrow
in some case , whereas the db layer won't emit a result value ofundefined
.)
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:
sqlite3Worker1Promiser( config )
The config object is described in detail below.sqlite3Worker1Promiser()
Will use thesqlite3Worker1Promiser.defaultConfig
config object.sqlite3Worker1Promiser( function )
Is equivalent to passing{onready: theFunction}
and accepting defaults for the remaining options.
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 forsqlite3Worker1Promiser()
turns out to be clumsier to use than anonready
callback.
The config object may have any of the following properties, all of
which have usable defaults except for onready
:
onready
:function()
Called with no arguments when the async loading of the sqlite3 module and Worker APIs are done. This is the only way of knowing that the loading has completed.worker
: Worker or function
A Worker instance which loadssqlite3-worker1.js
or a functional equivalent. Note that the promiser factory replaces the worker.onmessage property. This config option may alternately be a function, in which case this function re-assigns this property with the result of calling that function, enabling delayed instantiation of a Worker.generateMessageId
:function(messageObject)
A function which, when passed an about-to-be-posted message object, generates a unique message ID for the message, which this API then assigns as the messageId property of the message. It must generate unique IDs on each call so that dispatching can work. If not defined, a default generator is used (which should be sufficient for most or all cases).debug
:function(...)
Aconsole.debug()
-style function for logging information about Worker messages.onunhandled
:function(event)
A callback which gets passed the message event object for anyworker.onmessage()
events which are not handled by this proxy. Ideally that "should" never happen, as this proxy aims to handle all known message types.
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:
( messageType, messageArguments )
is equivalent to:( {type: messageType, args: type-specific value} )
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.
- ^
countChanges
was added in version 3.43