Drizzle Integration

The @jlnstack/filter/drizzle entry point provides utilities for converting filter values to Drizzle ORM SQL conditions.

createWhereBuilder

Create a reusable where clause builder by mapping your filter schema to database columns:

import { createWhereBuilder } from "@jlnstack/filter/drizzle"
import { stringFilter, numberFilter, booleanFilter } from "@jlnstack/filter"
import { users } from "./schema"

const schema = {
  name: stringFilter(),
  age: numberFilter(),
  verified: booleanFilter(),
}

// Map filter fields to database columns
const toWhere = createWhereBuilder(schema, {
  name: users.name,
  age: users.age,
  verified: users.emailVerified,
})

// Convert filters to SQL - built-in filters work automatically!
const where = toWhere(filter)

// Use in query
const results = await db.select().from(users).where(where)

Built-in filters (stringFilter, numberFilter, dateFilter, booleanFilter) are automatically converted to the appropriate SQL conditions based on their operators. No manual handlers needed.

API

function createWhereBuilder<Schema>(
  schema: Schema,
  columns: ColumnMapping<Schema>
): ToWhereFn<Schema>

Parameters

Parameter	Type	Description
`schema`	`Schema`	Your filter schema
`columns`	`ColumnMapping<Schema>`	Mapping of filter field names to Drizzle columns

Return Value

Returns a function that converts a filter tree to SQL:

// If schema has only built-in filters:
(filter: Group<Schema>, options?: { handlers?: CustomHandlers }) => SQL | undefined

// If schema has custom filters:
(filter: Group<Schema>, options: { handlers: CustomHandlers }) => SQL | undefined

Built-in Filter Conversions

The following conversions are applied automatically:

String Filter

Operator	SQL
`eq`	`column = value`
`neq`	`column <> value`
`contains`	`column LIKE '%value%'`
`startsWith`	`column LIKE 'value%'`
`endsWith`	`column LIKE '%value'`

Number Filter

Operator	SQL
`eq`	`column = value`
`neq`	`column <> value`
`gt`	`column > value`
`gte`	`column >= value`
`lt`	`column < value`
`lte`	`column <= value`

Date Filter

Operator	SQL
`eq`	`column = value`
`neq`	`column <> value`
`gt`	`column > value`
`gte`	`column >= value`
`lt`	`column < value`
`lte`	`column <= value`

Boolean Filter

Boolean values are converted directly to equality checks: column = value

Custom Filters

For custom filter types, you must provide handlers:

import { createFilter } from "@jlnstack/filter"
import { createWhereBuilder } from "@jlnstack/filter/drizzle"
import { and, gte, lte } from "drizzle-orm"

const dateRangeFilter = createFilter("dateRange")
  .input<{ from: string; to: string }>()
  .options()()

const schema = {
  name: stringFilter(),
  dateRange: dateRangeFilter,
}

const toWhere = createWhereBuilder(schema, {
  name: users.name,
})

// Custom filters require handlers - TypeScript enforces this
const where = toWhere(filter, {
  handlers: {
    dateRange: (v) => and(
      gte(users.createdAt, v.from),
      lte(users.createdAt, v.to)
    ),
  },
})

TypeScript will require the handlers option when your schema includes custom filters.

Overriding Built-in Behavior

You can override the default handling for built-in filters:

const toWhere = createWhereBuilder(schema, {
  name: users.name,
  age: users.age,
})

// Override the name filter with custom logic
const where = toWhere(filter, {
  handlers: {
    name: (v) => {
      // Custom case-insensitive search
      return ilike(users.name, `%${v.value}%`)
    },
  },
})

Complete Example

// filters.ts
import { defineFilters, stringFilter, booleanFilter, dateFilter, createFilter } from "@jlnstack/filter"

const dateRangeFilter = createFilter("dateRange")
  .input<{ from: string; to: string }>()
  .options()()

export const userFilters = defineFilters({
  name: stringFilter({ label: "Name" }),
  verified: booleanFilter({ label: "Verified" }),
  createdAt: dateFilter({ label: "Created" }),
  dateRange: dateRangeFilter,
})

// db.ts
import { createWhereBuilder } from "@jlnstack/filter/drizzle"
import { and, gte, lte } from "drizzle-orm"
import { users } from "./schema"
import { userFilters } from "./filters"

export const toWhere = createWhereBuilder(userFilters, {
  name: users.name,
  verified: users.emailVerified,
  createdAt: users.createdAt,
})

export function getUsersWhere(filter: Group<typeof userFilters>) {
  return toWhere(filter, {
    handlers: {
      dateRange: (v) => and(
        gte(users.createdAt, v.from),
        lte(users.createdAt, v.to)
      ),
    },
  })
}

// page.tsx (Server Component)
import { db } from "@/db"
import { users } from "@/db/schema"
import { getUsersWhere } from "@/db"

export default async function UsersPage({ searchParams }) {
  const filter = parseFiltersFromSearchParams(searchParams)
  const where = getUsersWhere(filter)

  const data = await db
    .select()
    .from(users)
    .where(where)

  return <UsersTable data={data} />
}

Tips

Combine with Other Conditions

The result can be combined with other conditions:

const filterConditions = toWhere(filter)

const results = await db
  .select()
  .from(users)
  .where(and(
    eq(users.deletedAt, null), // Always exclude deleted
    filterConditions,
  ))

Handle Empty Results

toWhere returns undefined if no filters are active, which Drizzle handles correctly (no WHERE clause):

const where = toWhere(emptyFilter) // undefined
await db.select().from(users).where(where) // SELECT * FROM users

Drizzle Integration

On this page