The Current State of SQLite Persistence on the Web: February 2024 Update

This post has been referenced often, so we are maintaining it as a comprehensive view of the SQLite for web landscape. We’ve added a changelog to keep track of major updates. Please contact us if you notice something wrong.

Landscape Update 2024-02-08

Since this post was first published on 2023-07-19, there have been some updates in the landscape.

On the browser side:

1. The OPFS readwrite-unsafe mode has landed in Chrome 121. This allows concurrent access to the same file.
2. WebAssembly JavaScript Promise Integration (JSPI) is a proposal to remove the need for Asyncify. This is currently available as a flag in Chrome, and Firefox support is planned. Unfortunately the current version is much slower in Chrome than Asyncify, and tab crashes are still common.
   

In the official sqlite3 WASM build, the opfs-sahpool VFS has been released in SQLite 3.43.0.

In wa-sqlite, a rewrite of the current VFS implementations is in progress, which can use the above browser functionality when available:

1. IDBBatchAtomicVFS can now use JSPI instead of Asyncify.
2. OriginPrivateVFS replaces OriginPrivateFileSystemVFS, and can support concurrent reads via the readwrite-unsafe mode. This still uses the asynchronous OPFS APIs, so performance is not expected to be great.
3. AccessHandlePoolVFS is rewritten to have file system transparency, and support concurrent reads via readwrite-unsafe. This requires pre-creating the database files from database - it is not possible to just ATTACH to new databases at runtime, but that is not a feature that many web applications would require. This VFS does not require Asyncify or JSPI.
   

Details on the new VFS implementations are here.

With file system transparency and concurrent read support, the new AccessHandlePoolVFS should be a great default choice for most web apps.

There is still no web VFS that supports WAL mode yet. There is one experimental VFS in wa-sqlite that implements similar ideas on the JavaScript side, supporting concurrent reads while writing. This is a space to watch.

In the ORM space, there are now some options for using higher-level APIs:

• SQLocal provides Kysely and Drizzle wrappers for the official sqlite3 WASM build.
• Another project provides Kysely dialects for both sqlite3 WASM and wa-sqlite.

Background

SQLite is the most widely deployed database in the world. Recently, it has also become a viable option for data persistence in browser-based applications.

There are a few projects bringing SQLite to the web:

1. SQL.js has been around since 2014 — initially cross-compiling SQLite to JavaScript, then WASM (WebAssembly) when browsers started supporting it. By itself, SQL.js only supports in-memory databases, and does not support persistence other than importing or exporting the entire database file at a time.
2. In 2021, the wa-sqlite project was created, implementing experimental support for persisting SQLite data in IndexedDB. Its performance was quite slow at that point.
3. Later in 2021, the absurd-sql project did the same, but using a couple of new techniques to achieve much faster performance — even surpassing direct IndexedDB performance for some query patterns. The accompanying blog post has a lot of details on the complexities of the implementation. While it was a great showcase of what can be done, it was never maintained as a project to be used in production. The wa-sqlite project built further on some of these ideas.
4. Towards the end of 2022, the official SQLite project released their own beta WASM build.

While none of these projects currently claim “production level stability” for persistence, the current support could already be sufficient for some projects. But there are many options to choose from, and pros and cons may not be immediately clear.

This post gives an overview of the various considerations involved in persisting SQLite data in a browser, with details about each currently available implementation. If you're only interested in learning what to use right now, skip to the "Recommendations" section at the end of the post.

Concurrency in SQLite

SQLite supports concurrent access to a single database file, by utilizing multiple “connections” to a file. Concurrency should not be confused with thread-safety: A single connection can be “safe” to use from different native threads (depending on compile options used), but it serializes statements and can never execute more than one transaction at a time.

To execute multiple transactions concurrently, multiple connections can be used. Each connection operates independently and uses locking via the file system, so the effect is the same whether connections are used from different threads or different processes on the same machine.

By default, SQLite uses a rollback journal (DELETE, TRUNCATE or PERSIST modes) to protect against corruption, and supports rolling back transactions. Only a single transaction can modify the rollback journal at a time, which means that only a single write transaction can execute at a time. Any other transactions that attempt to write at the same time will fail with [.inline-code-snippet]SQLITE_BUSY[.inline-code-snippet]. Additionally, no read transactions can be performed while a write transaction is active. See the official documentation for more details. 

When using the WAL journal mode instead, the concurrency restrictions are relaxed somewhat: read transactions can be executed concurrently with a single write transaction. There is also an experimental WAL2 mode that allows multiple concurrent write transactions as long as they don’t use the same pages. This is still experimental and on a separate SQLite branch, so we won’t cover that here.

Persistence on web

SQLite has a VFS interface, which can be used to implement a file system on any system. Generally, the implementation must provide methods to write a block of data, read a block of data, flush data to the file system, and lock files, in addition to other more advanced methods.

For the underlying storage, there are two primary options:

1. OPFS (Origin Private File System). This API gives random access to files, private to the origin of the page. In principle, this API is ideal for databases like SQLite, but the APIs currently available do still have some limitations, covered later on.
2. IndexedDB. This is a more general purpose database for browsers. It can be used as a storage layer for SQLite, by storing individual blocks of data as IndexedDB objects.

Other options such as localStorage are too restrictive, so we won’t cover that here.

Asynchronous calls

SQLite itself is implemented using synchronous operations. This is still the case when compiling to WASM, which means that VFS implementations in JavaScript must also be synchronous. This is a problem in the JavaScript world, where file system operations are typically asynchronous.

To work around this, there are various options:

Emscripten Asyncify

https://emscripten.org/docs/porting/asyncify.html

This translates the synchronous calls into asynchronous calls, allowing the VFS implementation to be asynchronous. There are several downsides:

1. The built WASM file size increases by around 2x.
2. Performance can be reduced by 2-5x.
3. The behavior of SQLite may be affected by the transforms in asyncify, and this build is not as well tested. Any bugs in asyncify could cause subtle issues in the SQLite build. The SQLite team does not use it for this reason.

SharedArrayBuffer + Atomics API

This requires running a separate Worker process for the file system operations. It also places additional restrictions on the web origin, to allow safe usage of SharedArrayBuffer. The COOP and COEP headers required are explained here: https://sqlite.org/wasm/doc/trunk/persistence.md#coop-coep

Additionally, this adds significant overhead wherever this mechanism is used.

JSPI

Update 2024-02-14: WebAssembly JavaScript Promise Integration (JSPI) is a proposal that gives functionality similar to Asyncify, but as a core browser feature. This is currently available as a flag in Chrome, and Firefox support is planned. Unfortunately the current version is much slower in Chrome than Asyncify, and tab crashes are still common.

OPFS syncAccessHandle

This provides synchronous read and write access to files. The latest versions of Chrome, Safari and Firefox all support these APIs.

There are still some restrictions:

1. While operations on an open file are synchronous*, opening a file is still an asynchronous operation. This requires either pre-opening all files that could be used, or combining with the SharedArrayBuffer + Atomics API workaround above.
2. Opening a file takes out an exclusive lock, meaning no other connection can read or write the same file at the same time. This means that if files are pre-opened and kept open for the duration of the connection, only a single connection can be used.

There is a proposed API addition that would allow multiple concurrent readers and writers with a [.inline-code-snippet]readwrite-unsafe[.inline-code-snippet] mode: https://github.com/whatwg/fs/blob/main/proposals/MultipleReadersWriters.md

Chrome is currently working on a prototype, and other browsers may follow later, but no browsers currently support this.

*There was a time when [.inline-code-snippet]close[.inline-code-snippet], [.inline-code-snippet]flush[.inline-code-snippet] and [.inline-code-snippet]truncate[.inline-code-snippet] were asynchronous. In the current version of the spec, these methods are all synchronous.

Update 2024-02-14: The readwrite-unsafe mode is now available in Chrome 121+.

Concurrency on the web

Concurrency for SQLite on the web generally has the same restrictions as on other platforms: Each connection can run one transaction at a time. Browsers do not provide direct support for multi-threading, including in WASM, but multiple web workers can be used to get a similar effect.

Concurrent transactions may be supported over multiple tabs or within a tab, by utilizing multiple SQLite connections. However, some VFS implementations require an exclusive lock on the database and only support having a single connection open at a time, in which case concurrent transactions are not supported at all. The single connection has to be shared between any active tabs. Other VFS implementations allow multiple concurrent connections, but only a single concurrent transaction. And others do allow multiple concurrent read transactions, as long as no write transaction is active.

Concurrent transactions may be supported over multiple pages or within a page, by utilizing multiple connections. However, some VFS implementations only support having a single connection open at a time, in which case concurrent transactions are not supported at all. Some of these implementations further require sharing a single connection between multiple pages at the same time.

There is currently no implementation that supports read transactions concurrently with write transactions, although there is hope for that using WAL journal mode on OPFS in the future.

Future options: Concurrent write + read transactions

To support read transactions concurrently with a write transaction, WAL mode is required. This requires implementing additional VFS methods to provide “shared memory” between different connections.

While it is possible to use WAL mode without shared memory, this would prevent concurrent access, so it would not help here.

Currently, there is no VFS that implements this. It would also not help currently, since:

1. IndexedDB requires an exclusive lock on an object store for any write, so concurrent read + write access would still not be possible.
2. OPFS sync access handle APIs currently exclusively lock the file for read or write access, also making concurrent read + write access impossible.
3. OPFS asynchronous APIs have significantly worse performance, negating the gains from getting concurrency.

In the future, when the proposed [.inline-code-snippet]readwrite-unsafe[.inline-code-snippet] mode is available, an implementation supporting WAL mode could be great.

Update 2024-02-14: The readwrite-unsafe mode is now available in Chrome 121+. However, providing the required shared memory across connections in different web workers is still tricky, and no VFS implementation supports it yet.

Performance tweaks

Performance in SQLite is tightly related to how the file system operations are implemented.

There are a couple of ways to get better performance - either in the file system layer, or in higher-level configuration.

Batch-Atomic write transactions

By default, SQLite assumes that a sequence of writes to a file may be interrupted at any point in time, due to e.g. operating system crash or power loss. The rollback journal is used to allow recovery after a crash.

However, some file systems can guarantee that a batch of write operations will all either succeed or all fail - typically by implementing a journal mode as part of the file system itself. SQLite can exploit this behavior when available, avoiding the need for a rollback journal in many (but not all) cases. It still keeps a rollback journal in memory, and may need to persist it to a file if it grows too large. On the web, IndexedDB could also be used to provide the same guarantees, which then gives some nice performance improvements.

Unfortunately, even though this could in theory also provide similar concurrency to WAL mode, SQLite does not support that at the moment. There is some discussion around this: https://github.com/rhashimoto/wa-sqlite/discussions/78

Exclusive Locking

The database can be locked in exclusive mode,  only allowing a single connection to access the database as long as it is open. This reduces the number of locks needed for transactions, and can significantly speed up writes. This is configured using [.inline-code-snippet]PRAGMA locking_mode = EXCLUSIVE[.inline-code-snippet].

The caveat is that concurrent read transactions are not possible in this mode. But when the file system implementation only allows a single connection at a time anyway (like a couple of the options below), this is not an issue.

Relaxed durability

By default, SQLite only acknowledges a transaction when it is safely persisted to the underlying storage. In other words, even an operating system crash or power failure should not lose the transaction.

It also offers relaxed durability, where acknowledged transactions may not be persisted if the operating system crashes, but is safe if the application crashes. This is configured using [.inline-code-snippet]PRAGMA synchronous = NORMAL[.inline-code-snippet]. There is a further mode of [.inline-code-snippet]PRAGMA synchronous = OFF[.inline-code-snippet], which does not wait for the file system at all, but may cause database corruption if the operating system crashes.

Some VFS implementations may provide additional relaxed durability options specific to the storage layer.

When combining exclusive locking with [.inline-code-snippet]synchronous = OFF[.inline-code-snippet] (or an equivalent durability option in the VFS), write transactions may occur without waiting for the file system at all, which can result in a much higher number of transactions per second. Throughput within a large transaction is not expected to change much with this.

File system transparency

When using OPFS, files can be stored “transparently”, meaning the persistence format is exactly the same as what SQLite traditionally uses, with no workarounds or additional metadata required. This has the advantage of interoperability between different implementations - it may be possible to switch out libraries completely, without losing data already persisted.

Higher-level libraries

The implementations mentioned here are generally low-level - SQLite APIs are exposed directly. A typical application would ideally use a library that manages transactions, locking, connection pooling (optional), web workers (if applicable), and provides higher-level APIs for querying and persisting data. If you do know of any good options, let us know.

Implementations

wa-sqlite

wa-sqlite provides a WASM build of SQLite - both synchronous and Asyncify versions. It also provides a couple of examples, and various VFS implementations. Only the higher-performing persistent VFS implementations are compared here.

IDBBatchAtomicVFS

Persists versioned blocks of file data to IndexedDB. Can execute either in a worker process or the main page.

It uses batch-atomic write transactions to get very good write performance, despite the additional IndexedDB layer sitting between SQLite and the underlying file system.

Needs the Asyncify build. Restricted to a single transaction at a time by default, but can be configured to support concurrent read transactions.

This VFS can be configured with [.inline-code-snippet]durability: 'relaxed'[.inline-code-snippet] to reduce overhead per write transaction, similar to [.inline-code-snippet]PRAGMA synchronous = OFF[.inline-code-snippet], but without risking database corruption.

OriginPrivateFileSystemVFS

Persists files directly as files in OPFS. Uses asynchronous open and read operations, and synchronous access handles for write operations.

Restricted to a single transaction at a time by default, but can be configured to support concurrent read transactions. However, there are potential issues when writing from different connections.

This VFS has file system transparency, making it compatible with sqlite-wasm.

Needs the Asyncify build.

AccessHandlePoolVFS

This implementation pre-opens a number of files, so that it can be accessed synchronously in the VFS, making this work without Asyncify. This means there is a pre-configured limit on the number of databases that can be opened without re-instantiating the connection, but that should not be an issue for most applications.

The persisted files use auto-generated names, each with a header containing the original filename. This makes the storage format incompatible with other implementations. There are some ideas for getting file system transparency in the future.

Since a sync access handle locks a file exclusively, concurrent transactions are not possible. And more than that - only a single connection can be opened to a file at a time. This means that accessing the same database from multiple tabs needs additional coordination, by opening MessageChannels to a single worker process. Since the worker is associated with a single tab, some care is required to spawn a new worker when one tab closes. Issues may also arise if a page and associated worker is closed in the middle of a transaction - the application will need to be able to handle transactions failing.

In the future, when concurrent access to OPFS sync access handles is supported by browsers, better concurrency would be possible.

sqlite-wasm

SQLite now has an official WASM build. Only a synchronous build is supported.

opfs

The OPFS-based VFS uses sync access handles for read and write operations. Multiple connections can be open concurrently, but only a single read or write transaction can be open at a time.

Additionally, since opening a handle is an asynchronous operation, the SharedArrayBuffer + Atomics workaround is used to make this synchronous. This means COOP and COEP headers are required.

In the future, when concurrent access to OPFS sync access handles is supported by browsers, better concurrency would be possible.

This VFS does have file system transparency, making it interoperable with wa-sqlite’s OPFS VFS.

There is also another option using Emscripten’s WASMFS. It uses similar mechanisms, but has additional restrictions, so I would not recommend it over the OPFS VFS.

opfs-sahpool

This is an alternative implementation that is still work in progress at the time of writing. It uses the same ideas as wa-sqlite’s AccessHandlePoolVFS, avoiding the need for the SharedArrayBuffer + Atomics workaround, and getting much better performance. This VFS requires an exclusive database lock, and only a single connection can be open at a time.

It is available since SQLite 3.43.

absurd-sql

This project uses a synchronous SQLite build, with the SharedArrayBuffer + Atomics workaround to expose the IndexedDB operations as a synchronous VFS.

Summary Table

‍(1) Supported versions of browsers haven’t been tested as part of this post. It may be inaccurate, especially for the older version ranges.

(2) Concurrent connections means multiple connections can be open at the same time, but only a single transaction can be active at a time. Concurrent reads means that multiple read transactions can be open at the same time, as long as no write connection is open. May require additional configuration and application-level locking.

Recommendations

Right now, I’d (still) recommend wa-sqlite’s IDBBatchAtomicVFS for most use cases - it has good performance, supports concurrent reads, has wide browser support, and is a fairly mature implementation.

If supporting older browser versions is not required and performance is critical, wa-sqlite’s current AccessHandlePoolVFS is a good option. The lack of concurrency could be an issue for some use cases, but the increased performance makes up for it. SQLite’s opfs-sahpool should have similar advantages.

The SQLite WASM - OPFS build is also an option with good performance and with file system transparency. I would not recommend it over the wa-sqlite builds yet due to the restrictions around COOP and COEP headers, unless file system transparency is an important requirement.

When stable, the new wa-sqlite AccessHandlePoolVFS could be a great option, especially on Chrome where readwrite-unsafe is supported.

Acknowledgements

A special thanks to Roy Hashimoto, the author of wa-sqlite, for providing many corrections and additional details for this post. Thanks also to Stephan Beal for reaching out about clarifications and updates.

Changelog

2024-02-14

Updated the post to better cover the difference between support for concurrent connections, concurrent transactions, or no concurrency.

2024-02-08

Added a section summarizing major new developments in the landscape. Updated the summary table with newly available implementations.

2023-07-19

Initial publication date.

‍