Persistent Storage Options

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:

When loaded in the main UI thread, the following utility methods are added to the sqlite3.capi namespace:

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:

⚠️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...

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.

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:

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.

  1. ^ The whole JS/WASM effort of the sqlite project initially stemmed from interest in getting it working with OPFS.
  2. ^ The alternative being to fail the operation.
  3. ^ Noting that the C APIs also do not expose such platform-specific APIs.