Drizzle Filtering Pattern

Drizzle Filtering Pattern

Filter Drizzle queries with eq(), and(), or(), between(), sql template tag, and custom conditions

When to Use

• Adding WHERE clauses to Drizzle queries
• Combining multiple filter conditions with boolean logic
• Building dynamic filters from user input
• Using database-specific operators (LIKE, ILIKE, IN, BETWEEN)

Instructions

1. Basic equality with eq():

import { eq } from 'drizzle-orm';

const admins = await db.select().from(users).where(eq(users.role, 'admin'));

2. Comparison operators — ne, gt, gte, lt, lte:

import { gt, lte } from 'drizzle-orm';

const recentPosts = await db
  .select()
  .from(posts)
  .where(gt(posts.createdAt, new Date('2024-01-01')));

3. Combine conditions with and() and or():

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

const results = await db
  .select()
  .from(posts)
  .where(
    and(
      eq(posts.published, true),
      or(like(posts.title, `%${search}%`), like(posts.content, `%${search}%`))
    )
  );

4. IN operator with inArray and notInArray:

import { inArray } from 'drizzle-orm';

const tagged = await db
  .select()
  .from(posts)
  .where(inArray(posts.status, ['published', 'featured']));

5. BETWEEN:

import { between } from 'drizzle-orm';

const rangePosts = await db
  .select()
  .from(posts)
  .where(between(posts.viewCount, 100, 1000));

6. NULL checks with isNull and isNotNull:

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

const drafts = await db.select().from(posts).where(isNull(posts.publishedAt));

7. Pattern matching with like and ilike (PostgreSQL):

import { ilike } from 'drizzle-orm';

const matches = await db
  .select()
  .from(users)
  .where(ilike(users.name, `%${query}%`));

8. Negation with not:

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

const nonAdmins = await db
  .select()
  .from(users)
  .where(not(eq(users.role, 'admin')));

9. Build dynamic filters from user input:

import { and, eq, ilike, SQL } from 'drizzle-orm';

function buildFilters(params: SearchParams): SQL | undefined {
  const conditions: SQL[] = [];

  if (params.search) {
    conditions.push(ilike(posts.title, `%${params.search}%`));
  }
  if (params.authorId) {
    conditions.push(eq(posts.authorId, params.authorId));
  }
  if (params.published !== undefined) {
    conditions.push(eq(posts.published, params.published));
  }

  return conditions.length ? and(...conditions) : undefined;
}

const results = await db.select().from(posts).where(buildFilters(params));

10. Raw SQL conditions with the sql template tag:

import { sql } from 'drizzle-orm';

const results = await db
  .select()
  .from(posts)
  .where(sql`${posts.title} @@ plainto_tsquery('english', ${query})`);

Details

Drizzle filter operators are imported from drizzle-orm and return SQL objects that compose into WHERE clauses. The design mirrors SQL syntax, making it easy to translate SQL knowledge to Drizzle code.

Operator reference:

• Equality: eq, ne
• Comparison: gt, gte, lt, lte
• Set: inArray, notInArray
• Range: between, notBetween
• Null: isNull, isNotNull
• Pattern: like, ilike, notLike, notIlike
• Logic: and, or, not
• Existence: exists, notExists (for subqueries)

Relational query API filters: The relational API uses a different syntax:

const users = await db.query.users.findMany({
  where: (users, { eq, and }) => and(eq(users.role, 'admin'), eq(users.isActive, true)),
});

Type safety: Operators enforce column types at compile time. eq(users.email, 42) is a TypeScript error because email is a text column.

Trade-offs:

• ilike is PostgreSQL-specific — use like with manual lowercasing for MySQL compatibility
• and() and or() accept undefined values and filter them out, which makes dynamic filter building clean
• The sql template tag bypasses type checking — use it sparingly and validate inputs

Source

https://orm.drizzle.team/docs/operators

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.