Guides

Query rows

Filter, sort and page through records with the same grammar on every client. Dates, time variables and what the hub assumed.

query() reads one kind, a page at a time. It is the same on the app client (as the signed-in person) and the server client (as your account or key).

const { rows, next, applied } = await db.query('job', {
  where: { status: { in: ['open', 'quoted'] }, created_at: { gte: '$MONTH_BEGIN' } },
  orderBy: ['created_at', 'desc'],
  select: ['id', 'title', 'status'],
  limit: 50,
});

#Filters

{ column: value } means equals; a list means any of them (on a list column, such as tags, it means all of them). { column: { op: value } } uses an operator:

Combine with and: [...], or: [...] and not: {...}:

await db.query('contact', {
  where: { or: [{ tags: 'VIP' }, { suburb: 'Carindale' }], not: { email: { isNull: true } } },
});

A link reads as <link>_id: jobs for one customer are { customer_id: contactId }.

#Dates and time variables

A date such as '2026-09-30' compared with a timestamp means that whole day in your time zone (Australia/Sydney unless you pass tz). Time variables save working dates out:

They take an offset in their own unit: $MONTH_BEGIN-1 is the start of last month, $TODAY-7 a week ago.

#Order and pages

• One sort column, and id breaks ties. The default is created_at, newest first.
• limit is 1 to 1,000 rows, 50 by default.
• Pass next back as after for the following page, with the same orderBy. It is null on the last page.
• queryAll() walks every page for you, stopping at maxRows (10,000 by default) so a whole list is never read by accident.

for await (const person of db.queryAll('contact', { where: { tags: 'VIP' }, select: ['first_name', 'email'] })) {
  render(person.first_name, person.email);
}

#One record

const one = await app.get('job', job.id); // null when there is none this caller may read

#Counts and totals

For numbers rather than rows (how many people joined each month, the total of a number field), db.aggregate() asks the hub to do the counting. No person's details come back, only the numbers.

const joined = await db.aggregate({
  measures: [{ column: 'people', agg: 'count' }],
  time: { grain: 'month', preset: '12m' },
});

people counts people; any number field you've named under Contacts, Your fields can be summed or averaged. A range preset is one of 7d, 30d, 90d, 12m, mtd, qtd, ytd, fytd, this_month, last_month, this_fy or last_fy, or give from and to dates. It needs the account's token with Contacts read access.

#What the hub assumed

applied says what the query actually used: the order, the limit, the time zone and the date each time variable meant. Say it back when a person asked in their own words. On the server client, hidden also counts the columns left out (see Kinds and records).