How to Create Paginated, Sortable and Filterable Endpoints with NestJs

Pagination, sorting and filtering are common techniques used to deal with large sets of data returned by an API, in listing endpoints for example. They are crucial for optimizing performance, improving user experience and reducing server (and also client) load. Let’s talk briefly about this concepts before we dive into some NestJs code.

Pagination

Consists on the process of dividing a large dataset into smaller pages and serving this data to the client on an incremental, organized manner. This improves rendering speed on the client and reduces the amount of data being sent through the network.

Suppose you have a city table on you database. Brazil, for example, has more than 5.500 cities. It’s not reasonable to return all of them each time a user calls for it. Instead, we can send, say, 20 at a time, and render them accordingly (e.g. a table or a dropdown) on the frontend, which would be responsible for requesting the correct data.

And how is the frontend capable of “requesting the correct data”? It’s simple, we usually use query parameters such as page and size to specify which page and how many results to fetch.

In a paginated resource response, is common to include additional metadata along with the data fetched to provide relevant information about the pagination. Doing so allows the client to better orchestrate the requests. For example, we could return the total items count and the total pages count, then the client would know how many pages are still available to be fetched.

A Simple Example

Let’s understand how that would work with our cities table. Assume our database looks something like the following.

ID	NAME	STATE_ID
1	São Paulo	1
2	Santos	1
3	Campinas	1
4	Rio de Janeiro	2
5	Niterói	2
6	Belo Horizonte	3
7	Brasília	4
8	Curitiba	5
9	Porto Alegre	6
10	Florianópolis	7

If the getCities endpoint is correctly paginated, we could make the following requests:

https://our-api/v1/cities?page=0&size=2 : would return São Paulo and Santos
https://our-api/v1/cities?page=1&size=2 : would return Campinas and Rio de Janeiro
https://our-api/v1/cities?page=0&size=4 : would return the four of them.

And so on.

Sorting and Filtering

Sorting is about ordering the data according to a specific criteria, based on a specific field. Filtering is about selecting specific data entries depending on some other criteria, also based on specific fields.

As you may have guessed, since we are paginating our resource on the backend, here is where we also need to the the sorting and filtering. It won’t work on the client since all the data is not present there.

For illustration, assume we have a table (frontend) that renders our paginated cities. If we fetch the data with page=0 and size=2 we would receive São Paulo and Santos. If we try to sort in alphabetical order, on the client, we would get the same result (because the client only have this slice of the dataset) and that would be incorrect, the result should have been Belo Horizonte and Campinas.

In this case, when we try to sort the table on the client we should make a new request specifying that we still want the first page, with two entries, but that we need to sort them alphabetically first.

Laying the Groundwork

Let’s start getting our hands dirty. First of all, we will create some functions and custom decorators that will help us through our task.

@PaginationParams()

This first decorator (credit to my good friend Caio Argentino) is responsible for extracting and validating pagination parameters from an HTTP request query string.



import { BadRequestException, createParamDecorator, ExecutionContext } from '@nestjs/common';
import { Request } from 'express';

export interface Pagination {
    page: number;
    limit: number;
    size: number;
    offset: number;
}

export const PaginationParams = createParamDecorator((data, ctx: ExecutionContext): Pagination => {
    const req: Request = ctx.switchToHttp().getRequest();
    const page = parseInt(req.query.page as string);
    const size = parseInt(req.query.size as string);

    // check if page and size are valid
    if (isNaN(page) || page < 0 || isNaN(size) || size < 0) {
        throw new BadRequestException('Invalid pagination params');
    }
    // do not allow to fetch large slices of the dataset
    if (size > 100) {
        throw new BadRequestException('Invalid pagination params: Max size is 100');
    }

    // calculate pagination parameters
    const limit = size;
    const offset = page * limit;
    return { page, limit, size, offset };
});

After extracting the page and size from the query and validating it, the decorator returns an object that contains the information of how many items will be taken (limit) and how many will be skipped (offset).

@SortingParams(validParams)

This decorator is responsible for parsing a query parameter that comes in the format paramName:direction into an object, validating if the direction and the parameter are valid. The valid values for direction are asc and desc , and the valid parameters is an array of strings sent by the controller.



import { BadRequestException, createParamDecorator, ExecutionContext } from '@nestjs/common';
import { Request } from 'express';

export interface Sorting {
    property: string;
    direction: string;
}

export const SortingParams = createParamDecorator((validParams, ctx: ExecutionContext): Sorting => {
    const req: Request = ctx.switchToHttp().getRequest();
    const sort = req.query.sort as string;
    if (!sort) return null;

    // check if the valid params sent is an array
    if (typeof validParams != 'object') throw new BadRequestException('Invalid sort parameter');

    // check the format of the sort query param
    const sortPattern = /^([a-zA-Z0-9]+):(asc|desc)$/;
    if (!sort.match(sortPattern)) throw new BadRequestException('Invalid sort parameter');

    // extract the property name and direction and check if they are valid
    const [property, direction] = sort.split(':');
    if (!validParams.includes(property)) throw new BadRequestException(`Invalid sort property: ${property}`);

    return { property, direction };
});

@FilteringParms(validParams)

This decorator is in charge of parsing the filtering parameters (in this example we can filter only one column at a time) that comes in the format paramName:rule:value and validate them similarly to the last one.



import { BadRequestException, createParamDecorator, ExecutionContext } from '@nestjs/common';
import { Request } from 'express';

export interface Filtering {
    property: string;
    rule: string;
    value: string;
}

// valid filter rules
export enum FilterRule {
    EQUALS = 'eq',
    NOT_EQUALS = 'neq',
    GREATER_THAN = 'gt',
    GREATER_THAN_OR_EQUALS = 'gte',
    LESS_THAN = 'lt',
    LESS_THAN_OR_EQUALS = 'lte',
    LIKE = 'like',
    NOT_LIKE = 'nlike',
    IN = 'in',
    NOT_IN = 'nin',
    IS_NULL = 'isnull',
    IS_NOT_NULL = 'isnotnull',
}

export const FilteringParams = createParamDecorator((data, ctx: ExecutionContext): Filtering => {
    const req: Request = ctx.switchToHttp().getRequest();
    const filter = req.query.filter as string;
    if (!filter) return null;

    // check if the valid params sent is an array
    if (typeof data != 'object') throw new BadRequestException('Invalid filter parameter');

    // validate the format of the filter, if the rule is 'isnull' or 'isnotnull' it don't need to have a value
    if (!filter.match(/^[a-zA-Z0-9_]+:(eq|neq|gt|gte|lt|lte|like|nlike|in|nin):[a-zA-Z0-9_,]+$/) && !filter.match(/^[a-zA-Z0-9_]+:(isnull|isnotnull)$/)) {
        throw new BadRequestException('Invalid filter parameter');
    }

    // extract the parameters and validate if the rule and the property are valid
    const [property, rule, value] = filter.split(':');
    if (!data.includes(property)) throw new BadRequestException(`Invalid filter property: ${property}`);
    if (!Object.values(FilterRule).includes(rule as FilterRule)) throw new BadRequestException(`Invalid filter rule: ${rule}`);

    return { property, rule, value };
});

TypeORM Helpers

Last but not least, we will write some helper functions to generate our where object and our order object for using with TypeORM repository methods.



import { IsNull, Not, LessThan, LessThanOrEqual, MoreThan, MoreThanOrEqual, ILike, In } from "typeorm";

import { Filtering } from "src/helpers/decorators/filtering-params.decorator"
import { Sorting } from "src/helpers/decorators/sorting-params.decorator";
import { FilterRule } from "src/helpers/decorators/filtering-params.decorator";

export const getOrder = (sort: Sorting) => sort ? { [sort.property]: sort.direction } : {};

export const getWhere = (filter: Filtering) => {
    if (!filter) return {};

    if (filter.rule == FilterRule.IS_NULL) return { [filter.property]: IsNull() };
    if (filter.rule == FilterRule.IS_NOT_NULL) return { [filter.property]: Not(IsNull()) };
    if (filter.rule == FilterRule.EQUALS) return { [filter.property]: filter.value };
    if (filter.rule == FilterRule.NOT_EQUALS) return { [filter.property]: Not(filter.value) };
    if (filter.rule == FilterRule.GREATER_THAN) return { [filter.property]: MoreThan(filter.value) };
    if (filter.rule == FilterRule.GREATER_THAN_OR_EQUALS) return { [filter.property]: MoreThanOrEqual(filter.value) };
    if (filter.rule == FilterRule.LESS_THAN) return { [filter.property]: LessThan(filter.value) };
    if (filter.rule == FilterRule.LESS_THAN_OR_EQUALS) return { [filter.property]: LessThanOrEqual(filter.value) };
    if (filter.rule == FilterRule.LIKE) return { [filter.property]: ILike(`%${filter.value}%`) };
    if (filter.rule == FilterRule.NOT_LIKE) return { [filter.property]: Not(ILike(`%${filter.value}%`)) };
    if (filter.rule == FilterRule.IN) return { [filter.property]: In(filter.value.split(',')) };
    if (filter.rule == FilterRule.NOT_IN) return { [filter.property]: Not(In(filter.value.split(','))) };
}

This functions basically create the objects based on the properties returned by the decorators. For example, if the filter is city:like:Campinas we would get:



{
    city: ILike(`%Campinas%`)
}

We also will be needing a specific DTO to use when returning data from paginated resources



export type PaginatedResource<T> = {
    totalItems: number;
    items: T[];
    page: number;
    size: number;
};

Creating the Endpoint

Finally, let’s use what we’ve created in our controller and service to make it all work! We will write code for our cities example. The controller should look like this.



@Controller('cities')
export class CitiesController {
    private readonly logger = new Logger(CitiesController.name);

    constructor(
        private readonly cityService: CityService,
    ) { }

    @Get()
    @HttpCode(HttpStatus.OK)
    public async getCities(
        @PaginationParams() paginationParams: Pagination,
        @SortingParams(['name', 'id', 'stateId']) sort?: Sorting,
        @FilteringParams(['name', 'id', 'stateId']) filter?: Filtering
    ): Promise<PaginatedResource<Partial<City>>> {
        this.logger.log(`REST request to get cities: ${JSON.stringify(paginationParams)}, ${sort}, ${filter}`);
        return await this.cityService.getCities(paginationParams, sort, filter);
    }
}

Now we are able to use the decorators we created to extract the parameters our service will use to paginate, sort and filter our data. Our service will look like this.



@Injectable()
export class CityService {
    constructor(
        @InjectRepository(City)
        private readonly cityRepository: Repository<City>,
    ) { }

    public async getCities(
        { page, limit, size, offset }: Pagination,
        sort?: Sorting,
        filter?: Filtering,
    ): Promise<PaginatedResource<Partial<Language>>> {
        const where = getWhere(filter);
        const order = getOrder(sort);

        const [languages, total] = await this.cityRepository.findAndCount({
            where,
            order,
            take: limit,
            skip: offset,
        });

        return {
            totalItems: total,
            items: languages,
            page,
            size
        };
    }
}

And there we have, our own paginated, sortable and filterable endpoint! This is a simple example that I have worked out while developing some personal projects and I hope it helps someone having difficulty in this concepts / implementations. Feel free to comment and give suggestions on how to improve. Also follow me on Twitter @kogab_ :)

Blog