Queries

Setup

These examples assume you attached ORM to ctx.orm once in your context (see /docs/quickstart#orm-setup):

await ctx.orm.query.users.findMany({ limit: 10 });

Basic Queries

findMany

Use findMany() to retrieve multiple rows:

import { publicQuery } from '../lib/crpc';

export const getAllUsers = publicQuery.query(async ({ ctx }) => {
  return ctx.orm.query.users.findMany({ limit: 50 });
});

findFirst

Use findFirst() to retrieve a single row matching your criteria. This example uses id which has a built-in index:

import * as z from 'zod';
import { publicQuery } from '../lib/crpc';

export const getFirstUser = publicQuery
  .input(z.object({ userId: z.string() }))
  .query(async ({ ctx, input }) => {
    return ctx.orm.query.users.findFirst({
      where: { id: input.userId },
    });
  });

If you expect a row to exist, use findFirstOrThrow():

const user = await ctx.orm.query.users.findFirstOrThrow({
  where: { id: input.userId },
});

For tables with a discriminator() discriminator column, query results automatically include a typed discriminated union at the alias field (default details). Use withVariants: true when you want all one() relations eagerly loaded for those variants. See Polymorphic Associations in the Relations guide.

Filtering With where

Use object filters (Drizzle style) to narrow results. The shape mirrors Drizzle but is executed in Convex:

// Schema: index('by_role').on(t.role) on users table
const admins = await db.query.users.findMany({
  where: {
    role: 'admin',
    age: { gt: 18 },
  },
});

Callback where is also supported for Drizzle-style operator syntax:

const admins = await db.query.users.findMany({
  where: (users, { eq }) => eq(users.role, 'admin'),
});

There are a few important differences from SQL-based Drizzle to keep in mind. SQL-only RQB features like RAW filters and sql.placeholder(...) are not supported in Convex. The extras option is supported, but computed post-fetch (no SQL computed fields).

Predicate where (function form) runs via stream filtering and requires an explicit .withIndex(name, range?) on the query. There is no implicit full-scan fallback.

Core Operators

For the complete operator list, see API Reference.

String Operators

The ORM supports familiar string matching operators. Most run post-fetch, but a few can leverage indexes. For the full operator list, see API Reference.

For large datasets, prefer indexed filters first. startsWith and like('prefix%') use index ranges when the field is indexed. between and notBetween are also index-compiled when the field is indexed.

Compound indexes follow Convex prefix rules for index compilation. For an index on [type, numLikes], where: { numLikes: 10 } can run, but it won't use that compound index prefix unless you also constrain the leading field (type) or explicitly anchor the query with .withIndex(...). Reversed AND equality order is normalized to index field order before query execution.

SQL Subquery Operators (exists, notExists)

Drizzle exposes exists(query) and notExists(query) as SQL subquery operators. The ORM does not support SQL subqueries at runtime, so these operators are unavailable.

Use relation filters to model existence checks instead.

Aggregations

The ORM supports count(), aggregate() (sum, avg, min, max), and ranked aggregate runtime — all backed by aggregateIndex for strict no-scan performance.

See Aggregates for full API, index declarations, and aggregate runtime setup.

Full-Scan Operators and Workarounds

These operators are post-fetch. In the typed API, they currently require explicit .withIndex(...) so scan scope is deliberate. For the full list with scalable workarounds for each, see API Reference.

Logical Filters

Combine operators with OR and NOT for complex conditions:

const users = await db.query.users.findMany({
  where: {
    OR: [{ role: 'admin' }, { role: 'premium' }],
    NOT: { email: { isNull: true } },
  },
});

Relation Filters

You can filter by relation existence or by nested relation conditions:

// Users with at least one post
const users = await db.query.users.findMany({
  where: { posts: true },
});

// Users with posts whose title starts with "A"
const users2 = await db.query.users.findMany({
  where: { posts: { title: { like: 'A%' } } },
});

Ordering

Control the sort order of your results with orderBy. You can use object syntax:

const posts = await db.query.posts.findMany({
  orderBy: { createdAt: 'desc', title: 'asc' },
});

You can also use callback syntax (Drizzle-style) with asc/desc helpers:

const posts2 = await db.query.posts.findMany({
  orderBy: (posts, { desc, asc }) => [
    desc(posts.createdAt),
    asc(posts.title),
  ],
});

Notes:

• An index that walks in the requested order serves the sort directly, so limit reads only the rows it returns. That needs the sort fields to be the index's leading keys, in order, all pointing the same way — index('by_type_likes').on(t.type, t.numLikes) serves orderBy: { type: 'asc', numLikes: 'asc' }. Fields pinned by an equality in where are constant across the scan, so they can appear anywhere in the sort and in either direction
• When the first requested field is equality-pinned but points opposite to the moving fields, include createdAt in the moving direction as the final sort field to make the implicit tie-break explicit. Without it, the ORM post-fetch sorts to preserve which tied rows survive limit
• Every unpinned declared index key must be requested. An extra key after the requested fields would break ties before Convex's implicit creation-time key, so the ORM keeps the post-fetch sort to preserve which rows survive limit
• createdAt is servable as the last sort field once every other index key is pinned or consumed, because Convex appends it as the implicit trailing key
• Non-null values use Convex value ordering on both index-backed and post-fetch paths, including UTF-8 string order and Float64 edge values such as signed zero and NaN
• Any other sort — mixed directions, a field the index does not sort by next, or a column that can be missing or null — is applied post-fetch. The ORM then sorts before applying offset/limit, which requires reading the full filtered candidate set first
• findMany({ cursor, limit }) orders pages by every orderBy field the index serves. When the index cannot serve them all, only the first field orders the cursor and the rest are dropped, which the ORM warns about

Pagination

Limit / Offset

For simple pagination, use limit and offset:

const posts = await db.query.posts.findMany({
  limit: 10,
  offset: 20,
});

Cursor Pagination

For efficient pagination over large datasets, use cursor-based pagination:

return db.query.posts.findMany({
  where: { published: true },
  orderBy: { createdAt: 'desc' },
  cursor: args.cursor ?? null,
  limit: 20,
});

in, notIn, ne, and same-field equality OR on a field an index leads with page from one index range per value, so they need no scan budget.

For cursor queries that fall back to a scan (predicate where or post-filter-only plans), use maxScan. allowFullScan is non-cursor only. With strict: true, missing maxScan on these fallback paths throws; with strict: false, it warns and runs uncapped.

Full-Text Search

Use search on findMany() / findFirst() when your table defines a searchIndex:

const posts = await db.query.posts.findMany({
  search: {
    index: 'text_search',
    query: 'galaxy',
    filters: { type: 'news' },
  },
  cursor: null,
  limit: 20,
});

search.filters is typed from the selected index filterFields. search is only available on tables that declare at least one searchIndex.

Search mode constraints

• orderBy is not allowed (Convex relevance ordering is used)
• callback where ((table, ops) => ...) is not allowed
• relation-based where is not allowed
• object where on base table fields is allowed (post-search filter)
• with: is allowed for eager loading

System Tables (db.system passthrough)

Use db.system for Convex system tables (_storage, _scheduled_functions). This is raw Convex access, not ORM query-builder behavior:

const job = await db.system.get(jobId);

const files = await db.system
  .query('_storage')
  .take(20);

const pendingJobs = await db.system
  .query('_scheduled_functions')
  .collect();

Vector Search

Use vectorSearch on findMany() when your table defines a vectorIndex:

const posts = await db.query.posts.findMany({
  vectorSearch: {
    index: 'embedding_vec',
    vector: args.embedding,
    limit: 10,
    includeScore: true,
    filter: (q) => q.eq('type', 'news'),
  },
  with: { author: true },
});

vectorSearch.index and vectorSearch.filter are strongly typed from your vector index definition. vectorSearch.includeScore: true opt-in adds _score to each returned row.

Vector mode constraints

• vectorSearch.limit is required (1..256)
• orderBy is not allowed
• cursor is not allowed
• maxScan is not allowed
• where is not allowed
• .withIndex(...) is not allowed
• offset is not allowed
• top-level limit is not allowed (use vectorSearch.limit)
• _score is returned only when vectorSearch.includeScore: true
• with:, columns, and extras are allowed

Choosing Filter vs Paginate

Use this decision matrix to pick the right approach and avoid accidental full scans:

Relation Loading With with:

Eager-load related data to avoid N+1 queries:

const users = await db.query.users.findMany({
  with: {
    posts: {
      limit: 5,
      offset: 2,
      orderBy: { createdAt: 'desc' },
    },
  },
});

Relation filters for many() are applied post-fetch. Nested with: loads every level you ask for, up to 10 levels deep; a tree that still has rows past that throws RELATION_DEPTH_EXCEEDED rather than coming back shorter than you asked for. The limit and offset in with: apply per parent relation, not globally.

Column Selection

Select only the columns you need with columns:

const users = await db.query.users.findMany({
  columns: { name: true, email: true },
});

Distinct (findMany Unsupported)

findMany({ distinct }) is not available to keep strict index-backed no-scan guarantees.

Use select-pipeline distinct instead:

const page = await ctx.orm.query.todos
  .select()
  .where({ projectId })
  .distinct({ fields: ['status'] })
  .paginate({ cursor: null, limit: 100 });

Extras (Computed Fields)

extras lets you attach computed properties to rows at query time:

const users = await db.query.users.findMany({
  extras: {
    emailDomain: (row) => row.email.split('@')[1]!,
  },
});

Callback form is also supported:

const users = await db.query.users.findMany({
  extras: () => ({
    emailDomain: (row) => row.email.split('@')[1]!,
  }),
});

Extras are computed in JavaScript after fetching documents (and after with: relations are loaded). They can't be used in where/orderBy and should be treated as post-fetch helpers.

Performance Tips

• Add index('...').on(t.field) for fields used in filters or primary ordering
• Use cursor pagination (cursor + limit) for large lists
• Prefer with: over per-row queries to avoid N+1

Common Gotchas

Next Steps