OP-SQLite

OP-SQLite is a high-performance SQLite wrapper supporting multiple platforms and advanced SQLite extensions. Key capabilities include:

• Platform Support: iOS, Android, macOS, and Web.
• SQLite Engines: Vanilla SQLite, Turso, Libsql, and SQLCipher (as compilation targets).
• Plugins: FTS5, Rtree, cr-sqlite, and sqlite-vec.
• Advanced Features:
  • Reactive queries
  • Custom tokenizers
  • Loading runtime extensions
  • JSONB support
  • Native query interruption via db.interrupt()
• Built-in Key-Value Store: Includes a simple Key-Value storage implementation to avoid extra dependencies.

The @op-engineering/op-sqlite-node package is a Node.js adapter that provides the same TypeScript API as the React Native version of @op-engineering/op-sqlite. It uses better-sqlite3 under the hood, allowing developers to share database logic between React Native mobile applications and Node.js backend services.

import { open } from '@op-engineering/op-sqlite-node';

While the core JSI module cannot run in Node.js, op-sqlite provides a NodeJS-compatible façade with the same API as the React Native version.

Purpose: This is intended for writing Jest tests to verify your SQL queries. It is not intended for production usage and is not a guarantee of the library's correctness, but rather a convenience for testing logic.

Reactive queries allow you to subscribe to database changes. When a change is detected in a specified table or row ID, the query is automatically re-executed.

Key Mechanics:

• Implementation: Reactivity is achieved via SQLite's native update hook and filtering is implemented in C++ for high performance.
• Optimization: Queries are internally stored as prepared statements to optimize callbacks.
• Row ID Requirement: Reactivity relies on SQLite's internal rowid column. If your table uses a different primary key, you must explicitly retrieve the rowid to subscribe to specific rows.
• Trigger Mechanism: Reactive queries are only triggered on transactions. The query re-runs at the end of a transaction that mutates the observed data.

To use the native C++ functions of op-sqlite without going through the React Native/JavaScript layer:

• Android: Implement your logic via JNI/NDK code.
• iOS: You can use Objective-C++ (.mm) files to import and use the headers directly.

Note: Swift integration may require setting the minimum Swift version to 5.9.

Because JavaScript numbers are 64-bit floats (double), they can only safely represent integers up to Number.MAX_SAFE_INTEGER ($2^{53} - 1$). SQLite supports 64-bit integers (long long), but values exceeding the JS safe limit will be truncated when returned to JavaScript.

To handle larger numbers, you must store them as TEXT in SQLite and manually serialize/deserialize them using JavaScript BigInt objects.

// 1. Create table with TEXT type and STRICT typing
db.executeSync(
  'CREATE TABLE IF NOT EXISTS NumbersTable (myBigInt TEXT NOT NULL) STRICT'
);

// 2. Insert by converting BigInt to string
db.executeSync('INSERT INTO NumbersTable VALUES (?)', [
  BigInt('12345678901234567890').toString(),
]);

// 3. Retrieve by converting string back to BigInt
let res = db.executeSync('SELECT * FROM NumbersTable');
let myBigint = BigInt(res.rows[0].myBigInt);

To install the library in a standard React Native project, use npm:

npm i -s @op-engineering/op-sqlite

In recent React Native versions, pod installation should be automatic. If not, run pod install manually.

Note for Expo users: You cannot use this library in the expo-go app. You must use a development build (pre-build). No specific Expo plugin is required, but ensure pods are properly set up.

npx expo install @op-engineering/op-sqlite
npx expo prebuild --clean

op-sqlite includes built-in JSONB support. Because it is a direct binding to SQLite, you must handle data serialization yourself. You can insert JSON as a string or as a blob (ArrayBuffer).

When using blobs, you must manually convert your JavaScript object to an ArrayBuffer using a TextEncoder.

// Inserting as a string
await db.execute('INSERT INTO states VALUES (?)', [JSON.stringify(states)]);

// Inserting as a blob (ArrayBuffer)
function objectToArrayBuffer(obj) {
  const jsonString = JSON.stringify(obj);
  const encoder = new TextEncoder();
  const uint8Array = encoder.encode(jsonString);
  return uint8Array.buffer;
}

await db.execute('INSERT INTO states VALUES (?)', [
  objectToArrayBuffer(states),
]);

// Querying JSONB data
let res = await db.execute(
  `SELECT data->>'country' FROM states WHERE data->>'capital'=='Amsterdam';`
);

To inspect data directly or export the database for support, you can retrieve the absolute file path of the current database using the getDbPath() method on the database instance. This is useful for locating the .sqlite file on a simulator or for attaching the file to support tickets in on-device scenarios.

const db = open({ name: 'dbPath.sqlite' });
await db.execute('CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT)');
const path = db.getDbPath();
console.warn(path);
// You can then use the path to copy to clipboard or upload