SQLite Document DB

Status: 2.0.0 is a rewrite on Node's built-in node:sqlite — zero dependencies, ESM only, Node ≥ 22.13. The CRUD and query surface below is substantial and every behaviour in it is verified against a real MongoDB, but this is a compatible subset, not a drop-in replacement — most notably there is no aggregation pipeline. See Missing Features.

Upgrading from 1.x is a breaking change: see CHANGELOG.md.

Use SQLite as a JSON Document Database.

API based on MongoDB's JavaScript API.

Documents are stored one-per-row in a data JSON column, and Mongo-style filter objects are compiled into SQLite JSON functions so that querying happens inside the database rather than in JavaScript.

Zero runtime dependencies — it uses Node's built-in node:sqlite module, so there is nothing to compile and no native binaries to install.

Requirements

Node.js 22.13 or newer (node:sqlite appeared in 22.5 and only became stable in Node 24; the custom SQL function behind $regex needs DatabaseSync.prototype.function, added in 22.13).

Getting started

1. Install using NPM:

npm i --save sqlite-document-db

2. Start using it:

import Db from 'sqlite-document-db'

const db = await Db.fromUrl(':memory:') // Can also be a path to your DB file

// Insert some users into a collection
await db.collection('users').insertOne({ username: 'test_user', email: '[email protected]' })
await db.collection('users').insertMany([
  { username: 'test_user2', email: '[email protected]' },
  { username: 'test_user3', email: '[email protected]' },
])

const user = await db.collection('users').findOne({ email: '[email protected]' })

console.log(user)

Console output of the above:

{
  _id: '626964400e547e782d04d7f1',
  username: 'test_user2',
  email: '[email protected]'
}

Examples

Runnable examples live in examples/ — CRUD, every query operator, arrays, indexes with a before/after timing, cursors, dates, upsert, error handling, TypeScript schemas, and a Deno one. They are executed by CI on Node and Deno, so they cannot rot:

npm run examples

Features and examples

Insert documents

// Insert a single document
db.collection('inventory').insertOne({ item: 'canvas', qty: 100, tags: ['cotton'], size: { h: 28, w: 35.5, uom: 'cm' } })

// Insert multiple documents
db.collection('inventory').insertOne([
  { _id: undefined, item: 'journal', qty: 25, tags: ['blank', 'red'], size: { h: 14, w: 21, uom: 'cm' } },
  { item: 'mat', qty: 85, tags: ['gray'], size: { h: 27.9, w: 35.5, uom: 'cm' } },
  { item: 'mousepad', qty: 25, tags: ['gel', 'blue'], size: { h: 19, w: 22.85, uom: 'cm' } }
])

Query documents

const items = [
  { item: 'journal', qty: 25, size: { h: 14, w: 21, uom: 'cm' }, status: 'A' },
  { item: 'notebook', qty: 50, size: { h: 8.5, w: 11, uom: 'in' }, status: 'A' },
  { item: 'paper', qty: 100, size: { h: 8.5, w: 11, uom: 'in' }, status: 'D' },
  { item: 'postcard', qty: 45, size: { h: 10, w: 15.25, uom: 'cm' }, status: 'C' },
  { item: 'planner', qty: 75, size: { h: 22.85, w: 30, uom: 'cm' }, status: 'D' },
  { item: 'postcard', qty: 45, size: { h: 10, w: 15.25, uom: 'cm' }, status: 'A' }
]

await db.collection('items').insertMany(items)

// Select a single document
db.collection('items').findOne({ item: 'paper' })

// Select all documents in a collection
const allItemsArray = await db.collection('items').find().toArray()

// Query using equality conditions
db.collection('items').find({ status: 'D' })
db.collection('items').find({ status: { $in: ['A', 'D'] } })
db.collection('items').find({ qty: { $lt: 30 } })
db.collection('items').find({ qty: { $gt: 30 } })
db.collection('items').find({ qty: { $lte: 45 } })
db.collection('items').find({ qty: { $gte: 45 } })
db.collection('items').find({ qty: { $eq: 45 } })
db.collection('items').find({ qty: { $ne: 45 } })
db.collection('items').find({ status: 'A', qty: { $lt: 30 } })
db.collection('items').find({ $or: [{ status: 'A' }, { qty: { $lt: 30 } }] })

// Query nested fields with dot notation
db.collection('items').find({ 'size.uom': 'in' })

Indexes

Collections always have a unique index on _id. Additional fields can be indexed with the MongoDB createIndex API — backed by real SQLite expression indexes, so filtered queries stop being full-table scans:

await db.collection('items').createIndex({ qty: 1 })                       // -> 'qty_1'
await db.collection('items').createIndex({ 'size.uom': 1, status: -1 })    // compound
await db.collection('users').createIndex({ email: 1 }, { unique: true })   // unique
await db.collection('items').indexes()                                     // list
await db.collection('items').dropIndex('qty_1')                            // drop

Single-field indexes automatically cover Date values too (they are stored in a wrapped format — see below — and get a companion index on the wrapped path).

Iterate a cursor

Cursors are async-iterable, and fetch one document at a time rather than materialising the whole result set:

for await (const item of db.collection('items').find({ status: 'A' })) {
  console.log(item)
}

Project fields to return

// Only these fields (plus _id)...
db.collection('items').find({ status: 'A' }, { projection: { item: 1, status: 1 } })
// ...without _id, via the chainable form
db.collection('items').find({ status: 'A' }).project({ item: 1, status: 1, _id: 0 })
// Exclusions, nested fields, and fields inside arrays of documents
db.collection('items').find().project({ 'size.uom': 0 })
db.collection('items').find().project({ item: 1, 'instock.qty': 1 })

Sort, limit and skip

db.collection('items').find().sort({ qty: -1 }).skip(10).limit(5)
db.collection('items').find({}, { sort: { qty: -1 }, skip: 10, limit: 5 }) // same thing

// Multi-key sorting, in MongoDB's BSON type order
db.collection('items').find().sort({ status: 1, qty: -1 })

Sorting follows MongoDB's type comparison order (null/missing < numbers < strings < booleans < dates), verified against real MongoDB.

Query arrays

// Implicit element matching, like MongoDB: matches documents where tags IS
// 'red' or where tags is an array CONTAINING 'red'
db.collection('items').find({ tags: 'red' })
db.collection('items').find({ dim_cm: { $gt: 25 } })       // any element > 25
db.collection('items').find({ tags: { $in: ['red', 'blue'] } })

await db.collection('survey').insertMany([
  { results: [{ product: 'abc', score: 10 }, { product: 'xyz', score: 5 }] },
  { results: [{ product: 'abc', score: 7 }, { product: 'xyz', score: 8 }] }
])

// Match array elements against multiple criteria
db.collection('survey').find({ results: { $elemMatch: { product: 'xyz', score: { $gte: 8 } } } })

// Match on array length, or on an array containing all of a set of values
db.collection('survey').find({ results: { $size: 2 } })
db.collection('items').find({ tags: { $all: ['blank', 'red'] } })

Match with regular expressions, types and modulo

db.collection('items').find({ item: /^p/ })                          // implicit regex match
db.collection('items').find({ item: { $regex: '^p', $options: 'i' } })
db.collection('items').find({ item: { $in: [/^p/, 'notebook'] } })   // regexes inside $in/$nin
db.collection('items').find({ qty: { $mod: [4, 0] } })               // qty % 4 === 0
db.collection('items').find({ qty: { $type: 'number' } })            // BSON type aliases and codes
db.collection('items').find({ qty: { $type: ['int', 'string'] } })

$regex runs JavaScript RegExp inside SQLite (via a registered SQL function), so JS regex syntax applies. MongoDB's x (extended) option is not supported.

Update documents

await db.collection('items').updateOne({ item: 'paper' }, { $set: { status: 'P' } })
await db.collection('items').updateMany({ qty: { $lt: 50 } }, { $set: { status: 'P' }, $inc: { qty: 5 } })
await db.collection('items').updateOne({ item: 'paper' }, { $unset: { status: '' } })

Updates are validated the way MongoDB validates them, rather than being applied loosely: _id is immutable, a field cannot be targeted by two operators in one update, and $inc on a non-numeric field is an error.

Upsert, and find-and-modify

upsert inserts when nothing matched, seeding the new document from the filter's equality conditions (a range or $in names no single value, so it contributes nothing) and then applying the update over them:

await db.collection('items').updateOne(
  { item: 'planner', 'size.uom': 'cm' },   // -> { item: 'planner', size: { uom: 'cm' } }
  { $inc: { qty: 1 }, $setOnInsert: { createdAt: new Date() } },
  { upsert: true }
)                                          // -> { ..., qty: 1, createdAt: <Date> }

$setOnInsert applies only when the upsert actually inserts. replaceOne and updateMany take upsert too — an upsert that matches nothing always inserts exactly one document.

The find-and-modify trio returns the document itself, defaulting to the version from before the write, like the driver:

await db.collection('items').findOneAndUpdate({ item: 'paper' }, { $inc: { qty: -1 } })
await db.collection('items').findOneAndUpdate(
  {}, { $set: { picked: true } },
  { sort: { qty: -1 }, returnDocument: 'after', projection: { item: 1 } }
)
await db.collection('items').findOneAndReplace({ item: 'paper' }, { item: 'card' }, { upsert: true })
await db.collection('items').findOneAndDelete({ status: 'D' }, { sort: { qty: 1 } })

Handle errors

Write failures carry MongoDB's error codes, so the usual catch works unchanged:

import { DUPLICATE_KEY_ERROR } from 'sqlite-document-db' // === 11000

try {
  await db.collection('users').insertOne({ _id: 'taken' })
} catch (error) {
  if (error.code === DUPLICATE_KEY_ERROR) { /* already exists */ }
}

MongoServerError is also exported, but branch on code — instanceof cannot match the official driver's class without depending on mongodb.

Typed collections

Pass a schema to db.collection<T>() and filters, update documents and results are all checked against it — including dot-notation paths:

interface Item { _id: string, item: string, qty: number, size: { uom: string }, tags: string[] }
const items = db.collection<Item>('items')

await items.find({ qty: { $lt: 30 } })            // ok
await items.find({ 'size.uom': 'cm' })            // ok - nested paths are typed
await items.find({ tags: 'red' })                 // ok - matches an array element
await items.updateOne({ item: 'x' }, { $inc: { qty: 1 } })

await items.find({ qtyy: { $lt: 30 } })           // error: no such field
await items.find({ qty: { $lt: 'thirty' } })      // error: qty is a number
await items.find({ qty: { $gtt: 1 } })            // error: no such operator
await items.updateOne({ item: 'x' }, { $inc: { item: 1 } })  // error: $inc needs a number

Only operators this library actually implements appear in the types, so anything that compiles will run. Collections opened without a schema stay completely permissive, so untyped code is unaffected.

Collection names

Names are case-sensitive, as MongoDB's are, and accept anything MongoDB accepts (my-data, audit.log, Items). Rejected: an empty name, a $, a NUL byte, and the system. / sqlite_ prefixes.

db.collection('Users')  // a different collection from...
db.collection('users')  // ...this one

Development

npm install
npm test         # runs every assertion against BOTH this library and a real MongoDB
npm run test:types  # type-level assertions, including cases that must NOT compile
npm run examples # builds, then runs every example in examples/
npm run bench    # benchmarks (indexed vs full-scan queries, writes) over 20k docs
npm run lint
npm run build

The test suite is the interesting part: each assertion runs twice, once against sqlite-document-db and once against a real MongoDB booted in-memory, so MongoDB itself acts as the oracle for correct behaviour. Running the tests therefore downloads a mongod binary the first time.

Missing Features

Many MongoDB features are missing - either because I have not gotten time to implement them (feel free to help out!) or SQLite can't support them.

What is supported

Operators: $eq $gt $gte $lt $lte $ne $in $nin $and $or $not $nor $exists $type $regex (with $options) $mod $all $elemMatch $size.

Methods: find() findOne() countDocuments() insertOne() insertMany() updateOne() updateMany() deleteOne() deleteMany() replaceOne() findOneAndUpdate() findOneAndReplace() findOneAndDelete() createIndex() dropIndex() indexes() listIndexes().

Update operators: $set $unset $inc $setOnInsert, and the upsert option on updateOne/updateMany/replaceOne. Result objects match the official driver's shapes (acknowledged, matchedCount, modifiedCount, upsertedId, ...), and errors match its codes (11000 for a duplicate key).

Supported value types

Supported: object, array, string, number, boolean, null — and Date, which is stored in MongoDB's Extended JSON format ({"$date": "..."}), round-trips as a real Date, and works in equality and range queries:

await db.collection('events').insertOne({ name: 'launch', at: new Date('2020-06-15') })
await db.collection('events').find({ at: { $gte: new Date('2020-01-01') } }).toArray()

Anything else JSON cannot represent (RegExp, Uint8Array/Buffer, Map, Set, bigint, functions, NaN/Infinity) is rejected at write time with an error naming the offending path, rather than silently corrupted the way JSON.stringify would. (RegExp still works fine as a query value via $regex — it just cannot be stored in documents.) Design notes in DR-1 in the backlog.

One field shape is reserved by that format: an object that is exactly { "$date": "<string>" } is indistinguishable from a stored Date, so it is rejected on write too. Objects that merely contain a $date key ({ $date: '…', tz: 'UTC' }) store normally.

Concurrency

node:sqlite is synchronous, so the async API never actually yields mid-operation — there is no interleaving within a single call. Two things follow:

• Do not write to a collection while iterating a cursor over it. SQLite leaves the result of modifying a table mid-SELECT unspecified; rows may be visited twice or skipped. Materialise with toArray() first.

• File-backed databases across processes work under WAL, but writers still serialise. busyTimeoutMs (default 5000) controls how long a write waits for a competing writer before failing:

  const db = await Db.fromUrl('./data.db', { busyTimeoutMs: 10_000 })

Still missing

The planned work is tracked in BACKLOG.md, prioritised and with notes on how each piece would be implemented. The headlines:

Querying documents

• Projection $-operators: $slice, $elemMatch, $ positional
• Remaining Evaluation Query Operators — $expr, $text, and the $bits* operators. $where will not be supported (it executes arbitrary JavaScript).

Updating documents

• The remaining update operators: $mul, $min, $max, $rename, $push, $pull, $addToSet, $pop

Thanks

Thanks to https://github.com/thomas4019/mongo-query-to-postgres-jsonb for being a huge inspiration for this project.