Filter Operators

Drizzle provides filter operators as functions imported from drizzle-orm. They're used in .where() clauses and relational query where options.

import { eq, gt, and } from 'drizzle-orm'

const orders = await db.select()
  .from(schema.orders)
  .where(and(
    eq(schema.orders.status, 'active'),
    gt(schema.orders.amount, 100)
  ))

Operator Reference

Operator	Description	Example
`eq(col, val)`	Equal to	`eq(schema.orders.status, 'active')`
`ne(col, val)`	Not equal to	`ne(schema.orders.status, 'cancelled')`
`gt(col, val)`	Greater than	`gt(schema.orders.amount, 100)`
`gte(col, val)`	Greater than or equal	`gte(schema.orders.amount, 100)`
`lt(col, val)`	Less than	`lt(schema.orders.amount, 500)`
`lte(col, val)`	Less than or equal	`lte(schema.orders.amount, 500)`
`inArray(col, vals)`	In array	`inArray(schema.orders.status, ['active', 'pending'])`
`notInArray(col, vals)`	Not in array	`notInArray(schema.orders.status, ['cancelled'])`
`like(col, pattern)`	SQL LIKE (case-sensitive)	`like(schema.customers.name, 'Acme%')`
`ilike(col, pattern)`	SQL LIKE (case-insensitive)	`ilike(schema.customers.email, '%@acme.com')`
`isNull(col)`	Is null	`isNull(schema.orders.deletedAt)`
`isNotNull(col)`	Is not null	`isNotNull(schema.orders.deletedAt)`
`between(col, a, b)`	Between two values	`between(schema.orders.amount, 100, 500)`
`and(...conds)`	Logical AND	`and(eq(...), gt(...))`
`or(...conds)`	Logical OR	`or(eq(...), eq(...))`
`not(cond)`	Logical NOT	`not(eq(schema.orders.status, 'cancelled'))`

Comparison Operators

eq — Equal

import { eq } from 'drizzle-orm'

const active = await db.select()
  .from(schema.orders)
  .where(eq(schema.orders.status, 'active'))

ne — Not Equal

import { ne } from 'drizzle-orm'

const notCancelled = await db.select()
  .from(schema.orders)
  .where(ne(schema.orders.status, 'cancelled'))

gt, gte, lt, lte — Range

import { gte, lt, and } from 'drizzle-orm'

const midRange = await db.select()
  .from(schema.orders)
  .where(and(
    gte(schema.orders.amount, 100),
    lt(schema.orders.amount, 1000)
  ))

between — Range (Inclusive)

import { between } from 'drizzle-orm'

const midRange = await db.select()
  .from(schema.orders)
  .where(between(schema.orders.amount, 100, 1000))

Set Operators

inArray — In Array

import { inArray } from 'drizzle-orm'

const selected = await db.select()
  .from(schema.orders)
  .where(inArray(schema.orders.status, ['active', 'pending', 'shipped']))

notInArray — Not In Array

import { notInArray } from 'drizzle-orm'

const excluded = await db.select()
  .from(schema.orders)
  .where(notInArray(schema.orders.status, ['cancelled', 'expired']))

Pattern Matching

like — Case-Sensitive

import { like } from 'drizzle-orm'

const matched = await db.select()
  .from(schema.customers)
  .where(like(schema.customers.name, 'Acme%'))

ilike — Case-Insensitive

import { ilike } from 'drizzle-orm'

const matched = await db.select()
  .from(schema.customers)
  .where(ilike(schema.customers.email, '%@acme.com'))

Null Checks

import { isNull, isNotNull } from 'drizzle-orm'

// Find rows where deletedAt is null (active records)
const active = await db.select()
  .from(schema.orders)
  .where(isNull(schema.orders.deletedAt))

// Find rows where deletedAt is not null (soft-deleted)
const deleted = await db.select()
  .from(schema.orders)
  .where(isNotNull(schema.orders.deletedAt))

Logical Operators

and — All Conditions Must Match

import { and, gte, lte } from 'drizzle-orm'

const result = await db.select()
  .from(schema.orders)
  .where(and(
    gte(schema.orders.amount, 100),
    lte(schema.orders.amount, 500)
  ))

or — Any Condition Must Match

import { or, eq } from 'drizzle-orm'

const result = await db.select()
  .from(schema.orders)
  .where(or(
    eq(schema.orders.status, 'active'),
    eq(schema.orders.status, 'pending')
  ))

not — Negate a Condition

import { not, inArray } from 'drizzle-orm'

const result = await db.select()
  .from(schema.orders)
  .where(not(inArray(schema.orders.status, ['cancelled', 'expired'])))

Combining Operators

Nest logical operators for complex queries.

import { and, or, eq, gte, isNull } from 'drizzle-orm'

const result = await db.select()
  .from(schema.orders)
  .where(and(
    or(
      eq(schema.orders.status, 'active'),
      eq(schema.orders.status, 'shipped')
    ),
    gte(schema.orders.amount, 100),
    isNull(schema.orders.deletedAt)
  ))

On this page