Filtering

Build filter objects for findAll() and count(): bare values, arrays, operators like eq, gte and isNull, date strings, and the exact-vs-pattern gotcha.

findAll(filters) and count(filters) accept a plain object keyed by column name. null and undefined values are skipped, so a query DTO with empty fields can go straight in. A key that is not a column or a configured relation is a 400 (BadRequestException), the same as an unknown sortBy.

Bare values

await service.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
});

Can I pass query string values as strings?

Yes. HTTP query values always arrive as strings, and the package handles that per column type:

Text columns (text, varchar, char) compare case-insensitively with lower(column) = lower(value) when sql.caseSensitive is false, the default.
Integer, uuid, enum and other non-text columns compare with a plain =, and Postgres casts the string to the column type. { author_id: '1' } matches author_id = 1.
Date and timestamp columns turn the string 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.

Strings are exact, not partial

A bare string is an exact match. On a text column with sql.caseSensitive set to false (the default) the package compiles it to lower(column) = lower(value), so the match is case-insensitive and % / _ / \ in the value are treated as literal data, never wildcards. For partial / pattern matching, use the explicit ilike operator with your own % wildcards:

await service.findAll({ name: { ilike: 'jo%' } });   // starts with "jo"
await service.findAll({ name: { ilike: '%doe%' } }); // contains "doe"

Operator object

For anything beyond equality, wrap the value in an object with operator keys:

await service.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 },    // archivedAt IS NULL
  publishedAt: { isNull: false },  // publishedAt IS NOT NULL
  state: { name: 'Karnataka' },    // filter by related column: see Relations
});

Multiple operators in the same object are AND-ed together. An operator the package does not know, such as { between: [1, 2] }, is a 400 rather than being dropped.

Operator reference

Operator	SQL	Notes
`eq`	`=`	Exact and case-sensitive, even on text columns.
`gt` / `gte` / `lt` / `lte`	`>` `>=` `<` `<=`	Numeric and date columns. Date strings are parsed.
`neq`	`<>`	Not equal.
`like`	`LIKE`	Caller supplies `%` wildcards.
`ilike`	`ILIKE` (PostgreSQL) / `LIKE` (MySQL)	Case-insensitive. Caller supplies `%`. MySQL has no `ILIKE`; its default collations already ignore case.
`in`	`IN (...)`	The value must be an array, otherwise 400.
`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`.

Combining filters

await service.findAll({
  status: 'active',
  role: ['admin', 'editor'],     // status='active' AND role IN (...)
  createdAt: { gte: new Date('2025-01-01') },
});

All top-level keys are AND-ed. There is no built-in OR for plain column filters; for that, drop down to a custom method that uses or(...) from drizzle-orm directly. The package re-exports the most common operators:

import { or, and, sql } from 'nestjs-drizzle-crud';

List search across columns

findAll and count also accept options.search when you need a user-facing search box to combine with normal filters, pagination, sorting, relation filters, and a correct total count.

const page = await service.findAll(
  { status: 'active' },
  { page: 1, limit: 20, sortBy: 'name', sortOrder: 'asc' },
  {
    search: {
      term: 'john',
      columns: ['name', 'email'],
    },
  },
);

const total = await service.count(
  { status: 'active' },
  { search: { term: 'john', columns: ['name', 'email'] } },
);

By default, search compiles to cross-column ILIKE '%term%' checks joined by OR (LIKE on MySQL). Empty terms and unknown column names are ignored.

await service.findAll(
  {},
  { page: 1, limit: 20 },
  { search: { term: 'john', columns: ['name', 'email'], mode: 'ilike' } },
);

For PostgreSQL full-text matching inside the findAll / count path, set mode: 'fullText':

await service.findAll(
  { status: 'active' },
  { page: 1, limit: 20 },
  {
    search: {
      term: 'john doe',
      columns: ['name', 'bio'],
      mode: 'fullText',
    },
  },
);

Use fullTextSearch() directly when ranked full-text results are the primary operation. Use findAll(..., ..., { search }) when search needs to compose with the list endpoint's filters and count query.

What happens with an unknown filter key?

It is rejected with a 400 (BadRequestException, message Unknown filter column: <key>). Before 3.3.1 the key was dropped, so a typo such as { titel: 'x' } returned every row. The same rule covers a column inside a relation filter ({ author: { nmae: 'Bob' } }) and an unknown operator.

Pull pagination and other non-filter fields out of the query DTO before passing it on, and let ValidationPipe({ whitelist: true }) strip keys the DTO does not declare:

@Get()
findAll(@Query() query: UserQueryDto) {
  const { page, limit, sortBy, sortOrder, ...filters } = query;
  return this.users.findAll(filters, { page, limit, sortBy, sortOrder });
}

Combining with pagination & sorting

findAll takes three positional arguments: filters, pagination, options. See Pagination & sorting for the full shape.

await service.findAll(
  { status: 'active' },
  { page: 2, limit: 25, sortBy: 'createdAt', sortOrder: 'desc' },
);

When you've declared a many-to-one relation, use the relation name as a filter key. The package will join the related table for you. With soft delete on, a soft-deleted parent never matches.

await service.findAll({
  state: { name: 'Karnataka' },
});

Multi-level filtering works through the intermediate FK column:

// cities where state.country_id = 3: no direct relation to countries required
await service.findAll({
  state: { countryId: 3 },
});