# Filters > The where model - scalar operators, logical combinators, null handling, and dropping to raw Drizzle SQL. URL: https://better-drizzle.com/docs/querying/filters `where` is shared by every read, `count`, `exists`, `update`, `delete`, and `upsert`. It is typed against your table's columns and relations. ## Equality shorthand The simplest filter is a direct value - it means "equals": **better-drizzle** ```ts await client.users.findMany({ where: { active: true, name: 'Alice' }, }); ``` **Drizzle** ```ts import { and, eq } from 'drizzle-orm'; await db .select() .from(users) .where(and(eq(users.active, true), eq(users.name, 'Alice'))); ``` That is identical to the explicit form: **better-drizzle** ```ts await client.users.findMany({ where: { active: { equals: true } }, }); ``` **Drizzle** ```ts import { eq } from 'drizzle-orm'; await db.select().from(users).where(eq(users.active, true)); ``` Multiple keys are combined with **AND**. ## String filters String columns accept equality, membership, and pattern operators: **better-drizzle** ```ts await client.users.findMany({ where: { name: { contains: 'ali', mode: 'insensitive' }, email: { endsWith: '@example.com' }, }, }); ``` **Drizzle** ```ts import { and, ilike, like } from 'drizzle-orm'; await db .select() .from(users) .where(and(ilike(users.name, '%ali%'), like(users.email, '%@example.com'))); ``` | Operator | Meaning | | ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `equals` | exact match | | `in` / `notIn` | value is (not) in a list | | `contains` | substring match | | `startsWith` / `endsWith` | prefix / suffix match | | `mode` | `'insensitive'` makes every string operator ignore case: `contains` / `startsWith` / `endsWith` use `ILIKE` on PostgreSQL and `lower(column) LIKE lower(pattern)` on SQLite and MySQL; `equals`, `in`, `notIn`, and a scalar `not` compare `lower(column)` with `lower(value)` on every dialect. A nested `not` filter takes its own `mode`. `'default'` compiles to `LIKE` / `=`, whose case sensitivity follows the dialect and collation | | `not` | negate a value or a nested filter | ## Numeric and date filters Numbers, bigints, and dates accept range comparisons: **better-drizzle** ```ts await client.users.findMany({ where: { id: { gte: 100, lte: 200 }, }, }); ``` **Drizzle** ```ts import { and, gte, lte } from 'drizzle-orm'; await db .select() .from(users) .where(and(gte(users.id, 100), lte(users.id, 200))); ``` | Operator | Meaning | | -------------- | ------------------------------------ | | `equals` | exact match | | `in` / `notIn` | value is (not) in a list | | `lt` / `lte` | less than / less than or equal | | `gt` / `gte` | greater than / greater than or equal | | `not` | negate a value or a nested filter | `contains` and `startsWith` treat `%`, `_`, and the SQL escape character as literal search text. This applies to case-insensitive filters, nested `not`, PostgreSQL JSONB paths and array elements, and prepared params. Use a Drizzle `SQL` fragment when you need a wildcard pattern. ## Boolean filters **better-drizzle** ```ts await client.users.findMany({ where: { active: { equals: true } }, }); ``` **Drizzle** ```ts import { eq } from 'drizzle-orm'; await db.select().from(users).where(eq(users.active, true)); ``` Boolean columns support `equals` and `not`. ## Null and negation Nullable columns accept `null` directly, and any operator can be negated with `not`: **better-drizzle** ```ts await client.posts.findMany({ where: { deletedAt: null, // IS NULL title: { not: { contains: 'draft' } }, }, }); ``` **Drizzle** ```ts import { and, isNull, like, not } from 'drizzle-orm'; await db .select() .from(posts) .where(and(isNull(posts.deletedAt), not(like(posts.title, '%draft%')))); ``` ## Logical operators Combine conditions with `AND`, `OR`, and `NOT`. They nest arbitrarily: **better-drizzle** ```ts await client.users.findMany({ where: { AND: [ { active: true }, { OR: [ { email: { endsWith: '@company.com' } }, { name: { startsWith: 'Admin' } }, ], }, ], }, }); ``` **Drizzle** ```ts import { and, eq, like, or } from 'drizzle-orm'; await db .select() .from(users) .where( and( eq(users.active, true), or(like(users.email, '%@company.com'), like(users.name, 'Admin%')), ), ); ``` > **Top-level keys are already AND-ed:** You only need explicit `AND` when you want to group an `OR` next to other > conditions, or to express the same condition on a field twice. **PostgreSQL only:** ## PostgreSQL arrays Native PostgreSQL array columns support exact equality, containment, overlap, emptiness, and total element-count filters. These filters are PostgreSQL-only. ```ts const posts = await client.posts.findMany({ where: { tags: { has: 'drizzle', length: { lte: 3 } }, roles: { hasEvery: ['admin', 'member'] }, scores: { hasSome: [10, 20], hasNone: [0] }, }, }); ``` Use `equals` (or pass an array directly) for exact equality, `containedBy` when every stored value must be in a supplied list, and `isEmpty` for empty/non-empty arrays. `length` uses PostgreSQL `cardinality`, including every dimension. GIN indexes help containment and overlap queries; use an expression index on `cardinality(column)` for frequent length filters. See [Array filters](/docs/querying/arrays) for every operator, the generated SQL, `NULL` semantics, and indexing. **PostgreSQL only:** ## JSONB filters > **PostgreSQL only:** JSONB path filters are unavailable on SQLite and MySQL. Typed paths inside PostgreSQL JSONB columns have their own API and limitations: - [JSONB filters](/docs/querying/jsonb): Filter typed scalar JSONB paths such as `'profile.age'`. ## Filtering by relations `where` also reaches across relations with `some` / `every` / `none` (to-many) and `is` / `isNot` (to-one). That has its own page: - [Relation filters](/docs/querying/relations): `some`, `every`, `none`, `is`, `isNot`, and combining them with scalars. ## Dropping to raw Drizzle SQL When you need an expression the structured filter does not model, pass a Drizzle `SQL` fragment straight into `where`: **better-drizzle** ```ts import { eq } from 'drizzle-orm'; import { users } from './schema'; const user = await client.users.findFirst({ where: eq(users.id, 1), }); ``` **Drizzle** ```ts import { eq } from 'drizzle-orm'; import { users } from './schema'; const [user = null] = await db .select() .from(users) .where(eq(users.id, 1)) .limit(1); ``` Anything that produces a Drizzle `SQL` / `SQLWrapper` is accepted, so you keep the full expressive power of Drizzle for the cases that need it. ## Reusing a filter in raw Drizzle (Added in 0.3.0) `$where()` goes the other way: it compiles a typed `where` into a Drizzle `SQL` condition, so the same filter works in raw queries, joins, and subqueries. The condition includes every plugin scope of the model, so it selects the same rows as `findMany`: [soft-delete](/docs/plugins/soft-delete) hides deleted rows, and a [tenant](/docs/plugins/tenant)-scoped client adds its tenant filter. `deleted` and other per-call arguments do not apply. It returns `undefined` only when the `where` is empty and the model has no scope. **better-drizzle** ```ts import { and, eq } from 'drizzle-orm'; import { users, posts } from './schema'; const rows = await db .select() .from(posts) .innerJoin(users, eq(posts.authorId, users.id)) .where( and( client.users.$where({ OR: [{ age: { gte: 18 } }, { active: true }] }), client.posts.$where({ published: true, score: { gt: 10 } }), ), ); ``` **Drizzle** ```ts import { and, eq, gt, gte, or } from 'drizzle-orm'; import { users, posts } from './schema'; const rows = await db .select() .from(posts) .innerJoin(users, eq(posts.authorId, users.id)) .where( and( or(gte(users.age, 18), eq(users.active, true)), eq(posts.published, true), gt(posts.score, 10), ), ); ``` * It takes the same `where` as `findMany`, including logical operators and relation filters. * An empty `where` returns `undefined`, which Drizzle treats as no filter. * Static plugin filters such as the soft-delete visibility rule are **not** applied. Add them yourself when you need them. On a [tenant](/docs/plugins/tenant)-scoped client the tenant filter **is** applied, so the condition never reaches another tenant's rows.