Querying the Database

Convex functions can find documents in your database tables using queries.

Queries always begin by choosing the table to query with the db.query method:

const usersTable = db.query("users");

Then you can:

1. Pick an order
2. Filter records
3. await the results

We'll see how this works in the examples below.

Full Table Scans

The examples in this document are all full table scan queries meaning that they may require Convex to inspect every document in the table. If your tables have more than a few thousand documents you should use indexes to improve your query performance.

Ordering

By default Convex always returns documents ordered by _creationTime. You can use .order("asc" | "desc") to pick whether the order is ascending or descending.

If the order isn't specified, it defaults to ascending.

// Get all messages, oldest to newest.
const messages = await db.query("messages").order("asc").collect();

// Get all messages, newest to oldest.
const messages = await db.query("messages").order("desc").collect();

If your query is against an index, documents will be returned in index order (ascending or descending).

Filtering

The filter method allows you to restrict the documents that your query returns. This method takes a filter constructed by FilterBuilder and will only select documents that match.

The examples below demonstrate some of the common uses of filter. You can see the full list of available filtering methods in the reference docs.

Equality Conditions

This query finds documents in the users table where doc.name === "Alex":

// Get all users named "Alex".
const usersNamedAlex = await db
  .query("users")
  .filter(q => q.eq(q.field("name"), "Alex"))
  .collect();

Here q is the FilterBuilder utility object. It contains methods for all of our supported filter operators.

This filter will run on all documents in the table. For each document, q.field("name") evaluates to the name property. Then q.eq checks if this property is equal to "Alex".

Absent fields

If your query references a field that is missing from a given document then that field will be considered to have the value null.

Comparisons

Filters can also be used to compare fields against values. This query finds documents where doc.age >= 18:

// Get all users with an age of 18 or higher.
const adults = await db
  .query("users")
  .filter(q => q.gte(q.field("age"), 18))
  .collect();

Here the q.gte operator checks if the first argument (doc.age) is greater than or equal to the second (18).

Here's the full list of comparisons:

Operator	Equivalent TypeScript
q.eq(l, r)	l === r
q.neq(l, r)	l !== r
q.lt(l, r)	l < r
q.lte(l, r)	l <= r
q.gt(l, r)	l > r
q.gte(l, r)	l >= r

Arithmetic

You can also include basic arithmetic in your queries. This query finds documents in the carpets table where doc.height * doc.width > 100:

// Get all carpets that have an area of over 100.
const largeCarpets = await db
  .query("carpets")
  .filter(q => q.gt(q.mul(q.field("height"), q.field("width")), 100))
  .collect();

Here's the full list of arithmetic operators:

Operator	Equivalent TypeScript
q.add(l, r)	l + r
q.sub(l, r)	l - r
q.mul(l, r)	l * r
q.div(l, r)	l / r
q.mod(l, r)	l % r
q.neg(x)	-x

Combining Operators

You can construct more complex filters using methods like q.and, q.or, and q.not. This query finds documents where doc.name === "Alex" && doc.age >= 18:

// Get all users named "Alex" whose age is at least 18.
const adultAlexes = await db
  .query("users")
  .filter(q => q.and(q.eq(q.field("name"), "Alex"), q.gte(q.field("age"), 18)))
  .collect();

Here is a query that finds all users where doc.name === "Alex" || doc.name === "Emma":

// Get all users named "Alex" or "Emma".
const usersNamedAlexOrEmma = await db
  .query("users")
  .filter(q =>
    q.or(q.eq(q.field("name"), "Alex"), q.eq(q.field("name"), "Emma"))
  )
  .collect();

Retrieving results

All of our previous examples have ended the query with the .collect() method, which returns all the documents that match your filters. Here are the other options for retrieving results.

Taking n results

.take(n) selects only the first n results that match your query.

const user: User[] = await db.query("users").take(5);

Finding the first result

.first() selects the first document that matches your query and returns null if no documents were found.

// We expect only one user with that email address.
const user: User | null = await db
  .query("users")
  .filter(q => q.eq(q.field("email"), "test@example.com"))
  .first();

Using a unique result

.unique() selects the single document from your query. If there are multiple results or no results it will throw an exception.

// Our counter table only has one document.
const counter: Counter = await db.query("counter").unique();

Selecting by ID

If you are trying to load a document by ID, use db.get(id) instead of a query. It's faster and less code!

Loading a page of results

.paginate(opts) loads a page of results and returns a Cursor for loading additional results.

See Paginated Queries to learn more.

More complex queries

Convex prefers to have a few, simple ways to walk through and select documents from tables. When there is complex logic to perform, like an aggregation, a group by, or a join, the way to solve that in Convex is different than many of the databases you're used to.

In Convex, instead of using some specific query language, you can just write the complex logic in TypeScript!

Here's an example of computing an average:

import { query } from "./_generated/server";

export const averagePurchasePrice = query(({ db }, email: string): number => {
  const userPurchases = await db
    .query("purchases")
    .filter(q => q.eq(q.field("buyer"), email));
  const average = userPurchases.reduce((a, b) => a + b) / userPurchases.length;
  return average;
});

Joining two tables might look like:

import { query } from "./_generated/server";

interface UserPurchaseSummary {
  email: string;
  purchases: number;
}
const purchasesByUser = query(async ({ db }): UserPurchaseSummary[] => {
  return db.query("users").map(user => {
    const email = user.email;
    const userPurchases = await db
      .query("purchases")
      .filter(q => q.eq(q.field("buyer"), user.email));
    return { email, purchases };
  });
});