Evaluate the SQLite driver: node-sqlite3 is now unmaintained upstream

[rmyndharis]

Context

node-sqlite3 — the driver behind the default SQLite backend, and behind the always-SQLite main connection that stores auth and audit data — is now formally marked unmaintained upstream. The README carries a deprecation banner, and 6.0.1 (2026-03-12) is plausibly the last release the package will receive.

This is not urgent and nothing is broken. #846 moved us to 6.0.1, which resolved the node-tar advisory chain and left the tree with zero known advisories. The concern is forward-looking: with the package unmaintained, the next advisory in that dependency chain has no upstream fix path, and we would be choosing between an overrides pin and a driver migration under time pressure.

Three specifics worth recording while they are fresh:

• The bundled SQLite is 3.52.0, which upstream withdrew, folding its features into 3.53.0. The pin-back PR in node-sqlite3 (#1858) is open and unlikely to merge. This is a hygiene issue rather than a correctness one for us: the bundled wal.c is byte-identical to fixed 3.51.3 and carries the WAL-reset guard, and we never set journal_mode=WAL, so we run the default rollback journal regardless.
• prebuild-install, the install path, is itself deprecated. Prebuilt binaries currently exist for all six platform/arch targets including musl, so installs need no toolchain — but that is a snapshot, not a guarantee.
• npm RFC chore(deps): bump the major group across 1 directory with 3 updates #868 would block package install scripts by default. It is warn-only today (npm warn allow-scripts … sqlite3@6.0.1), and npm 12 does not document a hard block. If it ever becomes one, npm install would leave sqlite3 without a native binary. Worth watching for CI and Docker.

What to evaluate

• better-sqlite3 — actively maintained, synchronous API, widely used. Needs a check against TypeORM's driver support.
• node:sqlite — built into Node, no native install step at all. Needs a check on whether TypeORM can drive it, and on our Node floor (currently >=22.12).
• Staying put — legitimate if the dependency chain stays quiet. The cost is reactive rather than planned migration.

Constraints for whoever picks this up

• SQLite is not only the dev default: the main connection (auth + audit) is always SQLite, on every deployment including PostgreSQL ones.
• FTS5 is load-bearing — the AddMessagesFts migration and the cross-session message search depend on it. Any replacement must compile it in.
• The 19 existing migrations must apply unchanged, and PostgreSQL deployments must be unaffected.
• Prebuilt binaries for linux amd64/arm64 and musl are effectively required; needing a C++ toolchain to install would be a real regression for self-hosters.

No action needed for the current release.

[rmyndharis]

Status update — several premises above have shifted since this was written, all in the direction of "act sooner":

Corrections to the issue body

• "chore(deps): clear the dependency advisories and stop them masking lint #846 moved us to 6.0.1" — no longer true. The 6.0.1 prebuilds are linked against glibc 2.38, which broke the node:22-slim (glibc 2.36) production image on both architectures, so we rolled back to sqlite3@5.1.7 with overrides pinning node-gyp/tar forward (v0.10.4). That also makes the 3.52.0 / wal.c analysis moot: what we actually run today is the SQLite 3.44.2 bundled in 5.1.7 (November 2023).
• "unmaintained upstream" — stronger now: TryGhost/node-sqlite3 was archived (read-only) on 2026-07-01. PR #1858 can no longer be merged by anyone. No successor or handover was announced.
• npm RFC chore(deps): bump the major group across 1 directory with 3 updates #868 — no longer hypothetical. The RFC merged 2026-06-08; npm 11.16 shipped the warn-only phase, and npm 12.0.0 (2026-07-08) skips unapproved install scripts by default. A skip is enough to produce exactly the failure this issue feared: the install "succeeds", no binary is fetched, and require('sqlite3') fails at runtime. Not an immediate problem for our Docker images (Node 22 bundles npm 10/11), but any environment on npm 12 hits it.

Answers to the evaluation items

• better-sqlite3 — viable, and the evaluation is done. TypeORM has shipped the driver since 0.2.26 (type: 'better-sqlite3'); our 0.3.31 caps the peer range at ^12, so the target is 12.x (12.11.1 today). FTS5 is compiled in (SQLITE_ENABLE_FTS5 in its default build flags) — the AddMessagesFts migration and the built-in search run unchanged against it. Its 12.x linux prebuilds require only glibc 2.29, verified to load on node:22-slim on both amd64 and arm64 — the exact failure mode that broke 6.0.1 does not apply. It bundles a current SQLite (3.53.x), which includes the FTS5 fixes (notably the 3.53.2 corruption fix) that the sqlite3 package line will never receive — no published sqlite3 version ever bundled ≥ 3.53.2, and with the repo archived none ever will.
• node:sqlite — blocked for now, on two independent grounds. TypeORM has no driver for it in any released version (typeorm#11164 is open with no implementation), and our Node floor is insufficient anyway: node:sqlite is flag-gated until 22.13, and Node's bundled SQLite only gained FTS5 in 22.16 — so >=22.12 admits four minor versions where it cannot work. Worth revisiting if TypeORM grows a driver and we raise the floor to ≥ 22.16; the payoff (no native binary, immune to install-script policy) is real.
• Staying put — weaker than described above, given the corrections: it now means SQLite 3.44.2 indefinitely, with the post-3.44.2 FTS5 fixes out of reach and the overrides doing loadbearing work against an archived upstream.

One more data point: TypeORM 1.x dropped the sqlite3 package entirely and defaults to better-sqlite3 (typeorm#11836), with 0.3.x now on the legacy dist-tag — so this migration is also a prerequisite for any future TypeORM upgrade.

Decision: migrating to better-sqlite3@^12. PR: #863.

[rmyndharis]

Resolved by #863: the gateway now runs on better-sqlite3@^12 (current SQLite 3.53.x, FTS5 included) for both the main and data connections, with the DATABASE_TYPE contract unchanged. The node:sqlite option remains documented above for a future revisit — it needs a TypeORM driver and a Node floor of >=22.16 (FTS5) before it becomes viable.