Querying the Database
Convex functions can find documents in your database tables using queries.
Queries always begin by getting a reference to a table with the db.table
method:
const usersTable = db.table("users");
Then you can:
- Pick an order
- Filter records
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.table("messages").order("asc").collect();
// Get all messages, newest to oldest.
const messages = await db.table("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
.table("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
.table("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
.table("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
.table("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
.table("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.table("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
.table("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.table("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!
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
.table("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.table("users").map(user => {
const email = user.email;
const userPurchases = await db
.table("purchases")
.filter(q => q.eq(q.field("buyer"), user.email));
return { email, purchases };
});
});