jarDB

A lightweight document-oriented database built on top of SQLite using better-sqlite3.

jarDB provides a MongoDB-like API for storing JSON documents while retaining SQLite's simplicity, local persistence, transactions, indexing, and SQL-backed performance.

It is designed for small to medium Node.js applications that need a simple embedded document database without running a separate database server.

Features

• 💾 Persistent SQLite storage
• 📄 JSON document storage
• 🗂️ Collection-based API
• 🔍 MongoDB-style query operators
• ✏️ Atomic document updates
• 🔄 Upsert support
• 🗑️ Single and bulk deletes
• 📊 Basic aggregation pipelines
• ⚡ SQLite JSON expression indexes
• 🔎 Regular-expression queries
• ↕️ Sorting, pagination, and projections
• 🔐 Sanitized collection and field identifiers
• 🧵 SQLite WAL journal mode
• 🆔 Automatic UUID document IDs
• 🔒 Prepared statements for database values
• 📦 No external database server required

Installation

Install jarDB and its SQLite dependency:

npm install @andycosow/jardb

Requirements

• Node.js with ES module support
• SQLite support provided by better-sqlite3
• A Node.js version compatible with the installed better-sqlite3 release

Basic Usage

import { jarDB } from "@andycosow/jardb";

const db = new jarDB("./data/app.db");

const users = db.collection("users");

const result = users.insertOne({
  name: "Alice",
  age: 28,
  email: "[email protected]",
});

console.log(result);

const user = users.findOne({
  email: "[email protected]",
});

console.log(user);

db.close();

A generated document looks similar to:

{
  _id: "8d7d6d3a-...",
  name: "Alice",
  age: 28,
  email: "[email protected]"
}

If _id is not supplied, jarDB automatically generates a UUID.

Database API

new jarDB(dbPath)

Creates or opens a SQLite database.

const db = new jarDB("./data/app.db");

Parameters

| Parameter | Type | Default | Description | | --------- | -------- | --------------- | ------------------------- | | dbPath | string | "./jar-db.db" | SQLite database file path |

The database automatically enables SQLite WAL mode:

PRAGMA journal_mode = WAL;

db.collection(name)

Creates a collection if it does not already exist and returns the collection instance.

const users = db.collection("users");
const products = db.collection("products");

Collection names may contain:

• Letters
• Numbers
• _
• .

Examples:

db.collection("users");
db.collection("app.users");
db.collection("user_profiles");

Invalid identifiers throw a JarDBError.

db.listCollections()

Returns the names of all SQLite tables in the database.

const collections = db.listCollections();

console.log(collections);

Example:

[
  "users",
  "products",
  "orders"
]

db.dropCollection(name)

Drops a collection completely.

db.dropCollection("users");

This permanently removes the underlying SQLite table and all documents stored in it.

db.close()

Closes the SQLite database connection.

db.close();

Always close the database when your application no longer needs it.

Collection API

Once a collection has been created:

const users = db.collection("users");

you can use the CRUD and query methods below.

Insert Documents

insertOne(document)

Inserts one document.

const result = users.insertOne({
  name: "Alice",
  age: 28,
  active: true,
});

Returns:

{
  acknowledged: true,
  insertedId: "generated-or-provided-id"
}

Custom _id

You can provide your own ID:

users.insertOne({
  _id: "user-001",
  name: "Alice",
  age: 28,
});

Duplicate IDs result in a JarDBError with code:

DUPLICATE_ID

insertMany(documents)

Inserts multiple documents in a SQLite transaction.

const result = users.insertMany([
  {
    name: "Alice",
    age: 28,
  },
  {
    name: "Bob",
    age: 35,
  },
  {
    name: "Charlie",
    age: 42,
  },
]);

Returns an array:

[
  {
    acknowledged: true,
    insertedId: "..."
  },
  {
    acknowledged: true,
    insertedId: "..."
  },
  {
    acknowledged: true,
    insertedId: "..."
  }
]

Because the operation uses a transaction, the batch is handled atomically by SQLite.

Find Documents

find(filter, options)

Finds documents matching a filter.

const users = db.collection("users");

const results = users.find({
  age: 30,
});

If no filter is supplied, all documents are returned:

const users = users.find();

Equality Queries

Simple values perform equality matching.

users.find({
  name: "Alice",
});

Multiple fields are combined with AND:

users.find({
  active: true,
  age: 28,
});

Nested Fields

Fields can be accessed using dot notation.

For documents such as:

{
  name: "Alice",
  address: {
    city: "Nairobi",
    country: "Kenya"
  }
}

you can query:

users.find({
  "address.city": "Nairobi",
});

Nested fields can also be sorted and indexed.

Query Operators

jarDB supports the following query operators.

| Operator | Description | | --------- | -------------------------------- | | $eq | Equal | | $ne | Not equal | | $gt | Greater than | | $gte | Greater than or equal | | $lt | Less than | | $lte | Less than or equal | | $in | Value exists in an array | | $nin | Value does not exist in an array | | $regex | Regular expression matching | | $exists | Tests whether a field exists | | $not | Negates a condition | | $or | Logical OR | | $and | Logical AND |

$eq

users.find({
  age: {
    $eq: 30,
  },
});

$ne

users.find({
  status: {
    $ne: "inactive",
  },
});

$gt

users.find({
  age: {
    $gt: 18,
  },
});

$gte

users.find({
  age: {
    $gte: 18,
  },
});

$lt

users.find({
  age: {
    $lt: 65,
  },
});

$lte

users.find({
  age: {
    $lte: 65,
  },
});

$in

Matches values contained in an array.

users.find({
  role: {
    $in: ["admin", "moderator"],
  },
});

You can also use an array directly as shorthand:

users.find({
  role: ["admin", "moderator"],
});

$nin

Matches values that are not contained in an array.

users.find({
  role: {
    $nin: ["banned", "suspended"],
  },
});

$regex

Performs regular-expression matching.

users.find({
  name: {
    $regex: "^Ali",
  },
});

For example:

users.find({
  email: {
    $regex: "@example\\.com$",
  },
});

The regular expression is evaluated using JavaScript's RegExp.

Note: Although the query syntax recognizes $options, the current implementation does not actually pass $options flags into the RegExp constructor. Case-insensitive matching therefore should not be assumed to work through $options: "i".

$exists

Find documents where a field exists:

users.find({
  email: {
    $exists: true,
  },
});

Find documents where it does not exist:

users.find({
  email: {
    $exists: false,
  },
});

$not

Negates a condition.

users.find({
  age: {
    $not: {
      $gt: 18,
    },
  },
});

Logical Operators

$or

users.find({
  $or: [
    { role: "admin" },
    { role: "moderator" },
  ],
});

$and

users.find({
  $and: [
    { active: true },
    { age: { $gte: 18 } },
  ],
});

Null Queries

To find documents where a field is NULL/missing:

users.find({
  deletedAt: null,
});

findOne()

Returns the first matching document or null.

const user = users.findOne({
  email: "[email protected]",
});

Example:

if (user) {
  console.log(user.name);
}

Count Documents

count(filter)

Counts documents matching a filter.

const count = users.count({
  active: true,
});

console.log(count);

Count everything:

const total = users.count();

Sorting

Use the sort option.

const users = users.find(
  {},
  {
    sort: {
      age: 1,
    },
  }
);

Use 1 for ascending and -1 for descending:

users.find(
  {},
  {
    sort: {
      age: -1,
    },
  }
);

Multiple sort fields are supported:

users.find(
  {},
  {
    sort: {
      country: 1,
      age: -1,
    },
  }
);

Nested fields can also be sorted:

users.find(
  {},
  {
    sort: {
      "profile.score": -1,
    },
  }
);

Pagination

Use skip and limit.

const page = users.find(
  {
    active: true,
  },
  {
    skip: 20,
    limit: 10,
  }
);

A common pagination formula is:

const page = 3;
const pageSize = 20;

const results = users.find(
  {},
  {
    skip: (page - 1) * pageSize,
    limit: pageSize,
  }
);

Projections

Projections control which fields are returned.

Inclusion

const users = users.find(
  {},
  {
    projection: {
      name: 1,
      email: 1,
    },
  }
);

Exclusion

const users = users.find(
  {},
  {
    projection: {
      password: 0,
      secret: 0,
    },
  }
);

Nested fields are supported:

users.find(
  {},
  {
    projection: {
      "profile.bio": 1,
      name: 1,
    },
  }
);

Updating Documents

jarDB supports atomic update operators.

Supported update operators:

| Operator | Description | | --------- | -------------------------- | | $set | Sets a field | | $unset | Removes a field | | $inc | Increments a numeric field | | $rename | Renames a field |

$set

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $set: {
      name: "Alice Smith",
      active: true,
    },
  }
);

$unset

Remove fields:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $unset: {
      temporaryToken: true,
    },
  }
);

You can also provide an array:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $unset: ["temporaryToken", "oldField"],
  }
);

$inc

Increment a numeric field:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $inc: {
      loginCount: 1,
    },
  }
);

Multiple increments:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $inc: {
      points: 10,
      loginCount: 1,
    },
  }
);

If the field does not exist, $inc starts from 0.

$rename

Rename a field:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $rename: {
      username: "displayName",
    },
  }
);

Update Shorthand

Fields without an explicit update operator are treated as $set.

users.updateOne(
  {
    _id: "user-001",
  },
  {
    name: "Alice",
    active: true,
  }
);

This is equivalent to:

users.updateOne(
  {
    _id: "user-001",
  },
  {
    $set: {
      name: "Alice",
      active: true,
    },
  }
);

updateOne()

Updates the first matching document.

const result = users.updateOne(
  {
    email: "[email protected]",
  },
  {
    $set: {
      active: false,
    },
  }
);

Returns:

{
  matchedCount: 1,
  modifiedCount: 1
}

An empty filter is rejected by updateOne() to help prevent accidental full-collection updates.

Upsert

Use upsert: true to insert a document if no matching document exists.

const result = users.updateOne(
  {
    email: "[email protected]",
  },
  {
    $set: {
      name: "New User",
      active: true,
    },
  },
  {
    upsert: true,
  }
);

If no document matches, the result contains:

{
  matchedCount: 0,
  modifiedCount: 0,
  upsertedId: "..."
}

The current upsert implementation uses simple equality fields from the filter when constructing the new document. Complex operator-based filters should not be relied upon as complete upsert documents.

updateMany()

Updates every matching document.

users.updateMany(
  {
    active: true,
  },
  {
    $inc: {
      loginCount: 1,
    },
  }
);

An empty filter updates every document in the collection.

Use this carefully.

Find and Update

findOneAndUpdate()

Finds a document, updates it, and returns the resulting document.

const result = users.findOneAndUpdate(
  {
    email: "[email protected]",
  },
  {
    $set: {
      active: true,
    },
  }
);

console.log(result.value);

Successful result:

{
  value: {
    _id: "...",
    email: "[email protected]",
    active: true
  },
  lastErrorObject: {
    updatedExisting: true
  }
}

If no document exists:

{
  value: null
}

With upsert:

const result = users.findOneAndUpdate(
  {
    email: "[email protected]",
  },
  {
    $set: {
      name: "New User",
    },
  },
  {
    upsert: true,
  }
);

Deleting Documents

deleteOne()

Deletes the first matching document.

const result = users.deleteOne({
  _id: "user-001",
});

Returns:

{
  acknowledged: true,
  deletedCount: 1
}

deleteOne() requires a non-empty filter.

deleteMany()

Deletes all matching documents.

const result = users.deleteMany({
  active: false,
});

You can intentionally delete every document with an empty filter:

users.deleteMany({});

Use this carefully because it removes the entire collection contents.

Find and Delete

findOneAndDelete()

Finds and deletes one document.

const result = users.findOneAndDelete({
  email: "[email protected]",
});

console.log(result.value);

If no document is found:

{
  value: null
}

Indexes

For frequently queried fields, create an index.

users.createIndex("email");

Nested fields are supported:

users.createIndex("profile.country");

The index is implemented using SQLite's JSON expression indexes.

Example:

users.createIndex("email");

users.find({
  email: "[email protected]",
});

Creating the same index more than once through the same collection instance is ignored.

Important

The index name and field path are generated from the supplied field name. Field names are validated to contain only alphanumeric characters, underscores, and dots.

Aggregation

aggregate() provides a limited MongoDB-style aggregation pipeline backed by SQLite.

Supported stages include:

• $match
• $group
• $project
• $sort
• $limit
• $skip

Example:

const result = users.aggregate([
  {
    $match: {
      active: true,
    },
  },
  {
    $group: {
      _id: "country",
      total: {
        $count: true,
      },
      averageAge: {
        $avg: "age",
      },
    },
  },
]);

$match

Filters documents.

users.aggregate([
  {
    $match: {
      active: true,
    },
  },
]);

The same query operators available to find() can be used in $match.

$group

Groups documents by a field.

const result = users.aggregate([
  {
    $group: {
      _id: "country",
      total: {
        $count: true,
      },
    },
  },
]);

Supported aggregation operators:

| Operator | Description | | -------- | ---------------------- | | $sum | Sum numeric values | | $avg | Average numeric values | | $max | Maximum value | | $min | Minimum value | | $count | Count documents |

Example:

const result = users.aggregate([
  {
    $group: {
      _id: "country",
      totalUsers: {
        $count: true,
      },
      totalAge: {
        $sum: "age",
      },
      averageAge: {
        $avg: "age",
      },
      oldest: {
        $max: "age",
      },
      youngest: {
        $min: "age",
      },
    },
  },
]);

Grouping Without a Group Key

Use _id: null to aggregate the entire collection:

const result = users.aggregate([
  {
    $group: {
      _id: null,
      totalUsers: {
        $count: true,
      },
      averageAge: {
        $avg: "age",
      },
    },
  },
]);

$sort in Aggregation

const result = users.aggregate([
  {
    $group: {
      _id: "country",
      total: {
        $count: true,
      },
    },
  },
  {
    $sort: {
      total: -1,
    },
  },
]);

Use:

• 1 for ascending
• -1 for descending

$limit

const result = users.aggregate([
  {
    $sort: {
      age: -1,
    },
  },
  {
    $limit: 10,
  },
]);

$skip

const result = users.aggregate([
  {
    $skip: 20,
  },
  {
    $limit: 10,
  },
]);

$project

The aggregation API recognizes $project stages.

const result = users.aggregate([
  {
    $match: {
      active: true,
    },
  },
  {
    $project: {
      name: 1,
      age: 1,
    },
  },
]);

Current implementation note: $project is recognized, but projection after a $group stage is not fully transformed into SQL and should not be treated as equivalent to MongoDB's complete $project behavior.

Dropping a Collection

A collection can drop itself:

const users = db.collection("users");

users.drop();

This removes the underlying SQLite table.

The index state maintained by the collection instance is also cleared.

Error Handling

jarDB provides a custom JarDBError class.

Errors contain:

{
  name: "JarDBError",
  code: "..."
}

Example:

try {
  users.updateOne(
    {},
    {
      $set: {
        name: "Alice",
      },
    }
  );
} catch (error) {
  console.error(error.name);
  console.error(error.code);
  console.error(error.message);
}

Common error codes include:

| Code | Meaning | | ---------------------- | ------------------------------- | | JAR_DB_ERROR | Generic jarDB error | | INVALID_IDENTIFIER | Invalid collection identifier | | INVALID_FIELD_PATH | Invalid field path | | INVALID_OPERATOR | Invalid operator argument | | UNSUPPORTED_OPERATOR | Unsupported query operator | | INVALID_QUERY | Invalid query value | | DUPLICATE_ID | Duplicate document _id | | INVALID_UPDATE | Invalid update operation | | INVALID_INCREMENT | Invalid $inc value | | INVALID_DELETE | Invalid deleteOne() operation |

Complete Example

import { jarDB } from "jardb";

const db = new jarDB("./data/app.db");

const users = db.collection("users");

// Create an index
users.createIndex("email");

// Insert documents
users.insertMany([
  {
    name: "Alice",
    age: 28,
    email: "[email protected]",
    country: "Kenya",
    active: true,
  },
  {
    name: "Bob",
    age: 35,
    email: "[email protected]",
    country: "Kenya",
    active: true,
  },
  {
    name: "Charlie",
    age: 17,
    email: "[email protected]",
    country: "Uganda",
    active: false,
  },
]);

// Find
const adults = users.find({
  age: {
    $gte: 18,
  },
});

console.log(adults);

// Find one
const alice = users.findOne({
  email: "[email protected]",
});

console.log(alice);

// Update
users.updateOne(
  {
    email: "[email protected]",
  },
  {
    $set: {
      active: false,
    },
    $inc: {
      loginCount: 1,
    },
  }
);

// Update many
users.updateMany(
  {
    country: "Kenya",
  },
  {
    $inc: {
      points: 10,
    },
  }
);

// Count
const activeUsers = users.count({
  active: true,
});

console.log(activeUsers);

// Aggregation
const statistics = users.aggregate([
  {
    $group: {
      _id: "country",
      total: {
        $count: true,
      },
      averageAge: {
        $avg: "age",
      },
    },
  },
]);

console.log(statistics);

// Delete
users.deleteOne({
  email: "[email protected]",
});

// Close database
db.close();

API Summary

Database

| Method | Description | | ---------------------- | -------------------------- | | new jarDB(path) | Open/create a database | | collection(name) | Get or create a collection | | listCollections() | List database tables | | dropCollection(name) | Drop a collection | | close() | Close the database |

Collection

| Method | Description | | ------------------------------------------- | ------------------------------ | | insertOne(doc) | Insert one document | | insertMany(docs) | Insert multiple documents | | find(filter, options) | Find matching documents | | findOne(filter) | Find one document | | count(filter) | Count matching documents | | updateOne(filter, update, options) | Update one document | | updateMany(filter, update) | Update multiple documents | | findOneAndUpdate(filter, update, options) | Find and update | | deleteOne(filter) | Delete one document | | deleteMany(filter) | Delete multiple documents | | findOneAndDelete(filter) | Find and delete | | createIndex(field) | Create a JSON expression index | | aggregate(pipeline) | Run an aggregation pipeline | | drop() | Drop the collection |

Query Operator Summary

$eq
$ne
$gt
$gte
$lt
$lte
$in
$nin
$regex
$exists
$not
$or
$and

Update Operator Summary

$set
$unset
$inc
$rename

Aggregation Operator Summary

$match
$group
$project
$sort
$limit
$skip

Group aggregation operators:

$sum
$avg
$max
$min
$count

Security and Identifier Validation

Collection names and field paths are validated before being inserted into SQL statements.

Allowed characters are:

a-z
A-Z
0-9
_
.

For example:

db.collection("user_profiles");
db.collection("app.users");

are valid.

Names containing SQL syntax or other special characters are rejected.

Document values are passed to SQLite using parameters rather than being directly interpolated into SQL.

Storage Model

Documents are stored in SQLite tables using two columns:

_id TEXT PRIMARY KEY
data TEXT NOT NULL

The complete JavaScript document is serialized as JSON into the data column.

For example:

{
  _id: "123",
  name: "Alice",
  age: 28
}

is stored conceptually as:

_id  = "123"
data = '{"_id":"123","name":"Alice","age":28}'

SQLite's JSON functions such as json_extract, json_set, and json_remove are used for querying and updating document fields.

When to Use jarDB

jarDB is a good fit for:

• Local applications
• CLI tools
• Desktop applications
• Prototypes
• Small APIs
• Embedded applications
• Development environments
• Medium-sized applications with straightforward document storage
• Applications that do not need a database server

It is especially useful when you want a document-oriented API but still want SQLite's single-file database architecture.

Limitations

jarDB is intentionally lightweight and does not attempt to implement the complete MongoDB API.

In particular:

• Aggregation supports a limited set of stages/operators.
• $project has limited behavior in aggregation pipelines.
• $options for $regex is recognized but is not currently applied as JavaScript RegExp flags.
• Query/update field paths are restricted to alphanumeric characters, _, and ..
• The database is embedded and local rather than a network database server.
• Advanced MongoDB features such as transactions exposed through a MongoDB-style API, change streams, geospatial queries, and full aggregation semantics are not provided.
• The implementation stores complete documents as JSON, so query performance for unindexed fields depends on SQLite JSON extraction.

For high-scale distributed applications, a server-based database may be more appropriate.

License

Add your project's license information here.

For example:

MIT License

Contributing

Contributions, bug reports, feature requests, and pull requests are welcome.

Before submitting a change, consider adding tests for:

• CRUD operations
• Query operators
• Nested fields
• Updates
• Upserts
• Aggregation
• Indexing
• Error handling

Quick Reference

import { jarDB } from "jardb";

const db = new jarDB("./app.db");
const users = db.collection("users");

// Insert
users.insertOne({
  name: "Alice",
  age: 28,
});

// Query
users.find({
  age: {
    $gte: 18,
  },
});

// Query + sort + pagination
users.find(
  {
    active: true,
  },
  {
    sort: {
      age: -1,
    },
    skip: 0,
    limit: 20,
  }
);

// Update
users.updateOne(
  {
    name: "Alice",
  },
  {
    $set: {
      active: true,
    },
    $inc: {
      points: 10,
    },
  }
);

// Delete
users.deleteOne({
  name: "Alice",
});

// Aggregate
users.aggregate([
  {
    $group: {
      _id: "country",
      count: {
        $count: true,
      },
    },
  },
]);

// Close
db.close();

License

This project is distributed under the license specified by the package repository.