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. 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.
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:
*As a side note: there is also the Web SQL project which has built-in support in Chrome and older versions of Safari. It had too many limitations and difficulties to be a cross-browser standard, and is being phased out. For that reason, we’re not exploring it any further in this post.
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.
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.
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:
Other options such as localStorage are too restrictive, so we won’t cover that here.
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:
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:
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.
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:
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.
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 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.
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:
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
Note: Unfortunately SharedWorker cannot be used directly for this, since OPFS sync access handles are not available in SharedWorker or ServiceWorker.
In the future, when concurrent access to OPFS sync access handles is supported by browsers, better concurrency would be possible.
SQLite now has an official WASM build. Only a synchronous build is supported.
The OPFS-based VFS uses sync access handles for read and write operations, meaning that only a single read or write transaction is supported 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.
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.
It may form part of the 3.43 release.
This project uses a synchronous SQLite build, with the SharedArrayBuffer + Atomics workaround to expose the IndexedDB operations as a synchronous VFS.
This project is not actively maintained, and only included as a reference.
(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) Requires additional configuration and application-level locking.
Right now, I’d 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 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. When released, 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.
Once multiple readers and writers for sync access handles are widely supported, AccessHandlePoolVFS and similar options could become even more attractive.
A special thanks to Roy Hashimoto, the author of wa-sqlite, for providing many corrections and additional details for this post.
