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 formatcolumn:direction
- Example:
?sortBy=name:ASC&sortBy=createdAt:DESC
- Example:
Search
search
- Search termsearchBy
- Columns to search in- Example:
?search=john&searchBy=name&searchBy=email
- Example:
Filtering
filter.column
- Filter by specific column- Example:
?filter.name=john&filter.createdAt.gte=2023-01-01
- Example:
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:
Operator | Description | Example |
---|---|---|
eq | Equal | filter.status=active |
ne | Not equal | filter.status.ne=inactive |
gt | Greater than | filter.age.gt=18 |
gte | Greater than or equal | filter.age.gte=21 |
lt | Less than | filter.score.lt=100 |
lte | Less than or equal | filter.score.lte=90 |
in | In array | filter.status.in=active,pending |
notIn | Not in array | filter.role.notIn=admin,super |
like | Like pattern | filter.name.like=%john% |
notLike | Not like pattern | filter.email.notLike=%test% |
isNull | Is null | filter.deletedAt.isNull |
isNotNull | Is not null | filter.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