Nest.js Paginate
![GitHub](https://img.shields.io/github/license/ppetzold/nestjs-paginate)
Pagination and filtering helper method for TypeORM repositories or query builders using Nest.js framework.
- Pagination conforms to JSON:API
- Sort by multiple columns
- Search across columns
- Select columns
- Filter using operators (
$eq
, $not
, $null
, $in
, $gt
, $gte
, $lt
, $lte
, $btw
, $ilike
, $sw
, $contains
) - Include relations and nested relations
- Virtual column support
Installation
npm install nestjs-paginate
Usage
Example
The following code exposes a route that can be utilized like so:
Endpoint
http://localhost:3000/cats?limit=5&page=2&sortBy=color:DESC&search=i&filter.age=$gte:3&select=id,name,color,age
Result
{
"data": [
{
"id": 4,
"name": "George",
"color": "white",
"age": 3
},
{
"id": 5,
"name": "Leche",
"color": "white",
"age": 6
},
{
"id": 2,
"name": "Garfield",
"color": "ginger",
"age": 4
},
{
"id": 1,
"name": "Milo",
"color": "brown",
"age": 5
},
{
"id": 3,
"name": "Kitty",
"color": "black",
"age": 3
}
],
"meta": {
"itemsPerPage": 5,
"totalItems": 12,
"currentPage": 2,
"totalPages": 3,
"sortBy": [["color", "DESC"]],
"search": "i",
"filter": {
"age": "$gte:3"
}
},
"links": {
"first": "http://localhost:3000/cats?limit=5&page=1&sortBy=color:DESC&search=i&filter.age=$gte:3",
"previous": "http://localhost:3000/cats?limit=5&page=1&sortBy=color:DESC&search=i&filter.age=$gte:3",
"current": "http://localhost:3000/cats?limit=5&page=2&sortBy=color:DESC&search=i&filter.age=$gte:3",
"next": "http://localhost:3000/cats?limit=5&page=3&sortBy=color:DESC&search=i&filter.age=$gte:3",
"last": "http://localhost:3000/cats?limit=5&page=3&sortBy=color:DESC&search=i&filter.age=$gte:3"
}
}
Code
import { Controller, Injectable, Get } from '@nestjs/common'
import { InjectRepository } from '@nestjs/typeorm'
import { FilterOperator, FilterSuffix, Paginate, PaginateQuery, paginate, Paginated } from 'nestjs-paginate'
import { Repository, Entity, PrimaryGeneratedColumn, Column } from 'typeorm'
@Entity()
export class CatEntity {
@PrimaryGeneratedColumn()
id: number
@Column('text')
name: string
@Column('text')
color: string
@Column('int')
age: number
@Column({ nullable: true })
lastVetVisit: Date | null
@CreateDateColumn()
createdAt: string
}
@Injectable()
export class CatsService {
constructor(
@InjectRepository(CatEntity)
private readonly catsRepository: Repository<CatEntity>
) {}
public findAll(query: PaginateQuery): Promise<Paginated<CatEntity>> {
return paginate(query, this.catsRepository, {
sortableColumns: ['id', 'name', 'color', 'age'],
nullSort: 'last',
defaultSortBy: [['id', 'DESC']],
searchableColumns: ['name', 'color', 'age'],
select: ['id', 'name', 'color', 'age', 'lastVetVisit'],
filterableColumns: {
name: [FilterOperator.EQ, FilterSuffix.NOT],
age: true,
},
})
}
}
@Controller('cats')
export class CatsController {
constructor(private readonly catsService: CatsService) {}
@Get()
public findAll(@Paginate() query: PaginateQuery): Promise<Paginated<CatEntity>> {
return this.catsService.findAll(query)
}
}
Config
const paginateConfig: PaginateConfig<CatEntity> {
sortableColumns: ['id', 'name', 'color'],
nullSort: 'last',
defaultSortBy: [['name', 'DESC']],
searchableColumns: ['name', 'color'],
select: ['id', 'name', 'color'],
maxLimit: 20,
defaultLimit: 50,
where: { color: 'ginger' },
filterableColumns: { age: [FilterOperator.EQ, FilterOperator.IN] },
relations: [],
loadEagerRelations: true,
withDeleted: false,
paginationType: PaginationType.LIMIT_AND_OFFSET,
relativePath: true,
origin: 'http://cats.example',
ignoreSearchByInQueryParam: true,
ignoreSelectInQueryParam: true,
}
Usage with Query Builder
You can paginate custom queries by passing on the query builder:
Example
const queryBuilder = repo
.createQueryBuilder('cats')
.leftJoinAndSelect('cats.owner', 'owner')
.where('cats.owner = :ownerId', { ownerId })
const result = await paginate<CatEntity>(query, queryBuilder, config)
Usage with Relations
Similar as with repositories, you can utilize relations
as a simplified left-join form:
Example
Endpoint
http://localhost:3000/cats?filter.toys.name=$in:Mouse,String
Code
const config: PaginateConfig<CatEntity> = {
relations: ['toys'],
sortableColumns: ['id', 'name', 'toys.name'],
filterableColumns: {
'toys.name': [FilterOperator.IN],
},
}
const result = await paginate<CatEntity>(query, catRepo, config)
Note: Embedded columns on relations have to be wrapped with brackets:
const config: PaginateConfig<CatEntity> = {
sortableColumns: ['id', 'name', 'toys.(size.height)', 'toys.(size.width)'],
searchableColumns: ['name'],
relations: ['toys'],
}
Usage with Nested Relations
Similar as with relations, you can specify nested relations for sorting, filtering and searching:
Example
Endpoint
http://localhost:3000/cats?filter.home.pillows.color=pink
Code
const config: PaginateConfig<CatEntity> = {
relations: { home: { pillows: true } },
sortableColumns: ['id', 'name', 'home.pillows.color'],
searchableColumns: ['name', 'home.pillows.color'],
filterableColumns: {
'home.pillows.color': [FilterOperator.EQ],
},
}
const result = await paginate<CatEntity>(query, catRepo, config)
Usage with Eager Loading
Eager loading should work with TypeORM's eager property out of the box:
Example
Code
@Entity()
export class CatEntity {
@OneToMany(() => CatToyEntity, (catToy) => catToy.cat, {
eager: true,
})
toys: CatToyEntity[]
}
const config: PaginateConfig<CatEntity> = {
loadEagerRelations: true,
sortableColumns: ['id', 'name', 'toys.name'],
filterableColumns: {
'toys.name': [FilterOperator.IN],
},
}
const result = await paginate<CatEntity>(query, catRepo, config)
Filters
Filter operators must be whitelisted per column in PaginateConfig
.
Examples
Code
const config: PaginateConfig<CatEntity> = {
filterableColumns: {
id: [FilterOperator.EQ, FilterSuffix.NOT],
age: true,
},
}
?filter.name=$eq:Milo
is equivalent with ?filter.name=Milo
?filter.age=$btw:4,6
where column age
is between 4
and 6
?filter.id=$not:$in:2,5,7
where column id
is not 2
, 5
or 7
?filter.summary=$not:$ilike:term
where column summary
does not contain term
?filter.summary=$sw:term
where column summary
starts with term
?filter.seenAt=$null
where column seenAt
is NULL
?filter.seenAt=$not:$null
where column seenAt
is not NULL
?filter.createdAt=$btw:2022-02-02,2022-02-10
where column createdAt
is between the dates 2022-02-02
and 2022-02-10
?filter.createdAt=$lt:2022-12-20T10:00:00.000Z
where column createdAt
is before iso date 2022-12-20T10:00:00.000Z
?filter.roles=$contains:moderator
where column roles
is an array and contains the value moderator
?filter.roles=$contains:moderator,admin
where column roles
is an array and contains the values moderator
and admin
Multi Filters
Multi filters are filters that can be applied to a single column with a comparator.
Examples
?filter.createdAt=$gt:2022-02-02&filter.createdAt=$lt:2022-02-10
where column createdAt
is after 2022-02-02
and before 2022-02-10
?filter.id=$contains:moderator&filter.id=$or:$contains:admin
where column roles
is an array and contains moderator
or admin
?filter.id=$gt:3&filter.id=$and:$lt:5&filter.id=$or:$eq:7
where column id
is greater than 3
and less than 5
or equal to 7
Note: The $and
comparators are not required. The above example is equivalent to:
?filter.id=$gt:3&filter.id=$lt:5&filter.id=$or:$eq:7
Note: The first comparator on the the first filter is ignored because the filters are grouped by the column name and chained with an $and
to other filters.
...&filter.id=5&filter.id=$or:7&filter.name=Milo&...
is resolved to:
WHERE ... AND (id = 5 OR id = 7) AND name = 'Milo' AND ...
Swagger
You can use two default decorators @ApiOkResponsePaginated and @ApiPagination to generate swagger documentation for your endpoints
@ApiOkPaginatedResponse
is for response body, return http status is 200
@ApiPaginationQuery
is for query params
@Get()
@ApiOkPaginatedResponse(
UserDto,
USER_PAGINATION_CONFIG,
)
@ApiPaginationQuery(USER_PAGINATION_CONFIG)
async findAll(
@Paginate()
query: PaginateQuery,
): Promise<Paginated<UserEntity>> {
}
There is also some syntax sugar for this, and you can use only one decorator @PaginatedSwaggerDocs
for both response body and query params
@Get()
@PaginatedSwaggerDocs(UserDto, USER_PAGINATION_CONFIG)
async findAll(
@Paginate()
query: PaginateQuery,
): Promise<Paginated<UserEntity>> {
}
Troubleshooting
The package does not report error reasons in the response bodies. They are instead
reported as debug
level logging.
Common errors include missing sortableColumns
or filterableColumns
(the latter only affects filtering).