@filter-def/drizzle

Drizzle ORM adapter for filter-def. Define type-safe filters that compile to SQL WHERE clauses.

Installation

npm install @filter-def/drizzle drizzle-orm
# or
pnpm add @filter-def/drizzle drizzle-orm

Note: drizzle-orm is a peer dependency and must be installed separately.

Quick Start

import { drizzleFilter } from '@filter-def/drizzle';
import type { DrizzleFilterInput } from '@filter-def/drizzle';
import { pgTable, text, integer, boolean } from 'drizzle-orm/pg-core';

// Define your Drizzle table
const usersTable = pgTable('users', {
    id: integer('id').primaryKey(),
    name: text('name').notNull(),
    email: text('email').notNull(),
    age: integer('age').notNull(),
    isActive: boolean('is_active').notNull(),
});

// Create a filter definition
const userFilter = drizzleFilter(usersTable).def({
    name: { kind: 'eq' },
    emailContains: { kind: 'contains', field: 'email' },
    minAge: { kind: 'gte', field: 'age' },
    isActive: { kind: 'eq' },
});

// Use in queries
const where = userFilter({
    name: 'John',
    emailContains: '@example.com',
    minAge: 18,
    isActive: true,
});

const results = await db.select().from(usersTable).where(where);

Features

• Type-safe: Filter inputs are inferred from your Drizzle table schema
• SQL compilation: Filters compile to efficient Drizzle SQL expressions
• Composable: Combine filters with AND/OR logic
• Custom filters: Write raw SQL for complex queries and subqueries
• Returns SQL | undefined: Empty filters return undefined for clean query composition

API

drizzleFilter(table)

Creates a filter builder for the specified Drizzle table.

const productFilter = drizzleFilter(productsTable).def({
    // Filter definitions...
});

Filter Output

The filter function returns SQL | undefined:

const where = userFilter({ name: 'John' });
// where: SQL

const empty = userFilter({});
// empty: undefined

const noInput = userFilter();
// noInput: undefined

Filter Types

Primitive Filters

| Kind | Drizzle Operator | Description | | ----------- | ------------------------ | -------------------------------- | | eq | eq() | Exact equality | | neq | ne() | Not equal | | contains | like('%value%') | String contains (case-sensitive) | | inArray | inArray() | Value in array | | gt | gt() | Greater than | | gte | gte() | Greater than or equal | | lt | lt() | Less than | | lte | lte() | Less than or equal | | isNull | isNull()/isNotNull() | Check null | | isNotNull | isNotNull()/isNull() | Check not null |

Field Inference

When the filter name matches a column name, the field property is inferred:

const filter = drizzleFilter(usersTable).def({
    name: { kind: 'eq' }, // field: "name" inferred
    email: { kind: 'contains' }, // field: "email" inferred
    minAge: { kind: 'gte', field: 'age' }, // explicit field required
});

Nested Fields

Nested field paths (e.g., "name.first") are not supported by the Drizzle adapter, since Drizzle operates on flat table columns. Using a dot-separated path will throw a runtime error. For nested data (e.g., JSON columns), use a custom filter with Drizzle's JSON operators.

Case-Insensitive Contains

Use caseInsensitive: true to use ILIKE instead of LIKE:

const filter = drizzleFilter(usersTable).def({
    nameSearch: {
        kind: 'contains',
        field: 'name',
        caseInsensitive: true, // Uses ilike('%value%')
    },
});

Boolean Filters (AND/OR)

Combine conditions with logical operators. All conditions must have explicit field properties.

const filter = drizzleFilter(usersTable).def({
    // OR: match any condition
    searchTerm: {
        kind: 'or',
        conditions: [
            { kind: 'contains', field: 'name' },
            { kind: 'contains', field: 'email' },
        ],
    },

    // AND: match all conditions
    ageRange: {
        kind: 'and',
        conditions: [
            { kind: 'gte', field: 'age' },
            { kind: 'lte', field: 'age' },
        ],
    },
});

const where = userFilter({
    searchTerm: 'john', // name LIKE '%john%' OR email LIKE '%john%'
    ageRange: 30, // age >= 30 AND age <= 30
});

Custom Filters

Custom filters receive the input value and return a Drizzle SQL expression:

import { sql, eq, exists } from 'drizzle-orm';

const userFilter = drizzleFilter(usersTable).def({
    // Raw SQL expression
    ageDivisibleBy: (divisor: number) =>
        sql`${usersTable.age} % ${divisor} = 0`,

    // Return undefined to skip filter
    optionalStatus: (status: string | 'all') =>
        status === 'all' ? undefined : eq(usersTable.status, status),

    // EXISTS subquery for related tables
    hasPostWithTitle: (title: string) =>
        exists(
            db
                .select()
                .from(postsTable)
                .where(
                    and(
                        eq(postsTable.authorId, usersTable.id),
                        ilike(postsTable.title, `%${title}%`)
                    )
                )
        ),
});

Related Tables / Joins

The drizzle adapter generates WHERE clauses, not JOIN clauses. For filtering by related table data, use EXISTS subqueries via custom filters:

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

const usersTable = pgTable('users', {
    id: integer('id').primaryKey(),
    name: text('name').notNull(),
});

const postsTable = pgTable('posts', {
    id: integer('id').primaryKey(),
    authorId: integer('author_id').notNull(),
    title: text('title').notNull(),
});

const userFilter = drizzleFilter(usersTable).def({
    name: { kind: 'eq' },

    // Custom filter with EXISTS subquery
    hasPostWithTitle: (title: string) =>
        exists(
            db
                .select()
                .from(postsTable)
                .where(
                    and(
                        eq(postsTable.authorId, usersTable.id),
                        ilike(postsTable.title, `%${title}%`)
                    )
                )
        ),
});

// No join needed - EXISTS handles the relationship
const where = userFilter({ hasPostWithTitle: 'TypeScript' });
const authors = await db.select().from(usersTable).where(where);

Type Utilities

DrizzleFilterInput<T>

Extract the input type from a filter definition:

import type { DrizzleFilterInput } from '@filter-def/drizzle';

const userFilter = drizzleFilter(usersTable).def({
    name: { kind: 'eq' },
    minAge: { kind: 'gte', field: 'age' },
});

type UserFilterInput = DrizzleFilterInput<typeof userFilter>;
// { name?: string; minAge?: number }

DrizzleFilter<TFilterInput>

Type for the compiled filter function:

import type { DrizzleFilter } from '@filter-def/drizzle';

// The filter is a function that takes input and returns SQL | undefined
type UserFilter = DrizzleFilter<{ name?: string }>;
// (filterInput?: { name?: string }) => SQL | undefined

DrizzleCustomFilter<Input>

Type for custom filter functions:

import type { DrizzleCustomFilter } from '@filter-def/drizzle';

// Custom filters take input and return SQL | undefined
type DivisibleByFilter = DrizzleCustomFilter<number>;
// (input: number) => SQL | undefined

Complete Example

import { drizzleFilter } from '@filter-def/drizzle';
import type { DrizzleFilterInput } from '@filter-def/drizzle';
import {
    pgTable,
    text,
    integer,
    boolean,
    timestamp,
} from 'drizzle-orm/pg-core';
import { drizzle } from 'drizzle-orm/node-postgres';

// Table definition
const productsTable = pgTable('products', {
    id: integer('id').primaryKey(),
    name: text('name').notNull(),
    description: text('description'),
    price: integer('price').notNull(),
    category: text('category').notNull(),
    inStock: boolean('in_stock').notNull(),
    createdAt: timestamp('created_at').notNull(),
});

// Filter definition
const productFilter = drizzleFilter(productsTable).def({
    // Inferred fields
    name: { kind: 'eq' },
    category: { kind: 'eq' },
    inStock: { kind: 'eq' },

    // Explicit fields
    nameContains: { kind: 'contains', field: 'name', caseInsensitive: true },
    minPrice: { kind: 'gte', field: 'price' },
    maxPrice: { kind: 'lte', field: 'price' },
    inCategories: { kind: 'inArray', field: 'category' },

    // Boolean filter for search
    search: {
        kind: 'or',
        conditions: [
            { kind: 'contains', field: 'name' },
            { kind: 'contains', field: 'description' },
        ],
    },
});

type ProductFilterInput = DrizzleFilterInput<typeof productFilter>;

// Usage
async function searchProducts(
    db: ReturnType<typeof drizzle>,
    input: ProductFilterInput
) {
    const where = productFilter(input);
    return db.select().from(productsTable).where(where);
}

// Example queries
const electronics = await searchProducts(db, {
    category: 'electronics',
    inStock: true,
    maxPrice: 500,
});

const searchResults = await searchProducts(db, {
    search: 'laptop',
    minPrice: 200,
    maxPrice: 1000,
});

Database-Specific Behaviors

PostgreSQL

• contains uses LIKE (case-sensitive) by default
• contains with caseInsensitive: true uses ILIKE
• All Drizzle PostgreSQL operators are supported

MySQL / SQLite

• contains uses LIKE which is case-insensitive by default in MySQL
• caseInsensitive option may behave differently depending on collation

Related Packages

• @filter-def/core - Core types and utilities
• @filter-def/in-memory - In-memory filtering with native array methods
• @filter-def/bigquery - BigQuery adapter