Skip to main content

NestJS TypeORM Pagination

Advanced pagination, filtering, sorting, and search capabilities for TypeORM repositories in NestJS applications.

Installation

npm install @globalart/nestjs-typeorm-pagination

Overview

This package provides a comprehensive solution for implementing pagination, filtering, sorting, and searching in your NestJS applications using TypeORM. It includes decorators, DTOs, and utility functions that make it easy to add these features to your API endpoints.

Key Features

  • Pagination - Configurable page-based pagination with limits
  • Search - Full-text search across multiple columns
  • Filtering - Advanced filtering with multiple operators
  • Sorting - Multi-column sorting with custom order
  • Swagger Integration - Automatic OpenAPI documentation
  • Type Safety - Full TypeScript support with IntelliSense
  • Performance Optimized - Efficient queries with proper indexing

Quick Start

import { Controller, Get } from '@nestjs/common';
import { Paginate, PaginateQuery, paginate, Paginated } from '@globalart/nestjs-typeorm-pagination';
import { Repository } from 'typeorm';
import { InjectRepository } from '@nestjs/typeorm';
import { User } from './user.entity';

@Controller('users')
export class UsersController {
constructor(
@InjectRepository(User)
private readonly userRepository: Repository<User>,
) {}

@Get()
async getUsers(@Paginate() query: PaginateQuery): Promise<Paginated<User>> {
return paginate(query, this.userRepository, {
sortableColumns: ['id', 'name', 'email', 'createdAt'],
searchableColumns: ['name', 'email'],
filterableColumns: {
name: true,
email: true,
createdAt: ['gte', 'lte'],
},
});
}
}

Configuration Options

PaginateConfig Interface

interface PaginateConfig<T> {
sortableColumns: Column<T>[]; // Columns that can be sorted
searchableColumns?: Column<T>[]; // Columns to search in
filterableColumns?: { // Filterable columns and operators
[key in Column<T>]?: (FilterOperator | FilterSuffix)[] | true;
};
relations?: FindOptionsRelations<T>; // Relations to include
select?: Column<T>[]; // Columns to select
maxLimit?: number; // Maximum items per page
defaultSortBy?: SortBy<T>; // Default sorting
defaultLimit?: number; // Default items per page
where?: FindOptionsWhere<T>; // Additional where conditions
}

Query Parameters

Pagination

  • page - Page number (default: 1)
  • limit - Items per page (default: 20, max: 100)

Sorting

  • sortBy - Sort configuration in format column:direction
    • Example: ?sortBy=name:ASC&sortBy=createdAt:DESC
  • search - Search term
  • searchBy - Columns to search in
    • Example: ?search=john&searchBy=name&searchBy=email

Filtering

  • filter.column - Filter by specific column
    • Example: ?filter.name=john&filter.createdAt.gte=2023-01-01

Usage Examples

Basic Pagination

@Controller('users')
export class UsersController {
@Get()
async getUsers(@Paginate() query: PaginateQuery): Promise<Paginated<User>> {
return paginate(query, this.userRepository, {
sortableColumns: ['id', 'name', 'email', 'createdAt'],
defaultSortBy: [['createdAt', 'DESC']],
defaultLimit: 20,
maxLimit: 100,
});
}
}

With Search and Filtering

@Controller('users')
export class UsersController {
@Get()
async getUsers(@Paginate() query: PaginateQuery): Promise<Paginated<User>> {
return paginate(query, this.userRepository, {
sortableColumns: ['id', 'name', 'email', 'createdAt'],
searchableColumns: ['name', 'email'],
filterableColumns: {
name: true,
email: true,
createdAt: ['gte', 'lte'],
status: ['eq', 'in'],
},
defaultSortBy: [['createdAt', 'DESC']],
defaultLimit: 20,
});
}
}

With Relations

@Controller('posts')
export class PostsController {
@Get()
async getPosts(@Paginate() query: PaginateQuery): Promise<Paginated<Post>> {
return paginate(query, this.postRepository, {
sortableColumns: ['id', 'title', 'createdAt'],
searchableColumns: ['title', 'content'],
relations: ['author', 'categories'],
filterableColumns: {
authorId: ['eq'],
categoryId: ['in'],
published: ['eq'],
createdAt: ['gte', 'lte'],
},
});
}
}

Using Query Builder

@Controller('users')
export class UsersController {
@Get('active')
async getActiveUsers(@Paginate() query: PaginateQuery): Promise<Paginated<User>> {
const queryBuilder = this.userRepository
.createQueryBuilder('user')
.where('user.isActive = :isActive', { isActive: true });

return paginate(query, queryBuilder, {
sortableColumns: ['id', 'name', 'email', 'createdAt'],
searchableColumns: ['name', 'email'],
filterableColumns: {
name: true,
email: true,
createdAt: ['gte', 'lte'],
},
});
}
}

Filter Operators

The package supports various filter operators:

OperatorDescriptionExample
eqEqualfilter.status=active
neNot equalfilter.status.ne=inactive
gtGreater thanfilter.age.gt=18
gteGreater than or equalfilter.age.gte=21
ltLess thanfilter.score.lt=100
lteLess than or equalfilter.score.lte=90
inIn arrayfilter.status.in=active,pending
notInNot in arrayfilter.role.notIn=admin,super
likeLike patternfilter.name.like=%john%
notLikeNot like patternfilter.email.notLike=%test%
isNullIs nullfilter.deletedAt.isNull
isNotNullIs not nullfilter.verifiedAt.isNotNull

Response Structure

interface Paginated<T> {
data: T[]; // Array of items
payload: {
pagination: {
limit: number; // Items per page
totalItems: number; // Total number of items
page: number; // Current page
total: number; // Total number of pages
sortBy: SortBy<T>; // Current sorting
searchBy: Column<T>[]; // Search columns
search: string; // Search term
select: string[]; // Selected columns
filter?: Record<string, string | string[]>; // Applied filters
links: PaginationLink[]; // Pagination links
};
};
}

Swagger Integration

The package includes Swagger decorators for automatic API documentation:

import { 
Paginate,
PaginateQuery,
ApiPaginatedQuery,
ApiOkPaginatedResponse
} from '@globalart/nestjs-typeorm-pagination';

@Controller('users')
export class UsersController {
@Get()
@ApiPaginatedQuery()
@ApiOkPaginatedResponse(User)
async getUsers(@Paginate() query: PaginateQuery): Promise<Paginated<User>> {
return paginate(query, this.userRepository, {
sortableColumns: ['id', 'name', 'email', 'createdAt'],
searchableColumns: ['name', 'email'],
filterableColumns: {
name: true,
email: true,
createdAt: ['gte', 'lte'],
},
});
}
}

Best Practices

  • Define Sortable Columns - Always specify which columns can be sorted to prevent SQL injection
  • Set Reasonable Limits - Use maxLimit to prevent performance issues
  • Index Searchable Columns - Add database indexes on columns used for search
  • Use Relations Wisely - Only load necessary relations to avoid N+1 queries
  • Validate Filter Operators - Only allow safe filter operators for each column
  • Cache When Possible - Consider caching for frequently accessed data

Performance Tips

  • Add database indexes on sortable and searchable columns
  • Use select to limit returned columns when full entities aren't needed
  • Consider using query builder for complex queries with joins
  • Set appropriate maxLimit to prevent large result sets
  • Use withDeleted: false when you don't need soft-deleted records