SQLite a.k.a. SQLite3

(Specifically, this page is about my role in the sqlite ecosystem, not about how to use sqlite or some such.)

sqlite3 is the world's most widely-deployed database engine and is widely believed to contend for first place as the single most widely-deployed piece of software in the world (zlib being the other most likely candidate).

Since 2008 i have had the pleasure of working with sqlite's creator, Richard Hipp, via his Fossil SCM project and, in recent years, in much more limited capacities on other pet projects of his such as the althttpd web server and the pikchr "declarative diagram description" tool (for lack of a better description). (Althttpd is, not coincidentally, the web server which runs all of the wanderinghorse.net websites.) More than a quarter of my life has been spent coding in and around that community.

In late 2021 i was "drafted" into the sqlite team, primarily in an administrative support role, e.g. assisting in administration of its public support forum in order to free Richard up from some of that workload. (Though i'm quite proficient in C, the relational algebra in sqlite's core code is well above my proverbial paygrade, and i've never had any delusions/aspirations of working on the sqlite internals.)

In mid-2022 the project began to investigate the feasibility of using WebAssembly, in combination with with the (then-)up-and-coming Origin-Private FileSystem web storage technology, to bring persistent sqlite3 databases to the browsers. (A small number of bleeding edge folks had already been using sqlite3 via WASM for at least 3 years, but without any sort of persistence.) Since WebAssembly is closely associated with JavaScript, and i'm the Fossil SCM's de facto "JavaScript Guy," that new effort fell neatly into my lap with no objection on my part. Indeed, that's the type of thing i do for fun, so welcomed it.

Our first proof-of-concept was sqlite3 fiddle, an HTML interface for the sqlite3 shell app (which is implemented in C), all run in the browser (as opposed to having the database running on the server). The very first iteration of it took roughly half an hour to get running thanks to the deep voodoo of the Emscripten tools, and about 25 minutes of that time was obtaining, installing, and learning how to invoke Emscripten. The ease with which that could be created convinced us that WASM was definitely something we wanted to play more with.

Going in, we'd all heard the word "WASM" but none of us had ever investigated WASM. Within only a few weeks of getting our feet wet in with it, we were convinced that having sqlite3 run browser-side, especially once such databases can be made persistent, will open up a floodgate of new opportunities for web app features and catapult sqlite usage into niches which have historically been off limits to it.

The best part is that the core library requires zero code changes for this: the C code compiles flawlessly to WASM and the friction between its interface and JavaScript is minimal, in particular once one has a bit of JS/WASM "glue code" packed up for reuse.

In October 2022 sqlite.org/wasm went online and in November 2022 sqlite 3.40 became the first release to include the JS/WASM components. The technical goals have been achieved and more than 100 pages of docs have been written, but the work is far from "done" (insofar as maintained software is ever "done"). We're now at the stage where we can start to collect and address beta-testing user feedback. It's long been my experience that end users often come up with the best ideas for new features and improvements.

In January 2023, Google posted about the arrival of SQLite's OPFS support. We were not the first to do so, by the way: to the very best of my knowledge, Roy Hashimoto's wa-sqlite project was the first such implementation (and is admittedly much more geared towards modern JS developers' expectations of how JS should be written and published, whereas we prefer to produce lower-level components with as little "voodoo" as possible between the C code, the JS code, and the user). Google's excitement about it is because this support is now part of the "official" releases of SQLite, raising the profile of OPFS somewhat and providing web developers with a truly compelling use for it. (To be clear, we had frequent contact with folks from Google's Chrome and Emscripten teams during the development, and the OPFS integration came as no suprise to them.)

As of this writing, August 2023, SQLite in the browser is slowly picking up steam. We'll see what happens...

Meanwhile, in July 2023 we started implementing a Java Native Interface (JNI) binding to the core library. Like the JavaScript/WASM bindings, that subproject fell neatly into my lap and is the 8th or 9th non-C/C++ binding i've done for sqlite3. That extension is now feature-complete, but whether or not it will ever amount to much is a mystery, especially now that JDK24 actively aims to dissuade folks from using JNI.