Filtering with Drizzle in NestJS
Drizzle filtering in NestJS with findAll and count. Pass exact values, arrays, operators such as gte, between and contains, and or, and, not groups.
Drizzle filtering in NestJS works through one plain object: findAll(filters) and count(filters) take an object keyed by column name, and the package compiles it to a Drizzle WHERE. A bare value is an exact match, an array is IN (...), and an object of operators such as gte, between or contains covers everything else. Keys are ANDed, or, and and not group them, and an unknown key is a 400.
Keys are Drizzle keys
A filter key is the key of the column in your Drizzle table object (createdAt), not the SQL
column name (created_at). The examples on this page use a users table served by
UsersService, with a belongs-to organization relation and a has-many posts relation.
How do I filter by a column value?
Pass the value under the column key. null and undefined values are skipped, so a query DTO with empty fields can go straight in.
await this.users.findAll({
status: "active", // string: exact match (case-insensitive on text columns)
isVerified: true, // boolean: equality
organizationId: 42, // number: equality
role: ["admin", "editor"], // array: IN (...)
createdAt: new Date("2025-01-01T00:00:00Z"), // Date: equality
});count takes the same object, so a list and its total always agree:
const active = await this.users.count({ status: "active" });Can I pass query string values as strings?
Yes. HTTP query values always arrive as strings, and the package handles them by column type:
| Column | What a string does |
|---|---|
text, varchar, char | Compared with lower(column) = lower(value) while sql.caseSensitive is false, the default. |
| integer, uuid, enum and other non-text columns | Compared with a plain =, and PostgreSQL casts the string to the column type. { organizationId: "1" } matches organization_id = 1. |
| date and timestamp (date mode) | Turned into a Date first, so { createdAt: { gte: "2025-01-01" } } works. A string that is not a date is a 400. |
A value the column cannot hold, such as "abc" for an integer or an enum label that does not exist, is a 400 too, never a raw 500. To coerce a whole query string up front (numbers, booleans, bigints, dates), use parseQuery.
A bare string is an exact match, not a partial one
On a text column { name: "jo" } compiles to lower(name) = lower('jo'). The match ignores
case, and %, _ and \ in the value are literal data, never wildcards. For a partial match
use startsWith, endsWith or contains (which escape those characters for you, so
{ contains: "50%" } matches the text "50%"), or like / ilike with your own pattern.
await this.users.findAll({ name: { startsWith: "jo" } }); // starts with "jo"
await this.users.findAll({ name: { contains: "doe" } }); // contains "doe"
await this.users.findAll({ name: { ilike: "jo%n" } }); // your own patternIs findOne case-sensitive?
Yes. findOne turns a string into a plain =, whatever sql.caseSensitive says, while findAll, count, countBy and ?column= use lower() on text columns by default. With the default setting, findOne({ email: "A@X.IO" }) can return null for a row that findAll({ email: "A@X.IO" }) finds. Pass the stored case to findOne, or use findAll(filters, { limit: 1 }) when case must not matter.
A plain index on the column cannot serve lower(column) = lower($1). Add one on lower(column), or see Database indexes for fast Drizzle queries for the other options and the index behind every operator.
How do I use operators like gte, between and contains?
Wrap the value in an object of operator keys. Several operators on one column are ANDed.
await this.users.findAll({
age: { gte: 18, lt: 65 }, // 18 <= age < 65
email: { neq: "system@example.com" }, // not the system account
name: { ilike: "jo%" }, // pattern match: you supply the wildcards
archivedAt: { isNull: true }, // archived_at IS NULL
createdAt: { between: ["2025-01-01", "2025-12-31"] }, // both ends inclusive
});An operator the package does not know, such as { roughly: 2 }, is a 400 (Unknown filter operator: roughly) rather than being dropped. An operator whose value is undefined adds nothing.
Operator reference
All 22 operators, as the service and the query parser handle them:
| Operator | SQL | Notes |
|---|---|---|
eq | = | A plain =, with no lower(). Case-sensitive on PostgreSQL and SQLite even on text columns; MySQL's default collations ignore case. |
neq | <> | Not equal. |
gt / gte / lt / lte | > >= < <= | Numeric and date columns. Date strings are parsed. NaN or Infinity is a 400. |
like | LIKE | You supply the % wildcards. The value is passed as is. On SQLite the package adds ESCAPE '\', so a backslash escapes as it does on the other dialects. |
ilike | ILIKE (PostgreSQL), LIKE (MySQL, SQLite) | Case-insensitive, you supply the %. MySQL and SQLite have no ILIKE; MySQL's default collations ignore case, and SQLite's LIKE ignores ASCII case. |
in | IN (...) | The value must be an array, otherwise 400. A list past the database's bound parameter limit (65,535 on PostgreSQL and MySQL, 32,766 on SQLite, 100 on D1, every list in the statement counted) is a 400, see bulk operations. |
notIn | NOT IN (...) | The value must be an array, otherwise 400. |
between | BETWEEN a AND b | [from, to], both inclusive. Anything other than two values is a 400. Date strings are parsed. |
isNull | IS NULL / IS NOT NULL | true or "true" gives IS NULL, false or "false" gives IS NOT NULL. Anything else is a 400. |
isNotNull | IS NOT NULL / IS NULL | The mirror of isNull. |
startsWith / endsWith / contains | LIKE 'x%', '%x', '%x%' | Text columns and string values only. The value is escaped. Case-insensitive (ILIKE on PostgreSQL) unless sql.caseSensitive is true. On SQLite the case-sensitive forms are GLOB (since 3.7.2), since SQLite's LIKE ignores ASCII case. |
iStartsWith / iEndsWith / iContains | ILIKE (PostgreSQL), LIKE (MySQL, SQLite) | Always case-insensitive, whatever sql.caseSensitive says. Escaped like the plain forms. |
arrayContains | @> | PostgreSQL array columns: the column holds every value. { tags: { arrayContains: ["a", "b"] } }. |
arrayOverlaps | && | PostgreSQL array columns: the column holds at least one of the values. |
jsonContains | @> '...'::jsonb | PostgreSQL jsonb columns only (not json): { meta: { jsonContains: { plan: "pro" } } }. |
A pattern operator on a column that is not text, an array operator on a column that is not an array, a jsonContains on a column that is not jsonb, or any of the three PostgreSQL operators on MySQL or SQLite is a 400 that says so. On MySQL the case-sensitive pattern forms still ignore case for the letters the default collations fold, because its LIKE does. On SQLite they match case exactly since 3.7.2: contains: "Ann" no longer matches ann. The like operator stays plain LIKE, which ignores ASCII case on SQLite.
How do I combine filters?
Put them side by side: every top-level key is ANDed.
await this.users.findAll({
status: "active",
role: ["admin", "editor"], // status = 'active' AND role IN (...)
createdAt: { gte: new Date("2025-01-01") },
});How do I OR two filters?
Put them in an or array. and, or and not are group keys, and each member is a full filter object, so it can hold columns, relation filters, collection filters and more groups.
await this.users.findAll({
status: "active",
or: [
{ organization: { name: "Acme" } }, // a belongs-to relation
{ age: { gte: 30 } },
],
not: { posts: { status: "draft" } }, // a has-many: no draft post
});
// status = 'active'
// AND (organization.name = 'Acme' OR age >= 30)
// AND NOT (id IN (SELECT ... posts.status = 'draft'))How the groups behave:
andandortake an array of filter objects;nottakes one filter object. Any other shape is a 400.- Groups nest, work inside a relation filter (
{ organization: { or: [{ name: "Acme" }, { name: "Globex" }] } }) and apply tocounttoo. - An
orwith an empty member matches every row, so the whole group adds nothing. An emptyandmember is skipped. - A table column (or relation) named
and,orornotwins over the group key. - A filter may nest at most 32 objects deep. A deeper one is a 400, from a query string or from code.
NOT and a missing parent
A belongs-to filter inside not compares against a LEFT JOIN. A row whose parent is missing
has NULL there, and NOT (NULL = 'Acme') is not true, so
{ not: { organization: { name: "Acme" } } } also leaves out users with no organization. Add
{ or: [{ not: ... }, { organizationId: { isNull: true } }] } if you want them.
How do I filter by a related column?
Use the relation name as the key and a filter on the related table's columns as the value. You need a relation declared in the entity config; a relation value that is not an object is a 400.
// belongs-to: joined with a LEFT JOIN
await this.users.findAll({ organization: { name: "Acme" } });
// has-many: users with at least one published post, as IN (SELECT ...)
await this.users.findAll({ posts: { status: "published" } });A top-level belongs-to is joined into the main query. A has-many or many-to-many relation becomes an IN (SELECT ...) subquery, so a user with three matching posts still appears once and total stays right. With soft delete on, a soft-deleted related row never matches.
When the foreign key is all you need, filter on it directly: { organizationId: 42 } needs no relation at all.
Can I filter through several relations or quantify a collection?
Yes. Nest the relation names to any depth, and use some, none or every on a has-many or many-to-many relation:
await this.users.findAll({ organization: { country: { code: "IN" } } });
await this.users.findAll({ posts: { none: { status: "draft" } } });Both compile to EXISTS or NOT EXISTS subqueries, never joins that multiply rows. Filtering by relation in Drizzle covers paths, quantifiers and the SQL they produce.
How do I add a search box to a list?
Pass options.search to findAll or count. It searches a term across columns and composes with your filters, pagination, sorting, relation filters and the total count.
const page = await this.users.findAll(
{ status: "active" },
{ page: 1, limit: 20, sortBy: "name", sortOrder: "asc" },
{ search: { term: "john", columns: ["name", "email"] } },
);
const total = await this.users.count(
{ status: "active" },
{ search: { term: "john", columns: ["name", "email"] } },
);search.termRequiredstring
search.columnsRequiredstring[]
search.modeOptional"ilike" | "fullText"
Defaults to "ilike"
search.languageOptionalstring
Defaults to "english"
search.queryModeOptional"plain" | "websearch"
Defaults to "plain"
mode: "fullText" on MySQL or SQLite is a 400. For results ordered by relevance, call fullTextSearch() instead; full-text search in PostgreSQL compares the two.
await this.users.findAll(
{ status: "active" },
{ page: 1, limit: 20 },
{ search: { term: "john doe", columns: ["name", "bio"], mode: "fullText" } },
);What happens with an unknown filter key?
It is rejected with a 400 (BadRequestException, message Unknown filter column: <key>), the same as an unknown sortBy. Before 3.3.1 the key was dropped, so a typo such as { nmae: "x" } returned every row. The same rule covers a column inside a relation filter ({ organization: { nmae: "Acme" } }, reported as organization.nmae) and an unknown operator.
A key outside filterableColumns is a 400 too, so a client can only filter on the columns you mean to expose.
How do I filter from a query string in a controller?
Hand the query to parseQuery. It splits off pagination, sort and options, coerces the values by column type and rejects unknown keys before any SQL runs.
@Get()
findAll(@Query() query: Record<string, unknown>) {
const q = this.users.parseQuery(query);
return this.users.findAll(q.filters, q.pagination, q.options);
}How do filters work with pagination and sorting?
findAll takes three positional arguments: filters, pagination, options. The filters decide which rows match, and the pagination picks the page. See pagination and sorting for the full shape.
await this.users.findAll(
{ status: "active" },
{ page: 2, limit: 25, sortBy: "createdAt", sortOrder: "desc" },
);Common questions
A bare string on a text column is not: it compiles to lower(column) = lower(value) while sql.caseSensitive is false, the default. The eq operator is a plain =, which is case-sensitive on PostgreSQL.
Use startsWith, endsWith or contains, which escape %, _ and \ in your value. Use like or ilike when you want to write the pattern and its wildcards yourself.
The call fails with a 400 and the message Unknown filter column: <key>. Unknown operators and unknown columns inside a relation filter are rejected the same way, so a typo never returns every row.
Yes. Put each condition in an or array, as in { or: [{ role: "admin" }, { age: { gte: 30 } }] }. and and not work the same way, and groups nest.
No. arrayContains, arrayOverlaps and jsonContains are PostgreSQL only, and on MySQL or SQLite they are a 400 that says so.
Next steps
Parse query strings into filters
Turn ?age[gte]=18&status=active into typed filters with one call.
Filter by relation
Paths through relations and some, none, every on collections.
Pagination and sorting
The findAll envelope, page limits and the default sort.
Allowlists
Limit which columns a client may filter, sort, select and search.
Database indexes for fast Drizzle queries
The index each filter, sort and search needs on PostgreSQL.
Drizzle UUID primary keys in NestJS
Use a Drizzle uuid primary key in NestJS, or serial and bigint. The key type comes from the column; on MySQL you send the uuid on create.
Filter by relation in Drizzle ORM
Drizzle filter by relation in NestJS: paths at any depth and some, none, every on collections, compiled to EXISTS subqueries that never multiply rows.