Nest.js 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
• Cursor-based pagination

Installation

npm install nestjs-paginate

Usage

Example

The following code exposes a route that can be utilized like so:

Endpoint

1http://localhost:3000/cats?limit=5&page=2&sortBy=color:DESC&search=i&filter.age=$gte:3&select=id,name,color,age

Result

1{
2  "data": [
3    {
4      "id": 4,
5      "name": "George",
6      "color": "white",
7      "age": 3
8    },
9    {
10      "id": 5,
11      "name": "Leche",
12      "color": "white",
13      "age": 6
14    },
15    {
16      "id": 2,
17      "name": "Garfield",
18      "color": "ginger",
19      "age": 4
20    },
21    {
22      "id": 1,
23      "name": "Milo",
24      "color": "brown",
25      "age": 5
26    },
27    {
28      "id": 3,
29      "name": "Kitty",
30      "color": "black",
31      "age": 3
32    }
33  ],
34  "meta": {
35    "itemsPerPage": 5,
36    "totalItems": 12,
37    "currentPage": 2,
38    "totalPages": 3,
39    "sortBy": [["color", "DESC"]],
40    "search": "i",
41    "filter": {
42      "age": "$gte:3"
43    }
44  },
45  "links": {
46    "first": "http://localhost:3000/cats?limit=5&page=1&sortBy=color:DESC&search=i&filter.age=$gte:3",
47    "previous": "http://localhost:3000/cats?limit=5&page=1&sortBy=color:DESC&search=i&filter.age=$gte:3",
48    "current": "http://localhost:3000/cats?limit=5&page=2&sortBy=color:DESC&search=i&filter.age=$gte:3",
49    "next": "http://localhost:3000/cats?limit=5&page=3&sortBy=color:DESC&search=i&filter.age=$gte:3",
50    "last": "http://localhost:3000/cats?limit=5&page=3&sortBy=color:DESC&search=i&filter.age=$gte:3"
51  }
52}

Example (Cursor-based Pagination)

The following code exposes a route using cursor-based pagination:

Endpoint

1http://localhost:3000/cats?limit=5&sortBy=lastVetVisit:ASC&cursor=V998328469600000

Result

1{
2  "data": [
3    {
4      "id": 3,
5      "name": "Shadow",
6      "lastVetVisit": "2022-12-21T10:00:00.000Z"
7    },
8    {
9      "id": 4,
10      "name": "Luna",
11      "lastVetVisit": "2022-12-22T10:00:00.000Z"
12    },
13    {
14      "id": 5,
15      "name": "Pepper",
16      "lastVetVisit": "2022-12-23T10:00:00.000Z"
17    },
18    {
19      "id": 6,
20      "name": "Simba",
21      "lastVetVisit": "2022-12-24T10:00:00.000Z"
22    },
23    {
24      "id": 7,
25      "name": "Tiger",
26      "lastVetVisit": "2022-12-25T10:00:00.000Z"
27    }
28  ],
29  "meta": {
30    "itemsPerPage": 5,
31    "cursor": "V998328469600000"
32  },
33  "links": {
34    "previous": "http://localhost:3000/cats?limit=5&sortBy=lastVetVisit:DESC&cursor=V001671616800000",
35    "current": "http://localhost:3000/cats?limit=5&sortBy=lastVetVisit:ASC&cursor=V998328469600000",
36    "next": "http://localhost:3000/cats?limit=5&sortBy=lastVetVisit:ASC&cursor=V998328037600000"
37  }
38}

Code

1import { Controller, Injectable, Get } from '@nestjs/common'
2import { InjectRepository } from '@nestjs/typeorm'
3import { FilterOperator, FilterSuffix, Paginate, PaginateQuery, paginate, Paginated } from 'nestjs-paginate'
4import { Repository, Entity, PrimaryGeneratedColumn, Column } from 'typeorm'
5
6@Entity()
7export class CatEntity {
8  @PrimaryGeneratedColumn()
9  id: number
10
11  @Column('text')
12  name: string
13
14  @Column('text')
15  color: string
16
17  @Column('int')
18  age: number
19
20  @Column({ nullable: true })
21  lastVetVisit: Date | null
22
23  @CreateDateColumn()
24  createdAt: string
25}
26
27@Injectable()
28export class CatsService {
29  constructor(
30    @InjectRepository(CatEntity)
31    private readonly catsRepository: Repository<CatEntity>
32  ) {}
33
34  public findAll(query: PaginateQuery): Promise<Paginated<CatEntity>> {
35    return paginate(query, this.catsRepository, {
36      sortableColumns: ['id', 'name', 'color', 'age'],
37      nullSort: 'last',
38      defaultSortBy: [['id', 'DESC']],
39      searchableColumns: ['name', 'color', 'age'],
40      select: ['id', 'name', 'color', 'age', 'lastVetVisit'],
41      filterableColumns: {
42        name: [FilterOperator.EQ, FilterSuffix.NOT],
43        age: true,
44      },
45    })
46  }
47}
48
49@Controller('cats')
50export class CatsController {
51  constructor(private readonly catsService: CatsService) {}
52
53  @Get()
54  public findAll(@Paginate() query: PaginateQuery): Promise<Paginated<CatEntity>> {
55    return this.catsService.findAll(query)
56  }
57}

Config

1const paginateConfig: PaginateConfig<CatEntity> {
2  /**
3   * Required: true (must have a minimum of one column)
4   * Type: (keyof CatEntity)[]
5   * Description: These are the columns that are valid to be sorted by.
6   */
7  sortableColumns: ['id', 'name', 'color'],
8
9  /**
10   * Required: false
11   * Type: 'first' | 'last'
12   * Description: Define whether to put null values at the beginning
13   * or end of the result set.
14   */
15  nullSort: 'last',
16
17  /**
18   * Required: false
19   * Type: [keyof CatEntity, 'ASC' | 'DESC'][]
20   * Default: [[sortableColumns[0], 'ASC]]
21   * Description: The order to display the sorted entities.
22   */
23  defaultSortBy: [['name', 'DESC']],
24
25  /**
26   * Required: false
27   * Type: (keyof CatEntity)[]
28   * Description: These columns will be searched through when using the search query
29   * param. Limit search scope further by using `searchBy` query param.
30   */
31  searchableColumns: ['name', 'color'],
32
33  /**
34   * Required: false
35   * Type: (keyof CatEntity)[]
36   * Default: None
37   * Description: TypeORM partial selection. Limit selection further by using `select` query param.
38   * https://typeorm.io/select-query-builder#partial-selection
39   * Note: if you do not contain the primary key in the select array, primary key will be added automatically.
40   * 
41   * Wildcard support:
42   * - Use '*' to select all columns from the main entity.
43   * - Use 'relation.*' to select all columns from a relation.
44   * - Use 'relation.subrelation.*' to select all columns from nested relations.
45   * 
46   * Examples:
47   * select: ['*'] - Selects all columns from main entity
48   * select: ['id', 'name', 'toys.*'] - Selects id, name from main entity and all columns from toys relation
49   * select: ['*', 'toys.*'] - Selects all columns from both main entity and toys relation
50   */
51  select: ['id', 'name', 'color'],
52
53  /**
54   * Required: false
55   * Type: number
56   * Default: 100
57   * Description: The maximum amount of entities to return per page.
58   * Set it to -1, in conjunction with limit=-1 on query param, to disable pagination.
59   */
60  maxLimit: 20,
61
62  /**
63   * Required: false
64   * Type: number
65   * Default: 20
66   */
67  defaultLimit: 50,
68
69  /**
70   * Required: false
71   * Type: TypeORM find options
72   * Default: None
73   * https://typeorm.io/#/find-optionsfind-options.md
74   */
75  where: { color: 'ginger' },
76
77  /**
78   * Required: false
79   * Type: { [key in CatEntity]?: FilterOperator[] } - Operators based on TypeORM find operators
80   * Default: None
81   * https://typeorm.io/#/find-options/advanced-options
82   */
83  filterableColumns: { age: [FilterOperator.EQ, FilterOperator.IN] },
84
85  /**
86   * Required: false
87   * Type: RelationColumn<CatEntity>
88   * Description: Indicates what relations of entity should be loaded.
89   */
90  relations: [],
91
92  /**
93   * Required: false
94   * Type: boolean
95   * Default: false
96   * Description: Load eager relations using TypeORM's eager property.
97   * Only works if `relations` is not defined.
98   */
99  loadEagerRelations: true,
100
101  /**
102   * Required: false
103   * Type: boolean
104   * Description: Disables the global condition of "non-deleted" for the entity with delete date columns.
105   * https://typeorm.io/select-query-builder#querying-deleted-rows
106   */
107  withDeleted: false,
108
109  /**
110   * Required: false
111   * Type: string
112   * Description: Allow user to choose between limit/offset and take/skip, or cursor-based pagination.
113   * Default: PaginationType.TAKE_AND_SKIP
114   * Options: PaginationType.LIMIT_AND_OFFSET, PaginationType.TAKE_AND_SKIP, PaginationType.CURSOR
115   *
116   * However, using limit/offset can cause problems with relations.
117   */
118  paginationType: PaginationType.LIMIT_AND_OFFSET,
119
120  /**
121   * Required: false
122   * Type: boolean
123   * Default: false
124   * Description: Generate relative paths in the resource links.
125   */
126  relativePath: true,
127
128  /**
129   * Required: false
130   * Type: string
131   * Description: Overrides the origin of absolute resource links if set.
132   */
133  origin: 'http://cats.example',
134
135  /**
136   * Required: false
137   * Type: boolean
138   * Default: false
139   * Description: Prevent `searchBy` query param from limiting search scope further. Search will depend upon `searchableColumns` config option only
140   */
141  ignoreSearchByInQueryParam: true,
142
143  /**
144   * Required: false
145   * Type: boolean
146   * Default: false
147   * Description: Prevent `select` query param from limiting selection further. Partial selection will depend upon `select` config option only
148   */
149  ignoreSelectInQueryParam: true,
150
151  /**
152   * Required: false
153   * Type: 'leftJoinAndSelect' | 'innerJoinAndSelect'
154   * Default: 'leftJoinAndSelect'
155   * Description: Relationships will be joined with either LEFT JOIN or INNER JOIN, and their columns selected. Can be specified per column with `joinMethods` configuration.
156   */
157  defaultJoinMethod: 'leftJoinAndSelect',
158
159  /**
160   * Required: false
161   * Type: MappedColumns<T, JoinMethod>
162   * Default: false
163   * Description: Overrides the join method per relationship.
164   */
165  joinMethods: {age: 'innerJoinAndSelect', size: 'leftJoinAndSelect'},
166
167  /**
168   * Required: false
169   * Type: boolean
170   * Default: false
171   * Description: Enable multi-word search behavior. When true, each word in the search query
172   * will be treated as a separate search term, allowing for more flexible matching.
173   */
174  multiWordSearch: false,
175
176  /**
177   * Required: false
178   * Type: (qb: SelectQueryBuilder<T>) => SelectQueryBuilder<any>
179   * Default: undefined
180   * Description: Callback that lets you override the COUNT query executed by
181   * paginate(). The function receives a **clone** of the original QueryBuilder,
182   * so it already contains every WHERE clause and parameter parsed by
183   * nestjs-paginate.
184   *
185   * Typical use-case: remove expensive LEFT JOINs or build a lighter DISTINCT
186   * count when getManyAndCount() becomes a bottleneck.
187   *
188   * Example:
189   * ```ts
190   * buildCountQuery: qb => {
191   *   qb.expressionMap.joinAttributes = [];   // drop all joins
192   *   qb.select('p.id').distinct(true);       // keep DISTINCT on primary key
193   *   return qb;                              // paginate() will call .getCount()
194   * }
195   * ```
196   */
197  buildCountQuery: (qb: SelectQueryBuilder<T>) => SelectQueryBuilder<any>,
198}

Usage with Query Builder

You can paginate custom queries by passing on the query builder:

Example

1const queryBuilder = repo
2  .createQueryBuilder('cats')
3  .leftJoinAndSelect('cats.owner', 'owner')
4  .where('cats.owner = :ownerId', { ownerId })
5
6const 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

1http://localhost:3000/cats?filter.toys.name=$in:Mouse,String

Code

1const config: PaginateConfig<CatEntity> = {
2  relations: ['toys'],
3  sortableColumns: ['id', 'name', 'toys.name'],
4  filterableColumns: {
5    'toys.name': [FilterOperator.IN],
6  },
7}
8
9const result = await paginate<CatEntity>(query, catRepo, config)

Note: Embedded columns on relations have to be wrapped with brackets:

1const config: PaginateConfig<CatEntity> = {
2  sortableColumns: ['id', 'name', 'toys.(size.height)', 'toys.(size.width)'],
3  searchableColumns: ['name'],
4  relations: ['toys'],
5}

Usage with Nested Relations

Similar as with relations, you can specify nested relations for sorting, filtering and searching:

Example

Endpoint

1http://localhost:3000/cats?filter.home.pillows.color=pink

Code

1const config: PaginateConfig<CatEntity> = {
2  relations: { home: { pillows: true } },
3  sortableColumns: ['id', 'name', 'home.pillows.color'],
4  searchableColumns: ['name', 'home.pillows.color'],
5  filterableColumns: {
6    'home.pillows.color': [FilterOperator.EQ],
7  },
8}
9
10const 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

1@Entity()
2export class CatEntity {
3  // ...
4
5  @OneToMany(() => CatToyEntity, (catToy) => catToy.cat, {
6    eager: true,
7  })
8  toys: CatToyEntity[]
9}
10
11const config: PaginateConfig<CatEntity> = {
12  loadEagerRelations: true,
13  sortableColumns: ['id', 'name', 'toys.name'],
14  filterableColumns: {
15    'toys.name': [FilterOperator.IN],
16  },
17}
18
19const result = await paginate<CatEntity>(query, catRepo, config)

Filters

Filter operators must be whitelisted per column in PaginateConfig.

Examples

Code

1const config: PaginateConfig<CatEntity> = {
2  // ...
3  filterableColumns: {
4    // Enable individual operators on a column
5    id: [FilterOperator.EQ, FilterSuffix.NOT],
6
7    // Enable all operators on a column
8    age: true,
9  },
10}

?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

Jsonb Filters

You can filter on jsonb columns by using the dot notation. Json columns is limited to $eq operators only.

?filter.metadata.enabled=$eq:true where column metadata is jsonb and contains an object with the key enabled.

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.roles=$contains:moderator&filter.roles=$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 ...

Cursor-based Pagination

• paginationType: PaginationType.CURSOR
• Cursor format:
  • Numbers: [prefix1][integer:11 digits][prefix2][decimal:4 digits] (e.g., Y00000000001V2500 for -1.25 in ASC).
  • Dates: [prefix][value:15 digits] (e.g., V001671444000000 for a timestamp in DESC).
• Prefixes:
  • null: A (lowest priority, last in results).
  • ASC:
    • positive-int: V (greater than or equal to 1), X (less than 1)
    • positive-decimal: V (not zero), X (zero)
    • zero-int: X
    • zero-decimal: X
    • negative-int: Y
    • negative-decimal: V
  • DESC:
    • positive-int: V
    • positive-decimal: V
    • zero-int: N
    • zero-decimal: X
    • negative-int: M (less than or equal to -1), N (greater than -1)
    • negative-decimal: V (not zero), X (zero)
• Logic:
  • Numbers: Split into integer (11 digits) and decimal (4 digits) parts, with separate prefixes. Supports negative values, with sorting adjusted per direction.
  • Dates: Single prefix with 15-digit timestamp padded with zeros.
  • ASC: Negative → Zero → Positive → Null.
  • DESC: Positive → Zero → Negative → Null.
• Notes:
  • Multiple columns: sortBy can include multiple columns to create and sort by the cursor (e.g., sortBy=age:ASC&sortBy=createdAt:DESC), but at least one column must be unique to ensure consistent ordering.
  • Supported columns: Cursor sorting is available for numeric and date-related columns (string columns are not supported).
  • Decimal support: Numeric columns can include decimals, limited to 11 digits for the integer part and 4 digits for the decimal part.

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

1  @Get()
2  @ApiOkPaginatedResponse(
3    UserDto,
4    USER_PAGINATION_CONFIG,
5  )
6  @ApiPaginationQuery(USER_PAGINATION_CONFIG)
7  async findAll(
8    @Paginate()
9    query: PaginateQuery,
10  ): Promise<Paginated<UserEntity>> {
11
12  }

There is also some syntax sugar for this, and you can use only one decorator @PaginatedSwaggerDocs for both response body and query params

1  @Get()
2  @PaginatedSwaggerDocs(UserDto, USER_PAGINATION_CONFIG)
3  async findAll(
4    @Paginate()
5    query: PaginateQuery,
6  ): Promise<Paginated<UserEntity>> {
7
8  }

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).