Querying Views
Views are read-only — you can query them with the same filter, sort, and pagination controls as tables, but no write operations are available.
Registering Views
Use db.getView() to get a read-only AtscriptDbView instance:
import { DbSpace } from "@atscript/db";
import { ActiveTask } from "./schema/active-task.as";
const db = new DbSpace(adapterFactory);
const view = db.getView(ActiveTask);2
3
4
5
getView() returns a cached instance — calling it again with the same type returns the same view. See Setup for how to create a DbSpace.
Read Operations
AtscriptDbView provides all the read operations from AtscriptDbReadable:
findMany
Retrieve multiple records matching a query:
const tasks = await view.findMany({
filter: { projectTitle: "Website" },
controls: { $sort: { title: 1 }, $limit: 20 },
});2
3
4
findOne
Retrieve a single record:
const task = await view.findOne({
filter: { assigneeName: "Alice" },
});2
3
findManyWithCount
Retrieve records and total count in a single call — useful for pagination:
const result = await view.findManyWithCount({
filter: { status: "in_progress" },
controls: { $skip: 20, $limit: 10 },
});
console.log(result.data); // 10 records (page 3)
console.log(result.count); // total matching records2
3
4
5
6
7
count
Count records matching a query:
const total = await view.count({
filter: { status: "in_progress" },
});2
3
findById
Look up a single record by primary key (if the view has @meta.id):
const task = await view.findById(42);Filtering and Sorting
Filters apply to the view's output columns, not directly to source tables:
// Filter on a view field (even if it's aggregated)
const topCategories = await stats.findMany({
filter: { orderCount: { $gte: 100 } },
controls: { $sort: { totalRevenue: -1 } },
});2
3
4
5
- Sorting works on any view field, including aggregated fields and computed columns — a computed
rankgives a global ranking that pages stably ($sort: { rank: -1, id: 1 }) - Pagination via
$skipand$limitworks as expected - Field selection via
$selectpicks specific columns from the view output
For the full query syntax, see Queries & Filters.
Performance: Unused Joins Are Skipped
Since 0.1.153, a read of a managed view on MySQL, SQLite and MongoDB skips every left join it does not need. A count(), or a page that selects only entry-table columns, reads the entry table alone instead of probing every joined table for every row:
// vp_order_view joins customers, regions, products, notes, … — all `left`
await orders.count({ filter: { amount: { $gt: 100 } } });
// MySQL: SELECT COUNT(*) FROM (SELECT … FROM `vp_orders`) AS `vp_order_view` WHERE `amount` > ?
await orders.findMany({ filter: {}, controls: { $select: ["id", "customerName"], $limit: 20 } });
// keeps the customers join (and any join its ON clause reads), skips the rest2
3
4
5
6
The result is always the same as reading the stored view. A join is skipped only when both hold:
- Nothing in the read uses it. The read's
$select(no$select= every column), filter,$sort,$groupBy, aggregates and$havingname none of its columns — nor of a computed column built on them, nor the ON clause of another join the read keeps, nor the view's@db.view.filter. - It can never add or remove rows. It is a
leftjoin to a table (not a view) that matches at most one row:- a first-row join, or
- an ON clause that is a plain
andof=comparisons covering every field of the target's primary key or of one of its unique indexes (@db.index.unique). Each such field must be compared with a field of the same type and collation, or with a literal of its type. Extra conditions are fine — they only narrow the match.
So these joins are never skipped: inner joins, joins whose ON uses or / not, joins on a non-unique column (or on part of a composite unique key), joins to a view. Grouped views (@db.agg.*), materialized views and external views are always read as stored. On MongoDB a unique key over an optional field does not count (its index is partial — a missing value would match many documents).
Model the uniqueness you rely on. The decision is taken from the .as model, not from the database: a join on code is skippable only when code is declared @db.index.unique (and the index exists — run schema sync).
Per adapter
| Adapter | Default | How |
|---|---|---|
| MySQL | on | Reads an inline copy of the view's definition without the skipped joins, with a MERGE hint (merged even with derived_merge=off) |
| SQLite | on | Same inline definition (SQLite skips unused joins itself only outside COUNT(*) and aggregates) |
| MongoDB | on | Runs the view's pipeline on the entry collection without the skipped joins' $lookup + $unwind |
| PostgreSQL | — | Not needed: PostgreSQL removes such joins itself, through views, COUNT(*) and first-row joins included |
To read a view by name again, pass viewJoinPruning: false to the adapter (for a whole space), or set it on one view's adapter:
createAdapter(uri, { viewJoinPruning: false }); // MySQL, SQLite, MongoDB
new MysqlAdapter(driver, { viewJoinPruning: false });
new SqliteAdapter(driver, { viewJoinPruning: false });
new MongoAdapter(db, client, { viewJoinPruning: false });
db.getAdapter(OrderView).viewJoinPruning = false; // just this view2
3
4
5
6
Reads use the definition in your model
A skipped-join read runs the view definition generated from the .as model, not the one stored in the database. A view altered by hand in the database (outside schema sync) is honoured only by reads that skip nothing — keep views managed by schema sync, or turn pruning off for that view.
Such a read also reads the entry and joined tables (collections) directly: a database user granted access to the view but not to its tables needs viewJoinPruning: false.
DOs and DON'Ts
- Do declare the unique index a lookup join relies on — without it the join is always read.
- Do pass
$selecton hot list endpoints: without it every column (and so every join that feeds one) is read. - Don't expect a join used only to filter rows (an
innerjoin, or one the view filter reads) to be skipped — dropping it would change the rows. - MongoDB: a read that carries an operation-wide collation (a filter on a
@db.column.collatefield) reads the stored view.
HTTP Access
Use AsDbReadableController to expose a view as a read-only HTTP endpoint:
import { AsDbReadableController, ReadableController } from "@atscript/moost-db";
import { ActiveTask } from "./schema/active-task.as";
@ReadableController(ActiveTask, "active-tasks")
export class ActiveTaskController extends AsDbReadableController<typeof ActiveTask> {}2
3
4
5
This provides:
GET /active-tasks/query— list with filter, sort, paginationGET /active-tasks/pages— paginated results with metadataGET /active-tasks/one/:id— single record by IDGET /active-tasks/meta— view metadata
No POST, PUT, PATCH, or DELETE endpoints — views are read-only.
The same URL query syntax applies — field filters, $sort, $skip, $limit, $select. See HTTP — CRUD Endpoints for details.
Value help through reference chains
A view field is declared through its source column (projectId: Task.projectId), and the DB layer keeps that first hop — it is the column the view reads. Since 0.1.128 the /meta endpoint resolves the chain to its terminal field instead: when Task.projectId: Project.id carries @db.rel.FK, the view's projectId is serialized with ref → Project.id (shallow, refDepth stays 0.5) and db.rel.FK: true, so the client value-help picker opens the projects endpoint rather than the tasks endpoint. Fields over plain columns keep their direct ref and gain no marker.
Row actions on a view
Since 0.1.147 a view controller can list its source table's row actions with @DbActionsFrom(() => SourceController) — the actions stay the source's, the view maps its rows to source ids. See Actions on a View.
Refreshing Materialized Views
Materialized views store precomputed results that need periodic refreshing. Refresh behavior is adapter-specific:
| Adapter | Refresh method |
|---|---|
| PostgreSQL | REFRESH MATERIALIZED VIEW (native) |
| MongoDB | Re-run aggregation pipeline with $merge |
| SQLite | Not applicable (no materialized view support) |
| MySQL | Not applicable (no materialized view support) |
INFO
Materialized view refresh is currently an adapter-level operation — there is no high-level API on AtscriptDbView. Consult your adapter's documentation for refresh mechanics.
See Also: Ad-Hoc Aggregation
Views are the right tool when the aggregation shape is stable. For dynamic, query-time aggregations against a regular @db.table, use table.aggregate() — pass $groupBy and $select (with $fn: 'sum' | 'avg' | 'min' | 'max' | 'count', or a calendar bucket) inline per call. See Grouped Queries.
Next Steps
- Defining Views — how to define views in
.asfiles - View Types — managed, materialized, and external views
- Aggregation Views — views with computed aggregates
- CRUD Operations — table read operations (same API)
- HTTP — CRUD Endpoints — HTTP endpoint reference