Query Language
MasterRecord queries read like JavaScript. You filter with arrow functions and bind values through $$ placeholders, which are always parameterized — so there is no string concatenation and no injection risk. If you know LINQ-to-Entities, the shape will feel familiar; the EF Core equivalent is named wherever one exists.
$$ (or ctx.$$ for lint-clean TypeScript) rather than closures, and why a string form like 'u => u.id == $$' works identically.Creating & saving#
// Active Record style — the entity saves itself
const user = db.User.new();
user.name = 'Teela';
user.email = 'teela@eternia.dev';
await user.save();
// Entity Framework style — change-tracked context
const u = db.User.new();
u.name = 'Adam';
await db.saveChanges();
// Context-level add/remove (EF DbContext.Add / Remove)
db.add(u); db.remove(u); db.addRange([a, b]);Reading#
Filtering with where#
// One value bound through $$
const alice = await db.User.where((u) => u.email == $$, 'alice@example.com').single();
// Combine conditions with .and()
const admins = await db.User
.where((u) => u.active == true)
.and((u) => u.role == $$, 'admin')
.toList();
// By primary key
const one = await db.User.find(1); // EF Find(): identity map first — no query if already tracked
const two = await db.User.findById(2); // always queriesLIKE and IN#
// LIKE
const johns = await db.User.where((u) => u.name.like($$), 'John%').toList();
// IN — string form
const some = await db.User.where((u) => u.id.any($$), '1,2,3').toList();
// IN — array form
const ids = [1, 2, 3];
const more = await db.User.where((u) => $$.includes(u.id), ids).toList();Executing a query#
| Method | Returns | EF Core |
|---|---|---|
.toList() | An array of all matching entities. | ToListAsync() |
.single() | Exactly one entity (or null). | SingleOrDefaultAsync() |
.first() / .last() | First / last by primary key unless you ordered. | FirstOrDefaultAsync() |
.find(pk) / .findById(pk) | One entity by key (composite: find(a, b)). | FindAsync() |
.count() | Row count (honours filters). | CountAsync() |
.any(pred?, …args) / .exists() | Boolean. | AnyAsync() |
.sum(f) / .avg(f) / .min(f) / .max(f) | Aggregate (sum of no rows is 0, the others null). | SumAsync() … |
.pluck(field) | Array of one column (SELECT field, untracked). | Select(x => x.F) |
.toObjectList(opts) | Plain objects (DTOs), untracked. | projection |
await the terminal call — every engine is async and awaiting keeps your code portable across SQLite, MySQL and PostgreSQL.Ordering, pagination & counting#
const page = await db.Post
.where((p) => p.published == true)
.orderByDescending((p) => p.created_at) // EF OrderByDescending
.thenBy((p) => p.title) // EF ThenBy / thenByDescending
.skip(20)
.take(10)
.toList();
const total = await db.Post.where((p) => p.published == true).count();
const views = await db.Post.sum('views');
const cats = await db.Post.select('p => p.category').distinct().toList(); // SELECT DISTINCTgroupBy().aggregate()#
EF Core’s GroupBy(x => x.Status).Select(g => new { g.Key, g.Count(), g.Sum(…) }): group the filtered rows by one or more columns, then project aggregates. Translates to SELECT … GROUP BY … [HAVING …] [ORDER BY …] [LIMIT/OFFSET] on every engine.
const byStatus = await db.Order
.where((o) => o.year == $$, 2026)
.groupBy('o => o.status') // or groupBy('status', 'region')
.aggregate(
{ n: 'count', total: ['sum', 'amount'], avg: ['avg', 'amount'] }, // count | sum | avg | min | max
{ having: { n: ['>', 1] }, orderBy: [['total', 'desc']] } // ==, !=, >, >=, <, <=
);
// → [{ status: 'paid', n: 12, total: 980, avg: 81.6 }, ...]
// take()/skip() page the GROUPS; global query filters apply (ignoreQueryFilters() lifts them)
const top = await db.Order.groupBy('region').take(1).aggregate({ total: ['sum', 'amount'] }, { orderBy: [['total', 'desc']] });Including relationships#
const post = await db.Post
.where((p) => p.id == $$, id)
.include('User')
.include('Tags')
.single();
console.log(post.User.name, post.Tags.length);
// nested levels and split queries
const posts = await db.Post.asSplitQuery().include('Tags').thenInclude('Category').toList();Eager, explicit and lazy loading, thenInclude() and asSplitQuery() are covered in depth on Loading Related Data.
Tracking: asNoTracking / asTracking#
Queries track their results by default so that edits persist with saveChanges(). For read-only work use asNoTracking() (EF AsNoTracking()) — nothing is retained and mutations are not saved:
const feed = await db.Post.asNoTracking().orderByDescending((p) => p.id).take(50).toList();
// context-wide default + opt back in per query
db.setQueryTrackingBehavior('no-track');
const editable = await db.Post.asTracking().where((p) => p.id == $$, id).single();See Change Tracking & Context Lifetime.
Global query filters#
EF Core’s HasQueryFilter (named filters as in EF 10): a where-lambda appended to every query on an entity — soft delete and multi-tenancy without repeating the predicate. Filters apply to toList/single/first/findById/count/exists, to executeUpdate/executeDelete, to groupBy().aggregate() and inside include().
// AppContext.js
this.dbset(Blog)
.queryFilter('softDelete', 'b => b.deletedAt == null')
.queryFilter('tenant', 'b => b.tenantId == $$', (ctx) => ctx.tenantId); // arg evaluated per query
// or: this.queryFilter('Blog', 'softDelete', 'b => b.deletedAt == null');
await db.Blog.toList(); // filtered
await db.Blog.ignoreQueryFilters().toList(); // all rows (EF IgnoreQueryFilters)
await db.Blog.ignoreQueryFilters(['softDelete']).toList(); // by name (EF 10)
db.removeQueryFilter('Blog', 'tenant');Updating & deleting#
// Update
const u = await db.User.where((x) => x.id == $$, id).single();
u.name = 'He-Man';
await u.save();
// Delete (Active Record)
await u.delete();
// Delete (context)
db.User.remove(u);
await db.saveChanges();Set-based writes: executeUpdate / executeDelete#
EF Core’s ExecuteUpdate / ExecuteDelete: one SQL statement over the rows the query selects, bypassing the change tracker, executed immediately, returning rows affected.
import { sql } from 'masterrecord/sql'; // or masterrecord.sql
const n = await db.Blog
.where((b) => b.rating < $$, 3)
.executeUpdate({ hidden: true, views: sql`views + 1` }); // EF SetProperty(b => b.Views, b => b.Views + 1)
const gone = await db.Session.where((s) => s.expires_at < $$, Date.now()).executeDelete();sql tag refuses interpolations so fragments cannot smuggle values. Tracked instances are not refreshed (EF caveat) — avoid mixing with pending tracked edits to the same rows. Inside db.transaction() they join the transaction. include() and raw() are rejected; the primary key cannot be updated.Raw SQL escape hatch#
For SQL the builder cannot express, db.query(sql, params) (alias db.execute) runs a statement on any engine and returns an array of rows. Placeholders are engine-native (? on SQLite/MySQL, $1 on Postgres).
const rows = await db.query('SELECT u.name, COUNT(p.id) AS posts FROM User u LEFT JOIN Post p ON p.user_id = u.id GROUP BY u.id HAVING COUNT(p.id) > ?', [5]);
await db.execute('UPDATE Step SET run_id = ? WHERE id = ?', ['run_x', 1]);What is not supported#
join()/leftJoin()— LINQ-style joins are unsupported by design.include()/thenInclude()load relationships; usedb.query()for a hand-written JOIN. Calling them throws a clear error.- JavaScript inside lambdas —
.includes()is rewritten toany(), but other array/string methods and closure variables are not translated; bind values through$$. include()parameters (filtered include) — use a global query filter on the target entity.