@nodewave/prisma-ezfilter

A TypeScript package for building dynamic Prisma queries with type-safe filtering, searching, ordering, and range filtering with support for multi-level nested relations. This package provides a flexible and extensible way to translate standard query parameters into Prisma query options with optional validation and field mapping.

Features

• 🎯 Type-safe query building with full TypeScript support
• 🔍 Dynamic filtering with support for exact matches, arrays, and relational data
• 🔗 Multi-level nested relations - handle deeply nested fields like author.profile.address.city
• 🔎 Search functionality with contains operator, multi-field search, case-sensitive/insensitive modes, and multiple keywords support
• 📊 Range filtering for dates and numbers with automatic date parsing
• 📝 Ordering with customizable sort directions
• 📄 Pagination with configurable page sizes
• ✅ Validation with customizable specifications
• 🔄 Field mapping and transformation support
• 🌐 Framework-agnostic query parameter extraction
• 🏗️ Modular architecture for easy extension

Installation

npm install @nodewave/prisma-ezfilter

Quick Start

Direct Usage

import { BuildQueryFilter, createQueryBuilder } from '@nodewave/prisma-ezfilter';

// Create a query builder
const queryBuilder = new BuildQueryFilter();

// Define your filter with multi-level relations
const filter = {
  filters: {
    status: 'active',
    category: ['tech', 'business'],
    'author.profile.department.region': 'US', // Multi-level relation
    'company.departments.teams.lead.role': 'Senior Developer' // Deep nesting
  },
  searchFilters: {
    title: 'react',
    'author.profile.bio': 'software engineer' // Search in nested relations
  },
  rangedFilters: [
    {
      key: 'author.profile.createdAt', // Range on nested field
      start: '2023-01-01T00:00:00Z',
      end: '2023-12-31T23:59:59Z'
    }
  ],
  orderKey: 'author.profile.department.name', // Order by nested field
  orderRule: 'asc',
  page: 1,
  rows: 20
};

// Build the query
const result = queryBuilder.build(filter);

// Use with Prisma
const posts = await prisma.post.findMany(result.query);

From HTTP Query Parameters

import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

// Extract from query parameters (framework-agnostic)
const queryParams = {
  filters: '{"status":"active","author.profile.department.region":"US"}',
  searchFilters: '{"title":"react","author.profile.bio":"engineer"}',
  orderKey: 'author.profile.createdAt',
  orderRule: 'desc',
  page: '1',
  rows: '20'
};

const filter = extractQueryFromParams(queryParams);
const queryBuilder = new BuildQueryFilter();
const result = queryBuilder.build(filter);

// Use with Prisma
const posts = await prisma.post.findMany(result.query);

Multi-Level Relations Support

The package now supports unlimited levels of nested relations, allowing you to query deeply nested data structures:

Examples of Supported Nesting Levels

// Two levels
'author.profile.name'

// Three levels  
'posts.comments.user.profile.name'

// Four levels
'company.departments.teams.members.profile.role'

// Five levels
'organization.locations.buildings.floors.rooms.equipment.type'

// And so on...

How It Works

The package automatically converts dot-notation fields into nested Prisma query objects:

// Input: 'author.profile.department.region'
// Output: 
{
  author: {
    profile: {
      department: {
        region: 'US'
      }
    }
  }
}

// Input: 'company.departments.teams.lead.profile.role'
// Output:
{
  company: {
    departments: {
      teams: {
        lead: {
          profile: {
            role: 'Senior Developer'
          }
        }
      }
    }
  }
}

Supported in All Query Types

Multi-level relations work across all filtering types:

• ✅ Filters - Exact matches on nested fields
• ✅ Search Filters - Text search in nested fields
• ✅ Range Filters - Date/number ranges on nested fields
• ✅ Order By - Sorting by nested fields
• ✅ Validation - Relation validation at each level

API Reference

Core Types

FilteringQuery

interface FilteringQuery {
  filters?: Record<string, any | any[] | null>;
  searchFilters?: Record<string, any | any[] | null>;
  rangedFilters?: RangedFilter[];
  orderKey?: string;
  orderRule?: 'asc' | 'desc';
  page?: number;
  rows?: number;
}

RangedFilter

interface RangedFilter {
  key: string;
  start: string | number | Date;
  end: string | number | Date;
}

QuerySpecification

interface QuerySpecification {
  allowedFields?: string[];
  allowedOperators?: PrismaOperator[];
  allowedRelations?: string[];
  maxPageSize?: number;
  defaultPageSize?: number;
  requiredFields?: string[];
  forbiddenFields?: string[];
  defaultSearchMode?: 'insensitive' | 'sensitive'; // NEW: Global search mode setting
}

QueryParams & QueryExtractor

interface QueryParams {
  [key: string]: string | string[] | undefined;
}

interface QueryExtractor {
  getQueryParams(): QueryParams;
}

BuildQueryFilter Class

Constructor

new BuildQueryFilter(specification?: QuerySpecification, transformConfig?: TransformConfig)

Methods

• build(filter: FilteringQuery): BuildQueryResult - Builds a query with validation
• buildWithoutValidation(filter: FilteringQuery): PrismaQueryOptions - Builds a query without validation
• validate(filter: FilteringQuery): ValidationResult - Validates a query without building it
• setSpecification(specification: QuerySpecification): void - Updates the specification
• setTransformConfig(config: TransformConfig): void - Updates the transform configuration

Utility Functions

• createQueryBuilder(specification?, transformConfig?) - Convenience function to create a query builder
• buildFilterQuery(filter: FilteringQuery): PrismaQueryOptions - Builds a query without validation
• validateQuery(filter: FilteringQuery, specification?): ValidationResult - Validates a query
• transformWhereClause(whereConditions, config): any - Transforms where conditions
• transformOrderBy(orderBy, config): any - Transforms order by clause
• extractQueryFromParams(queryParams: QueryParams): FilteringQuery - Extracts filter from query parameters
• createQueryExtractor(params: QueryParams): QueryExtractor - Creates a query extractor

Usage Examples

Basic Usage

import { BuildQueryFilter } from '@nodewave/prisma-ezfilter';

const queryBuilder = new BuildQueryFilter();

const filter = {
  filters: {
    status: 'published',
    authorId: 'user123'
  },
  orderKey: 'createdAt',
  orderRule: 'desc',
  page: 1,
  rows: 10
};

const result = queryBuilder.build(filter);
// result.query contains the Prisma query options
// result.validation contains validation results

Multi-Level Relational Filters

const filter = {
  filters: {
    status: 'active',
    'author.profile.department.name': 'Engineering',
    'company.departments.teams.lead.profile.role': 'Senior Developer',
    'posts.comments.user.profile.verified': true
  }
};

// Generated Prisma query will include nested objects for each relation level

Array Filters with Multi-Level Relations

const filter = {
  filters: {
    status: 'active',
    category: ['tech', 'business', 'design'], // Creates OR condition
    priority: [1, 2, 3], // Numbers work too
    'author.profile.department.region': ['US', 'EU'], // Multi-level arrays
    'company.departments.teams.members.profile.skills': ['React', 'TypeScript'] // Deep nesting
  }
};

// Generated SQL equivalent:
// WHERE status = 'active' 
//   AND (category = 'tech' OR category = 'business' OR category = 'design')
//   AND (priority = 1 OR priority = 2 OR priority = 3)
//   AND (author.profile.department.region = 'US' OR author.profile.department.region = 'EU')
//   AND (company.departments.teams.members.profile.skills = 'React' OR company.departments.teams.members.profile.skills = 'TypeScript')

Case-Sensitive Search

The query builder supports both case-sensitive and case-insensitive search modes. By default, all searches are case-insensitive for better user experience.

Global Case-Sensitive Search

const specification: QuerySpecification = {
  allowedFields: ['name', 'email', 'title', 'content'],
  allowedRelations: ['author', 'profile'],
  defaultSearchMode: 'sensitive' // Enable case-sensitive search globally
};

const queryBuilder = new BuildQueryFilter(specification);

const filter = {
  searchFilters: {
    name: 'John', // Will only match exact case
    email: '[email protected]', // Will only match exact case
    'author.profile.bio': 'Software Engineer' // Will only match exact case
  }
};

// Generated Prisma query:
// {
//   where: {
//     AND: [
//       {
//         OR: [
//           { name: { contains: 'John', mode: 'sensitive' } },
//           { email: { contains: '[email protected]', mode: 'sensitive' } },
//           { author: { profile: { bio: { contains: 'Software Engineer', mode: 'sensitive' } } } }
//         ]
//       }
//     ]
//   }
// }

Field-Specific Case Sensitivity

const specification: QuerySpecification = {
  allowedFields: ['name', 'email', 'title', 'content'],
  allowedRelations: ['author', 'profile'],
  defaultSearchMode: 'insensitive' // Default to case-insensitive
};

const transformConfig: TransformConfig = {
  fieldMappings: {},
  fieldTypeHandlers: {
    name: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'sensitive' // Case-sensitive for names
    },
    email: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'sensitive' // Case-sensitive for emails
    },
    'author.profile.bio': {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'insensitive' // Case-insensitive for bio
    },
    title: {
      type: 'string',
      searchOperator: 'contains',
      searchMode: 'insensitive' // Case-insensitive for titles
    }
  }
};

const queryBuilder = new BuildQueryFilter(specification, transformConfig);

const filter = {
  searchFilters: {
    name: 'John', // Case-sensitive
    email: '[email protected]', // Case-sensitive
    'author.profile.bio': 'software engineer', // Case-insensitive
    title: 'react tutorial' // Case-insensitive
  }
};

Best Practices for Case Sensitivity

Use Case-Sensitive Search For:

• Email addresses
• Usernames
• Exact identifiers
• Code/technical terms
• Database keys

Use Case-Insensitive Search For:

• User search queries
• Content descriptions
• General text search
• User-friendly interfaces
• Search suggestions

Framework Integration

Express.js

import express from 'express';
import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

const app = express();
const queryBuilder = new BuildQueryFilter();

app.get('/posts', async (req, res) => {
  try {
    // Extract from Express query parameters
    const filter = extractQueryFromParams(req.query);
    const result = queryBuilder.build(filter);
    
    if (!result.validation.isValid) {
      return res.status(400).json({
        error: 'Invalid query parameters',
        details: result.validation.errors
      });
    }

    // Use with Prisma
    const posts = await prisma.post.findMany(result.query);
    res.json(posts);
  } catch (error) {
    res.status(500).json({ error: 'Server error' });
  }
});

// Example request with multi-level relations:
// GET /posts?filters={"status":"active","author.profile.department.region":"US"}&searchFilters={"author.profile.bio":"engineer"}&orderKey=author.profile.createdAt&orderRule=desc&page=1&rows=20

Hono

import { Hono } from 'hono';
import { BuildQueryFilter, extractQueryFromParams } from '@nodewave/prisma-ezfilter';

const app = new Hono();
const queryBuilder = new BuildQueryFilter();

app.get('/posts', async (c) => {
  try {
    // Extract from Hono query parameters
    const queryParams = c.req.query();
    const filter = extractQueryFromParams(queryParams);
    const result = queryBuilder.build(filter);
    
    if (!result.validation.isValid) {
      return c.json({
        error: 'Invalid query parameters',
        details: result.validation.errors
      }, 400);
    }

    // Use with Prisma
    const posts = await prisma.post.findMany(result.query);
    return c.json(posts);
  } catch (error) {
    return c.json({ error: 'Server error' }, 500);
  }
});

Custom Framework Integration

import { BuildQueryFilter, extractQueryFromParams, createQueryExtractor } from '@nodewave/prisma-ezfilter';

// Custom query extractor for any framework
class CustomQueryExtractor {
  private params: any;
  
  constructor(params: any) {
    this.params = params;
  }
  
  getQueryParams() {
    return this.params;
  }
}

// Usage
const customExtractor = new CustomQueryExtractor({
  filters: '{"status":"active","author.profile.department.region":"US"}',
  orderKey: 'author.profile.createdAt'
});

const queryParams = customExtractor.getQueryParams();
const filter = extractQueryFromParams(queryParams);
const queryBuilder = new BuildQueryFilter();
const result = queryBuilder.build(filter);

With Validation

const specification: QuerySpecification = {
  allowedFields: ['title', 'status', 'authorId', 'createdAt'],
  allowedRelations: ['author', 'profile', 'department', 'company', 'teams', 'members'], // Include all relation levels
  maxPageSize: 100,
  forbiddenFields: ['password', 'secret']
};

const queryBuilder = new BuildQueryFilter(specification);

const filter = {
  filters: {
    status: 'published',
    'author.profile.department.region': 'US', // Will be validated
    'company.departments.teams.lead.role': 'Developer' // Will be validated
  }
};

const result = queryBuilder.build(filter);
console.log(result.validation.warnings); // Check for warnings

With Field Mapping

const transformConfig: TransformConfig = {
  fieldMappings: {
    'userName': 'user.profile.name',
    'userDepartment': 'user.profile.department.name'
  },
  fieldNameMappings: {
    'userName': 'name',
    'userDepartment': 'departmentName'
  },
  fieldTypeHandlers: {
    'createdAt': {
      type: 'date',
      searchOperator: 'gte'
    }
  }
};

const queryBuilder = new BuildQueryFilter(undefined, transformConfig);

const filter = {
  filters: {
    userName: 'john', // Will be mapped to user.profile.name
    userDepartment: 'engineering' // Will be mapped to user.profile.department.name
  }
};

const result = queryBuilder.build(filter);

Complex Multi-Level Queries

const filter = {
  filters: {
    status: 'published',
    category: ['tech', 'business'],
    'author.profile.verified': true,
    'company.departments.teams.lead.profile.role': 'Senior Developer'
  },
  searchFilters: {
    title: 'react',
    'author.profile.bio': 'software engineer',
    'company.departments.teams.members.profile.skills': 'TypeScript'
  },
  rangedFilters: [
    {
      key: 'author.profile.createdAt',
      start: '2023-01-01T00:00:00Z',
      end: '2023-12-31T23:59:59Z'
    },
    {
      key: 'company.departments.teams.createdAt',
      start: '2023-06-01T00:00:00Z',
      end: '2023-06-30T23:59:59Z'
    }
  ],
  orderKey: 'company.departments.teams.members.profile.createdAt',
  orderRule: 'desc',
  page: 2,
  rows: 25
};

Query Parameter Format

The package supports standard query parameters that can be sent via HTTP requests:

Supported Parameters

• filters - JSON string containing exact match filters
• searchFilters - JSON string containing search filters (uses contains operator)
• rangedFilters - JSON string containing range filters
• orderKey - Field name for ordering
• orderRule - Order direction (asc or desc)
• page - Page number for pagination
• rows - Number of rows per page

Example HTTP Request with Multi-Level Relations

GET /posts?filters={"status":"active","author.profile.department.region":"US"}&searchFilters={"author.profile.bio":"engineer"}&orderKey=author.profile.createdAt&orderRule=desc&page=1&rows=20

Array Support with Multi-Level Relations

Arrays in filters automatically create OR conditions, even with deeply nested relations:

// Single value with multi-level relation
filters: '{"author.profile.department.region":"US"}'
// SQL: WHERE author.profile.department.region = 'US'

// Array values with multi-level relations
filters: '{"author.profile.department.region":["US","EU","APAC"]}'
// SQL: WHERE (author.profile.department.region = 'US' OR author.profile.department.region = 'EU' OR author.profile.department.region = 'APAC')'

// Mixed types with multi-level relations
filters: '{"status":"active","author.profile.department.region":["US","EU"],"company.departments.teams.lead.role":["Developer","Senior Developer"]}'
// SQL: WHERE status = 'active' AND (author.profile.department.region = 'US' OR author.profile.department.region = 'EU') AND (company.departments.teams.lead.role = 'Developer' OR company.departments.teams.lead.role = 'Senior Developer')'

Advanced Features

Custom Type Handlers

const transformConfig: TransformConfig = {
  fieldTypeHandlers: {
    'price': {
      type: 'number',
      searchOperator: 'gte'
    },
    'isActive': {
      type: 'boolean'
    },
    'customField': {
      type: 'custom',
      customHandler: (value) => {
        // Custom transformation logic
        return { equals: value.toUpperCase() };
      }
    }
  }
};

Custom Relation Handlers

const transformConfig: TransformConfig = {
  relationHandlers: {
    'user': {
      type: 'one-to-many',
      relationQuery: 'some',
      customHandler: (value, field) => {
        return {
          some: {
            [field]: value
          }
        };
      }
    }
  }
};

Error Handling

const result = queryBuilder.build(filter);

if (!result.validation.isValid) {
  console.error('Validation errors:', result.validation.errors);
  return;
}

if (result.validation.warnings.length > 0) {
  console.warn('Validation warnings:', result.validation.warnings);
}

// Use the query
const data = await prisma.model.findMany(result.query);

Testing

The package includes comprehensive end-to-end tests that cover:

• ✅ Query parameter extraction from HTTP requests
• ✅ Array filter processing (OR conditions)
• ✅ Multi-level relational query handling (NEW!)
• ✅ Search filter functionality in nested relations
• ✅ Range filter processing on nested fields
• ✅ Ordering by deeply nested fields
• ✅ Validation with specifications for multi-level relations
• ✅ Error handling for malformed data
• ✅ Framework-agnostic query extraction

Multi-Level Relations Test Coverage

The new test suite specifically covers:

• ✅ Two-level relations (author.profile.name)
• ✅ Three-level relations (posts.comments.user.profile.name)
• ✅ Four-level relations (company.departments.teams.members.profile.role)
• ✅ Array values with multi-level relations
• ✅ Search filters in nested relations
• ✅ Range filters on nested fields
• ✅ Ordering by nested fields
• ✅ Complex queries combining multiple nesting levels
• ✅ Validation of multi-level relations

Run tests with:

npm test

Complete Type Definitions

Core Interfaces

FilteringQuery

interface FilteringQuery {
  filters?: Record<string, any | any[] | null>;
  searchFilters?: Record<string, any | any[] | null>;
  rangedFilters?: RangedFilter[];
  orderKey?: string;
  orderRule?: OrderDirection;
  page?: number;
  rows?: number;
}

RangedFilter

interface RangedFilter {
  key: string;
  start: string | number | Date;
  end: string | number | Date;
}

QuerySpecification

interface QuerySpecification {
  allowedFields?: string[];
  allowedOperators?: PrismaOperator[];
  allowedRelations?: string[];
  maxPageSize?: number;
  defaultPageSize?: number;
  requiredFields?: string[];
  forbiddenFields?: string[];
  defaultSearchMode?: 'insensitive' | 'sensitive'; // Global search mode setting
}

TransformConfig

interface TransformConfig {
  fieldMappings: Record<string, string>;
  fieldNameMappings?: Record<string, string>;
  fieldTypeHandlers?: Record<string, FieldTypeHandler>;
  relationHandlers?: Record<string, RelationHandler>;
}

FieldTypeHandler

interface FieldTypeHandler {
  type: 'string' | 'number' | 'boolean' | 'date' | 'custom';
  searchOperator?: PrismaOperator;
  searchMode?: 'insensitive' | 'sensitive'; // Field-specific search mode
  customHandler?: (value: any) => any;
}

RelationHandler

interface RelationHandler {
  type: 'one-to-many' | 'many-to-many' | 'one-to-one';
  relationQuery: 'some' | 'every' | 'none';
  nestedField?: string;
  customHandler?: (value: any, actualField: string) => any;
}

PrismaOperator

type PrismaOperator =
  | 'equals'
  | 'not'
  | 'in'
  | 'notIn'
  | 'lt'
  | 'lte'
  | 'gt'
  | 'gte'
  | 'contains'
  | 'startsWith'
  | 'endsWith'
  | 'mode'
  | 'search';

BuildQueryResult

interface BuildQueryResult {
  query: PrismaQueryOptions;
  validation: ValidationResult;
}

ValidationResult

interface ValidationResult {
  isValid: boolean;
  errors: string[];
  warnings: string[];
}

Migration from v0.1.0

If you're upgrading from the previous version:

1. Package name changed: prisma-ezfilter → @nodewave/prisma-ezfilter
2. New feature: Multi-level relations are now supported (previously only single-level)
3. New feature: Case-sensitive/insensitive search modes are now supported
4. Enhanced validation: Better support for validating nested relations
5. Improved types: Better TypeScript support for nested objects

Breaking Changes

• None - all existing functionality remains the same
• Multi-level relations are additive and don't affect existing single-level queries
• Case-sensitive search is backward compatible - existing searches remain case-insensitive by default

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests for new functionality
5. Submit a pull request

License

MIT License - see LICENSE file for details.