# als-sqlite

> Works with sqlite db like you do it with mongoose.

Latest version **1.0.0** (published 2026-07-02) · ISC license · 0 weekly downloads

## Install

```sh
npm install als-sqlite
pnpm add als-sqlite
yarn add als-sqlite
bun add als-sqlite
```

## Health

**Score 55/100 (C)** — status: active.

Positive: esm support; no vulnerabilities; recently updated.

Warnings: low downloads; no types.

## Facts

| | |
|---|---|
| Version | 1.0.0 |
| Published | 2026-07-02 |
| First published | 2022-06-04 |
| Weekly downloads | 0 |
| License | ISC |
| TypeScript types | none |
| Module format | ESM + CommonJS |
| Node | >=22.5.0 |
| Dependencies | 0 |
| Unpacked size | 129.3 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| Author | Alex Sorkin[alexsorkin1980@gmail.com] |
| Maintainers | alexsorkin |
| Keywords | sqlite, orm, odm, mongoose, node-sqlite, database, sql, query-builder, schema, migrations |

## Links

- npm: https://www.npmjs.com/package/als-sqlite
- npm.io page: https://npm.io/package/als-sqlite

## Alternatives

- [mobx-react](https://npm.io/package/mobx-react.md) — 2.8M weekly downloads
- [rc-tree](https://npm.io/package/rc-tree.md) — 2.6M weekly downloads
- [@react-oauth/google](https://npm.io/package/@react-oauth/google.md) — 1.3M weekly downloads
- [@wagmi/connectors](https://npm.io/package/@wagmi/connectors.md) — 877.0K weekly downloads
- [vee-validate](https://npm.io/package/vee-validate.md) — 836.4K weekly downloads

## Recent versions

- 1.0.0 (latest) — 2026-07-02
- 0.5.2 — 2022-11-10
- 0.5.1 — 2022-10-31
- 0.5.0 — 2022-10-30
- 0.4.2 — 2022-09-30
- 0.4.1 — 2022-09-26
- 0.4.0 — 2022-09-24
- 0.3.5 — 2022-09-23
- 0.3.0 — 2022-06-07
- 0.2.0 — 2022-06-06
- 0.1.5 — 2022-06-05
- 0.1.1 — 2022-06-05
- 0.1.0 — 2022-06-05
- 0.0.1 — 2022-06-04

## README

# 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

```bash
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](#connection-nodejs--browser)), 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:

```js
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:

```js
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](#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

```js
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: 'A@MAIL.COM', 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.

```js
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()`.

```js
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:

```js
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):

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

Server (Node.js sketch):

```js
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

```js
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

```js
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`):

```js
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:

```js
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:

```js
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`.

```js
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:

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

```js
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:

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

### Automatic many-to-many

If two models have reciprocal array refs, for example:

```js
// 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:

```js
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:

```js
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:

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

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

Then just use:

```js
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):

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

```js
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:

```js
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:

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

Result:

```js
{
  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:

```js
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:

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

Global example without grouping:

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

Result:

```js
{
  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)

```js
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

```js
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:

```js
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:

```js
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:

```js
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

```bash
npm test
```

---
_Source: https://npm.io/package/als-sqlite · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._