05 - Filters & Operators

Drizzle ships every SQL comparison operator as a function. Import them from drizzle-orm and pass them to .where().

Basic Comparisons

import { eq, ne, gt, gte, lt, lte } from 'drizzle-orm';

// Exact match
await db.select().from(users).where(eq(users.role, 'admin'));
// WHERE role = 'admin'

// Not equal
await db.select().from(users).where(ne(users.status, 'banned'));
// WHERE status != 'banned'

// Greater than
await db.select().from(orders).where(gt(orders.total, 100));
// WHERE total > 100

// Greater than or equal
await db.select().from(orders).where(gte(orders.total, 50));
// WHERE total >= 50

// Less than
await db.select().from(users).where(lt(users.age, 18));
// WHERE age < 18

// Less than or equal
await db.select().from(users).where(lte(users.age, 65));
// WHERE age <= 65

String Matching

like is case-sensitive. ilike ignores case (PostgreSQL only).

import { like, ilike } from 'drizzle-orm';

// Case-sensitive
await db.select().from(users).where(like(users.name, 'Al%'));
// WHERE name LIKE 'Al%'

// Case-insensitive
await db.select().from(users).where(ilike(users.email, '%@gmail.com'));
// WHERE email ILIKE '%@gmail.com'

Null Checks

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

await db.select().from(users).where(isNull(users.deletedAt));
// WHERE deleted_at IS NULL

await db.select().from(users).where(isNotNull(users.verifiedAt));
// WHERE verified_at IS NOT NULL

Array and Range Operators

import { inArray, notInArray, between } from 'drizzle-orm';

// IN list
await db.select().from(users).where(inArray(users.role, ['admin', 'editor']));
// WHERE role IN ('admin', 'editor')

// NOT IN list
await db.select().from(orders).where(notInArray(orders.status, ['cancelled', 'refunded']));
// WHERE status NOT IN ('cancelled', 'refunded')

// BETWEEN range (inclusive on both ends)
await db.select().from(orders).where(between(orders.total, 10, 100));
// WHERE total BETWEEN 10 AND 100 (matches 10, 100, and everything in between)

Combining Conditions

Use and() and or() to build compound filters. They accept any number of conditions.

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

// AND: all conditions must match
const activeAdmins = await db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    eq(users.status, 'active')
  )
);
// WHERE role = 'admin' AND status = 'active'

// OR: any condition can match
const flagged = await db.select().from(orders).where(
  or(
    gt(orders.total, 10000),
    eq(orders.flagged, true)
  )
);
// WHERE total > 10000 OR flagged = true

Nesting AND/OR

Combine them for complex logic.

const results = await db.select().from(users).where(
  and(
    eq(users.status, 'active'),
    or(
      eq(users.role, 'admin'),
      eq(users.role, 'editor')
    )
  )
);
// WHERE status = 'active' AND (role = 'admin' OR role = 'editor')

Negation

not() inverts any condition.

import { not, eq } from 'drizzle-orm';

await db.select().from(users).where(not(eq(users.role, 'guest')));
// WHERE NOT (role = 'guest')

Common Mistake

❌ Passing multiple conditions directly to .where() without wrapping:

// Only the last condition applies
await db.select().from(users).where(
  eq(users.role, 'admin'),
  eq(users.status, 'active')  // This is NOT how you combine
);

✅ Wrap with and() or or():

await db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    eq(users.status, 'active')
  )
);

Key Takeaways

All operators are imported from drizzle-orm, not from the dialect package
eq, ne, gt, gte, lt, lte cover basic comparisons
like and ilike handle pattern matching; ilike is PostgreSQL only
inArray, notInArray, and between filter against sets and ranges
and(), or(), and not() combine conditions; always wrap multiple conditions explicitly
isNull and isNotNull check for null values instead of using eq(col, null)