- Key-Value VFS (kvvfs): localStorage and sessionStorage
- Origin-Private FileSystem (OPFS)
- Cross-thread Communication
This API provides database persistence via localStorage/sessionStorage and, in compatible browsers, the Origin-Private FileSystem.
Key-Value VFS (kvvfs): localStorage
and sessionStorage
kvvfs
is an sqlite3_vfs implementation conceived and created to
store a whole sqlite3 database in the localStorage
or
sessionStorage
objects. Those objects are only available in the main
UI thread, not Worker threads, so this feature is only available in
the main thread. kvvfs
stores each page of the database into a
separate entry of the storage object, encoding each database page into
an ASCII form so that it's JS-friendly.
This VFS supports only a single database per storage object. That
is, there can be, at most, one localStorage
database and one
sessionStorage
database.
To use it, pass the VFS name "kvvfs" to any database-opening routine
which accepts a VFS name. The file name of the db must be either
local
or session
, or their aliases :localStorage:
and
:sessionStorage:
. Any other names will cause opening of the db to
fail. When using URI-style names, use one of:
file:local?vfs=kvvs
file:session?vfs=kvvs
When loaded in the main UI thread, the following utility methods are
added to the sqlite3.capi
namespace:
sqlite3_js_kvvfs_size(which='')
returns an estimate of how many bytes of storage are used by kvvfs.sqlite3_js_kvvfs_clear(which='')
clears all kvvfs-owned state and returns the number of records it deleted (one record per database page).
In both cases, the argument may be one of ("local"
, "session"
,
""
). In the first two cases, only localStorage
resp. sessionStorage
are acted upon and in the latter case both are
acted upon.
Storage limits are small: typically 5MB, noting that JS uses a two-byte character encoding so the effective storage space is less than that. The encoding of the database into a format JS can make use of is slow and consumes a good deal of space, so these storage options are not recommended for any "serious work." Rather, they were added primarily so that clients who do not have OPFS support can have at least some form of persistence.
When the storage is full, database operations which modify the db will fail. Because of the inherent inefficiency of storing a database in persistent JS objects, which requires encoding them in text form, databases in kvvfs are larger than their on-disk counterparts and considerably slower (computationally speaking, though the perceived performance may be fast enough for many clients).
JsStorageDb: kvvfs the Easy Way
Using the kvvfs is much simpler with the OO1 API. See the JsStorageDb class for details.
The Origin-Private FileSystem (OPFS)
The Origin-Private FileSystem, OPFS, is an API providing browser-side persistent storage which, not coincidentally, sqlite3 can use for storing databases1.
As of March 2023 the following browsers are known to have the necessary APIs:
- Chromium-derived browsers released since approximately mid-2022
- Firefox v111 (March 2023) and later
⚠️Achtung: COOP and COEP HTTP Headers
JavaScript's SharedArrayBuffer
type is required for OPFS support,
and that class is only available if the web server includes the
so-called COOP and COEP response headers when delivering scripts:
Cross-Origin-Embedder-Policy: require-corp
Cross-Origin-Opener-Policy: same-origin
Without these headers, the SharedArrayBuffer
will not be available,
so OPFS support will not load. That class is required in order to
coordinate communication between the synchronous and asynchronous
parts of the sqlite3_vfs OPFS proxy.
Apache Web Server
For an Apache web server, these headers are set using something the following:
<Location "/">
Header always append Cross-Origin-Embedder-Policy "require-corp"
Header always append Cross-Origin-Opener-Policy: "same-origin"
AddOutputFilterByType DEFLATE application/wasm
</Location>
Althttpd Web Server
For the althttpd web server, start it
with the --enable-sab
flag ("sab" being short for SharedArrayBuffer).
OPFS via sqlite3_vfs
This support is only available when sqlite3.js
is loaded from a
Worker thread, whether it's loaded in its own dedicated worker or in a
worker together with client code. This OPFS wrapper implements an
sqlite3_vfs wrapper entirely in JavaScript.
This feature is activated automatically if the browser appears to have the necessary APIs to support it. It can be tested for in JS code using one of:
if(sqlite3.capi.sqlite3_vfs_find("opfs")){ ... OPFS VFS is available ... }
// Alternately:
if(sqlite3.oo1.OpfsDb){ ... OPFS VFS is available ... }
If it is available, the VFS named "opfs" can be used with any sqlite3
APIs which accept a VFS name, such as sqlite3_vfs_find()
,
sqlite3_db_open_v2()
, and the sqlite3.oo1.DB
constructor,
noting that OpfsDb
is a convenience subclass
of oo1.DB
which automatically uses this VFS. For URI-style
names, use file:my.db?vfs=opfs
.
Directory Parts in DB Names
Unlike most sqlite3_vfs implementations, this one will automatically create any leading directory parts of a database file name if the database is opened with the "create" flag. This divergence from common conventions was made in the interest of...
- Making life easier for web developers.
- Avoiding having to expose OPFS-specific APIs for creating directories to client code. Ideally, client DB-related code should be agnostic of the storage being used.
For example:
const db = new sqlite3.oo1.OpfsDb('/path/to/my.db','c');
will, if needed, create the /path/to
directories.
Concurrency and File Locking
Forewarning: concurrent access to OPFS-hosted files is a pain point for this VFS. Client applications will not be able to achieve desktop-app-grade concurrency in this environment, but a moderate degree of concurrency across browser tabs and/or Workers is possible.
Background: OPFS offers a handful of synchronous APIs which are required by this API. A file can be opened in asynchronous mode without any sort of locking, but acquiring access to the synchronous APIs requires what OPFS calls a "sync access handle," which exclusively locks the file. So long as an OPFS file is locked, it may not be opened by any other service running in that same HTTP origin. e.g. code running in one browser tab cannot access that file so long as it is locked by another tab running from that same origin.
In essence, that means that no two database handles can have the same OPFS-hosted database open at one time. If the same page is opened in two tabs, the second tab will hit a locking error the moment it tries to open the same OPFS-hosted database!
To help alleviate contention between instances of sqlite3 running in multiple tabs or worker threads, sqlite3 only acquires a write-mode handle when the database API requires a lock. If it cannot acquire a lock, it will wait a brief period and try again, repeating this several times before giving up. Failure to obtain a lock will bubble up through to client-level code in the form of a generic I/O error. The OPFS API does not have a way to unambiguously distinguish locking-related errors from other I/O errors, so an I/O error is what the client will see.
Sidebar: the (former) inability to unambiguously distinguish locking errors has been lifted in late-2022 Chromium versions. See the discussion at https://github.com/whatwg/fs/pull/21 for details. It is as yet unclear whether the ability to unambiguously distinguish such cases can be used to improve concurrency support or whether it will simply allow us to fail faster if acquisition of a sync access handle fails for any reason other than the new
NoModificationAllowedError
.
The reliable limit of connections on a given OPFS-hosted database is unknown and depends largely on how the database is used. Basic tests suggests that 3 connections can, if they limit their work to small chunks, reliably cooperate. The chance of locking failure increases drastically for each connection beyond that.
Here are some hints to help improve OPFS concurrency, in particular in the face of a client opening an application via multiple tabs.
- Do not open a database until it's known to be required by the application.
- Never use two database handles to the same db file within the same thread, as that can hypothetically lead to certain deadlock situations.
- Always perform work in "small" chunks, where "small" is measured in milliseconds of I/O, as opposed to data sizes. The less time the database spends on I/O, the lower the chance of contention.
- Do not hold transactions open for any significant length of time. An open transaction necessarily locks the OPFS file.
Work to improve concurrency support on OPFS-hosted databases is an ongoing process. As OPFS's locking support evolves, and more fine-grained control of locking becomes widely available, the sqlite3 VFS will take advantage of it to help improve concurrency.
Unlock-ASAP Mode
Sometimes sqlite3 will call into a VFS without explicitly acquiring a lock on the storage in advance (on journal files, for example). When it does so, an operation which requires a sync access handle necessarily acquires the lock itself2 and holds on to it until the VFS is idle for some unspecified brief period (less than half a second), at which point all implicitly-acquired locks are relinquished.
Such locks are internally called implicit locks or auto-locks. They are locks which are not required by the sqlite3 VFS but are required by OPFS. Normally, an operation which acquires the lock does not automatically relinquish the lock at the end of the operation because doing so imposes a tremendous performance hit (up to 400% run time increase in I/O-heavy benchmarks). However, concurrency can be improved considerably by telling the VFS to immediately relinquish such locks at the earliest opportunity. This is colloquially known as "unlock-asap" mode, and it's disabled by default because of the performance penalty but can be enabled by clients on a per-db-connection basis using URI-style database names:
file:db.file?vfs=opfs&opfs-unlock-asap=1
That string can be supplied to any APIs which permit URI-style filenames. Similarly:
new sqlite3.oo1.OpfsDb('file:db.file?opfs-unlock-asap=1');
That flag should only be used if an application specifically has
concurrency-related issues. If all else fails, opfs-unlock-asap=1
might help, but whether or not it genuinely will depends largely on
how the database is being used. e.g. long-running transactions will
lock it regardless of whether the opfs-unlock-asap
option is used.
Maintaining OPFS-hosted Files
For SQLite's purposes, the OPFS API is an internal implementation detail which is not exposed directly to client code. This means, for example, that the SQLite API cannot be used to traverse the list of files stored in OPFS, nor to delete a database file3. Though it may initially seem feasible to provide a virtual table which provides a list of OPFS-hosted files, and the ability to delete them, that cannot work because the relevant OPFS APIs are all asynchronous, making them impossible to use together with the C-level SQLite APIs.
As of this writing, the following possibilities are known for managing such files:
- Using the OPFS API from the browser's developer tools. This is not for the faint-of-heart but will do in a pinch.
- The OPFS Explorer extension for Chromium-based browsers provides an interactive tree of OPFS-hosted files for a given HTTP origin.
Sidebar: Cross-thread Communication via OPFS
sqlite3 over OPFS opens up a possibility in JS which does not otherwise readily exist: communication between arbitrary threads.
There are no mechanisms in JS to share state between two threads
except postMessage()
, SharedArrayBuffer
, and (to a very limited
extent) Atomics
. localStorage
, sessionStorage
, and the
long-defunc (but still extant) WebSQL, are main-thread
only. Presumably WebSQL was not permitted in Workers for the very
reason that it would open up a communication channel between arbitrary
threads.
However, if a client loads the sqlite3 module from multiple threads, they can communicate freely via an OPFS-hosted database. Mostly. Such usage would introduce file-locking contention between the threads. So long as each thread uses only very brief transactions, the automatic lock-retry mechanism will transparently account for the locking, but as soon as one thread holds a transaction open for any significant amount of time, or too many threads are contending for access, locking-related exceptions would result and would be translated as I/O errors to the C API.
Whether the capability of communicating across threads via a database is a feature or a bug is left for the client to decide.