Queries

Read, filter, sort, page, count, aggregate, search and write many rows from server code.

Each model has its queries: Product.findMany, Product.create… They take an object of arguments, then { as }, the caller whose access rules apply. The rows come back typed from the arguments: a select returns the fields it names, no more.

import { testApplication } from '@ultimabase/testing'
import { master } from 'ultimabase'
import { beforeEach, expect, it } from 'vitest'
import { app } from './app.ts'
import { Product } from './models.ts'

testApplication(app)

const as = { as: master }
const names = (rows: readonly { readonly name: string }[]): string[] =>
	rows.map((row) => row.name)

beforeEach(async () => {
	// One write of every row, which returns { count: 4 }.
	await Product.createMany(
		{
			data: [
				{
					name: 'Desk lamp',
					sku: 'LMP-001',
					price: '39.90',
					stock: 5,
					status: 'published',
					keywords: ['light', 'office'],
					releasedOn: '2026-03-01',
				},
				{
					name: 'Floor lamp',
					sku: 'LMP-002',
					price: '89.00',
					status: 'published',
					keywords: ['light'],
					releasedOn: '2026-05-10',
				},
				{
					name: 'Mug',
					sku: 'MUG-001',
					price: '12.00',
					stock: 40,
					keywords: ['kitchen'],
				},
				{
					name: 'Teapot',
					sku: 'TEA-001',
					price: '24.50',
					stock: 12,
					status: 'archived',
					keywords: ['kitchen'],
				},
			],
		},
		as,
	)
})

The full example, with its tests: examples/models-and-queries.

The examples run as Master, which every rule allows; a function or a hook passes its own caller. See Access rules. The transports take the same arguments: see GraphQL and REST.

Filter

where keeps the rows that match every field it names. A value compares for equality; an object holds operators:

const published = await Product.findMany(
	{ where: { status: 'published', price: { lt: '50.00' } } },
	as,
)
expect(names(published)).toEqual(['Desk lamp'])

const filtered = await Product.findMany(
	{
		where: {
			name: { contains: 'LAMP', mode: 'insensitive' },
			releasedOn: { gte: '2026-04-01' },
		},
	},
	as,
)
expect(names(filtered)).toEqual(['Floor lamp'])

// A list: has, hasSome, hasEvery, isEmpty.
const kitchen = await Product.findMany(
	{ where: { keywords: { has: 'kitchen' }, stock: { gt: 20 } } },
	as,
)
expect(names(kitchen)).toEqual(['Mug'])

// AND, OR and NOT combine filters; null matches a missing value.
const either = await Product.findMany(
	{
		where: {
			OR: [{ stock: 0 }, { releasedOn: null }],
			NOT: { status: 'archived' },
		},
	},
	as,
)
expect(names(either)).toEqual(['Floor lamp', 'Mug'])

A field left undefined filters nothing, so an optional argument can pass straight through. A relation filters by its target's fields: see Relations.

Sort and page

orderBy takes a field and 'asc' or 'desc', or a list of them, the first deciding. Without it, rows come in id order, which is the order they were created in. skip leaves rows out, take keeps at most that many:

const cheapest = await Product.findMany(
	{ orderBy: { price: 'asc' }, skip: 1, take: 2 },
	as,
)
expect(names(cheapest)).toEqual(['Teapot', 'Desk lamp'])

// An optional field's nulls sort last unless `nulls` says otherwise.
const byRelease = await Product.findMany(
	{
		orderBy: [{ releasedOn: { sort: 'desc', nulls: 'last' } }, { name: 'asc' }],
	},
	as,
)
expect(names(byRelease)).toEqual(['Floor lamp', 'Desk lamp', 'Mug', 'Teapot'])

A cursor names a unique row the page starts at: skip: 1 starts after it, and a negative take reads the rows before it.

// The next page starts after the last row of this one.
const first = await Product.findMany({ orderBy: { name: 'asc' }, take: 2 }, as)
const last = first.at(-1)
if (last === undefined) throw new Error('The first page is empty.')
const next = await Product.findMany(
	{
		orderBy: { name: 'asc' },
		cursor: { id: last.id },
		skip: 1,
		take: 2,
	},
	as,
)
expect(names(next)).toEqual(['Mug', 'Teapot'])

Find one row

findUnique takes a unique where: the id, a unique field, or the fields of a unique index together. findFirst takes any where and returns the first row in its order. Both return null when nothing matches; their OrThrow forms fail with NOT_FOUND.

const mug = await Product.findUnique({ where: { sku: 'MUG-001' } }, as)
expect(mug?.price).toBe('12.00')
// A unique index's fields together find a row too.
const lamp = await Product.findUnique(
	{ where: { name: 'Desk lamp', releasedOn: '2026-03-01' } },
	as,
)
expect(lamp?.sku).toBe('LMP-001')
expect(await Product.findFirst({ where: { stock: 99 } }, as)).toBeNull()
await expect(
	Product.findUniqueOrThrow({ where: { sku: 'CUP-001' } }, as),
).rejects.toMatchObject({ code: 'NOT_FOUND', message: 'No Product matches.' })

Choose the fields

select returns the fields it names; omit returns all but those. A read returns every stored field by default, relations only when include or select names them.

const rows = await Product.findMany(
	{ where: { status: 'published' }, select: { name: true, price: true } },
	as,
)
expect(rows).toEqual([
	{ name: 'Desk lamp', price: '39.90' },
	{ name: 'Floor lamp', price: '89.00' },
])
const mug = await Product.findUniqueOrThrow(
	{ where: { sku: 'MUG-001' }, omit: { specs: true, keywords: true } },
	as,
)
expect(mug).not.toHaveProperty('specs')

_search keeps the rows whose search: true fields hold the words, and orderBy: { _relevance: 'desc' } puts the best matches first:

// Words, "quoted phrases", `or`, and -word to leave one out.
const lamps = await Product.findMany(
	{
		where: { _search: 'lamp -floor' },
		orderBy: { _relevance: 'desc' },
	},
	as,
)
expect(names(lamps)).toEqual(['Desk lamp'])

Words are lowered, without stems: lamps does not find lamp. postgres({ searchLanguage: 'english' }) stems them with PostgreSQL's configuration of that name.

Count and aggregate

count counts the rows of a where. aggregate computes _count, _sum, _avg, _min and _max of the rows the caller reads:

expect(await Product.count({ where: { stock: { gt: 0 } } }, as)).toBe(3)

const totals = await Product.aggregate(
	{
		_count: true,
		_sum: { stock: true, price: true },
		_min: { price: true },
		_max: { releasedOn: true },
	},
	as,
)
expect(totals).toEqual({
	_count: 4,
	// A decimal's sum is a decimal string.
	_sum: { stock: 57, price: '165.40' },
	_min: { price: '12.00' },
	_max: { releasedOn: '2026-05-10' },
})

groupBy computes them per group of by's values; having keeps the groups whose values or aggregates match:

const byStatus = await Product.groupBy(
	{
		by: ['status'],
		_count: true,
		_sum: { stock: true },
		having: { stock: { _sum: { gt: 0 } } },
		orderBy: { status: 'asc' },
	},
	as,
)
expect(byStatus).toEqual([
	{ status: 'archived', _count: 1, _sum: { stock: 12 } },
	{ status: 'draft', _count: 1, _sum: { stock: 40 } },
	{ status: 'published', _count: 2, _sum: { stock: 5 } },
])

distinct keeps, of the rows with the same values, the first in the read's order:

// The first row of each status, in the read's order.
const priciest = await Product.findMany(
	{
		distinct: ['status'],
		orderBy: { price: 'desc' },
		select: { status: true, name: true },
	},
	as,
)
expect(priciest).toEqual([
	{ status: 'published', name: 'Floor lamp' },
	{ status: 'archived', name: 'Teapot' },
	{ status: 'draft', name: 'Mug' },
])

aggregate and groupBy are for server code; the transports do not expose them. A field with its own read rule is aggregated by Master only.

Write many rows

createMany, updateMany and deleteMany return { count }; each writes all its rows or none. createManyAndReturn and updateManyAndReturn return the rows instead. upsert updates the row its unique where finds, or creates it:

const { count } = await Product.updateMany(
	{
		where: { keywords: { has: 'light' } },
		data: { stock: { increment: 10 } },
	},
	as,
)
expect(count).toBe(2)

const archived = await Product.deleteMany({ where: { status: 'archived' } }, as)
expect(archived.count).toBe(1)

// Updates the row the unique `where` finds, or creates it.
const cup = await Product.upsert(
	{
		where: { sku: 'CUP-001' },
		create: { name: 'Cup', sku: 'CUP-001', price: '8.00' },
		update: { stock: { increment: 1 } },
	},
	as,
)
expect(cup.stock).toBe(0)

updateMany writes no relation, and createMany no nested row. Each row's access rules and hooks run as for one write. To write several models together, see Transactions.

Reference

Queries

QueryArgumentsReturns
findManywhere, orderBy, skip, take, cursor, distinctrows
findFirst, findFirstOrThrowwhere, orderBy, skip, take, cursora row, or null
findUnique, findUniqueOrThrowwhere (unique)a row, or null
countwherenumber
aggregatewhere, _count, _sum, _avg, _min, _maxthe aggregates
groupByby, the aggregates, where, having, orderBy, skip, takegroups
createdatathe row
updatewhere (unique), datathe row
upsertwhere (unique), create, updatethe row
deletewhere (unique)the row deleted
createManydata, a row or a list{ count }
updateManywhere, data{ count }
deleteManywhere{ count }
createManyAndReturndatarows
updateManyAndReturnwhere, datarows, in id order

Every query that returns rows also takes select, or include and omit. The second argument is { as }: master, a function's or a hook's caller, or in a test asUser({ id }).

Filters

FieldOperators
every fielda value, equals, not, in, notIn; null for a missing value
stringcontains, startsWith, endsWith; mode: 'insensitive'
number, dateTime, decimal, datelt, lte, gt, gte
listhas, hasSome, hasEvery, isEmpty
jsonequals of the whole value, or path with equals, lt… stringContains, arrayContains
one relationis, isNot
many relationsome, every, none
the rowAND, OR, NOT, _search

null never compares: { stock: { lt: 10 } } and its NOT both leave out a row without a stock. A contains filter on a large table needs an index with contains: true: see Models and fields.

Sorts

SortEffect
{ field: 'asc' | 'desc' }By a stored field that is not a list or json.
{ field: { sort, nulls } }An optional field, its nulls 'first' or 'last'; last ascending by default.
{ relation: { field: 'asc' } }A one relation by its target's fields.
{ relation: { _count: 'desc' } }A many relation by its number of rows.
{ _relevance: 'desc' }With _search, by how well each row matches.

Errors

CodeWhen
INVALID_QUERYAn unknown field or operator, a value of the wrong type, select with include or omit. Its path names the argument: where.titel.
NOT_FOUNDfindFirstOrThrow, findUniqueOrThrow, update or delete finds no row: No Product matches.

A client's query refused as INVALID_QUERY answers BAD_USER_INPUT, a 400, with the same message and the path in its details.

Limits

  • skip and take are whole numbers; distinct takes no cursor.
  • aggregate takes the fields a sort takes; groupBy and distinct take them and the one relations.

Models and fields

Declare a model, its fields of each type, enums, indexes and virtual fields.

Relations

Link models with `one` and `many`, write related rows in one call, read and filter through them, and choose what deleting a row does.

On this page

FilterSort and pageFind one rowChoose the fieldsSearchCount and aggregateWrite many rowsReferenceQueriesFiltersSortsErrorsLimits