nestjs-paginate

nestjs-paginate is a helper library for Nest.js that provides advanced pagination and filtering for TypeORM. Key features include:

• JSON:API Compliance: Pagination follows the JSON:API specification.
• Advanced Filtering: Use operators like $eq, $not, $null, $in, $gt, $gte, $lt, $lte, $btw, $ilike, $sw, and $contains.
• Flexible Sorting & Selection: Sort by multiple columns and select specific columns.
• Relation Support: Include both direct and nested relations.
• Specialized Pagination: Supports both standard and cursor-based pagination.
• Virtual Columns: Support for virtual column handling.

If the automatic count query generated by paginate() becomes a performance bottleneck (e.g., due to expensive LEFT JOINs), you can provide a custom buildCountQuery callback. This callback receives a clone of the original SelectQueryBuilder that already contains the parsed WHERE clauses.

You You can use this to drop unnecessary joins or use a lighter DISTINCT count.

Note: If you set optimizedCount: true, the library already attempts to prune the query for you. If you provide buildCountQuery, it will override the optimized behavior.

buildCountQuery: qb => {
  qb.expressionMap.joinAttributes = [];   // drop all joins
  qb.select('p.id').distinct(true);       // keep DISTINCT on primary key
  return qb;                              // paginate() will call .getCount()
}

Polymorphic sorting allows you to sort by the COALESCE of several columns (the first non-null value). This is useful when a record links to one of several possible relations.

Usage: Join columns using the ~ symbol in the sortBy query parameter. http://localhost:3000/cats?sortBy=bestFriend.age~nemesis.age:DESC

Requirements:

• Every column in the group must be listed in sortableColumns.
• Columns must be type-compatible (e.g., all numbers).
• Only plain and relation columns are supported (no embedded, virtual, or JSONB).
• Warning: Polymorphic sorting is NOT supported with cursor pagination.

const config: PaginateConfig<CatEntity> = {
  sortableColumns: ['id', 'bestFriend.age', 'nemesis.age'],
  relations: { bestFriend: true, nemesis: true },
}

When dealing with one-to-many or many-to-many relationships, you can use quantifiers to define how many related rows must satisfy a condition:

• $any (default): At least one related row matches.
• $all: All related rows match.
• $none: No related rows match.

Examples (assuming toys is a to-many relation):

• At least one toy named 'Ball': ?filter.toys.name=$any:$eq:Ball
• All toys start with 'Chew': ?filter.toys.name=$all:$sw:Chew
• No toys named 'Squeaky': ?filter.toys.name=$none:$eq:Squeaky
• One or more toys NOT named 'Squeaky': ?filter.toys.name=$any:$not:$eq:Squeaky

To use cursor-based pagination, set paginationType: PaginationType.CURSOR in your configuration.

Supported Columns

• Numeric columns: Supports integers and decimals (up to 11 integer digits and 4 decimal digits).
• Date columns: Supports timestamp-based cursors.
• Note: String columns are not supported for cursor pagination.

Cursor Formats

• Numbers: [prefix1][integer:11 digits][prefix2][decimal:4 digits] (e.g., Y00000000001V2500).
• Dates: [prefix][value:15 digits] (e.g., V001671444000000).

Multi-column Sorting

You can use sortBy with multiple columns (e.g., sortBy=age:ASC&sortBy=createdAt:DESC), but at least one column in the sort must be unique to ensure consistent ordering and reliable cursor movement.

Similar to sorting, you can filter based on the COALESCE of multiple columns using the ~ operator. This works in both the filter= expression and per-column form.

Example: ?filter=bestFriend.age~nemesis.age=$eq:4

Requirements:

• Each part must be a plain or to-one relation column.
• Relation parts are left-joined automatically but not added to the result set.
• Nested paths (e.g., a.b.c.leaf) are supported as long as every segment before the leaf is a to-one relation.

For arbitrary boolean logic (AND, OR, NOT, and parentheses), use the filter= query parameter instead of per-column parameters.

Syntax Rules:

• Precedence: NOT > AND > OR.
• Values containing whitespace or parentheses must be quoted: ?filter=name=$eq:"Milo the cat".
• Backslashes can escape quotes: " or \'.
• Complexity Limit: To prevent DoS attacks, expressions are capped at 100 nodes by default. You can adjust this using filterExpressionMaxComplexity in the config.

Example: ?filter=(color=$eq:black OR color=$eq:white) AND NOT name=$eq:Leche

const config: PaginateConfig<CatEntity> = {
  sortableColumns: ['id'],
  filterableColumns: { color: true, name: true },
  filterExpressionMaxComplexity: 50, // custom limit
}

Use the provided decorators to automatically generate Swagger/OpenAPI documentation for your paginated routes.

Default Decorators

• @ApiOkPaginatedResponse(Dto, config): Documents the successful (200) response body.
• @ApiPaginationQuery(config): Documents the query parameters.
• @PaginatedSwaggerDocs(Dto, config): Syntax sugar that applies both of the above decorators at once.

Customizing Swagger Documentation

If you need custom descriptions (e.g., for sortBy), you can create a custom decorator using applyDecorators and then wrap it in a custom version of PaginatedSwaggerDocs.

@Get()
@PaginatedSwaggerDocs(UserDto, USER_PAGINATION_CONFIG)
async findAll(
  @Paginate()
  query: PaginateQuery,
): Promise<Paginated<UserEntity>> {
  // ...
}

AND Logic

To apply multiple conditions to the same column with AND logic, repeat the filter.<column>= parameter in the query string: ?filter.createdAt=$gt:2022-02-02&filter.createdAt=$lt:2022-02-10

OR Logic

For OR logic (within a column or across columns), use the filter= expression syntax: ?filter=id=$eq:5 OR id=$eq:7

The library supports TypeORM's eager: true property. To ensure these are handled correctly during pagination, set loadEagerRelations: true in your PaginateConfig.

const config: PaginateConfig<CatEntity> = {
  loadEagerRelations: true,
  sortableColumns: ['id', 'name', 'toys.name'],
  filterableColumns: {
    'toys.name': [FilterOperator.IN],
  },
}

const result = await paginate<CatEntity>(query, catRepo, config)

To require that a parent entity has all of several specific related values, use a filter= expression with multiple terms. Each term is treated as an independent EXISTS clause.

Example: To find cats that have both a 'Ball' toy AND a 'Mouse' toy: GET /cats?filter=toys.name=$eq:Ball AND toys.name=$eq:Mouse