Support shared cache mode

[lrowe]

SQLite Shared-Cache Mode supports sharing in memory databases between threads in the same process. This is potentially interesting for sharing data between Workers. However It doesn't currently work with this WebAssembly sqlite for a number of reasons:

1. Shared WebAssembly.Memory needs to be setup while constructing the WebAssembly.Instance.

2. While the built wasm exports its memory, it does not appear to import it when supplied on the importObject as per: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/WebAssembly/Memory#creating_a_new_memory_object

3. WASI does not seem to support Worker-style threading currently.

• Threads WebAssembly/wasi-sdk#5 (comment)
• Multi-threading and Atomics WebAssembly/WASI#296 (comment)

4. The SQLite build configuration is single threaded and disables shared cache. https://github.com/dyedgreen/deno-sqlite/blob/master/build/Makefile#L34-L40

With the Deno sqlite3 FFI module it is possible to use shared cache as follows:

// sqlite3.ts
// wait for the worker to run first.
const worker: Worker = await new Promise((resolve, reject) => {
  const worker = new Worker(new URL("./sqlite3.worker.ts", import.meta.url), {
    type: "module",
    // @ts-ignore
    deno: { namespace: true },
  });
  worker.addEventListener("error", reject);
  worker.addEventListener(
    "message",
    (msg) => {
      worker.removeEventListener("error", reject);
      msg.data === "ready" ? resolve(worker) : reject(msg);
    },
    { once: true }
  );
});
console.log("main");
import * as constants from "https://deno.land/x/[email protected]/src/constants.ts";
import { Database } from "https://deno.land/x/[email protected]/mod.ts";
const db = new Database("file:memdb1?mode=memory&cache=shared", {
  flags:
    constants.SQLITE3_OPEN_URI |
    constants.SQLITE3_OPEN_CREATE |
    constants.SQLITE3_OPEN_READWRITE,
});
db.execute(`INSERT INTO kv (key, value) VALUES ('main', 'hello')`);
const result = db.queryArray(`select * from kv`);
console.log("main", { result });
worker.terminate();

// sqlite3.worker.ts
/// <reference lib="deno.worker" />
console.log("worker");
import * as constants from "https://deno.land/x/[email protected]/src/constants.ts";
import { Database } from "https://deno.land/x/[email protected]/mod.ts";
const db = new Database("file:memdb1?mode=memory&cache=shared", {
  flags:
    constants.SQLITE3_OPEN_URI |
    constants.SQLITE3_OPEN_CREATE |
    constants.SQLITE3_OPEN_READWRITE,
});
console.log("sqlite_version", db.queryArray(`select sqlite_version()`));
db.execute(`CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT)`);
db.execute(`INSERT INTO kv (key, value) VALUES ('worker', 'hello')`);
const result = db.queryArray(`select * from kv`);
console.log("worker", { result });
self.postMessage("ready");

% deno run --no-check --unstable --allow-ffi --allow-read --allow-net --allow-env sqlite3.ts
worker
sqlite_version [ [ "3.37.0" ] ]
worker { result: [ [ "worker", "hello" ] ] }
main
main { result: [ [ "worker", "hello" ], [ "main", "hello" ] ] }

[lrowe]

Failed attempt to configure shared memory for WebAssebly sqlite: https://gist.github.com/lrowe/70b28f6d0cecf833f0ec5202b3460911

[dyedgreen]

I think this does not fit well with the architecture of the project. The database instances are contained within their own WASM instances, since this allows them to be automatically collected by the garbage collector when DB objects go out of scope.

This is really nice for JS users, since you can rely on the garbage collector for in-memory databases, but won't mix well with having shared memory cache. (This is also a more secure setup, if you e.g. want to share multiple users databases in the same process, since the WASM binaries can't interact with each other.)