search-n-paginate

A flexible, type-safe utility for building dynamic Prisma queries with advanced filtering, searching, ranged filtering, sorting, and pagination.
Perfect for REST APIs and admin panels that need robust, frontend-driven data querying.

Features

• Dynamic Filtering: Filter by any column, including relations.
• Full-Text Search: Search across multiple fields, including related models.
• Ranged Filtering: Filter by date or number ranges.
• Sorting: Sort by any column, ascending or descending.
• Pagination: Limit, offset, and cursor-based pagination.
• Type-Safe: Written in TypeScript with clear interfaces.
• Easy Integration: Designed for Prisma, but adaptable for other ORMs.
• Operator Support: Use all common Prisma filter operators via key syntax.

Installation

npm install search-n-paginate

Supported Operators

You can use the following operators in your filter keys by appending __operator to the field name:

| Operator | Suffix | Example Key | Example Value | Description | | ---------- | -------------- | ------------------ | ---------------------- | ------------------------------------- | | equals | (default) | status | 'active' | Exact match | | not | __not | status__not | 'inactive' | Not equal | | in | __in | status__in | ['active','pending'] | In array | | notIn | __notIn | status__notIn | ['inactive'] | Not in array | | lt | __lt | price__lt | 100 | Less than | | lte | __lte | price__lte | 100 | Less than or equal | | gt | __gt | price__gt | 100 | Greater than | | gte | __gte | price__gte | 100 | Greater than or equal | | contains | __contains | name__contains | 'foo' | Contains substring (case-insensitive) | | startsWith | __startsWith | name__startsWith | 'foo' | Starts with substring | | endsWith | __endsWith | name__endsWith | 'bar' | Ends with substring |

Default:
If you use an array value with no operator (e.g. status: ['active','pending']), it will use in by default.

Usage

1. Define Your Query Interface

import { FilteringQueryV2, buildFilterQueryLimitOffsetV2 } from 'search-n-paginate'

2. Build Your Prisma Query

const filter: FilteringQueryV2 = {
  page: 1,
  rows: 20,
  orderKey: 'created_at',
  orderRule: 'desc',
  filters: {
    status: ['active', 'pending'], // uses 'in'
    price__gte: 100, // price >= 100
    'user.role__equals': 'ADMIN', // relation: user.role = 'ADMIN'
    name__contains: 'foo', // name contains 'foo'
  },
  searchFilters: { 'user.name': 'John' },
  rangedFilters: [{ key: 'created_at', start: '2024-01-01', end: '2024-12-31' }],
}

const prismaQuery = buildFilterQueryLimitOffsetV2(filter)
const results = await prisma.userTemplateData.findMany(prismaQuery)

3. Paginated Response

import { PagedList } from 'search-n-paginate'

const pagedResult: PagedList<User> = {
  entries: results,
  totalData: 100,
  totalPage: 5,
}

Filtering & Search Cases

Simple Filtering

filters: {
  status: 'active'
} // status = 'active'
filters: {
  status__not: 'inactive'
} // status != 'inactive'
filters: {
  status: ['active', 'pending']
} // status IN ('active', 'pending')
filters: {
  price__gte: 100
} // price >= 100
filters: {
  price__lt: 500
} // price < 500

Relation Filtering

filters: { 'user.role__equals': 'ADMIN' } // user.role = 'ADMIN'
filters: { 'user.name__contains': 'John' } // user.name contains 'John'

Search Across Multiple Fields

searchFilters: { name: 'foo', email: 'bar' } // (name contains 'foo' OR email contains 'bar')
searchFilters: { 'user.name': 'John' } // user.name contains 'John'

Ranged Filtering

rangedFilters: [
  { key: 'created_at', start: '2024-01-01', end: '2024-12-31' }, // created_at between dates
  { key: 'price', start: 100, end: 500 }, // price between 100 and 500
]

Sorting & Pagination

orderKey: 'created_at',
orderRule: 'desc',
page: 2,
rows: 10

API

FilteringQueryV2

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

RangedFilter

export interface RangedFilter {
  key: string
  start: any
  end: any
}

PagedList

export interface PagedList<T> {
  entries: T[]
  totalData: number
  totalPage: number
}

Example

import { buildFilterQueryLimitOffsetV2 } from 'search-n-paginate'

const filter = {
  page: 2,
  rows: 10,
  orderKey: 'created_at',
  orderRule: 'desc',
  filters: {
    status: ['active', 'pending'],
    price__gte: 100,
    'user.role__equals': 'ADMIN',
  },
  searchFilters: { 'user.email': 'example' },
  rangedFilters: [{ key: 'created_at', start: '2024-01-01', end: '2024-12-31' }],
}

const prismaQuery = buildFilterQueryLimitOffsetV2(filter)
const data = await prisma.userTemplateData.findMany(prismaQuery)

License

MIT

Contributing

Pull requests are welcome! For major changes, please open an issue first to discuss what you would like to change.

Author

Esa Rizki