prisma-flexi-query

A powerful Prisma and NestJS library for flexible filtering, sorting, and pagination. Provides decorators and utilities to streamline query parameter handling in your REST APIs.

Table of Contents

• Features
• Installation
• Quick Start
• API Reference
  • Decorators
  • Helper Functions
  • Types
• Filter Rules
• Advanced Usage
• Transaction Management
• Examples
• Contributing
• License

Features

• Pagination - Automatically extract and validate pagination parameters with Prisma-compatible skip and take
• Sorting - Parse and validate sorting parameters with relation support
• Filtering - Dynamically filter data based on 16+ filter rules including JSON field support
• Prisma Integration - Seamlessly integrates with Prisma Client
• Transaction Management - Built-in transaction interceptor and base repository for automatic transaction handling
• Type Safety - Full TypeScript support with proper type definitions

Installation

npm install prisma-flexi-query

Peer Dependencies

This package requires the following peer dependencies:

npm install @nestjs/common @nestjs/core @prisma/client

Quick Start

import {
  PaginationParams,
  Pagination,
  SortingParams,
  Sorting,
  FilteringParams,
  Filtering,
  applyFilters,
  applySorting,
  PaginatedResource,
} from 'prisma-flexi-query';

@Controller('users')
export class UsersController {
  constructor(private readonly userService: UserService) {}

  @Get()
  async getUsers(
    @PaginationParams() pagination: Pagination,
    @SortingParams(['name', 'email', 'createdAt']) sort?: Sorting,
    @FilteringParams(['name', 'email', 'status']) filter?: Filtering[]
  ): Promise<PaginatedResource<User>> {
    return this.userService.findAll(pagination, sort, filter);
  }
}

API Reference

Decorators

@PaginationParams()

Extracts pagination parameters (page and size) from the query string and calculates Prisma-compatible skip and take.

@Get()
async getItems(@PaginationParams() pagination: Pagination) {
  // pagination = { page: 0, size: 10, limit: 10, skip: 0, take: 10 }
  return this.prisma.user.findMany({
    skip: pagination.skip,
    take: pagination.take,
  });
}

Validation:

• page must be >= 0
• size must be >= 0 and <= 100

@SortingParams(validParams: string[])

Parses and validates sorting parameters. Only allows sorting on specified fields.

@Get()
async getItems(@SortingParams(['name', 'createdAt']) sort?: Sorting) {
  const orderBy = applySorting(sort);
  return this.prisma.user.findMany({ orderBy });
}

Query format: ?sort=property:direction

• Example: ?sort=name:asc or ?sort=createdAt:desc
• Supports relation sorting: ?sort=profile.name:asc

@FilteringParams(validParams: string[])

Parses and validates filtering parameters. Supports multiple filters and various filter rules.

@Get()
async getItems(@FilteringParams(['name', 'status', 'age']) filters?: Filtering[]) {
  const where = applyFilters(filters);
  return this.prisma.user.findMany({ where });
}

Query format: ?filter=property:rule:value

• Single filter: ?filter=status:eq:active
• Multiple filters: ?filter=status:eq:active&filter=name:contains:john

Helper Functions

applySorting(sort)

Converts a Sorting object to Prisma orderBy format.

const orderBy = applySorting(sort);
// Returns: { name: 'asc' }

// With relation
const orderBy = applySorting({ property: 'name', direction: 'asc', relation: 'profile' });
// Returns: { profile: { name: 'asc' } }

applyFilters(filters)

Converts Filtering[] to Prisma where conditions.

const where = applyFilters(filters);
const [items, total] = await Promise.all([
  this.prisma.user.findMany({ where, skip, take, orderBy }),
  this.prisma.user.count({ where }),
]);

combineFilters(...conditions)

Combines multiple where conditions with AND logic.

const baseFilter = { isActive: true };
const dynamicFilter = applyFilters(filters);
const where = combineFilters(baseFilter, dynamicFilter);

Types

Pagination

interface Pagination {
  page?: number;
  limit?: number;
  size?: number;
  skip?: number;   // For Prisma
  take?: number;   // For Prisma
}

Sorting

interface Sorting {
  property: string;
  direction: 'asc' | 'desc';
  relation?: string;
}

Filtering

interface Filtering {
  property: string;
  rule: string;
  value: string;
  relation?: string | null;
}

PaginatedResource

type PaginatedResource<T> = {
  totalItems: number;
  items: T[];
  page: number;
  size: number;
};

Filter Rules

| Rule | Description | Example | |------|-------------|---------| | eq | Equal to | ?filter=status:eq:active | | neq | Not equal to | ?filter=status:neq:deleted | | gt | Greater than | ?filter=age:gt:18 | | gte | Greater than or equal | ?filter=age:gte:21 | | lt | Less than | ?filter=price:lt:100 | | lte | Less than or equal | ?filter=price:lte:50 | | contains | Contains (case-insensitive) | ?filter=name:contains:john | | startswith | Starts with | ?filter=name:startswith:Jo | | endswith | Ends with | ?filter=email:endswith:@gmail.com | | in | In list | ?filter=status:in:active,pending | | nin | Not in list | ?filter=status:nin:deleted,archived | | isnull | Is null | ?filter=deletedAt:isnull | | isnotnull | Is not null | ?filter=email:isnotnull | | oreq | OR equal (multiple columns) | ?filter=firstName,lastName:oreq:john | | orcontains | OR contains (search) | ?filter=title,description:orcontains:search term | | json | JSON field filtering | See below |

JSON Filtering

Filter on JSON fields:

?filter=settings:json:{"property":"theme","rule":"=","value":"dark"}

Supported operators: =, !=, >, >=, <, <=

Filtering Related Entities

Filter on related entity fields using dot notation:

?filter=profile.name:contains:admin

Advanced Usage

Full Service Example

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service';
import {
  Pagination,
  Sorting,
  Filtering,
  applyFilters,
  applySorting,
  PaginatedResource,
  createPaginatedResponse,
} from 'prisma-flexi-query';

@Injectable()
export class UserService {
  constructor(private readonly prisma: PrismaService) {}

  async findAll(
    { page, size, skip, take }: Pagination,
    sort?: Sorting,
    filters?: Filtering[]
  ): Promise<PaginatedResource<User>> {
    const where = applyFilters(filters);
    const orderBy = applySorting(sort);

    const [items, totalItems] = await Promise.all([
      this.prisma.user.findMany({
        where,
        orderBy,
        skip,
        take,
        include: {
          profile: true,
        },
      }),
      this.prisma.user.count({ where }),
    ]);

    return createPaginatedResponse(items, totalItems, page!, size!);
  }

  async findWithCustomFilters(
    pagination: Pagination,
    sort?: Sorting,
    filters?: Filtering[]
  ): Promise<PaginatedResource<User>> {
    // Combine dynamic filters with base conditions
    const dynamicWhere = applyFilters(filters);
    const where = combineFilters(
      { isActive: true },
      { deletedAt: null },
      dynamicWhere
    );

    const orderBy = applySorting(sort) ?? { createdAt: 'desc' };

    const [items, totalItems] = await Promise.all([
      this.prisma.user.findMany({
        where,
        orderBy,
        skip: pagination.skip,
        take: pagination.take,
      }),
      this.prisma.user.count({ where }),
    ]);

    return createPaginatedResponse(items, totalItems, pagination.page!, pagination.size!);
  }
}

Sample API Requests

# Basic pagination
GET /users?page=0&size=10

# With sorting
GET /users?page=0&size=10&sort=name:asc

# With filtering
GET /users?page=0&size=10&filter=status:eq:active

# Multiple filters
GET /users?page=0&size=10&sort=createdAt:desc&filter=status:eq:active&filter=role:in:admin,moderator

# Search across columns
GET /users?page=0&size=10&filter=firstName,lastName:orcontains:john

# JSON column filtering
GET /users?page=0&size=10&filter=preferences:json:{"property":"newsletter","rule":"=","value":true}

# Relation filtering
GET /users?page=0&size=10&filter=profile.bio:contains:developer

Transaction Management

The package includes a transaction management system using Prisma's interactive transactions with automatic commit/rollback support.

PrismaTransactionInterceptor

Wraps controller methods in a Prisma interactive transaction. Automatically commits on success and rolls back on error.

import {
  PrismaTransactionInterceptor,
  BasePrismaRepository,
  PRISMA_CLIENT,
} from 'prisma-flexi-query';

// First, register your PrismaService with the PRISMA_CLIENT token
@Module({
  providers: [
    PrismaService,
    {
      provide: PRISMA_CLIENT,
      useExisting: PrismaService,
    },
  ],
})
export class AppModule {}

// Apply to entire controller
@Controller('users')
@UseInterceptors(PrismaTransactionInterceptor)
export class UsersController {
  // All methods will be wrapped in a transaction
}

// Or apply to specific methods
@Controller('orders')
export class OrdersController {
  @Post()
  @UseInterceptors(PrismaTransactionInterceptor)
  async createOrder(@Body() dto: CreateOrderDto) {
    // This method is wrapped in a transaction
  }
}

Transaction Options

Configure transaction behavior with timeout, isolation level, and retry options:

import { createPrismaTransactionInterceptor } from 'prisma-flexi-query';

const CustomTransactionInterceptor = createPrismaTransactionInterceptor({
  timeout: 10000, // 10 seconds
  isolationLevel: 'Serializable',
  maxRetries: 3,
});

@Controller('orders')
@UseInterceptors(CustomTransactionInterceptor)
export class OrdersController {}

BasePrismaRepository

An abstract class that provides transaction-aware Prisma client access. When used with PrismaTransactionInterceptor, all operations automatically use the transaction client.

import { Injectable, Scope, Inject } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { BasePrismaRepository } from 'prisma-flexi-query';
import { PrismaService } from './prisma.service';

@Injectable({ scope: Scope.REQUEST })
export class UserRepository extends BasePrismaRepository {
  constructor(
    prismaService: PrismaService,
    @Inject(REQUEST) request: any,
  ) {
    super(prismaService, request);
  }

  async findById(id: string): Promise<User | null> {
    return this.prisma.user.findUnique({ where: { id } });
  }

  async create(data: CreateUserDto): Promise<User> {
    return this.prisma.user.create({ data });
  }

  async createWithAudit(data: CreateUserDto): Promise<User> {
    // Both operations use the same transaction
    const user = await this.prisma.user.create({ data });
    await this.prisma.auditLog.create({
      data: { userId: user.id, action: 'CREATE' },
    });
    return user;
  }
}

Request Adapters

The package supports both Express and Fastify. Use the appropriate adapter or let the default adapter auto-detect:

import {
  createPrismaTransactionInterceptor,
  FastifyRequestAdapter,
  ExpressRequestAdapter,
} from 'prisma-flexi-query';

// For Fastify
const FastifyTransactionInterceptor = createPrismaTransactionInterceptor({
  requestAdapter: new FastifyRequestAdapter(),
});

// For Express
const ExpressTransactionInterceptor = createPrismaTransactionInterceptor({
  requestAdapter: new ExpressRequestAdapter(),
});

Checking Transaction Status

@Injectable({ scope: Scope.REQUEST })
export class UserRepository extends BasePrismaRepository {
  async conditionalOperation() {
    if (this.isTransactionActive()) {
      // We're in a transaction - use the transaction client
      return this.prisma.user.findMany();
    } else {
      // No transaction - use base Prisma client
      return this.getBasePrismaClient().user.findMany();
    }
  }
}

Contributing

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

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.