[Startup MVP recipes #8] Pagination – Nest.js, GraphQL, and TypeORM

For this topic, there are already good examples over the internet and I will just paste some references and give an code pointer directly.

References

The Code

// page-info.ts

import { Field, ObjectType } from '@nestjs/graphql';

@ObjectType()
export class PageInfo {
  @Field({ nullable: true })
  startCursor: string;

  @Field({ nullable: true })
  endCursor: string;

  @Field()
  hasPreviousPage: boolean;

  @Field()
  hasNextPage: boolean;

  @Field()
  countBefore: number;

  @Field()
  countNext: number;

  @Field()
  countCurrent: number;

  @Field()
  countTotal: number;
}

//pagination.args.ts

import { ArgsType, Field, Int } from '@nestjs/graphql';

@ArgsType()
export class PaginationArgs {
  @Field(() => Int, { nullable: true })
  first?: number;

  @Field(() => String, { nullable: true })
  after?: string;

  @Field(() => Int, { nullable: true })
  last?: number;

  @Field(() => String, { nullable: true })
  before?: string;
}

// paginated.ts

import { Type } from '@nestjs/common';
import { Field, ObjectType } from '@nestjs/graphql';
import { PageInfo } from './page-info';

interface IEdgeType<T> {
  cursor: string;
  node: T;
}

export interface IPaginatedType<T> {
  edges: IEdgeType<T>[];
  nodes: T[];
  pageInfo: PageInfo;
}

export function Paginated<T>(classRef: Type<T>): Type<IPaginatedType<T>> {
  @ObjectType(`${classRef.name}Edge`)
  abstract class EdgeType {
    @Field(() => String)
    cursor: string;

    @Field(() => classRef)
    node: T;
  }

  @ObjectType({ isAbstract: true })
  abstract class PaginatedType implements IPaginatedType<T> {
    @Field(() => [EdgeType], { nullable: true })
    edges: EdgeType[];

    @Field(() => [classRef], { nullable: true })
    nodes: T[];

    @Field(() => PageInfo, { nullable: true })
    pageInfo: PageInfo;
  }
  return PaginatedType as Type<IPaginatedType<T>>;
}

// paginate.ts

import { Logger } from '@nestjs/common';
import { LessThan, MoreThan, SelectQueryBuilder } from 'typeorm';
import { convertFirstLevelSnakeToCamelCase } from '../utils';
import { PageInfo } from './page-info';
import { PaginationArgs } from './pagination.args';

/**
 * Based on https://gist.github.com/VojtaSim/6b03466f1964a6c81a3dbf1f8cec8d5c
 * and https://gist.github.com/tumainimosha/6652deb0aea172f7f2c4b2077c72d16c
 */
export async function paginate<T>(
  query: SelectQueryBuilder<T>,
  paginationArgs: PaginationArgs,
  cursorColumn = 'id',
  defaultLimit = 100,
): Promise<any> {
  const logger = new Logger('Pagination');

  // pagination ordering
  query.orderBy({ [cursorColumn]: 'ASC' });

  const totalCountQuery = query.clone();

  // FORWARD pagination
  if (paginationArgs.first) {
    if (paginationArgs.after) {
      const offsetId = Number(
        Buffer.from(paginationArgs.after, 'base64').toString('ascii'),
      );
      logger.verbose(`Paginate AfterID: ${offsetId}`);
      query.where({ [cursorColumn]: MoreThan(offsetId) });
    }

    const limit = paginationArgs.first ?? defaultLimit;

    query.take(limit);
  }

  // REVERSE pagination
  else if (paginationArgs.last && paginationArgs.before) {
    const offsetId = Number(
      Buffer.from(paginationArgs.before, 'base64').toString('ascii'),
    );
    logger.verbose(`Paginate BeforeID: ${offsetId}`);

    const limit = paginationArgs.last ?? defaultLimit;

    query.where({ [cursorColumn]: LessThan(offsetId) }).take(limit);
  }

  const result = (await query.getMany()).map((row) =>
    convertFirstLevelSnakeToCamelCase(row),
  );

  const startCursorId: number =
    result.length > 0 ? result[0][cursorColumn] : null;
  const endCursorId: number =
    result.length > 0 ? result.slice(-1)[0][cursorColumn] : null;

  const beforeQuery = totalCountQuery.clone();

  const afterQuery = beforeQuery.clone();

  let countBefore = 0;
  let countAfter = 0;
  if (
    beforeQuery.expressionMap.wheres &&
    beforeQuery.expressionMap.wheres.length
  ) {
    countBefore = await beforeQuery
      .andWhere(`${cursorColumn} < :cursor`, { cursor: startCursorId })
      .getCount();
    countAfter = await afterQuery
      .andWhere(`${cursorColumn} > :cursor`, { cursor: endCursorId })
      .getCount();
  } else {
    countBefore = await beforeQuery
      .where(`${cursorColumn} < :cursor`, { cursor: startCursorId })
      .getCount();

    countAfter = await afterQuery
      .where(`${cursorColumn} > :cursor`, { cursor: endCursorId })
      .getCount();
  }

  logger.debug(`CountBefore:${countBefore}`);
  logger.debug(`CountAfter:${countAfter}`);

  const edges = result.map((value) => {
    return {
      node: value,
      cursor: Buffer.from(`${value[cursorColumn]}`).toString('base64'),
    };
  });

  const pageInfo = new PageInfo();
  pageInfo.startCursor = edges.length > 0 ? edges[0].cursor : null;
  pageInfo.endCursor = edges.length > 0 ? edges.slice(-1)[0].cursor : null;

  pageInfo.hasNextPage = countAfter > 0;
  pageInfo.hasPreviousPage = countBefore > 0;
  pageInfo.countBefore = countBefore;
  pageInfo.countNext = countAfter;
  pageInfo.countCurrent = edges.length;
  pageInfo.countTotal = countAfter + countBefore + edges.length;
  return { edges, nodes: result, pageInfo };
}

Usage

We have a field called sellerId which is just the user id. A sample usage:

// paginated-class-ref.output.ts

@ObjectType()
export class PaginatedClassRef extends Paginated(ClassRef) {}

// resolver

@Query(() => PaginatedClassRef, { name: 'findPaginatedClassRef' })
  async findPaginated(
    @Args() paginationArgs: PaginationArgs,
  ) {
    return this.emailSubscriptionsService.findPaginated(
      paginationArgs,
    );
  }

// service

async findPaginated(
    paginationArgs: PaginationArgs,
  ) {
    const query = this.emailSubscriptionRepo
      .createQueryBuilder()
      .select()
      // .where('seller_id = :sellerId', { sellerId });

    return paginate(query, paginationArgs);
  }

The Query

Will look like something similar to this (we use sms subscription entity as example)

query Query($last: Int) {
  v2smsSubscriptions(last: $last) {
    edges {
      cursor
      node {
        id
      }
    }
    nodes {
      id
    }
    pageInfo {
      countBefore
      countCurrent
      countNext
      countTotal
      endCursor
      hasNextPage
      hasPreviousPage
      startCursor
    }
  }
}

{
  "last": 5
}

{
  "data": {
    "v2smsSubscriptions": {
      "edges": [
        {
          "cursor": "NDk=",
          "node": {
            "id": "49"
          }
        },
        {
          "cursor": "NTA=",
          "node": {
            "id": "50"
          }
        }
      ],
      "nodes": [
        {
          "id": "49"
        },
        {
          "id": "50"
        }
      ],
      "pageInfo": {
        "countBefore": 0,
        "countCurrent": 2,
        "countNext": 0,
        "countTotal": 2,
        "endCursor": "NTA=",
        "hasNextPage": false,
        "hasPreviousPage": false,
        "startCursor": "NDk="
      }
    }
  }
}

Follow Up: Pointers for MikroOrm (added support for reverse order)

// paginate.utils.ts

import { QueryOrder } from '@mikro-orm/core';

export const getOrderOp = (queryOrder: QueryOrder) => {
  return queryOrder === QueryOrder.ASC ? '$gt' : '$lt';
};

export const getReverseOrderOp = (queryOrder: QueryOrder) => {
  return queryOrder === QueryOrder.ASC ? '$lte' : '$gte';
};

// paginate.ts

import { QueryOrder } from '@mikro-orm/core';
import { QueryBuilder } from '@mikro-orm/postgresql';

import PageInfo from './page-info';

import { getOrderOp, getReverseOrderOp } from './paginate.utils';

import PaginationArgs from './pagination.args';

/**
 * Based on https://gist.github.com/VojtaSim/6b03466f1964a6c81a3dbf1f8cec8d5c
 * and https://gist.github.com/tumainimosha/6652deb0aea172f7f2c4b2077c72d16c
 * but tailored for MikroOrm instead of TypeOrm
 */
export default async function paginate<T extends object>(
  qb: QueryBuilder<T>,
  paginationArgs: PaginationArgs,
  cursorColumn = 'id',
  queryOrder: QueryOrder = QueryOrder.ASC,
  defaultLimit = 100,
): Promise<any> {
  // const logger = new Logger('Pagination');

  // pagination ordering
  qb.orderBy({ [cursorColumn]: queryOrder });

  const totalCountQuery = qb.clone();

  // FORWARD pagination
  if (paginationArgs.first) {
    if (paginationArgs.after) {
      const offsetId = Number(
        Buffer.from(paginationArgs.after, 'base64').toString('ascii'),
      );
      // logger.verbose(`Paginate AfterID: ${offsetId}`);
      qb.where({ [cursorColumn]: { [getOrderOp(queryOrder)]: offsetId } });
    }

    const limit = paginationArgs.first ?? defaultLimit;

    qb.limit(limit);
  }

  // REVERSE pagination
  else if (paginationArgs.last && paginationArgs.before) {
    const offsetId = Number(
      Buffer.from(paginationArgs.before, 'base64').toString('ascii'),
    );
    // logger.verbose(`Paginate BeforeID: ${offsetId}`);

    const limit = paginationArgs.last ?? defaultLimit;

    qb.where({
      [cursorColumn]: { [getReverseOrderOp(queryOrder)]: offsetId },
    }).limit(limit);
  }

  const result = await qb.getResult();

  const startCursorId: number =
    result.length > 0 ? result[0][cursorColumn] : null;
  const endCursorId: number =
    result.length > 0 ? result.slice(-1)[0][cursorColumn] : null;

  const beforeQuery = totalCountQuery.clone();

  const afterQuery = beforeQuery.clone();

  let countBefore = 0;
  let countAfter = 0;
  if (totalCountQuery.getKnexQuery().toQuery().includes('WHERE')) {
    countBefore = await beforeQuery
      .andWhere({ [cursorColumn]: { $lt: startCursorId } })
      .getCount();
    countAfter = await afterQuery
      .andWhere({ [cursorColumn]: { $gt: endCursorId } })
      .getCount();
  } else {
    countBefore = await beforeQuery
      .where({ [cursorColumn]: { $lt: startCursorId } })
      .getCount();
    countAfter = await afterQuery
      .where({ [cursorColumn]: { $gt: endCursorId } })
      .getCount();
  }

  // logger.debug(`CountBefore:${countBefore}`);
  // logger.debug(`CountAfter:${countAfter}`);

  const edges = result.map(value => {
    return {
      node: value,
      cursor: Buffer.from(`${value[cursorColumn]}`).toString('base64'),
    };
  });

  const pageInfo = new PageInfo();
  pageInfo.startCursor = edges.length > 0 ? edges[0].cursor : null;
  pageInfo.endCursor = edges.length > 0 ? edges.slice(-1)[0].cursor : null;

  pageInfo.hasNextPage = countAfter > 0;
  pageInfo.hasPreviousPage = countBefore > 0;
  pageInfo.countBefore = countBefore;
  pageInfo.countNext = countAfter;
  pageInfo.countCurrent = edges.length;
  pageInfo.countTotal = countAfter + countBefore + edges.length;
  return { edges, nodes: result, pageInfo };
}