capacitor-community / capacitor-community/sqlite

Web: replacing the jeep-sqlite engine with @sqlite.org/sqlite-wasm + OPFS (complete implementation with tests and a data migration; offering it upstream)

Open
#693 0 comments 7 reactions 0 assignees View on GitHub
Dominant language
Swift
Stars
661
Forks
158
PR merge metrics
No merged PRs in 30d

Description

**Summary.** The web implementation forwards every call to `jeep-sqlite` (sql.js in-memory, whole-database images in a localforage IndexedDB store). jeep-sqlite's last publish is 2.8.0 (August 2024), the `^2.7.2` pin here has not moved since June 2024, and the web layer has no test coverage in CI. The official `@sqlite.org/sqlite-wasm` now offers an OPFS VFS (`opfs-sahpool`) that needs no COOP/COEP headers and no SharedArrayBuffer, which makes a proper, durable web engine possible for exactly the deployments this plugin serves.

We maintain a Capacitor reading app that depends on this plugin, and we have built and are running a complete replacement of the web implementation. We would like to contribute it.

**What it is.**

- All SQL runs in a dedicated worker on `@sqlite.org/sqlite-wasm`. Databases are durable OPFS files via `opfs-sahpool`: no COOP/COEP, no SharedArrayBuffer, works on ordinary hosting.
- Automatic fallback tier for browsers without OPFS sync access handles: the same engine with whole-image IndexedDB persistence (jeep's durability model), so FTS5 and behavior are identical on both tiers. Floors are measured and documented, including the syntax floor below which the wasm bundle cannot parse at all.
- A one-time, transparent migration of existing users' data out of the jeep store: raw IndexedDB read, `PRAGMA integrity_check` on every database, at-most-once by design, never overwrites, tolerates jeep's `backup-*` keys and never-saved placeholders. Interrupted migrations cannot corrupt anything.
- Full method parity for all 36 web-supported methods and all five web events, driven through the unmodified `SQLiteConnection`/`SQLiteDBConnection` wrappers. Read-only connections become real on web. `definitions.ts` signatures are untouched, so there is zero impact on iOS, Android or Electron.
- 259 tests in vitest browser mode (real Chromium) parameterized over both tiers, wired into the existing CI build.
- The worker ships as a classic script, so Firefox 111-113 (OPFS but no module workers) still works. Docs are swept: no element to define, no extra packages, no `sql-wasm.wasm` copy step.

**Defects found in the current lineage while porting** (fixed on our branch; we are happy to file the pieces that affect other platforms as standalone issues): the soft-delete rewrite performs a REAL delete when the table name is double-quoted; an unbracketed WHERE lets `AND` bind over `OR` and re-marks already-deleted rows; `execute()` bypasses soft-delete entirely; foreign-key enforcement was never enabled on web (`PRAGMA foreign_keys` defaults OFF), so declared constraints were silently ignored; and the foreign-key cascade logic misses referencing tables and deeper levels (that one also affects Electron).

**The one breaking change**, clearly marked in the branch as `fix(web)!`: foreign keys are now enforced on web connections. Apps whose data violates their declared schema will begin receiving constraint errors. We flag it for your versioning call.

**What we ask.** Is there interest in a PR? The branch is [`kklem0/sqlite:feat/web-sqlite-wasm`](https://github.com/kklem0/sqlite/tree/feat/web-sqlite-wasm), a reviewed series of conventional commits that each build. We are happy to split or reshape it however review works best for you. In the meantime we are publishing the fork as `sssf-capacitor-sqlite` so our app can ship, with the explicit goal of retiring it into upstream.

Contributor guide

Open the contributing guide

Research direction

Start with the feat/web-sqlite-wasm branch and review the web implementation alongside definitions.ts. Run the 259 Vitest browser tests in real Chromium for both persistence tiers, then inspect the migration and existing CI wiring. Done means method and event parity, migration safety, documented browser floors, and passing tests without changes to the native wrappers.

Written by the indexing model from the issue text.

Assessment

Tech stack
sqlite, typescript, wasm
Domain
database, web-dev
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.