Queries

In this guide, we'll learn how to query your Convex database with the ORM. You'll master findMany(), findFirst(), filtering with where, ordering, pagination, full-text search, vector search, and eager relation loading with with:.

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. Here's a simple query that fetches up to 50 users:

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 { 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 },
});

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

Here's the full set of comparison and logical operators you can use in where:

String Operators

The ORM supports familiar string matching operators. Most run post-fetch, but a few can leverage indexes:

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:

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

// Users with no posts
const withoutPosts = await db.query.users.findMany({
  where: { NOT: { posts: true } },
});

Aggregations (count, sum, avg, max, min)

For query-builder aggregations, use Convex aggregates via @convex-dev/aggregate. See /docs/server/advanced/aggregates.

Full-Scan Operators and Workarounds

These operators are post-fetch. In the typed API, they currently require explicit .withIndex(...) so scan scope is deliberate. Here are scalable workarounds for each:

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:

• The first orderBy field should have an index for stable ordering
• Secondary sort fields are applied post-fetch
• When post-fetch sorting is required (multi-field or non-indexable sort), the ORM sorts before applying offset/limit, which requires reading the full filtered candidate set first
• findMany({ cursor, limit }) uses only the first orderBy field for cursor ordering

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,
});

For cursor queries that need scan fallback paths (for example index-union/multi-probe 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. Here's how to load each user's recent posts:

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: has a depth limit to prevent infinite recursion. 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 },
});

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

Here's a quick reference for the most frequent issues:

You now know how to query, filter, paginate, search, and eager-load relations with the ORM.

Next Steps