Table totals
Need the total number of rows in a table.
Drizzle ORM · Module
db.$count
db.$count() wraps SQL count(*) for a table and can optionally apply a filter. It also works as a correlated subquery for related-record counts.
How it works
Call db.$count(table) to count every row, or pass a filter expression to restrict the count. The same utility can be embedded in a select projection, where its correlated condition turns it into a per-row subquery.
Syntax
db.$count(table, filter?)Parameters
| Name | Type | Description |
|---|---|---|
table | Table | The table whose rows are counted. |
filter | SQL condition | Optional Drizzle filter limiting which table rows are counted. |
Returns
A promise resolving to the row count, typed as number.
Key points
- $count provides a typed convenience wrapper around SQL's
count(*)aggregate. - Calling it with a table returns the total number of rows in that table.
- A condition can be supplied as the second argument to count only matching rows.
- Use $count inside a larger query when the count depends on the current outer row.
- A correlated predicate can count related posts for each selected user.
- Relational query extras can expose a related count using the current table alias.
Common mistakes
- Use $count directly for a total or embed it in a projection when a count must be calculated for each outer row.
Example: Count rows and use $count as a subquery
import { drizzle } from "drizzle-orm/node-postgres";
import { eq } from "drizzle-orm";
import { users, posts } from "./schema";
const db = drizzle(process.env.DATABASE_URL!);
const total = await db.$count(users);
const dans = await db.$count(users, eq(users.name, "Dan"));
const usersWithPostCounts = await db
.select({
id: users.id,
name: users.name,
postsCount: db.$count(posts, eq(posts.authorId, users.id)),
})
.from(users);
console.log({ total, dans, usersWithPostCounts });Counts all users, counts users matching a filter, and adds a correlated post count to each selected user.
When to use it
Filtered counts
Need a count restricted by a Drizzle predicate.
Correlated relation counts
Need each parent row to include a count of related records.
Relational-query extras
Need a related count in a relational query result.
Interview questions
QWhat does db.$count() do?
db.$count() is Drizzle's typed utility for a SQL count(*) operation. With only a table it counts all rows; a second condition adds filtering, and the result is typed as a number.
Follow-up: How would you count related records for every parent? Put db.$count(relatedTable, relationPredicate) in the parent select projection, correlating the predicate with the outer parent row.
QHow do you apply a filter to $count?
Pass the table as the first argument and an optional Drizzle condition as the second argument. For example, db.$count(users, eq(users.name, "Dan")) counts only rows satisfying that condition.
Follow-up: What SQL shape does the filtered call represent? It corresponds to count(*) over the table with a where predicate.
QWhy is $count useful in subqueries?
It can be used as a subquery in a select object, so each returned parent row can include a related-record count. The predicate references both the counted table and the current outer row.
Follow-up: Can it be used with relational queries? Yes; the documentation shows it in a relational query's extras configuration for a per-row count.
Related
countselecteqextrasRelational Queries