NOTE: This post has been superseded by the newer post: The Current State of SQLite Persistence on the Web
Can SQLite be used for the web?
Yes, however, SQLite web support has many caveats and there are many issues without good solutions at the moment.
Some implementations of SQLite for web are:
- Official SQLite WASM build — first official SQLite subproject using WebAssembly (aka WASM).
- wa-sqlite — first public project to use OPFS as the storage layer for sqlite3 (see Option 2 below). It also demonstrated how to use OPFS APIs.
- absurd-sql — older project that demonstrates persistent browser-side sqlite3 by storing the databases in IndexedDB storage (see Option 1 below).
What are the caveats for using SQLite for the web?
The SQLite for web caveats are primarily related to the storage layer. There are a couple of options, and each of them has different caveats.
Storage Layer Caveats
Depending on the implementation, one of two storage layer options could be used:
Option 1: IndexedDB
IndexedDB has fairly good cross-browser support, but using it for SQLite is essentially a big hack. You're basically storing a database (SQLite) in a database (IndexedDB) implemented on another database (SQLite or LevelDB, depending on the browser). This is not necessarily a relevant concern in practice, but it's arguably a convoluted architecture.
Option 2: OPFS (Origin Private File System)
OPFS (Origin Private File System) is a very new API (only recently supported in Firefox and Safari) that was designed to allow web apps to get the benefits of working with files, but without the restrictions and performance costs of traditional web-based file processing [1]. It is still rapidly evolving, and actually doesn't yet contain all the APIs to implement SQLite storage properly.
Both of these storage layer options have the additional issue of synchronous vs asynchronous APIs. SQLite needs synchronous filesystem access, while traditional web APIs like IndexedDB only offer asynchronous access.
Workarounds for achieving synchronous filesystem access
There are broadly three options here:
(A) Use Asyncify
Asyncify is a tool that transforms the entire built SQLite library into one that works with asynchronous methods. This works for both IndexedDB and OPFS. That solves the problem, but (a) adds significant overhead, both in download size and in performance and (b) fundamentally transforms the code in a way that makes it less predictable (i.e. it could introduce bugs in some edge cases).
(B) Web worker sequencing
A combination of web workers, SharedArrayBuffer and Atomics APIs can be used to allow one web worker to wait for another — effectively converting an asynchronous API into a synchronous one. This is also a massive hack, and may add significant performance overhead.
(C) Use synchronous APIs offered by OPFS
Unfortunately, this option doesn't cover all the APIs, and on Firefox and Safari even fewer APIs are supported in this way. This means that workaround (A) or (B) above is still needed. Some of these APIs also lock the entire file, which means only a single tab can access the database at a time.
Current approaches used in practice
The following approaches are currently used in practice:
- Official SQLite WASM build uses (C) combined with (B) above i.e. OPFS synchronous APIs together with SharedArrayBuffer/Atomics. It means it works decently on Chrome, but no concurrent access to the database works (not even reading from multiple tabs at the same time).
- wa-sqlite primarily uses (A) above, supporting either IndexedDB or OPFS. Concurrency is not a well-explored topic here, and may or may not work.
In addition to the concerns mentioned above, another caveat is that Write-Ahead Logging (WAL) is not supported by any implementation yet. This also limits concurrency, even if the other issues are solved.
Summary
In the current landscape, there is no implementation of SQLite for web that can be used without important caveats. However, progress is being made and the official WebAssembly SQLite project using OPFS could be the future of offline-first web app development.
