Relational queries design

Overview

123456789101112131415const threads = useQuery({
	collection: "threads",
	select: { id: true, title: true, status: true, updatedAt: true },
	where: { status: "active" },
	with: {
		messages: {
			select: { id: true, body: true, createdAt: true },
			orderBy: { createdAt: "desc" },
			limit: 1,
		},
	},
	orderBy: { updatedAt: "desc" },
	limit: 50,
	offset: 0,
})

12const threads = client.query(query)
const subscription = client.subscribe(query, callback)

Design goals

• Query a collection with useQuery({ collection: "collection", select, where, with, orderBy, limit, offset }).
• Include many-to-one and one-to-many relations through with using the same nested options shape as the root query.
• Keep the public query shape JSON-serializable so it can be encoded for scan windows and remote sync.
• Preserve relation cardinality in results: many-to-one returns object/null, one-to-many returns array.
• Make the final API type-safe against schema fields, relation names, relation targets, and selected result shape.

Non-goals for the first relational design

• No migrations or backfills.
• No many-to-many, through, polymorphic, aggregate, OR, NOT, full-text, or computed-field query syntax.
• No live nested collection handles; nested relation results are snapshots embedded in parent rows.
• No compatibility requirement for the old fluent QueryBuilder public API. Tandem is unreleased, so the object query shape should become canonical.

Query options

select

1select: { id: true, title: true }

• Omitted select means all scalar fields on that collection.
• Only true is allowed as a field value.
• false, exclusion maps, aliases, and computed fields are out of scope.
• Relations are never selected through select; they are included only through with.
• Tandem may read join fields internally even when omitted from select, but hidden join fields must not appear in the result unless selected.
• select: "*" is not part of the new public object API. If retained internally during migration, normalize it to omitted select before exposing docs or public types.

where

123456where: {
	status: "active",
	done: false,
	priority: { gt: 2 },
	updatedAt: { gte: since, lt: before },
}

• A bare value is equality shorthand and encodes as eq.
• Operator objects may use eq, gt, lt, gte, and lte.
• Multiple fields and multiple operators on one field are combined with AND.
• OR, NOT, in, contains, relation-field predicates, and arbitrary predicate functions are out of scope for v1.
• Nested relation where clauses target the related collection, not the parent collection.

orderBy

1orderBy: { updatedAt: "desc", id: "asc" }

• Values are only "asc" or "desc".
• Property insertion order defines sort precedence for multiple fields.
• Omitted orderBy leaves storage scan order unspecified.
• Callers that need stable pagination must provide an explicit order.
• Nested relation orderBy clauses target the related collection.

limit and offset

12limit: 50,
offset: 100,

• Omitted limit means no maximum result count.
• Omitted offset means 0.
• limit: 0 is valid and returns no rows.
• Negative, fractional, NaN, and infinite values are invalid.
• On one-to-many relation includes, limit and offset apply per parent.
• On many-to-one relation includes, limit and offset are invalid because the relation result is already at most one object.

with

123456789101112with: {
	author: true,
	messages: {
		select: { id: true, body: true, createdAt: true },
		where: { deleted: false },
		orderBy: { createdAt: "asc" },
		limit: 100,
		with: {
			author: { select: { id: true, name: true } },
		},
	},
}

• true means include the relation with all scalar fields and no nested filters, sorting, pagination, or nested relations.
• Object values accept select, where, with, orderBy, limit, and offset, scoped to the relation target collection.
• Relation objects do not use field-name shorthand like { id: true }; selected relation fields must live under select.
• Unknown relation names are invalid.
• Requested relation keys are always present in each parent result.
• Nested with recursion is part of the design, even if early execution phases limit depth for simplicity.

Relation result shape

123type PostWithAuthor = Post & {
	author: User | null
}

• The key is present when requested.
• The value is the selected related object when a matching record exists.
• The value is null when the parent join field is missing, the target record is missing, or relation-level where filters the target out.
• A many-to-one relation never returns an array.

123type ThreadWithMessages = Thread & {
	messages: Message[]
}

• The key is present when requested.
• The value is an array snapshot, not a live collection object.
• No matches produce [].
• limit: 1 still returns an array with zero or one item. It does not collapse to a single object.
• Subscription updates should eventually re-emit the parent row instead of exposing mutable nested child collections.

Type-safety requirements

• collection must be a known collection name from the client schema.
• select, where, and orderBy keys must be scalar fields on the current collection.
• where values must be assignable to the field's TypeScript type.
• Comparison operators should be restricted to comparable field types where practical.
• with keys must be relation names defined for the current collection.
• Nested with options must be scoped to the target collection of that relation.
• Result types must reflect selected scalar fields, included nested relations, and relation cardinality.

1234567891011121314const threads = useQuery({
	collection: "threads",
	select: { id: true, title: true },
	where: { status: "active" },
	with: {
		messages: {
			select: { id: true, body: true },
			limit: 1,
		},
	},
})

threads[0].id
threads[0].messages[0]?.body

1234useQuery({ collection: "threads", select: { missingField: true } })
useQuery({ collection: "threads", where: { status: 123 } })
useQuery({ collection: "threads", orderBy: { missingField: "asc" } })
useQuery({ collection: "threads", with: { missingRelation: true } })

Server authorization model

12345678910async function handleQuery(request: Request, session: Session) {
	const query = parseAndValidateQuery(await request.json())

	return db
		.withContext({
			tenantId: session.tenantId,
			userId: session.userId,
		})
		.runQuery(query)
}

• Derive tenant/user context from authenticated session state, never from client-provided query filters.
• Apply tenant scoping and RLS to every collection touched by the query, including nested relations.
• Validate query shape: collection names, field names, relation names, operators, include depth, and maximum limits.
• Enforce field-level and relation-level allowlists if some columns or relations are sensitive; RLS alone usually protects rows, not columns.
• Cap resource usage with maximum limit, maximum offset, maximum include depth, and possibly maximum relation fanout.

Comparison with inspirations

Drizzle Relations v2

1234567891011const threads = await db.query.threads.findMany({
	columns: { id: true, title: true, status: true },
	where: { status: "active" },
	with: {
		messages: {
			columns: { id: true, body: true, createdAt: true },
			orderBy: { createdAt: "desc" },
			limit: 1,
		},
	},
})

123456789101112const threads = useQuery({
	collection: "threads",
	select: { id: true, title: true, status: true },
	where: { status: "active" },
	with: {
		messages: {
			select: { id: true, body: true, createdAt: true },
			orderBy: { createdAt: "desc" },
			limit: 1,
		},
	},
})

TanStack DB query collections

12345678910111213const activeThreads = createLiveQueryCollection({
	query: (q) =>
		q
			.from({ thread: threadsCollection })
			.where(({ thread }) => eq(thread.status, "active"))
			.orderBy(({ thread }) => thread.updatedAt, "desc")
			.limit(50)
			.select(({ thread }) => ({
				id: thread.id,
				title: thread.title,
				status: thread.status,
			})),
})

1234567const activeThreads = useQuery({
	collection: "threads",
	select: { id: true, title: true, status: true },
	where: { status: "active" },
	orderBy: { updatedAt: "desc" },
	limit: 50,
})

12345queryFn: async (ctx) => {
	const { where, orderBy, limit, offset } = ctx.meta.loadSubsetOptions
	const parsed = parseLoadSubsetOptions({ where, orderBy, limit, offset })
	return api.getThreads(parsed)
}

Zero queries

1234567891011121314export const queries = defineQueries({
	threads: {
		active: defineQuery(
			z.object({ limit: z.number().int().nonnegative() }),
			({ args: { limit } }) =>
				zql.thread
					.where("status", "active")
					.orderBy("updatedAt", "desc")
					.limit(limit),
		),
	},
})

const [threads] = useQuery(queries.threads.active({ limit: 50 }))

123456const threads = useQuery({
	collection: "threads",
	where: { status: "active" },
	orderBy: { updatedAt: "desc" },
	limit: 50,
})

Important files and references

• packages/core/src/schema/Schema.ts: runtime schema and relation helpers
• packages/core/src/query/Query.ts: query types, result inference, and encoding
• packages/core/src/query/executeQuery.ts: query and scan window execution
• packages/core/src/TandemClient.ts: public query and subscribe
• packages/react/src/TandemClientProvider.tsx: useTandemQuery
• Product direction and examples
• Drizzle Relations v2
• TanStack DB query collections
• Zero queries