Allow defining user-defined functions and procedures in Wasm

[psarna]

SQLite allows creating application-defined SQL functions by implementing and registering them in C: https://www.sqlite.org/appfunc.html

With WebAssembly emerging as the default, secure, and fast way of implementing user-defined functions, we should consider making this interface as Wasm-friendly as possible.

The first iteration could simply extend the existing C API with helper functions, which facilitate creating new functions in Wasm.

Later, we should consider extending SQLite's dialect with CREATE FUNCTION, widely known in the SQL world. This is not strictly necessary, because all kinds of functions can be registered directly via the C API, but it might be much more developer-friendly to provide a way of creating functions from SQL level, without having to recompile the database. Also, not everyone is fluent in C.

Important note 1: runtime

WebAssembly programs need to be run on a virtual machine. Candidates (feel free to post more suggestions):

1. Wasmtime - my personal runtime of choice - I already have experience in using it, it's straightforward, fast, and quite feature-complete.

• Wasmtime comes with C/C++ bindings in the form of libwasmtime.a library, which is convenient for binding with C; using it is a good candidate for a proof-of-concept implementation, because it's just a single header and a library you link with.
• ... but in the long term we should consider implementing everything in Rust, Wasmtime's native language, and only then bind it to the existing C codebase; The original code has features that C bindings lack - the most important one being async support, but also some configuration options and tuning.
  Sample code using libwasmtime.a to register a hardcoded wasm function into sqlite: https://gist.github.com/psarna/93cdc1c2b4de36c98db8af5bc77b1351

2. Wasmer - haven't tried it yet, but it has gained considerable traction and is reportedly faster than Wasmtime. I'm pretty sure it lacks async support, but maybe we don't need it for the time being.

Important note 2: ABI

WebAssembly programs are usually not written by hand, but instead coded in other languages, like Rust, C++, AssemblyScript, etc. WebAssembly standard specifies a very concise list of supported types, so in order to be able to run Wasm-based user-defined functions in sqlite3, we need to be able to translate between sqlite's types and WebAssembly. The most dev-friendly way of doing so is by defining an ABI (e.g. here's one for C: https://github.com/WebAssembly/tool-conventions/blob/main/BasicCABI.md). We could, for instance, decide that each sqlite type should be passed in its serialized form (sqlite3_value). Then, people coding functions in WebAssembly should either deserialize and serialize by hand, or by using our helper library which automatically takes care of type translation. Customarily, this library for Rust language can be called bindgen, and for lack of better example, here's one from the crypto world: https://docs.near.org/sdk/rust/contract-structure/near-bindgen . Since sqlite types are very well defined, we could create a mini-SDK for implementing Wasm user-defined functions in Rust, by exposing a #[libsql_bindgen] macro that automatically sprinkles vanilla Rust code with type serialization/deserialization.

Given that a fair part of sqlite userbase is Web developers, we should also consider providing a mini SDK with helper functions for AssemblyScript - a language designed specifically for compiling it into WebAssembly and integrating the resulting code with TypeScript/javascript.

[psarna]

Ref:

• Implement libsql_bindgen in Rust for interoperability between libSQL and WebAssembly #15
• Define libSQL<->Wasm ABI for type translation #16
• Implement a WebAssembly runner function #17
• Implement CREATE FUNCTION for dynamic user-defined function creation #18

[tantaman]

Seems related to your goals -- https://github.com/mycelial/sqlite_wasm