@bingcoke/rxdb-storage-sqlite-json

SQLite JSON RxStorage for RxDB.

The storage stores documents as JSON in SQLite and translates MongoDB-style RxDB queries to SQLite JSON expressions. SQLite JSON does not support attachments.

Install

npm install rxdb @bingcoke/rxdb-storage-sqlite-json

Use

Provide a SQLiteBasics adapter for the SQLite implementation used by your application:

import { getRxStorageSQLiteJSON } from '@bingcoke/rxdb-storage-sqlite-json';

const storage = getRxStorageSQLiteJSON({
    sqliteBasics,
    databaseNamePrefix: './data/'
});

sqliteBasics must implement open, all, run, setPragma, and close. The adapter is kept separate so the same RxStorage can be used with different SQLite runtimes.

Supported queries

All operators of the RxDB query language are compiled to SQL, including nested combinations:

• Comparison: $eq, $gt, $gte, $lt, $lte, $ne
• Set: $in, $nin
• Existence/type/size: $exists, $type, $size
• Logic: $and, $or, $nor, $not (field-level and selector-level)
• Arrays: $elemMatch on object arrays, scalar arrays and nested arrays
• $regex / $options at any nesting depth (see Regex support)
• $mod with a valid [divisor, remainder] payload

Unsupported queries and fallback

A query falls back to in-memory filtering when it cannot be compiled to SQL. Results stay correct, but the SQL WHERE, LIMIT/OFFSET and fast count() are dropped, so the whole table is read and filtered in JavaScript. This is a performance issue, not a correctness issue.

Fallback happens only for:

1. $regex while regexSupport is disabled (the default).
2. $mod with an invalid payload (not [number, number] or divisor 0).
3. Invalid query shapes: $options without $regex, $not without an operator object, $and/$or/$nor without an array. These are not valid Mango/RxDB syntax — RxDB itself rejects them with an error, and this storage reports the same error instead of guessing a meaning.
4. Unknown operators that are not part of the RxDB query language.

When a fallback triggers, the storage logs a warning once per reason and instance:

sqlite-json warning: query falls back to in-memory filtering (full table scan, no SQL LIMIT/OFFSET).
Reason: $regex requires regexSupport: true and a regexp_match(pattern, text, options) function registered by the SQLite adapter. ...

The warning goes to console.warn by default; pass a custom log function in the settings to capture it elsewhere.

Regex support

Set regexSupport: true to compile $regex / $options to SQL. The compiler then calls:

regexp_match(pattern, text, options)

which must return 1 for a match and 0 otherwise. SQLite has no built-in function with this name — every adapter must register it when opening a connection, with JavaScript RegExp semantics so results match RxDB:

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync('mydb.sqlite');
db.function(
    'regexp_match',
    { deterministic: true },
    (pattern: unknown, text: unknown, options: unknown) => {
        if (typeof pattern !== 'string' || typeof text !== 'string') return 0;
        return new RegExp(pattern, typeof options === 'string' ? options : '').test(text) ? 1 : 0;
    }
);

Notes per runtime:

• node:sqlite (DatabaseSync): as above; keep three explicit parameters so the argument count is inferred correctly.
• better-sqlite3: db.function('regexp_match', (pattern, text, options) => ...) with the same callback.
• SQLite WASM / sql.js: use db.createFunction('regexp_match', ...) with the same signature.
• Any other runtime: register a deterministic UDF named regexp_match returning 0/1 before running queries.

If the function is missing while regexSupport: true, SQLite fails with no such function: regexp_match. If you cannot register UDFs, leave regexSupport off — $regex then uses the in-memory fallback and stays correct.

Tests

The package contains SQLite JSON lifecycle tests. It also runs the upstream RxDB storage contract tests in CI through RxDB's existing custom-storage.ts extension point.

npm install
npm test

Run the upstream RxDB storage suite locally at the pinned compatibility commit:

npm run test:upstream

Run it locally against the latest upstream master branch:

npm run test:upstream:latest

See docs/upstream-test-suite.md for the adapter boundary and the procedure for handling upstream API changes.

License

Apache-2.0