als-sqlite

SQLite ORM/ODM with a Mongoose-like API.

Why this library

• No need to write raw SQL for everyday work (JOIN, IN, filters, sorting, updates, deletes).
• Chainable query API close to Mongoose style.
• populate() works from schema relations.
• Many-to-many pivot tables are detected and created automatically.
• Cascade delete policy is inferred from ref + required.
• Per-db model registry and db.migrateAll() for one-shot setup.

Install

npm install als-sqlite

Requirements

• Node.js >= 22.5.0 — the library uses the built-in node:sqlite module, so no native SQLite dependency is bundled.
• Node 22.13+ / 23.4+ / 24 LTS run it out of the box. On Node 22.5–22.12 node:sqlite is behind the --experimental-sqlite flag.
• node:sqlite is still an experimental Node API, so you may see an ExperimentalWarning: SQLite is an experimental feature at startup.
• In the browser the library falls back to sql.js (see Connection), so this Node requirement applies to server-side usage only.

Upgrading from 0.5.x

Version 1.0 is a complete rewrite and is not a drop-in replacement for 0.5.x. The package keeps the same purpose and name, but applications must be migrated to the new API.

Main changes:

• the package now uses ES modules (import) instead of CommonJS (require)
• connect() and the global Model.db connection were replaced by Db
• models are registered per database with db.model()
• queries use the new chainable Query and Document APIs
• migrations and relation handling have been redesigned
• Node.js uses node:sqlite by default instead of bundling better-sqlite3

Before:

const { connect, Model, Schema } = require('als-sqlite');

Model.db = connect('./app.sqlite');
const User = new Model('users', new Schema({
  email: { type: 'text', unique: true }
}));

Version 1.0:

import { Db, Schema } from 'als-sqlite';

const db = new Db('./app.sqlite');
await db.connect();

const User = db.model('users', new Schema({
  email: { type: String, unique: true }
}));

db.migrateAll();

Back up an existing database and test its schema with version 1.0 before upgrading a production application. Automatic migrations create missing tables, but they do not rewrite existing tables to match a changed schema. See Safe table sync for existing tables for controlled schema updates.

Core idea

You work with:

• Schema (very close to Mongoose style)
• Model / Document
• chainable Query
• SQLite database connection via Db

API is designed to feel familiar if you already use Mongoose.

Quick start

import { Db, Schema } from 'als-sqlite';

const db = new Db('./app.sqlite');
await db.connect();

const userSchema = new Schema({
  email: { type: String, required: true, unique: true, lowercase: true, trim: true },
  age: { type: Number, min: 18 },
  active: { type: Boolean, default: true }
});

const User = db.model('users', userSchema);

// migrate all models registered in this db
// (works like a db-scoped bootstrap)
db.migrateAll();

User.create({ email: '[email protected]', age: 30 });
const adults = User.find({ age: { $gte: 18 } }).sort('-age').limit(10).all();

Connection (Node.js + Browser)

Db uses a unified connect() method and works in both environments.

• Node.js: await db.connect() opens SQLite via node:sqlite (DatabaseSync).
• Browser: await db.connect() loads sql.js from jsDelivr and opens the database bytes via fetch.

The default browser connection therefore requires network access to jsDelivr and permission to evaluate the downloaded script. Applications with a strict Content Security Policy should provide a compatible SQLite driver instead.

import { Db } from 'als-sqlite';

const db = new Db('./app.sqlite'); // in browser this can be '/assets/app.sqlite'
await db.connect();

Custom SQLite driver (optional)

If you assign a custom SQLite constructor before creating Db, it auto-connects in constructor and you can skip connect().

import { DatabaseSync } from 'node:sqlite';
import { Db } from 'als-sqlite';

Db.db = DatabaseSync; // alias to Db.DatabaseSync
// Db.DatabaseSync = DatabaseSync; // same effect

const db = new Db('./app.sqlite'); // already connected

This allows plugging any compatible SQLite implementation with constructor signature:

• new Driver(dbName, options)
• and instance methods: exec(sql), prepare(sql)

SQL log and browser sync

Each Db instance now has a built-in SQL log:

• db.log.read - read queries
• db.log.write - write/DDL/transaction queries
• db.log.changes - parsed write operations with { op, table, sql }
• db.log.byTable - changes grouped by table name

This is useful for browser-first flows where DB runs locally and write operations should be synced to server.

Example:

db.exec("INSERT INTO users (name) VALUES ('Alex')");
db.prepare('SELECT * FROM users').all();

console.log(db.log.write);
console.log(db.log.byTable.users);

Send browser changes to server

You can send db.log.write (or db.log.changes) from browser to server, then apply those SQL statements to a server-side SQLite file.

Client (browser):

await fetch('/api/sql-sync', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ writes: db.log.write })
});

Server (Node.js sketch):

app.post('/api/sql-sync', express.json(), (req, res) => {
  const writes = Array.isArray(req.body?.writes) ? req.body.writes : [];
  // Validate/authorize before applying.
  for (const sql of writes) serverDb.exec(sql);
  res.json({ applied: writes.length });
});

Recommended production hardening:

• validate allowed SQL operations/tables
• apply in transaction
• include auth and conflict/version checks

Exports

import {
  Db,
  Schema,
  Model,
  Query,
  QueryAggregate,
  RebuildTable
} from 'als-sqlite';

Db, model registry, and migrations

Each DB connection keeps its own model registry:

• db.models - models for this DB only
• db.migrateAll({ reset?: boolean }) (or db.migrateAll(true|false)) - migrate all models for this DB (including auto many-to-many pivots)
• db.optimize({ dedupeIndexes?: boolean, vacuum?: boolean }) - removes duplicate indexes and optionally runs VACUUM

db.model(tableName, schema, options?) is the primary way to create models. Model.model(...) is still available for compatibility.

Important nuance:

• Db is cached by dbName/path (singleton-like per path inside one process)

Schema (Mongoose-like)

Field definition styles

const postSchema = new Schema({
  title: { type: String, required: true },
  likes: { type: Number, default: 0, min: 0 },
  published: { type: Boolean, default: true },
  tags: [String],
  meta: { type: Object, default: () => ({}) }
});

Supported styles:

• field: String | Number | Boolean | Date | Object | Array
• field: { type, required, default, validate, enum, min, max, unique, trim, lowercase, uppercase }
• field: [String]
• field: { type: childSchema }
• nested object shorthand: field: { sub: String, ... }

Relations

Many-to-one (Mongoose-like ref):

userId: { ref: 'users', required: true }

For linked schemas, delete behavior is automatic:

• ref + required: true -> ON DELETE CASCADE
• ref + required: false -> ON DELETE SET NULL

Many relation via array ref:

videos: [{ ref: 'videos' }]

Behavior of reciprocal array refs:

• stored on the document as a JSON array of related ids
• default relation keys are id <-> id
• localField / foreignField override the default keys when needed
• save() / create() / updateOne() / updateMany() sync both document arrays
• the internal pivot table is synced automatically and used for populate() / aggregate relation queries

You can also declare the same relations with schema helpers:

const videoSchema = new Schema({
  title: String
}, { timestamps: false });

videoSchema.belongsTo('segment_id', 'segments', { required: true, index: true });
videoSchema.hasMany('clips', 'clips', { localField: 'id', foreignField: 'video_id' });
videoSchema.hasOne('thumbnail', 'thumbnails', { localField: 'id', foreignField: 'video_id' });

Helpers are just sugar over the existing relation fields:

• belongsTo(path, table, options) -> stored foreign key column with ref
• hasOne(path, table, options) -> virtual single relation
• hasMany(path, table, options) -> virtual many relation

Useful options:

• localField
• foreignField
• required
• index
• joinType
• as

Practical relation example

Use belongsTo and hasMany for a normal one-to-many relation:

• belongsTo goes on the child model, where the foreign key column is stored
• hasMany goes on the parent model, so you can populate() the child list

Example: one segment has many videos, and each video stores segment_id.

const segmentSchema = new Schema({
  fnName: { type: String, required: true }
}, { timestamps: false });

segmentSchema.hasMany('videos', 'videos', {
  localField: 'id',
  foreignField: 'segment_id'
});

const videoSchema = new Schema({
  title: { type: String, required: true }
}, { timestamps: false });

videoSchema.belongsTo('segment_id', 'segments', {
  required: true,
  index: true
});

Meaning in plain English:

• video belongsTo segment -> each video has one segment
• segment hasMany videos -> one segment can have many videos

Typical queries:

Segment.find()
  .populate('videos')
  .all();

Video.find()
  .populate('segment_id')
  .where({ 'segment_id.fnName': 'anova' })
  .all();

Important behavior difference:

• belongsTo / single relation filters can be pushed into SQL join mode
• hasMany / many relation filters run after populate(), in memory

Example of in-memory filtering on a populated many relation:

Segment.find()
  .populate('videos')
  .where({ 'videos.score': { $gte: 10 } })
  .all();

Automatic many-to-many

If two models have reciprocal array refs, for example:

// clusters schema
videos: [{ ref: 'videos' }]

// videos schema
clusters: [{ ref: 'clusters' }]

library automatically:

• detects many-to-many
• stores relation arrays on both documents as JSON id lists
• syncs reciprocal document arrays on write
• creates in-memory pivot model in db.models
• migrates pivot table on db.migrateAll()
• syncs the pivot table automatically
• uses this pivot in populate() and aggregate relation queries

No manual pivot model is required.

Practical write behavior:

const anova = Cluster.create({ name: 'anova' });
const ttest = Cluster.create({ name: 'ttest' });

const video = Video.create({
  title: 'Intro',
  clusters: [anova.id, ttest.id]
});

Video.findById(video.id).one().clusters;
// [anova.id, ttest.id]

Cluster.findById(anova.id).one().videos;
// [video.id]

Updating either side keeps the other side in sync:

Video.updateOne({ id: video.id }, { clusters: [ttest.id] }).exec();

Cluster.findById(anova.id).one().videos;
// []

Cluster.findById(ttest.id).one().videos;
// [video.id]

This is different from hasMany / belongsTo:

• use hasMany + belongsTo for one-to-many
• use reciprocal array refs for many-to-many

Practical many-to-many example:

const clusterSchema = new Schema({
  name: String,
  videos: [{ ref: 'videos' }]
});

const videoSchema = new Schema({
  title: String,
  clusters: [{ ref: 'clusters' }]
});

Then just use:

Cluster.find().populate('videos').all();
Video.find().populate('clusters').all();

Schema options

Defaults:

• autoId: true
• timestamps: true
• autoIndexes: true
• autoConstraints: true

timestamps: true currently adds createdAt only.

Nested mode:

• nested: true disables auto id/timestamps/indexes/constraints

Indexes syntax (both supported):

indexes: ['A', 'B', 'views']

indexes: [{ columns: ['A'] }, { columns: ['views'], unique: true }]

Model API

Similar to Mongoose model methods:

• create, createMany, insertMany
• find, findOne, findById, where
• select, sort, limit, skip, populate
• countDocuments, distinct, exists
• updateOne, updateMany, deleteOne, deleteMany
• findOneAndUpdate, findByIdAndUpdate, findOneAndDelete, findByIdAndDelete
• query
• migrate, drop, restore, rebuildTable

Document methods:

• set, save, deleteOne, toObject, toJSON

toObject() / toJSON() recursively include nested and populated data.

Safe table sync for existing tables

When a table already exists, migrate() / migrateAll() do not diff and alter it.
Use model.rebuildTable() for safe schema sync operations on an existing table:

const result = User.rebuildTable({
  addColumns: true,
  addIndexes: true,
  rebuildForeignKeys: false,
  dropColumns: false
});

Current behavior:

• addColumns: true adds missing columns with ALTER TABLE ... ADD COLUMN when the column is safe to add
• addIndexes: true creates missing indexes from schema options
• rebuildForeignKeys: false is currently a no-op placeholder
• dropColumns: false is currently a no-op placeholder

Important:

• this method is intentionally non-destructive
• it does not rebuild the whole table
• it does not drop old columns automatically
• it skips unsafe ADD COLUMN cases such as new primary key columns or NOT NULL columns without DEFAULT

Query API and operators

Supported operators:

• $eq, $ne, $gt, $gte, $lt, $lte
• $in, $nin
• $like, $notLike
• $between
• $exists
• $and, $or, $nor, $not

QueryAggregate

QueryAggregate is a separate query mode for grouped/global SQL aggregates.

Entry points from Query / Model:

• aggregateBy(path)
• avg(fields)
• sum(fields)
• min(fields)
• max(fields)
• count(fields?)

Grouped example:

const stats = Video.find()
  .populate('segment_id')
  .aggregateBy('segment_id.fnName')
  .avg(['A', 'B', 'C', 'D'])
  .count()
  .all();

Result:

{
  anova: {
    avg: { A: 6.5, B: 7.3, C: 5.4, D: 3.8 },
    count: 42
  },
  ttest: {
    avg: { A: 5.9, B: 6.1, C: 4.8, D: 2.7 },
    count: 31
  }
}

Many-to-many example:

const stats = Video.find()
  .aggregateBy('clusters.fn_name')
  .avg(['A', 'B', 'C', 'D'])
  .all();

If clusters and videos are defined as reciprocal array refs, aggregate mode resolves the pivot table automatically. In aggregate mode, populate('clusters') is not required for this case.

Aggregate mode can also reuse normal where(...) filters, including relation paths:

const stats = Video.find()
  .where({ 'clusters.verdict': 'good' })
  .aggregateBy('clusters.fn_name')
  .avg(['A', 'B', 'C', 'D'])
  .all();

Global example without grouping:

const totals = Video.find()
  .where({ views: { $gt: 1000 } })
  .avg(['A', 'B', 'C', 'D'])
  .count()
  .all();

Result:

{
  avg: { A: 6.1, B: 7.0, C: 5.0, D: 3.2 },
  count: 120
}

Current v1 limitations:

• aggregate mode is separate from normal groupBy(...)
• join paths support base fields, joinable single relations, and reciprocal many-to-many relations
• plain hasMany / non-joinable many relations are not supported in aggregate mode
• grouped results with a single grouping key return an object keyed by that group value

Populate

Basic populate (Mongoose-like)

Post.find().populate('userId').all();

Behavior nuance:

• single ref populate uses SQL JOIN mode by default (flat SQL result columns)
• many ref populate uses fetch-and-attach mode (nested array/object on the path)

Many populate + nested filtering/sorting

Cluster.find({ verdict: 'bad' })
  .populate('videos')
  .where({ 'videos.A': { $gt: 8 } })
  .sort('-videos.views')
  .limit(10)
  .all();

For many paths (videos.*), filtering/sorting is applied after populate (in query post-processing). So filtering/sorting for those paths runs in memory after SQL load.

This means you can filter parent rows by related many records as well:

Video.find()
  .populate('segments')
  .where({ 'segments.fnName': 'anova' })
  .all();

Important nuance:

• single relation filters can be pushed into SQL join mode
• many relation filters run after populate in memory
• for large datasets, prefer real foreign keys and single relations when you need SQL-level filtering

Hooks (middleware)

Schema hooks

• pre('save')
• post('save')

Query hooks (Mongoose-like direction)

• pre/post('find')
• pre/post('findOne')
• pre/post('countDocuments')
• pre/post('distinct')
• pre/post('updateOne')
• pre/post('updateMany')
• pre/post('deleteOne')
• pre/post('deleteMany')
• pre/post('exec')

Example:

videoSchema.pre('find', (query) => {
  // custom query normalization / scope
  query.where({ active: true });
});

Model-level pre/post methods proxy to schema hooks.

Hook nuance:

• hooks are synchronous (no async/await pipeline)

Example project structure

Recommended pattern:

• project/db.js - create and export db
• project/models/*.js - schema + model + hooks per model
• project/models/index.js - export models + db + migrateAll

project/db.js example:

import { Db } from 'als-sqlite';
export const db = new Db('./app.sqlite');

Notes

• API is intentionally Mongoose-like, but this is SQLite-first.
• Some complex SQL analytics are easier with raw SQL; for common cases use Query chain.
• Auto many-to-many follows naming conventions and reciprocal array refs contract.
• Object/Array fields are stored as JSON text and parsed on read.
• Boolean is stored as INTEGER (0/1) in SQLite.

Run tests

npm test