@samyx/drizzler-filters-sorters

Type-safe filter and sorter builders for Drizzle ORM with comprehensive operator support and Zod schema validation. Build dynamic, validated queries while maintaining full TypeScript type safety from your database schema to your API.

Table of Contents

• Installation
• Motivation
• Features
• Usage
  • Basic Filtering
  • Complex Filters
  • Sorting
  • Zod Schema Validation
• API Reference
  • Type Exports
  • Filter Operators
  • buildWhereClause
  • buildSortClause
  • Zod Schema Helpers
• Next Steps
• Contributing
• License

Installation

npm install @samyx/drizzler-filters-sorters
# or
yarn add @samyx/drizzler-filters-sorters
# or
pnpm add @samyx/drizzler-filters-sorters
# or
bun add @samyx/drizzler-filters-sorters

Peer Dependencies:

• drizzle-orm ^0.40.0
• zod ^4.1.12
• @standard-schema/spec ^1.0.0

Motivation

Building dynamic filters and sorting for database queries is a common requirement in modern applications, especially when working with APIs that need to support flexible data filtering. However, implementing this functionality can be:

• Repetitive and error-prone: Writing filter logic for each endpoint manually
• Type-unsafe: Losing TypeScript type safety when constructing dynamic queries
• Inconsistent: Different filtering patterns across your codebase
• Vulnerable to injection: Without proper validation, dynamic queries can be risky

@samyx/drizzler-filters-sorters solves these problems by providing:

• Type-safe filter builders that work seamlessly with Drizzle ORM's type system
• Comprehensive operator support including comparison, array operations, pattern matching, and more
• Built-in Zod validation for request payload validation
• Composable complex filters with AND/OR logical operators
• Consistent API across your entire application

Features

• 🔒 100% Type-Safe with Auto-Inference: Automatically infers types from your Drizzle table schema using InferData<T> from @samyx/drizzler-utils - no manual type definitions needed! Catch errors at compile time, not runtime.
• 🎯 60+ Filter Operators: Comprehensive support including:
  • Comparison operators: eq, neq, gt, gte, lt, lte
  • 16 Value pattern operators: like, startswith, endswith, contains (with case-insensitive & negative variants)
  • 24 Array element match operators: array_any_*, array_all_* for advanced array filtering
  • Array operations: in_array, array_contains, array_overlaps, and more
  • Null checks: isnull, not_isnull, string_isempty, array_isempty
• 🔄 Complex Logic: Compose filters with nested AND/OR conditions while preserving complete type safety
• 📊 Sorting Support: Build ORDER BY clauses with ASC/DESC and NULLS LAST handling, with type-checked column names
• ✅ Zod Integration: Generate validation schemas from your Drizzle models with full type inference
• 🎨 Extensible: Add custom type-safe filter operators
• 🚀 PostgreSQL Optimized: Built specifically for Postgres with advanced array operation support
• 🛡️ SQL Injection Safe: Uses Drizzle's parameterized queries under the hood

Usage

Basic Filtering

import { drizzle } from 'drizzle-orm/postgres-js';
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { buildWhereClause, type FilterOptions } from '@samyx/drizzler-filters-sorters';
import type { InferSelectModel } from 'drizzle-orm';

// Define your schema
const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name'),
  email: text('email'),
  age: integer('age'),
});

// Option 1: Automatic type inference from table (Recommended!)
const filter1 = {
  age: { gte: 18 },
  email: { like: '%@example.com' }
};
const whereClause1 = buildWhereClause(users, filter1);
// ✅ TypeScript automatically infers correct types from users table columns!

// Option 2: Explicit type definition for API/validation layer
type UserSelect = InferSelectModel<typeof users>;
type UserFilter = FilterOptions<UserSelect>;

const filter2: UserFilter = {
  age: { gte: 18 },
  email: { like: '%@example.com' }
  // age: { like: '%test%' } // ❌ TypeScript error: string not assignable to number!
};

const whereClause2 = buildWhereClause(users, filter2);

// Use in your query
const db = drizzle(/* your connection */);
const results = await db.select().from(users).where(whereClause1);

Complex Filters

Combine multiple conditions with AND/OR logic using ComplexFilterCondition:

import { buildWhereClause, type ComplexFilterCondition } from '@samyx/drizzler-filters-sorters';

// Define complex filter type
type UserComplexFilter = ComplexFilterCondition<{
  id: number;
  name: string;
  age: number;
  status: string;
  premium: boolean;
}>;

// Complex filter: (age >= 18 AND status = 'active') OR premium = true
const complexFilter: UserComplexFilter = {
  operator: 'or',
  conditions: [
    {
      operator: 'and',
      conditions: [
        { age: { gte: 18 } },
        { status: { eq: 'active' } }
      ]
    },
    { premium: { eq: true } }
  ]
};

const whereClause = buildWhereClause(users, complexFilter);
const results = await db.select().from(users).where(whereClause);

Sorting

Build type-safe ORDER BY clauses using SortOptions:

import { buildSortClause, type SortOptions } from '@samyx/drizzler-filters-sorters';

// Define your sort type - ensures column names are valid!
type UserSort = SortOptions<{
  id: number;
  name: string;
  email: string;
  age: number;
}>;

// Sort by multiple columns - TypeScript will validate column names
const sort: UserSort = [
  { column: 'age', order: 'desc' },
  { column: 'name', order: 'asc' }
  // { column: 'invalid', order: 'asc' } // ❌ TypeScript error!
];

const orderBy = buildSortClause(users, sort);
const results = await db
  .select()
  .from(users)
  .orderBy(...orderBy);

Zod Schema Validation

Generate validation schemas for your API endpoints:

import { z } from 'zod';
import {
  createSimpleFilterConditionSchema,
  createComplexFilterConditionSchema
} from '@samyx/drizzler-filters-sorters';

// Define your select schema
const userSelectSchema = z.object({
  id: z.number(),
  name: z.string(),
  email: z.string(),
  age: z.number(),
});

// Generate filter schema
const filterSchema = z.union([
  createSimpleFilterConditionSchema(userSelectSchema),
  createComplexFilterConditionSchema(userSelectSchema)
]);

// Use in your API validation
const apiSchema = z.object({
  filter: filterSchema.optional(),
  sort: z.array(z.object({
    column: z.enum(['id', 'name', 'email', 'age']),
    order: z.enum(['asc', 'desc'])
  })).optional()
});

// Validate incoming requests
const validated = apiSchema.parse(requestBody);

API Reference

Type Exports

The package exports several key TypeScript types for type-safe filtering and sorting:

FilterOptions

Main type for filter conditions - can be either simple or complex.

import type { FilterOptions } from '@samyx/drizzler-filters-sorters';

type UserFilter = FilterOptions<{
  id: number;
  name: string;
  age: number;
}>;

// Simple filter
const simpleFilter: UserFilter = {
  age: { gte: 18 }
};

// Complex filter
const complexFilter: UserFilter = {
  operator: 'and',
  conditions: [
    { age: { gte: 18 } },
    { name: { like: '%John%' } }
  ]
};

SimpleFilterCondition

Type for simple (non-nested) filter conditions.

import type { SimpleFilterCondition } from '@samyx/drizzler-filters-sorters';

type UserSimpleFilter = SimpleFilterCondition<{
  id: number;
  name: string;
  age: number;
}>;

const filter: UserSimpleFilter = {
  age: { gte: 18, lte: 65 },
  name: { like: '%Smith%' }
};

ComplexFilterCondition

Type for complex filters with nested AND/OR logic.

import type { ComplexFilterCondition } from '@samyx/drizzler-filters-sorters';

type UserComplexFilter = ComplexFilterCondition<{
  id: number;
  status: string;
  premium: boolean;
}>;

const filter: UserComplexFilter = {
  operator: 'or',
  conditions: [
    { status: { eq: 'active' } },
    { premium: { eq: true } }
  ]
};

SortOptions

Type for sort/order by options.

import type { SortOptions } from '@samyx/drizzler-filters-sorters';

type UserSort = SortOptions<{
  id: number;
  name: string;
  createdAt: Date;
}>;

const sort: UserSort = [
  { column: 'createdAt', order: 'desc' },
  { column: 'name', order: 'asc' }
];

FilterOperators

Union type of all available filter operators (60+ operators):

type FilterOperators =
  // Unary Operators
  | UnaryFilterOperator // isnull, not_isnull, string_isempty, not_string_isempty

  // Binary Comparison Operators
  | BinaryComparisonFilterOperator // eq, neq, gt, gte, lt, lte + value match operators

  // Value Match Operators (16 total)
  | ValueMatchFilterOperator // like, startswith, endswith, contains (with i_*, not_*, not_i_* variants)

  // Array Value Operators
  | ArrayValueFilterOperator // in_array, not_in_array

  // Array Unary Operators
  | ArrayUnaryFilterOperator // array_isempty, not_array_isempty

  // Array Comparison Operators
  | ArrayComparisonFilterOperator // array_contains, not_array_contains, array_contained, not_array_contained, array_overlaps, not_array_overlaps

  // Array Element Match Operators (32 total)
  | ArrayElementMatchFilterOperator // array_any_*, array_all_* (like, startswith, endswith, contains with i_* and not_* variants)

Value Match Operators (16 operators):

• like, not_like, i_like, not_i_like
• startswith, not_startswith, i_startswith, not_i_startswith
• endswith, not_endswith, i_endswith, not_i_endswith
• contains, not_contains, i_contains, not_i_contains

Array Element Match Operators (32 operators):

• Basic operators (8): array_any_like, array_any_i_like, array_all_like, array_all_i_like, not_array_any_like, not_array_any_i_like, not_array_all_like, not_array_all_i_like
• Custom pattern operators (24): array_any_startswith, array_any_endswith, array_any_contains, array_all_startswith, array_all_endswith, array_all_contains (each with i_* and not_* variants)

LogicalOperator

Union type for logical operators in complex filters.

type LogicalOperator = 'and' | 'or';

FilterOperatorFunction

Type for custom filter operator functions.

import type { FilterOperatorFunction } from '@samyx/drizzler-filters-sorters';

const customOperator: FilterOperatorFunction = (col, value) => {
  return sql`${col} CUSTOM_OP ${value}`;
};

Filter Operators

All operators support both modern snake_case naming (recommended) and legacy camelCase (deprecated).

Unary Operators (No value required)

Null Checks:

• isnull / isNull (deprecated): Column is NULL
• not_isnull / isNotNull (deprecated): Column is NOT NULL

Empty Checks:

• string_isempty: String is empty ('')
• not_string_isempty: String is not empty
• array_isempty: Array is empty ([])
• not_array_isempty: Array is not empty

Binary Comparison Operators

• eq: Equal to
• neq: Not equal to
• gt: Greater than
• gte: Greater than or equal to
• lt: Less than
• lte: Less than or equal to

Value Pattern Matching (16 operators)

Basic Pattern:

• like: SQL LIKE with custom pattern
• not_like: SQL NOT LIKE

Startswith Pattern:

• startswith: Value starts with pattern (adds % suffix)
• not_startswith: Value does not start with pattern
• i_startswith: Case-insensitive startswith
• not_i_startswith: Case-insensitive negative startswith

Endswith Pattern:

• endswith: Value ends with pattern (adds % prefix)
• not_endswith: Value does not end with pattern
• i_endswith: Case-insensitive endswith
• not_i_endswith: Case-insensitive negative endswith

Contains Pattern:

• contains: Value contains pattern (adds % prefix and suffix)
• not_contains: Value does not contain pattern
• i_contains: Case-insensitive contains
• not_i_contains: Case-insensitive negative contains

Legacy operators (deprecated):

• ilike (use i_like), notLike (use not_like), notIlike (use not_i_like)

Array Value Operators

• in_array / inArray (deprecated): Value is in array
• not_in_array / notInArray (deprecated): Value is not in array

Array Comparison Operators

• array_contains / arrayContains (deprecated): Array contains all specified elements
• not_array_contains / notArrayContains (deprecated): Array does not contain all elements
• array_contained / arrayContained (deprecated): Array is contained by specified array
• not_array_contained / notArrayContained (deprecated): Array is not contained
• array_overlaps / arrayOverlaps (deprecated): Arrays have overlapping elements
• not_array_overlaps / notArrayOverlaps (deprecated): Arrays have no overlap

Array Element Match Operators (32 operators)

Basic LIKE operators (8 total):

• array_any_like / arrayAnyLike (deprecated): Any array element matches LIKE pattern
• not_array_any_like / notArrayAnyLike (deprecated): No element matches LIKE
• array_any_i_like / arrayAnyILike (deprecated): Any element matches ILIKE (case-insensitive)
• not_array_any_i_like / notArrayAnyILike (deprecated): No element matches ILIKE
• array_all_like / arrayAllLike (deprecated): All elements match LIKE
• not_array_all_like / notArrayAllLike (deprecated): Not all elements match LIKE
• array_all_i_like / arrayAllILike (deprecated): All elements match ILIKE
• not_array_all_i_like / notArrayAllILike (deprecated): Not all elements match ILIKE

Custom Pattern operators (24 total):

ANY operators (12):

• array_any_startswith, not_array_any_startswith, array_any_i_startswith, not_array_any_i_startswith
• array_any_endswith, not_array_any_endswith, array_any_i_endswith, not_array_any_i_endswith
• array_any_contains, not_array_any_contains, array_any_i_contains, not_array_any_i_contains

ALL operators (12):

• array_all_startswith, not_array_all_startswith, array_all_i_startswith, not_array_all_i_startswith
• array_all_endswith, not_array_all_endswith, array_all_i_endswith, not_array_all_i_endswith
• array_all_contains, not_array_all_contains, array_all_i_contains, not_array_all_i_contains

Examples:

// Value pattern matching
const filter1 = {
  email: { endswith: '@example.com' },        // Ends with domain
  username: { i_startswith: 'admin' },        // Case-insensitive prefix
  bio: { not_i_contains: 'spam' }             // Exclude spam
};

// Array element matching
const filter2 = {
  tags: {
    array_any_startswith: 'tech-',            // Any tag starts with "tech-"
    array_all_contains: '-approved'            // All tags contain "-approved"
  },
  categories: {
    array_any_i_contains: 'TYPESCRIPT'         // Any category contains "typescript" (case-insensitive)
  }
};

buildWhereClause

function buildWhereClause<T, Select>(
  table: T,
  filter: FilterOptions<Select>,
  customOperators?: Record<string, FilterOperatorFunction>
): SQL | undefined

Builds a WHERE clause from filter options.

Parameters:

• table: Your Drizzle table definition
• filter: Simple or complex filter condition
• customOperators: Optional custom filter operator functions

Returns: SQL fragment for WHERE clause, or undefined if no valid conditions

buildSortClause

function buildSortClause<T, Select>(
  table: T,
  sort: SortOptions<Select> | undefined | null
): SQL[]

Builds ORDER BY clauses from sort options.

Parameters:

• table: Your Drizzle table definition
• sort: Array of column/order pairs

Returns: Array of SQL fragments for ORDER BY clause

Zod Schema Helpers

createSimpleFilterConditionSchema

function createSimpleFilterConditionSchema(
  selectSchema: z.ZodObject<any>
): z.ZodSchema

Creates a Zod schema for simple filter conditions based on your model schema.

createComplexFilterConditionSchema

function createComplexFilterConditionSchema(
  selectSchema: z.ZodObject<any>
): z.ZodSchema

Creates a Zod schema for complex (nested AND/OR) filter conditions.

createComparisonSchema

function createComparisonSchema(
  valueSchema: z.ZodTypeAny
): z.ZodSchema

Creates a Zod schema for all filter operators for a specific value type.

Next Steps

Planned Features

• MySQL Support: Extend filter operators for MySQL dialect
• SQLite Support: Add SQLite-specific filter implementations
• Custom Operator Registry: Plugin system for registering custom operators
• Query Builder Integration: Helper functions for common query patterns
• Performance Optimizations: Index hints and query optimization suggestions
• Filter Presets: Save and reuse common filter combinations
• JSON/JSONB Operations: Specialized operators for JSON column filtering
• Full-text Search: Integration with PostgreSQL full-text search
• Aggregation Helpers: Support for filtered aggregations
• Migration from Other ORMs: Guides and helpers for migrating from Prisma, TypeORM, etc.

Roadmap

v0.2.0: Multi-dialect support (MySQL, SQLite) v0.3.0: Custom operator plugin system v0.4.0: Advanced JSON/JSONB operations v1.0.0: Stable API with comprehensive test coverage

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT