Count all rows
Determine the total number of records in a table.
Drizzle ORM · Function
count
Drizzle ORM's count() aggregate counts all rows when called without an argument, or non-NULL values from a supplied column. Use it in a select query and combine it with filters, joins, and aggregations.
How it works
Import count from drizzle-orm and place it in a select projection. With no argument it counts rows, while passing a column counts non-NULL values for that column; combine the query with where, joins, or grouping as needed.
Syntax
count(expression?)Parameters
| Name | Type | Description |
|---|---|---|
expression | SQL expression or column | Optional SQL expression or table column whose values should be counted; omit it to count rows. |
Returns
A numeric count in Drizzle's runtime mapping; exact driver behavior can vary by database dialect.
Key points
countis an aggregate that returns the number of values in its expression.- Call
count()without an argument to count every row in the source table. - Pass a column to count only rows where that column is not NULL.
- Filter the rows first with
.where()when the count should include only records matching a condition. - The aggregate works in queries that include joins and aggregation patterns.
Common mistakes
- On PostgreSQL, MySQL, and Cockroach, the database driver may return bigint counts as strings, so cast the value to an integer when a numeric result is required.
- SQLite and MSSQL return the count as an integer, unlike the bigint-to-string behavior noted for some other databases.
Example: Count filtered rows
import { count, gt } from "drizzle-orm";
import { products } from "./schema";
const result = await db
.select({ total: count() })
.from(products)
.where(gt(products.price, 100));
console.log(result[0].total);Counts products whose price is greater than 100; the result is exposed as a number.
When to use it
Count non-NULL column values
Count populated values for a nullable field.
Count filtered rows
Measure matching records after applying predicates.
Count joined data
Produce counts across related tables or aggregate queries.
Interview questions
QWhat is the difference between count() and count(column)?
count() with no argument maps to count(*) and returns the number of rows. Passing a column maps to count(column), which counts only rows where that column is non-NULL.
Follow-up: How would you count only qualifying rows? Add a .where() predicate to the select query before executing it.
QHow do you use count in a Drizzle query?
Use count() in the selected object, then call .from(table). The result is runtime-cast to a number by Drizzle, so the selected count can be consumed as a numeric value.
Follow-up: How can you count values per group? Select the count alongside the grouping expression and use .groupBy().
QHow does Drizzle handle the result type of count()?
The helper is equivalent in purpose to a SQL count expression while preserving Drizzle's typed query construction. For explicit runtime conversion, a SQL expression can use .mapWith(Number).
Follow-up: What should be checked when the returned value is unexpectedly a string? Verify the database dialect and apply an integer cast or runtime mapping where appropriate.
Related
countDistinctdb.$countsqlmapWithgroupByhaving