typeorm-flexi-query

A powerful TypeORM 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 from HTTP request queries
• Sorting - Parse and validate sorting parameters, ensuring compatibility with your database schema
• Filtering - Dynamically filter data based on 15+ filter rules including JSON field support
• TypeORM Integration - Seamlessly integrates with TypeORM, providing a fluent API for both repository and query builder patterns
• 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 typeorm-flexi-query

Peer Dependencies

This package requires the following peer dependencies:

npm install @nestjs/common @nestjs/core typeorm

Quick Start

import {
  PaginationParams,
  Pagination,
  SortingParams,
  Sorting,
  FilteringParams,
  Filtering,
  applyFiltersToRepository,
  applySorting,
  PaginatedResource,
} from 'typeorm-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 validates them.

@Get()
async getItems(@PaginationParams() pagination: Pagination) {
  // pagination = { page: 0, size: 10, limit: 10, offset: 0 }
}

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) {
  // sort = { property: 'name', direction: 'asc' }
}

Query format: ?sort=property:direction

• Example: ?sort=name:asc or ?sort=createdAt:desc
• Supports table aliases: ?sort=user.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[]) {
  // filters = [{ property: 'status', rule: 'eq', value: 'active' }]
}

Query format: ?filter=property:rule:value

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

Helper Functions

applySorting(sort, tableAlias?)

Converts a Sorting object to TypeORM order format.

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

const orderWithAlias = applySorting(sort, 'user');
// Returns: { 'user.name': 'asc' }

applyFiltersToRepository(filters)

Converts Filtering[] to TypeORM repository where conditions.

const where = applyFiltersToRepository(filters);
const [items, total] = await repository.findAndCount({
  where,
  order,
  take: limit,
  skip: offset,
});

applyFiltersToQueryBuilder(queryBuilder, filters, tableAlias)

Applies filters to a TypeORM query builder.

let qb = repository.createQueryBuilder('user');
qb = applyFiltersToQueryBuilder(qb, filters, 'user');
const [items, total] = await qb.getManyAndCount();

Types

Pagination

interface Pagination {
  page?: number;
  limit?: number;
  size?: number;
  offset?: number;
}

Sorting

interface Sorting {
  property: string;
  direction: string;
  tableAlias?: string;
}

Filtering

interface Filtering {
  property: string;
  rule: string;
  value: string;
  tableAlias?: string;
}

PaginatedResource

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

Filter Rules

| Rule | Description | Example | |------|-------------|---------| | eq | Equal | ?filter=status:eq:active | | neq | Not equal | ?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 | | like | Contains (case-insensitive) | ?filter=name:like:john | | nlike | Does not contain | ?filter=name:nlike:test | | 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=first_name,last_name:oreq:john | | orlike | OR like (keyword search) | ?filter=title,description:orlike:search term | | json | JSON field filtering | See below |

JSON Filtering

Filter on JSON/JSONB columns:

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

Repository method operators: =, != Query builder operators: =, !=, >, >=, <, <=

Filtering Related Entities

Filter on related entity fields using dot notation:

?filter=entityRoles.role.name:like:admin

Advanced Usage

Full Service Example

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>
  ) {}

  async findAll(
    { page, limit, size, offset }: Pagination,
    sort?: Sorting,
    filters?: Filtering[]
  ): Promise<PaginatedResource<User>> {
    const where = applyFiltersToRepository(filters);
    const order = applySorting(sort);

    const [items, total] = await this.userRepository.findAndCount({
      where,
      order,
      take: limit,
      skip: offset,
    });

    return {
      totalItems: total,
      items,
      page,
      size,
    };
  }

  async findAllWithQueryBuilder(
    { page, limit, size, offset }: Pagination,
    sort?: Sorting,
    filters?: Filtering[]
  ): Promise<PaginatedResource<User>> {
    const order = applySorting(sort, 'user');

    let queryBuilder = this.userRepository.createQueryBuilder('user');
    queryBuilder = applyFiltersToQueryBuilder(queryBuilder, filters, 'user');

    const [items, total] = await queryBuilder
      .leftJoinAndSelect('user.profile', 'profile')
      .orderBy(order)
      .take(limit)
      .skip(offset)
      .getManyAndCount();

    return {
      totalItems: total,
      items,
      page,
      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

# OR condition across columns
GET /users?page=0&size=10&filter=first_name,last_name:oreq:john

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

Transaction Management

The package includes a transaction management system with automatic commit/rollback support for both Express and Fastify.

TransactionInterceptor

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

import {
  TransactionInterceptor,
  BaseRepository,
  ENTITY_MANAGER_KEY,
} from 'typeorm-flexi-query';

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

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

BaseRepository

An abstract class that provides transaction-aware repository access. When used with TransactionInterceptor, all operations automatically use the transaction's EntityManager.

import { Injectable, Scope, Inject } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { REQUEST } from '@nestjs/core';
import { BaseRepository } from 'typeorm-flexi-query';

@Injectable({ scope: Scope.REQUEST })
export class UserRepository extends BaseRepository {
  constructor(
    dataSource: DataSource,
    @Inject(REQUEST) request: any,
  ) {
    super(dataSource, request);
  }

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

  async create(data: CreateUserDto): Promise<User> {
    const user = this.getRepository(User).create(data);
    return this.getRepository(User).save(user);
  }

  async updateWithRelated(id: string, data: UpdateUserDto): Promise<User> {
    // All operations share the same transaction
    const user = await this.getRepository(User).findOne({ where: { id } });
    Object.assign(user, data);
    await this.getRepository(User).save(user);
    await this.getRepository(AuditLog).save({ userId: id, action: 'update' });
    return user;
  }
}

Request Adapters

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

import {
  createTransactionInterceptor,
  FastifyRequestAdapter,
  ExpressRequestAdapter,
} from 'typeorm-flexi-query';

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

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

@Controller('users')
@UseInterceptors(FastifyTransactionInterceptor)
export class UsersController {}

Checking Transaction Status

@Injectable({ scope: Scope.REQUEST })
export class UserRepository extends BaseRepository {
  async conditionalOperation() {
    if (this.isTransactionActive()) {
      // We're in a transaction
      return this.getEntityManager().query('SELECT ...');
    } else {
      // No transaction active
      return this.getRepository(User).find();
    }
  }
}

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.