driftschema-sqlite

A SQLite-backed storage engine for driftschema — a lightweight, dynamic schema library for TypeScript that defines and evolves entity fields at runtime, without migrations.

This package works from plain Node (via better-sqlite3), from Expo (via expo-sqlite), and from bare React Native (via @op-engineering/op-sqlite) — the same SqliteRecordStore/SqliteFieldDefinitionStore classes run against any of them, through a small SqliteDriver adapter.

This package provides:

• SqliteRecordStore — a RecordStore implementation that persists records as JSON text in a SQLite table.
• SqliteFieldDefinitionStore — a FieldDefinitionStore implementation that persists field definitions in a SQLite table.
• createBetterSqlite3Driver / createExpoSqliteDriver / createOpSqliteDriver — adapters exposed as separate subpaths (driftschema-sqlite/better-sqlite3, driftschema-sqlite/expo-sqlite, driftschema-sqlite/op-sqlite) so importing this package never pulls in a native driver you're not using.

SQLite has no native JSONB column type, but its JSON1 functions (json_extract, json_patch, ...) have shipped in the default amalgamation since 3.38 (2022) and are present in every binding this package supports — the fields column is stored as TEXT, queried and merged through those functions.

Installation

For Node:

npm install driftschema driftschema-sqlite better-sqlite3

For Expo:

npx expo install driftschema driftschema-sqlite expo-sqlite

For bare React Native:

npm install driftschema driftschema-sqlite @op-engineering/op-sqlite

Schema setup

Like driftschema-postgres, the records and field_definitions tables need to exist before use — this package exports the DDL as an array of statements plus a convenience helper, neither store runs this automatically; a real deployment should own schema changes through its own migration tool instead.

import Database from "better-sqlite3";
import { createSchema, createBetterSqlite3Driver } from "driftschema-sqlite/better-sqlite3";
// or: import { createSchema } from "driftschema-sqlite";

const db = new Database("app.db");
const driver = createBetterSqlite3Driver(db);
await createSchema(driver); // runs SQLITE_SCHEMA_STATEMENTS — idempotent (CREATE ... IF NOT EXISTS)

SQLITE_SCHEMA_SQL is the copy-pasteable source of truth if you'd rather commit it as a migration in your own tool; SQLITE_SCHEMA_STATEMENTS is the same DDL as an array — createSchema runs one statement per call, since (unlike pg.Pool.query) SqliteDriver.execute runs exactly one statement at a time.

Usage

Via RecordStoreFactory (recommended)

Importing driftschema-sqlite registers the "sqlite" engine with driftschema's RecordStoreFactory as a side effect, so you can create a SQLite-backed store the same way you'd create the in-memory or Postgres ones — just with a different engine name and config:

import Database from "better-sqlite3";
import { RecordStoreFactory } from "driftschema";
import { SqliteFieldDefinitionStore } from "driftschema-sqlite";
import { createBetterSqlite3Driver } from "driftschema-sqlite/better-sqlite3";

const db = new Database("app.db");
const driver = createBetterSqlite3Driver(db);

const fieldDefinitions = new SqliteFieldDefinitionStore(driver);

const caratWeight = await fieldDefinitions.add({
  entityType: "diamonds",
  name: "caratWeight",
  type: "number",
  required: true,
});

const recordStore = await RecordStoreFactory.create("sqlite", fieldDefinitions, { driver });

const diamond = await recordStore.createFlat("diamonds", { caratWeight: 1.5 });

RecordStoreFactory.create("sqlite", ...) dynamically imports driftschema-sqlite if it isn't already loaded, so this also works without an explicit import of this package.

Direct instantiation

import Database from "better-sqlite3";
import { SqliteFieldDefinitionStore, SqliteRecordStore } from "driftschema-sqlite";
import { createBetterSqlite3Driver } from "driftschema-sqlite/better-sqlite3";

const db = new Database("app.db");
const driver = createBetterSqlite3Driver(db);

const fieldDefinitions = new SqliteFieldDefinitionStore(driver);
const recordStore = new SqliteRecordStore(fieldDefinitions, driver);

Expo

Swap the driver — everything else (SqliteRecordStore, SqliteFieldDefinitionStore, the filter DSL, pagination) is identical. This is the simplest mobile option if you're on the Expo managed workflow: expo-sqlite ships as part of the Expo SDK, no native linking/config needed.

import * as SQLite from "expo-sqlite";
import { SqliteFieldDefinitionStore, SqliteRecordStore } from "driftschema-sqlite";
import { createExpoSqliteDriver } from "driftschema-sqlite/expo-sqlite";

const db = await SQLite.openDatabaseAsync("app.db");
const driver = createExpoSqliteDriver(db);

const fieldDefinitions = new SqliteFieldDefinitionStore(driver);
const recordStore = new SqliteRecordStore(fieldDefinitions, driver);

Bare React Native

For projects not on the Expo SDK (or that want op-sqlite's JSI performance specifically), swap in that driver instead — again, nothing else changes:

import { open } from "@op-engineering/op-sqlite";
import { SqliteFieldDefinitionStore, SqliteRecordStore } from "driftschema-sqlite";
import { createOpSqliteDriver } from "driftschema-sqlite/op-sqlite";

const db = open({ name: "app.db" });
const driver = createOpSqliteDriver(db);

const fieldDefinitions = new SqliteFieldDefinitionStore(driver);
const recordStore = new SqliteRecordStore(fieldDefinitions, driver);

See examples/basic-usage.ts and examples/query-usage.ts for fuller runnable walkthroughs (both use better-sqlite3 with an in-memory database, so they run standalone with no setup required — run them with npm run example).

API

SqliteFieldDefinitionStore

new SqliteFieldDefinitionStore(driver: SqliteDriver, tableName = "field_definitions")

Implements driftschema's FieldDefinitionStore interface: add, getByEntityType, getAll, upsert, delete.

SqliteRecordStore

new SqliteRecordStore(fieldDefinitionStore: FieldDefinitionStore, driver: SqliteDriver, tableName = "records")

Implements driftschema's RecordStore interface in full.

Filtering — query/queryFlat accept driftschema's baseline filter DSL: a plain object keyed by field name, where each value is either a direct value (implicit equality) or an operator object drawn from $eq/$ne/$gt/$gte/$lt/$lte/$in — the same syntax packages/core's in-memory engine and driftschema-postgres implement. This is not a native SQL passthrough — each filter is translated into a parameterized predicate against the fields column via json_extract.

Pagination — getByEntityType, query, and their flat counterparts accept either:

• { offset?, limit? } — page-N navigation.
• { after?, limit? } — keyset/cursor pagination, sorted by id. This is a stable total order but has no relationship to insertion order (ids are random UUIDs) — it exists to make paging through large result sets cheap and stable under concurrent inserts, not to convey recency.

Specifying both after and offset throws.

RecordStoreFactory config

When creating the "sqlite" engine via RecordStoreFactory.create, the config argument must be:

interface SqliteRecordStoreConfig {
  driver: SqliteDriver;
  tableName?: string;
}

Design notes

• SqliteDriver is a single-method interface — execute(sql, params?): Promise<Record<string, unknown>[]>. Every mutating statement that needs a result uses SQL RETURNING, so one atomic statement per operation is enough; there's no separate run/get/transaction method, and no dependence on any driver's transaction helper (notably sidestepping better-sqlite3's .transaction(), which rejects async callbacks).
• update() merges via json_patch(fields, ?), mirroring driftschema-postgres's fields || $patch::jsonb — a single atomic statement, so a concurrent writer's change to a field this call doesn't touch survives. json_patch follows RFC 7396 merge-patch rules, so an explicit null in the patch deletes that key rather than storing a literal JSON null. This is harmless in practice: validateFields (in driftschema core) already treats an explicit null and a missing key identically, so a required field set to null fails validation before this ever reaches SQL, and for a non-required field the two are already indistinguishable everywhere else in the system — the same collapse this project's Postgres README documents for { field: null } filters.
• { field: null } matches a wholly absent field, not just an explicit JSON null, same as driftschema-postgres and driftschema-mongo — json_extract can't tell "key absent" from "key present with JSON null" apart either.
• No native array or boolean column types. allowed_values (enum fields) is stored as JSON-encoded TEXT instead of Postgres's TEXT[]; required is stored as INTEGER (0/1). Both round-trip transparently through the FieldDefinition API.
• No UUID validation on ids. driftschema-postgres validates UUID format because an invalid one breaks its ::uuid cast; SQLite's id column is plain TEXT, so there's nothing to cast and this package doesn't bother — ids are still app-generated via crypto.randomUUID(), just not format-checked at the SQL layer.
• The expo-sqlite and op-sqlite adapters aren't covered by this repo's automated tests. Both require a real Expo/React Native runtime, which this repo's Node-based CI can't provide. They're kept deliberately minimal (a single pass-through call each) so the untested surface stays small — verify them manually inside a real app before relying on either in production. Everything else (filter translation, pagination, JSON mapping, both stores) is fully tested against the real better-sqlite3 driver, which exercises the exact same SqliteDriver contract.

License

MIT — see the root LICENSE.