Proposal: sqlite3-wasm as presistent storage

[aidenlx]

File-based Persistence: IndexedDB appears to have hard metadata limits and does not work for large vaults (where caching is most desparately needed). An alternative multi-file database (of sorted JSON documents), like LowDB or LevelDB, will work better.

Check out the exciting news of high performance sqlite3 backed by OPFS from File System API:

https://sqlite.org/wasm/doc/trunk/index.md https://developer.chrome.com/blog/sqlite-wasm-in-the-browser-backed-by-the-origin-private-file-system/

Note the API required for sqlite3 to work, including SharedArrayBuffer, OPFS, is not yet available in Obsidian Mobile (backed by capactorjs)

Running example

After some testing, I've got sqlite3-wasm working in obsidian desktop (Installer v1.1.9), you can

1. download production build from https://sqlite.org/2022/sqlite-wasm-3400000.zip
2. replace the following code in sqlite3.js:

  const loadWasm = WebAssembly.instantiateStreaming
        ? async ()=>{
          return WebAssembly.instantiateStreaming(wfetch(), imports)
            .then((arg)=>onSuccess(arg.instance, arg.module));
        }
        : async ()=>{ 
          return wfetch()
            .then(response => response.arrayBuffer())
            .then(bytes => WebAssembly.instantiate(bytes, imports))
            .then((arg)=>onSuccess(arg.instance, arg.module));
        };

to

  const loadWasm = async ()=>{ 
          return require('fs/promises').readFile(PATH_TO_SQLITE3_WASM)
            .then(bytes => WebAssembly.instantiate(bytes, imports))
            .then((arg)=>onSuccess(arg.instance, arg.module));
        };

(replace variable PATH_TO_SQLITE3_WASM to actual file path) (use nodejs's fsPromises.readFile for convenience, you can replace it with valut.adapter.readBinary())

3. load sqlite3.js

   function loadScript(script) {
   let scriptElement = document.createElement('script');
   scriptElement.type = 'text/javascript';
   scriptElement.appendChild(document.createTextNode(script));
   document.body.appendChild(scriptElement);
   }
   loadScript(require('fs').readFileSync(PATH_TO_SQLITE3_JS, 'utf8'))

   (replace variable PATH_TO_SQLITE3_JS to actual file path)

4. copy paste demo1() function definition and run the function after initialization:

   log = console.log;
   warn = console.warn;
   demo1(await window.sqlite3InitModule());

[blacksmithgu]

I'd be happy to use sqlite3 as a metadata store but the lack of mobile support makes it an incomplete solution; am I stuck using IndexedDB on mobile?

[aidenlx]

To my knowledge, the reason why sqlite3-wasm not working on mobile is that underlying webview framework don't support enable file access API and SharedArrayBuffer required for sqlite3-wasm, which won't be fixed easily. And the file access API is necessary to solve issue that other in-memory database solution like LowDB and sqljs have, because without direct file access, they will have to load the entire database to memory on load and overwrite the database file on save, and this behavior may be too resource-consuming for large vaults.

the sqlite3-wasm solution is bulit on top of latest web standard so it should be future-proof. To work around the issues on mobile, one potential solution is to requset obsidian mobile support for sqlite3 capactor plugin, which provide support to connect to sqlite3 database using native code. Building a compat layer should allow sql statements to be reused between two environments.

PS: on desktop, the most high-performace solution for sqlite3 may be using node native module, which can also bypass restrictions that web environment may have but unnecessary for applications, and I've being using this solution to read Zotero's database in obsidian-zotero project already. The only downside is that some bootstrap code is needed to install binary for every architecture (wasm binary is cross-platform) and the plugin have to manage database file itself (database file of sqlite3-wasm is stored in an isolated directory of Origin Private File System)

[jp-cen]

Make.md uses sql.js and it works fine on mobile. You can just copy it.

[jp-cen]

10,000 vault sizes are not an issue for the indexer if you load/save the entire db into mem based on user reports. Aiden is right that there are limits to in memory since the max heap that can be allocated is ~200mb but with proper mem management, 15,000 vault kept around 120mb total while the indexer is entirely in mem. The other solutions mentioned are great but years out from being nearly ready. There are alternative solutions if you keep desktop only as mentioned.

[jp-cen]

I haven't tested the SQLite3-wasm that they released, the api is not as rich as sqljs but based on benchmarks it's more performant. Aiden if you want to get it to work for a plug-in you need to package the wasm as a base64 encoded binary in the main.je

[aidenlx]

Good news: possibility to support sqlite3-wasm in mobile with newer webviews released after March 2023, since SharedArrayBuffer that blocks mobile support is no longer required:

[OPFS SyncAccessHandle Pool VFS](https://sqlite.org/wasm/doc/trunk/persistence.md#:~:text=option%20is%20used.-,OPFS%20SyncAccessHandle%20Pool%20VFS,-(Added%20in%20SQLite)

[jonathan-s]

I was about to say that there are approaches like the following too > https://github.com/jlongster/absurd-sql 😄