Compile SQLite to WebAssembly

2020-11-08

I'm working on a custom file format where SQLite fits the bill. It needs to be read by a web application, and that's super easy with sql.js, a version of SQLite compiled to WebAssembly. SQLite have a few extensions I'd like to use that's not in the distributed sql.js wasm file, so here's the steps to compile your own version of SQLite to WebAssembly.

Start by installing a fresh Ubuntu 20.04 or later in your preferred hypervisor or copy and paste a script to set up Ubuntu 20.04 on WSL. Emscripten installs Node globally and writes a bunch of text to the console every time you login afterwards, so you probably don't want to run this in an OS you use for other things.

Requirements

sudo apt-get install cmake default-jre git-core unzip

Install Emscripten

git clone https://github.com/emscripten-core/emsdk.git
cd emsdk
./emsdk install latest
./emsdk activate latest
source ./emsdk_env.sh

Compile sql.js

git clone https://github.com/sql-js/sql.js.git
cd sql.js
npm run rebuild

Let's see what flags sql.js is compiled with by default by investigating sql.js/Makefile

Flag	Description
O2	Optimization flag. Though O3 applies all optimizations, I got a slightly bigger wasm file when using O3 (only a few kilobytes though). More details here.
SQLITE_OMIT_LOAD_EXTENSION	Omits the entire extension loading mechanism from SQLite. Additional extensions can still be compiled into the binary, I think they just can't be loaded dynamically.
SQLITE_DISABLE_LFS	Disable Large File Support. This seems related to a kernel change in Linux regarding support for files greater than 2GB on 32-bit systems. Not supporting SQLite files greater than 2GB on web seems fair.
SQLITE_ENABLE_FTS3	Enable an old full-text search extension. You should remove this.
SQLITE_ENABLE_FTS3_PARENTHESIS	Adds support for operators AND and NOT as well as nested parenthesis in an extension you're not going to use. You should remove this.
SQLITE_THREADSAFE=0	Removes thread-safety logic. This flag makes sense because JavaScript is single-threaded.
SQLITE_ENABLE_NORMALIZE	This is an undocumented flag, but it enables the function sqlite3_normalized_sql. It's purpose seems to be about making it easier to write prepared statements. Read more.

I hope to write an article later about the extensions I'd like to use: JSON1, FTS5 and Spellcheck1. They're a great match between simple and powerful, and I believe they can be quite useful in a web application. When removing FTS3 from the build, the additional extensions only add 100 kb to the .wasm-file (before gzip).