Compiling a sqlite extension for wasm and ffi

[rodydavis]

I am trying to follow the readme for custom_wasm_build and it has a link that 404 for the setup:
https://github.com/simolus3/sqlite3.dart/tree/rust-wasm-build/sqlite3#compiling

What is the best way to run this on linux or macos? or even docker?

I want to take a custom sqlite-vec extension and compile it for wasm, and then compile it for ios,android,macos,window,linux.

The alternative would be to include the sqlite-vec extension into the build process itself like flutter_sqlite_libs but was also having a hard time getting that working.

Do you have any good docs to look at?

[simolus3]

I have fixed the link, it should point to https://github.com/simolus3/sqlite3.dart/tree/main/sqlite3#compiling

What is the best way to run this on linux or macos? or even docker?

On Linux, I'm using

WASI_SYSROOT=/usr/share/wasi-sysroot/ CC=clang cargo build --target wasm32-wasi --release

I think the following should work on macOS once you have set up the dependencies:

WASI_SYSROOT=/opt/wasi-sysroot CC=/opt/homebrew/opt/llvm/bin/clang cargo build --target wasm32-wasi --release

Note that adding the sqlite-vec crate is not likely to work out of the box as the compilation process needs to be configured to explicitly target wasm, the default cc setup doesn't do that.

Given that sqlite-vec is a C library, it may be easier to adapt the existing CMakeLists we have for sqlite3 on the web by changing add_executable in the base_sqlite3_target macro to also include sqlite-vec.c.

and then compile it for ios,android,macos,window,linux.

Instead of customizing the sqlite3 build, it would probably be easier to compile sqlite3-vec as a loadable extension (make loadable in the sqlite-vec repository). Then you're responsible for opening the dynamic library in your app, but can load the extension (I'm doing this on Linux):

void main() {
  sqlite3.ensureExtensionLoaded(SqliteExtension.inLibrary(
      DynamicLibrary.open('/tmp/sqlite-vec/dist/vec0.so'), 'sqlite3_vec_init'));
  final db = sqlite3.openInMemory();
  print(db.select("select vec_f32('[.1, .2, .3, 4]');"));

[rodydavis]

Those flags are really helpful because I was thinking you needed the wasi sdk via cloning the repo and the version of clang was not working for me, but the LLVM might be the part I was missing for that in the path.

I was actually following the loadable extension route and got it working on MacOS as a DynamicLibrary and worked well. I was having issues including the DLL on windows but that is a separate flutter issue I am investigating.

sqlite-vec does support compiling to wasm but the docs differed and could not see how to include the custom dart helpers for vfs.

https://github.com/asg017/sqlite-vec/blob/main/examples/wasm/README.md

Maybe when I get this figured out we could add it as an example to take any c sqlite extension and compile it for all platforms including web.

Here is the current example I am trying to build:

https://github.com/rodydavis/flutter_sqlite_document_search/blob/main/lib/src/database/connection/native.dart

Works on MacOS with dynamic library but want to convert it to use and compile the c library for all platforms plus wasm.

There are not a lot of docs on this and really appreciate the reply.

[simolus3]

I've tried compiling sqlite-vec to WebAssembly in 1ea51fe (which already requires some hacks because sqlite-vec doesn't properly check for mutexes being omitted). When I've compiled it, the generated WebAssembly module required a WASI import (so probably something in sqlite-vec is using IO functions). That unfortunately means that it can't be loaded by package:sqlite3 without further modifications to remove the code that causes wasi imports to be added to the WASM module.

[rodydavis]

So does that mean it will only work for native platforms and not web?

I am assuming there is no way to use the sqlite-vec wasm module with sqlite3.dart

[asg017]

I've tried compiling sqlite-vec to WebAssembly in 1ea51fe (which already requires some hacks because sqlite-vec doesn't properly check for mutexes being omitted). When I've compiled it, the generated WebAssembly module required a WASI import (so probably something in sqlite-vec is using IO functions). That unfortunately means that it can't be loaded by package:sqlite3 without further modifications to remove the code that causes wasi imports to be added to the WASM module.

This isn't well documented but you can use -DSQLITE_VEC_OMIT_FS=1when compiling sqlite-vec to disable all the bits that use IO functions, which I put it when compiling it to WASM for a specific Go build. That should avoid all the WASI errors

Will also fix up the mutex compile errors

[simolus3]

I was already compiling with -DSQLITE_VEC_OMIT_FS=1. After a lot of searching and looking at linker outputs, the problem actually is the strtod call in fvec_from_value. When using the wasi libc, this pulls in a bunch of code also including references to fd_seek, fd_write and fd_close from the WASI specification. sqlite3 itself doesn't have this problem only because it handrolls everything. When running DCE, these are actually all removed but wasm-dce does not strip unused imports so they remain in the WebAssembly module and we then can't load it.

Maybe it's possible to rewrite the JSON parsing logic in fvec_from_value with a cached prepared statement calling json_each to extract values?

[asg017]

@simolus3 do you think you could try again with v0.1.2-alpha.5? I made some fixes with SQLITE_VEC_OMIT_FS that ensures stdio.h is never used when that compile time option is defined. I also fixed the mutex issues so you can remove those shims

I'd be very surprised to if had to do with strtod(). I also have a build of sqlite-vec that uses WASI for a custom WASM SQLite build in Go, so I'd be surprised if that WASI build works but this one fails

Happy to debug any linker error logs you come across, I'm having trouble running that branch myself

[simolus3]

Thanks for the help! The SQLite build for Go was a good pointer, I can't reproduce the wasi imports with that build script. One difference is that I'm using a standard clang installation with a wasi sysroot instead of a full wasi SDK, that may also be related to this. I think the problem is most likely coming from something I'm doing wrong in my CMake build, so I've just switched that to custom targets directly invoking clang as a driver.

@rodydavis, you can apply this patch to the current main branch to build a WebAssembly module with sqlite-vec (note that the debug build still includes the wasi imports, but at least they're now removed by DCE in release builds):

diff --git a/sqlite3/assets/wasm/CMakeLists.txt b/sqlite3/assets/wasm/CMakeLists.txt
index c557d78..2b6e10f 100644
--- a/sqlite3/assets/wasm/CMakeLists.txt
+++ b/sqlite3/assets/wasm/CMakeLists.txt
@@ -15,8 +15,14 @@ FetchContent_Declare(
     URL https://sqlite.org/2024/sqlite-autoconf-3460000.tar.gz
     DOWNLOAD_EXTRACT_TIMESTAMP NEW
 )
+FetchContent_Declare(
+  sqlite_vec
+  URL https://github.com/asg017/sqlite-vec/releases/download/v0.1.2-alpha.5/sqlite-vec-0.1.2-alpha.5-amalgamation.tar.gz
+  DOWNLOAD_EXTRACT_TIMESTAMP NEW
+)
 
 FetchContent_MakeAvailable(sqlite3)
+FetchContent_MakeAvailable(sqlite_vec)
 
 file(DOWNLOAD https://raw.githubusercontent.com/sqlite/sqlite/master/src/test_vfstrace.c "${CMAKE_BINARY_DIR}/vfstrace.c")
 
@@ -39,6 +45,7 @@ macro(base_sqlite3_target name debug)
     ${CMAKE_CURRENT_SOURCE_DIR}/os_web.c
     ${CMAKE_CURRENT_SOURCE_DIR}/helpers.c
     ${sqlite3_SOURCE_DIR}/sqlite3.c
+    ${sqlite_vec_SOURCE_DIR}/sqlite-vec.c
   )
   set(flags -Wall -Wextra -Wno-unused-parameter -Wno-unused-function)
 
@@ -54,8 +61,9 @@ macro(base_sqlite3_target name debug)
     COMMAND ${clang} --target=${triple} -std=c23
       ${flags}
       -o ${clang_output}
-      -I ${PROJECT_SOURCE_DIR} -I ${sqlite3_SOURCE_DIR}
+      -I ${PROJECT_SOURCE_DIR} -I ${sqlite3_SOURCE_DIR} -I ${sqlite_vec_SOURCE_DIR}
       -D_HAVE_SQLITE_CONFIG_H
+      -DSQLITE_VEC_OMIT_FS=1
       -mcpu=generic
       -mexec-model=reactor
       -fno-stack-protector -fno-stack-clash-protection
diff --git a/sqlite3/assets/wasm/os_web.c b/sqlite3/assets/wasm/os_web.c
index 9800443..fc5e490 100644
--- a/sqlite3/assets/wasm/os_web.c
+++ b/sqlite3/assets/wasm/os_web.c
@@ -4,7 +4,11 @@
 
 #include "bridge.h"
 #include "sqlite3.h"
+#include "sqlite-vec.h"
 
-int sqlite3_os_init(void) { return SQLITE_OK; }
+int sqlite3_os_init(void) {
+    sqlite3_auto_extension((void (*)(void))sqlite3_vec_init);
+    return SQLITE_OK;
+}
 
 int sqlite3_os_end(void) { return SQLITE_OK; }
diff --git a/sqlite3/example/web/main.dart b/sqlite3/example/web/main.dart
index f7bf0bf..e97248f 100644
--- a/sqlite3/example/web/main.dart
+++ b/sqlite3/example/web/main.dart
@@ -9,8 +9,7 @@ Future<void> main() async {
   startIndexedDb.onClick.listen((_) async {
     startIndexedDb.remove();
 
-    final sqlite3 =
-        await WasmSqlite3.loadFromUrl(Uri.parse('sqlite3.debug.wasm'));
+    final sqlite3 = await WasmSqlite3.loadFromUrl(Uri.parse('sqlite3.wasm'));
 
     print(sqlite3.version);
 
@@ -19,6 +18,8 @@ Future<void> main() async {
       makeDefault: true,
     );
 
+    print(sqlite3.openInMemory().select('SELECT vec_debug()'));
+
     sqlite3.open('/database')
       ..execute('pragma user_version = 1')
       ..execute('CREATE TABLE foo (bar INTEGER NOT NULL);')

I've also uploaded a prebuilt module here: https://storage.googleapis.com/simon-public-euw3/assets/sqlite3/wasm/2.4.6/sqlite3_vec.wasm

[rodydavis]

Thank you so much both!!!

[rodydavis]

Flutter example updated and works really well!
rodydavis/flutter_sqlite_document_search@631e7ed