Compiling Full Text Search (FTS5) Into sql.js (SQLite WASM Build)

As part of another side project, scraping traditional stories into various searchable databases, I figured I should probably start making the simple SQlite databases searchable over the web. I don’t really want to have to run a db server, but there is an in-browser WASM build of SQLite available, sql.js, that can be used to provide SQLite support directly within a web page and hence served from a simple web server.

Using the off-the-shelf build with my database tables fails because I make use of the FTS-5 extension (the sql.js release only bundles FTS3). So can I find a build with FTS-5 support, or build one myself?

Poking around the sql.js repo turns up a note in the CONTRIBUTING.md guide a note that you can add extensions, such as FTS5, by making a tweak the the Makefile and adding a -DSQLITE_ENABLE_FTS5 switch to the CFLAGS declaration.

Checking the repo, there is indeed a Makefile and a place to add the switch:

To set up the development enviroment, a VS Code containerised environment is provided. This can be activitated simply by opening the repo folder inside VS Code which then detects the .devcontainer/devcontainer.json and associated Dcokerfile (the build iteself took quite some time…). To avail yourself of this route, you need to have VS Code and Docker installed in advance.

One of the handy things I noted was that the file mount from the directory on my desktop into the container was handled automatically. I also noted in passing (I forget where) the ability to forward ports from inside the container. For the official docs on this sort of development, see for example the VS Code docs Developing inside a Container. I’m also wondering now whether this would be a useful way of distributing code environments to students…

The sql.js docs then suggest all you need to do is run npm run rebuild. This didn’t actually run the build properly for me at all; instead, I had to manually invoke make. But when I did, everything I needed seemed to build okay, the distribution packages appeared in the dist directory, and now I can run my full text FTS5 searches solely within the browser.

As a PS, having managed to create my own custom build so easily, I guess there’s no reason now not to compile in other extensions or perform custom builds… such as the sql.js-httpvfs variant which lets you make requests from a web page to remotely hosted (and potentially very large) sqlite database files (about: Hosting SQLite databases on Github Pages (or any static file hoster)). Various bits of third party guidance about how best to do that in a simple web page context are also starting to appear, as for example here and here.