Skip to main content
The Drizzle adapter transforms filter definitions into SQL expressions that integrate seamlessly with Drizzle ORM queries. Define your filters once and get parameterized SQL that works with PostgreSQL, MySQL, and SQLite.

​
Installation

1

Install the packages

npm install @filter-def/drizzle drizzle-orm
# or
pnpm add @filter-def/drizzle drizzle-orm
drizzle-orm is a peer dependency and must be installed separately.

​
Basic Usage

Define filters for your Drizzle table and use them in queries: 
import { drizzleFilter } from "@filter-def/drizzle";
import type { DrizzleFilterInput } from "@filter-def/drizzle";
import { pgTable, text, integer, boolean } from "drizzle-orm/pg-core";
import { drizzle } from "drizzle-orm/node-postgres";

// 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 db = drizzle(client);

const where = userFilter({
    name: "John",
    emailContains: "@example.com",
    minAge: 18,
    isActive: true,
});

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

​
Filter Types

The Drizzle adapter supports all standard filter kinds and maps them to Drizzle operators:
KindDrizzle OperatorSQL OutputExample
eqeq()={ name: { kind: "eq" } }
neqne()!={ status: { kind: "neq" } }
containslike() / ilike()LIKE / ILIKE{ email: { kind: "contains" } }
inArrayinArray()IN{ status: { kind: "inArray" } }
gtgt()>{ age: { kind: "gt" } }
gtegte()>={ price: { kind: "gte" } }
ltlt()<{ age: { kind: "lt" } }
ltelte()<={ price: { kind: "lte" } }
isNullisNull() / isNotNull()IS NULL / IS NOT NULL{ deletedAt: { kind: "isNull" } }
isNotNullisNotNull() / isNull()IS NOT NULL / IS NULL{ email: { kind: "isNotNull" } }

​
Case-Insensitive String Matching

Use caseInsensitive: true to use ILIKE instead of LIKE (PostgreSQL): 
const userFilter = drizzleFilter(usersTable).def({
    nameSearch: {
        kind: "contains",
        field: "name",
        caseInsensitive: true, // Uses ilike('%value%')
    },
});

const where = userFilter({ nameSearch: "john" });
// SQL: name ILIKE '%john%'
In MySQL, LIKE is case-insensitive by default depending on collation. In SQLite, LIKE is case-insensitive for ASCII characters.

​
Empty Filters

The filter function returns undefined when no filters are provided: 
const where = userFilter({});
// where: undefined

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

// Drizzle handles undefined gracefully
await db.select().from(usersTable).where(where);
// SQL: SELECT * FROM users (no WHERE clause)

​
Boolean Filters (AND/OR)

Combine multiple conditions with logical operators: 
const userFilter = 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",
    ageRange: 30,
});
// SQL: (name LIKE '%john%' OR email LIKE '%john%') AND (age >= 30 AND age <= 30)

await db.select().from(usersTable).where(where);
All conditions in boolean filters must have explicit field properties.

​
Custom Filters

Define custom SQL expressions for complex filtering logic: 
import { sql, eq, and, gte, lte } from "drizzle-orm";

const postsTable = pgTable("posts", {
    id: integer("id").primaryKey(),
    title: text("title").notNull(),
    content: text("content").notNull(),
    authorId: integer("author_id").notNull(),
    viewCount: integer("view_count").notNull(),
    likeCount: integer("like_count").notNull(),
    commentCount: integer("comment_count").notNull(),
    publishedAt: timestamp("published_at").notNull(),
    isPublished: boolean("is_published").notNull(),
});

const postFilter = drizzleFilter(postsTable).def({
    // Standard primitive filters
    id: { kind: "eq" },
    authorId: { kind: "eq" },
    titleContains: { kind: "contains", field: "title", caseInsensitive: true },

    // Custom filter: Views divisible by a number
    viewsDivisibleBy: (divisor: number) =>
        sql`${postsTable.viewCount} % ${divisor} = 0`,

    // Custom filter: Published within X days
    publishedWithinDays: (days: number) =>
        sql`${postsTable.publishedAt} >= NOW() - INTERVAL '${sql.raw(String(days))} days'`,

    // Custom filter: Date range
    publishedBetween: (range: { start: Date; end: Date }) =>
        and(
            gte(postsTable.publishedAt, range.start),
            lte(postsTable.publishedAt, range.end)
        ),

    // Custom filter: Minimum engagement (likes + comments)
    minEngagement: (minTotal: number) =>
        sql`${postsTable.likeCount} + ${postsTable.commentCount} >= ${minTotal}`,

    // Custom filter: Popularity score (weighted calculation)
    minPopularityScore: (minScore: number) =>
        sql`(${postsTable.viewCount} * 1 + ${postsTable.likeCount} * 5 + ${postsTable.commentCount} * 10) >= ${minScore}`,

    // Custom filter: Return undefined to skip filtering
    optionalStatus: (status: "published" | "draft" | "all") =>
        status === "all"
            ? undefined
            : eq(postsTable.isPublished, status === "published"),
});

// Usage
const trendingPosts = await db
    .select()
    .from(postsTable)
    .where(
        postFilter({
            publishedWithinDays: 14,
            minEngagement: 30,
            minPopularityScore: 3000,
        })
    );

​
Conditional Filters

Return undefined from custom filters to skip them conditionally: 
const userFilter = drizzleFilter(usersTable).def({
    // Skip filter when status is "all"
    optionalStatus: (status: string | "all") =>
        status === "all" ? undefined : eq(usersTable.status, status),
});

const where1 = userFilter({ optionalStatus: "all" });
// where1: undefined (no filtering)

const where2 = userFilter({ optionalStatus: "active" });
// where2: SQL expression for status = 'active'
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);
EXISTS subqueries are often more performant than JOINs for filtering because they can short-circuit as soon as a match is found.

​
Complete Example

Here’s a real-world product catalog implementation: 
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
const db = drizzle(client);

async function searchProducts(
    input: ProductFilterInput
) {
    const where = productFilter(input);
    return db.select().from(productsTable).where(where);
}

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

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

​
Type Utilities

​
Extract Filter Input Type

Use DrizzleFilterInput to extract the input type: 
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 }

async function searchUsers(filters: UserFilterInput) {
    const where = userFilter(filters);
    return db.select().from(usersTable).where(where);
}

​
Custom Filter Type

Use DrizzleCustomFilter to type custom filter functions: 
import type { DrizzleCustomFilter } from "@filter-def/drizzle";
import type { SQL } from "drizzle-orm";

type DivisibleByFilter = DrizzleCustomFilter<number>;
// (input: number) => SQL | undefined

const ageDivisibleBy: DivisibleByFilter = (divisor) =>
    sql`${usersTable.age} % ${divisor} = 0`;

​
Database-Specific Behaviors

  • contains uses LIKE (case-sensitive) by default
  • contains with caseInsensitive: true uses ILIKE
  • All Drizzle PostgreSQL operators are supported
  • Use sql template for PostgreSQL-specific functions

​
Limitations

Nested Fields Not SupportedNested 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.
// ❌ This will throw an error
const filter = drizzleFilter(usersTable).def({
    firstName: { kind: "eq", field: "name.first" },
});
For nested data (e.g., JSON columns), use a custom filter with Drizzle’s JSON operators.
SQL Injection ProtectionWhen using custom filters with sql template, always use parameterized queries. Never concatenate user input directly into SQL strings.
// ✅ Safe: Uses parameterization
const filter = drizzleFilter(table).def({
    custom: (value: string) => sql`column = ${value}`,
});

// ❌ Unsafe: SQL injection risk
const filter = drizzleFilter(table).def({
    custom: (value: string) => sql.raw(`column = '${value}'`),
});

Build docs developers (and LLMs) love

Get started for freeTalk to us