Yes – I'm using JSON_extract and generated virtual columns https://www.sqlite.org/json1.html#jex
Edit: the database is stored in a sqlite.db file in the cwd
It looks like your tables have a single value column and a id generated column that extracts $.id from that value:
CREATE TABLE IF NOT EXISTS ${collection} (
value TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(value, "$.id")) VIRTUAL NOT NULL
)
GENERATED ALWAYS AS was added in a relatively recent SQLite version - 2020-01-22 (3.31.0) - do you have a feel for how likely it is for Node.js users to be stuck on an older version? I've had a lot of concern about Python users who are on a stale SQLite for my own projects.
Often sqlite libraries just bundle SQLite instead of relying on the system one, better-sqlite3 does that just fine and has provisions for building against a custom SQLite version if you really need it.
Yup! I actually don't know about that. I figured this would be used for setting up newer, server-side Remix or Next projects rather than more dated ones. I could imagine that by keeping sqlite and sqlite3 up to date, people might also not be stuck on more dated versions of SQLite. The tracing/profiling functions were also only implemented recently in sqlite3 (node to C interface), in 2023.
It looks like your tables have a single value column and a id generated column that extracts $.id from that value:
GENERATED ALWAYS AS was added in a relatively recent SQLite version - 2020-01-22 (3.31.0) - do you have a feel for how likely it is for Node.js users to be stuck on an older version? I've had a lot of concern about Python users who are on a stale SQLite for my own projects.