Where to place SQLite database and .db file, in TidGi/TWDesktop/Tiddloid/Quine/TiddlyStow, or as a plugin?

[linonetwo]

Problem

sqlite binary size

SQLite nodejs binding is 23M+ (Compared to tiddlywiki core + all core plugins = 19.7M). And every wiki will have a copy of this plugin (unless it becomes part of the core? This is not very possible, because it relies on 1 or 2 npm packages and their deps and deps of deps).

sqlite .db file size

My wiki is 40M, so sqlite's .db file will be at least 40M+. I won't want this database file sync to Tiddlyhost or GitHub.

Possible Solution

1. I have already integrated sqlite and sqlite-vss (vector search plugin for AI semantic search) into TidGi, it will be available under $tw.utils.Sqlite if nodejs wiki is running in TidGi. If other tw launcher apps want to do the same, I can point out the code lines, and give a hind.
2. .db file is not expected to be human readable, I consider it to be a cache that is only used by programs. So I put it in the appData folder (/Users/linonetwo/Library/Application Support/TidGi on MacOS), instead of in the wiki folder.

Seems I already mentioned this in #7302 (comment)

[Jermolene]

Hi @linonetwo my experiments in this area have focussed on the browser, not Node.js.

In terms of the size of the binaries, SQLite offers a distribution of the WASM build that is 782KB compressed:

https://sqlite.org/download.html

I downloaded that bundle, and then used a simple HTTP server in the root of the extracted ZIP and ran the following demo:

http://127.0.0.1:8080/demo-123.html

On my system, this demo used sqlite3.js, which weighs in at 369KB. So I think that's OK for an optional plugin.

[linonetwo]

That sounds ok.
Another problem is how to package wasm in a JSON plugin, and will it be possible to load wasm from it? Does this needs experiment?

Can you come up with the location to mount the sqlite instance? So sqlite provided by plugin or integrated in launchers can cooperate.
(maybe also need a interfere, since you menthoned lib might not have the same api as bettersqlite3 lib)

But I'm providing vector search in tidgi, this is not standard too...

[Jermolene]

I've seen code that compiles WASM code from a string, so I think we'll be OK just storing the wasm code in a tiddler of type application/wasm.

In the browser, SQLite stores data in a virtual file system that then has to be explicitly persisted if required. In general, I think of the SQLite database as a RAM-resident indexed cache of data whose canonical copy is stored in the file system as at present.