#Queries and updates

A filter describes the documents you want. Silt compiles that filter to SQL, so SQLite selects the result before documents reach your application.

The examples on this page share the following setup. Use a temporary in-memory database to experiment; replace ':memory:' with a filename when you want persistence on Node.

import { openDatabase } from '@silt-db/sqlite';

const db = await openDatabase(':memory:');
const tasks = db.collection('tasks');

await tasks.insertMany([
  {
    _id: 't1',
    title: 'Publish the guide',
    done: false,
    priority: 3,
    owner: { team: 'docs' },
    labels: ['release', 'writing'],
    checklist: [{ text: 'Review examples', done: false }],
  },
  {
    _id: 't2',
    title: 'Review the API',
    done: false,
    priority: 2,
    owner: { team: 'core' },
    labels: ['release'],
    checklist: [{ text: 'Run tests', done: true }],
  },
  {
    _id: 't3',
    title: 'Choose a name',
    done: true,
    priority: 1,
    owner: { team: 'core' },
    labels: [],
    checklist: [],
    reminder: null,
  },
]);

#Find and shape results

Ordinary filter fields combine with AND. $gte means “greater than or equal to”; $in matches any listed value. Use dotted paths to address nested fields.

const releaseTasks = await tasks
  .find({ done: false, priority: { $gte: 2 }, labels: 'release' })
  .sort({ priority: -1, _id: 1 })
  .project({ _id: 0, title: 1, priority: 1 })
  .limit(10)
  .toArray();

console.log(releaseTasks);
// [
//   { title: 'Publish the guide', priority: 3 },
//   { title: 'Review the API', priority: 2 }
// ]

const coreTasks = await tasks
  .find({ 'owner.team': 'core', priority: { $in: [1, 2] } })
  .sort({ _id: 1 })
  .toArray();

const oneTask = await tasks.findOne({ _id: 't1' });
const missingTask = await tasks.findOne({ _id: 'does-not-exist' });
console.log(coreTasks.length, oneTask.title, missingTask);
// 2, 'Publish the guide', null

Sort direction 1 is ascending and -1 is descending. Include a unique key such as _id when you need stable ordering for tied values. .skip(n) and .limit(n) support offset pagination; a cursor limit of zero means no limit.

An inclusion projection keeps named fields and includes _id by default. Set _id: 0 to omit it. An exclusion projection such as { checklist: 0 } removes fields. Do not mix inclusion and exclusion, except for excluding _id.

Use $or when either condition should match:

const selected = await tasks
  .find({ $or: [{ 'owner.team': 'docs' }, { done: true }] })
  .sort({ _id: 1 })
  .toArray();
console.log(selected.map((task) => task._id)); // ['t1', 't3']

The final .map() only formats the already selected result for display. Selection happens in SQLite.

#Arrays, missing fields, and null

A scalar filter on an array field matches a contained value: { labels: 'release' } matches the first two tasks. Use $elemMatch when multiple conditions must hold for the same array element.

const needsReview = await tasks
  .find({ checklist: { $elemMatch: { text: 'Review examples', done: false } } })
  .toArray();
console.log(needsReview.length); // 1

const noLabels = await tasks.find({ labels: { $size: 0 } }).toArray();
console.log(noLabels.length); // 1

console.log(await tasks.countDocuments({ reminder: null })); // 3
console.log(await tasks.countDocuments({ reminder: { $exists: false } })); // 2
console.log(await tasks.countDocuments({ reminder: { $type: 'null' } })); // 1

As in MongoDB, { reminder: null } matches an explicit null or a missing field. $exists: false selects missing fields, while $type: 'null' selects explicit null values in this example. Array traversal has additional rules; consult the compatibility reference for edge cases.

#Read large results incrementally

find() is lazy. It executes when you consume the cursor. .toArray() collects the full requested result; asynchronous iteration lets your application process documents as they are returned.

for await (const task of tasks.find({ done: false }).sort({ _id: 1 })) {
  console.log(task.title);
}

console.log(await tasks.countDocuments({ done: false })); // 2
console.log(await tasks.distinct('owner.team')); // 'docs' and 'core'; order unspecified

Counts and distinct-value gathering also run in SQL. Breaking an iteration releases its active statement. Finish a cursor before writing through the same better-sqlite3 connection: its native driver does not allow writes while that statement is active. A cursor executes again each time it is consumed; it is not a cached result.

#Update and delete documents

Use update operators to change selected fields. $set assigns a value and $inc adds to a numeric field. Silt compiles document selection and the update expression to SQL.

const changed = await tasks.updateOne(
  { _id: 't1' },
  { $set: { done: true }, $inc: { priority: 1 } },
);
console.log(changed.matchedCount, changed.modifiedCount); // 1, 1

await tasks.updateMany({ done: false }, { $addToSet: { labels: 'next' } });

const removed = await tasks.deleteOne({ _id: 't3' });
console.log(removed.deletedCount); // 1

replaceOne() replaces a complete document while preserving its existing _id if you omit that field. { upsert: true } on an update or replacement inserts a document when the filter matches none. _id is immutable, and inputs are validated as plain JSON.

insertMany() and multi-document updates are atomic in Silt: one invalid document rolls back the batch. This differs from MongoDB's default batch behavior. See transactions when multiple operations must commit together, and the database API for supported options and result shapes.

Close the shared example database after trying the snippets:

await db.close();

Next, use aggregations and joins to calculate reports without collecting and processing the source documents in JavaScript.