Awesome

Building a WASM sqlite with an embedded extension

I wanted to build a wasm version of sqlite, but I also wanted to include an extension in it.

As far as I know, you can't dynamically load extensions into the wasm version of sqlite, so I wanted to build a version that had it statically linked in.

As I don't know C very well, this seemed pretty hopeless - but thankfully I found sqlite-lines by @asg017 (who also kindly helped me on twitter), which demonstrated that it was possible.

• The pieces
• The idea
• The steps
• Build it yourself
• SQLite fiddle with extension

update (Nov 11 2024): there are official instructions for adding an extension to the official WASM build (this uses sql.js instead, as the official wasm build did not exist at the time of its writing). I have not yet tried them out

The pieces

To build it, I followed in Alex's footsteps and used these pieces:

• Emscripten
• The sqlite amalgamation file and extension header file
  • I just copied the versions Alex used
• The stats file from sqlean
• The sqlite wasm API from sql.js
• The Makefile and core-init.c file from sqlite-lines

The idea

The basic idea of building a sqlite that has an extension statically linked is that you can use an undocumented C preprocessor macro called SQLITE_EXTRA_INIT which will, if defined, load extra code into sqlite after finishing up its own initialization. If you point SQLITE_EXTRA_INIT at a function that calls sqlite3_auto_extension with a pointer to your extension's init function, it will load right after sqlite loads itself and the functions defined within will be available to sqlite.

The steps

To make the idea happen, this code:

• sets SQLITE_EXTRA_INIT here, pointing to the core_init function
• defines the core_init function here
• makes core_init available to sqlite by appending it to the end of the amalgamation file as the first step of our build process
• uses emscripten to build sqlite3-stats.c and the amalgamation file (with the appended SQLITE_EXTRA_INIT bit) together into a wasm output

Finally, I made an HTML page that makes the most basic possible use of the sqlite binary to make sure that the stats extension has been loaded.

Build it yourself

make clean wasm

To visit the web page that demonstrates how to use the file, serve the dist directory with a web server that serves proper mime types. I like to use devd, so I run devd -ol dist

SQLite fiddle with extension

I used the knowledge I gained in this effort to a SQLite fiddle with an embedded extension. Instructions for building the fiddle can be found in the build-fiddle branch.