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
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…
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.