sqlite in a plugin

[anderspitman]

I'm trying to get sqlite running in a rust-pdk plugin. I tried rusqlite but it fails to link. I'm assuming this is because it's trying to link the default C sqlite library.

I'm guessing what I need is something like https://github.com/ncruces/go-sqlite3 but for Rust instead of Go. Or maybe I could leverage the official sqlite3 wasm build (https://sqlite.org/wasm/doc/trunk/index.md), or eventually a native Rust implementation of sqlite.

Figured I would check here if there's any prior art before going too deep.

[nilslice]

yes, the rust bindings don't easily compile to wasm -- but I'm curious if you could instead add some host functions to query a sqlite that runs in your host?

is there a requirement that each plugin holds the sqlite code too?

[anderspitman]

So that's actually what I'm currently doing. The host provides a set of KV functions to the plugins. The problem is I now need to implement KV functionality for every host (I'm targeting at least Golang/JS/Rust) and every KV storage backend. I'm thinking instead maybe my plugin can provide KV functionality to the host, which would reduce the number of implementations necessary significantly. Obviously storage backend support will be limited to filesystem-based (or I guess Extism HTTP as well) solutions until wasip2 is more common, but in practice I really only need memory, sqlite, and maybe flat file for most of my use cases.

[anderspitman]

@ncruces any thoughts?

[ncruces]

I don't think ncruces/go-sqlite3 is what you're after (it's pretty tied to wazero), and neither are the official Wasm builds (which are meant for browser use).

I dunno much about Rust, tbh, so I can't really help much. But in theory, the amalgamation should build for WASI using wasi-sdk. No idea how you'd link that with Rust.

Accessing actual database files that way is dangerous, if you're not 100% sure there's no concurrency, but memory databases are fine.

[anderspitman]

Thanks, that's helpful.

Why is that dangerous? Does WASI not provide the same filesystem locking guarantees as native?

[ncruces]

WASI does not support file locking at all. The SQLite implementation transparently uses unix-dotfile instead, which is safe, as long as you don't access the same database with some other SQLite library (like the sqlite3 CLI, sqlite3_rsync, Litestream, etc) at the same time.

[anderspitman]

Why does WASI force unix-dotfile? At the end of the day isn't the WASI host just a process that can get a lock on the file like any other process, and thus work fine with things like Litestream?

[ncruces]

Because there's no WASI "system call" (or "host call") for file locks: WASI does not support file locking at all.

[anderspitman]

Ahh of course. I was thinking about it backwards, wondering why other processes would be able to steal the lock. The problem is WASI can't request a lock in the first place. Thanks!