Feature: expose sqlite3_interrupt() to allow cancellation of in-flight queries

[benhoweblog-dot]

Use case

We have a React Native app (Pangea, a HealthKit-backed health & nutrition app) where long-running aggregation queries can hang under specific data shapes. When a query hangs:

• The JS-side timeout fires (we use a SyncOrchestrator with per-job timeouts)
• The orchestrator marks the job "timed out"
• BUT the underlying op-sqlite query continues running, holding the writer mutex
• All subsequent queries pile up behind the stuck writer
• The cascade only resolves when the user kills the app — and even then, persisted job state can carry the wedged condition into the next session

We need a way to actually stop a hung in-flight query from the JS side. SQLite provides this primitive natively as sqlite3_interrupt():

https://sqlite.org/c3ref/interrupt.html

From the docs:

"This function causes any pending database operation to abort and return at its earliest opportunity. ... It is safe to call this routine from a thread different from the thread that is currently running the database operation."

The interrupted query returns SQLITE_INTERRUPT. Any in-flight transaction gets rolled back automatically per SQLite's contract.

Why this matters beyond our use case

This is a known gap across SQLite JS bindings in general — see also sql-js/sql.js#545 for the same request against sql.js. The native libraries in most other languages (Python's sqlite3, Rust's rusqlite, Go's mattn/go-sqlite3) all expose this primitive. The JS/RN ecosystem is the outlier.

Pangea is happy to test against an alpha implementation. We have a soak environment (a single power-user iOS device with multi-year HealthKit history) that consistently reproduces the hang condition, so we can validate that interrupt() actually stops the hang.

Alternative considered

We're shipping a JS-level watchdog as a workaround in our app — it tracks withWriteLock acquisitions and force-releases the JS-side mutex after a timeout. But this only frees the JS-side queue; the underlying SQLite work continues to consume resources until it naturally finishes (or until the app is killed). sqlite3_interrupt() is the canonical fix; the watchdog is belt-and-suspenders.

Happy to contribute the implementation if helpful — we'd just need direction on where in the native bindings layer to add it.

Proposed API

// On the database handle:
interface DBInstance {
  // ... existing methods
  interrupt(): void;  // calls sqlite3_interrupt() on the underlying connection
}

[ospfranco]

feel free to open a PR. Native functions live in DBHostObject.cpp. You will also need to modify the TypeScript wrapper in src/functions.ts, update the docs and add test(s) in queries.spec.ts.