Querying
In Akan, a database query is a named filter in
<model>.document.ts. Services and slices call it by name instead of rebuilding the same condition in every place.Building Blocks
PieceDescription
filter()
Starts one named query inside
from(cnst.Task, (filter) => …)..arg(name, Type)
A required input, such as
.arg("projectId", ID)..opt(name, Type)
An optional input. It comes after every
.arg() and is undefined when left out..query((...args, q) => …)
Gets the inputs in declared order, then
q, and returns the condition.q
The condition helper:
q.all, q.oneOf, q.between, q.when and more.sort
Named orders beside the built-in
latest, oldest and relevance. Write {} if you add none.Basic Filter
Start from the list your screen needs. A project page, for example, shows the tasks of one project that are not archived.
1. Declare It In document.ts
Add the filter to the model's filter class:
apps/myapp/lib/task/task.document.ts
2. Name It In The Dictionary
Give the filter and each of its arguments an
[en, ko] label. A missing entry is a type error:apps/myapp/lib/task/task.dictionary.ts
3. Call It By Name
Each filter becomes a set of methods on the model and the service, named after it:
apps/myapp/lib/task/task.service.ts
The methods you will reach for most:
MethodReturns
listInProject
Every match. A last argument
{ sort, skip, limit } pages it.findInProject
The first match, or
null.pickInProject
The first match. Throws when there is none.
countInProject
How many documents match.
existsInProject
The id of one match, or
null.queryInProject
The condition itself, not yet run. A slice's
exec returns this.- Eight more follow the same naming:
listIds,findId,pickId,insight, and the query-level writesremove,removeOne,updateandupdateOne, which run no hooks. - A service call has no page size. Without
limit,listInProject()returns every match, so pass one for lists that grow. sortnames a sort key. Uselatest,oldestor a key from the filter'ssortmap; an unknown key is refused.
Optional Conditions
An optional input should add its condition only when the user actually picked something.
q.when(condition, query) adds query when condition is truthy, and nothing otherwise:apps/myapp/lib/task/task.document.ts
q.whenbuilds its query even when the condition is false.q.oneOf(undefined)throws, which is why the snippet passesassigneeIds ?? [].- An
undefinedvalue throws.{ assignee: assigneeId }with noassigneeIdis refused, so wrap it inq.when. q.oneOf([])matches nothing. Checking?.length, not just presence, keeps an empty pick from emptying the list.
“Has no value” is
q.empty, never q.missing. q.missing means the key is absent from the stored JSON. A document read and saved again gets an explicit null from the read, so the key is there from then on. Use q.missing only to find rows written before the field was declared.Range And OR
Use
q.between for a period and q.any for OR. Date dashboards and status boards stay readable:apps/myapp/lib/task/task.document.ts
- Both ends are included.
q.between(from, to)compiles to>= from AND <= to. - Pass dates as they arrive. A
Dateargument reaches the query as aDayjs, and dates are compared as epoch milliseconds. - One object with several keys is already AND.
{ project: projectId, status: "done" }needs noq.all.
Raw Query
Reach for
q.raw only when no helper can express the condition. Keep it one small SQL fragment, and pass every value as a parameter:apps/myapp/lib/post/post.document.ts
- Values go in the array, never in the string. Each
?binds the next value, so user input never becomes SQL. - One fragment, one condition. It is wrapped in parentheses and joined like any other condition, and a fragment containing
;is refused. - Write it for your database. The snippet is SQLite. Postgres keeps
_docasjsonband reads a field as text, so the same condition is("_doc" #>> '{score}')::numeric > ?.
A raw fragment is not translated between databases. An app that runs on both SQLite and Postgres avoids
q.raw, or writes the fragment per database.How It Becomes SQL
Akan keeps a model's fields in one JSON column,
_doc, and compiles a filter object into a SQL WHERE clause. You write in the document's shape; the database adaptor writes the SQL.Where A Field Lives
ColumnDescription
_doc
A JSON column holding every declared field; SQLite reads one with
json_extract(_doc, '$.field').idcreatedAtupdatedAtremovedAt
Four real columns, compared directly:
"updatedAt" >= ?.- Removed documents never match. Every read and query-level write adds
"removedAt" IS NULL, and a condition only a removed row can meet throwscan never match. To read removed rows, pass{ withRemoved: true }. - The SQL below is simplified SQLite. Postgres compiles the same filter to
jsonboperators such as_doc #> '{status}'. - Values stay parameters. Every
?is bound separately, so user input is never pasted into the SQL text.
Compare Values
HelperMeaning and SQL
plain value
Equals. Several keys in one object are joined with AND.
q.eq
Equals, spelled out. Same as a plain value.
q.ne
Not equal.
q.oneOf
Equals any value in the list. An empty list matches nothing.
q.notOneOf
Equals none of the values. An empty list matches everything.
q.gt
Greater than.
q.gte
Greater than or equal to.
q.lt
Less than.
q.lte
Less than or equal to.
q.between
Inside a range, both ends included.
Presence
HelperMeaning and SQL
q.exists
The key is in the stored JSON, even when it holds
null.q.missing
The key is absent from the stored JSON. Use it only for rows older than the field.
q.empty
Has no value: the key is absent or holds
null.Arrays And Text
HelperMeaning and SQL
q.has
The array field contains the value.
array field
On an array field, a plain value or
q.oneOf also checks the items.q.contains
The text includes the value, bound as
%release%.q.search
Full-text search over
text-role fields, compiled to a JOIN. Works in every database mode.Combine Conditions
HelperMeaning and SQL
q.all
Every condition holds.
null, undefined and false entries are skipped.q.any
At least one condition holds.
q.not
The condition does not hold.
q.when
Adds the query when the condition is truthy, and nothing when it is falsy.
Paths And Raw SQL
HelperMeaning and SQL
nested path
A dotted key reaches into a nested object.
base column
id, createdAt, updatedAt and removedAt are compared as real columns.q.raw
Your own SQL fragment, wrapped in parentheses; write it in your database's dialect.
Why A JSON Document?
Lighter Schema Changes
Adding a small field usually needs no table migration, so product code moves faster.
Query-First Design
Data read together is stored together, which saves extra joins and service glue code.
Natural Nested Shapes
Settings, histories, options and snapshots keep their shape, and important paths stay filterable.
Index Only What Gets Hot
Denormalize on purpose for list and detail screens, then index only the paths that carry traffic.
Query Habits
Four habits keep filters easy to find and fast to run:
- Name filters with a preposition.
inProject,inPeriodandbyStatusessay what the list is scoped to; nevergetXInYorlistX. - Keep query building out of pages. Pages and services call the filter by name, so each condition lives in one place.
- Prefer helpers to raw SQL. Helpers work on both SQLite and Postgres and bind every value for you.
q.containsreads every row. It is aLIKE '%…%'scan that no index can serve; a search box wantsq.search.
Index The Hot Paths
When a filter becomes a busy traffic path, index the fields it compares in the model's
_onSchema:apps/myapp/lib/task/task.document.ts
- The index is built on the expression the filter compiles to. On SQLite,
schema.index({ project: 1 })indexesjson_extract(_doc, '$.project'), which{ project }then uses; Postgres indexes its own form of the same expression. - Sort keys are indexed for you. Each order in the filter's
sortmap gets an index together withremovedAt; the fields you filter on do not.