Prisma Pagination

How to Use Prisma Pagination Effectively

Pagination is a fundamental part of API design, ensuring that data retrieval remains efficient and scalable. Prisma provides robust pagination options, but integrating them effectively into your project can be tricky. This guide will walk you through implementing Prisma-based pagination and enhancing it with a reusable paginate library.


πŸ”Ή Understanding Prisma Pagination

Prisma supports two common pagination techniques:

  1. Offset-based Pagination (skip + take)
    • Simple and easy to implement.
    • Inefficient for large datasets due to performance issues with skip.
  2. Cursor-based Pagination (cursor + take)
    • More efficient, as it avoids skipping records.
    • Requires a stable, unique sorting key.

Most developers are familiar with using take and cursor for pagination. However, a structured approach using a dedicated pagination service improves maintainability and consistency across projects.


πŸ”Ή Implementing Pagination with Prisma

Offset-based Pagination Example

const getPaginatedResults = async (page: number, pageSize: number) => {
  const skip = (page - 1) * pageSize;
  return await prisma.user.findMany({
    skip,
    take: pageSize,
    orderBy: { createdAt: 'desc' },
  });
};

Cursor-based Pagination Example

const getCursorPaginatedResults = async (cursor?: string, pageSize: number) => {
  return await prisma.user.findMany({
    take: pageSize,
    skip: cursor ? 1 : 0, // Skip the cursor itself
    cursor: cursor ? { id: cursor } : undefined,
    orderBy: { createdAt: 'desc' },
  });
};

While these approaches work, manually implementing them in every service method can be tedious. Let’s build a generic solution using our paginate library.


πŸ”Ή Using getPaginate to Simplify Prisma Pagination

Our project already has a structured pagination utility (getPaginate), which abstracts pagination logic, making it reusable across different models.

πŸ“Œ Modifying getPaginate to Support Prisma

Our existing getPaginate function (found in paginate/service.ts) is flexible. Let’s ensure it works seamlessly with Prisma models.

βœ… Usage Example with Prisma

import { getPaginate } from '@/shared/paginate/service';
import { PrismaClient } from '@prisma/client';
import { UserDto } from '@/modules/user/dto/user.dto';

const prisma = new PrismaClient();

export async function getPaginatedUsers(options: { pageSize: number; pageToken?: string }) {
  return await getPaginate(prisma.user, UserDto, options);
}

πŸ”Ή How Our getPaginate Library Works

Our pagination utility is structured and reusable, handling:

πŸ›  Internals of getPaginate

Refactoring getPaginate for Prisma Models

We need to modify our pagination function to work well with Prisma.

export async function getPaginate<Dto>(
  paginateModel: any, // Prisma model
  dtoClass: { from: (input: any) => Dto },
  options: {
    pageSize: number;
    pageToken?: string;
    where?: any;
    orderBy?: any;
    include?: any;
    includePageCount?: boolean;
  },
): Promise<{ items: Dto[]; nextPageToken: string; totalCount: number }> {
  let page = options.pageToken ? parseInt(options.pageToken) : 1;
  
  const skip = (page - 1) * options.pageSize;

  const [results, totalCount] = await Promise.all([
    paginateModel.findMany({
      where: options.where,
      orderBy: options.orderBy,
      include: options.include,
      skip,
      take: options.pageSize,
    }),
    paginateModel.count({ where: options.where }),
  ]);

  const totalPages = Math.ceil(totalCount / options.pageSize);
  const nextPageToken = page < totalPages ? String(page + 1) : '';

  return {
    items: results.map((result: any) => dtoClass.from(result)),
    nextPageToken,
    totalCount,
  };
}

πŸ”Ή πŸ“Œ Example Usage in a Controller

Let’s integrate getPaginate into a NestJS service.

import { Injectable } from '@nestjs/common';
import { PrismaService } from '@/shared/prisma.service';
import { getPaginate } from '@/shared/paginate/service';
import { UserDto } from '@/modules/user/dto/user.dto';

@Injectable()
export class UserService {
  constructor(private readonly prisma: PrismaService) {}

  async getUsers(pageToken?: string, pageSize = 10) {
    return await getPaginate(this.prisma.user, UserDto, { pageSize, pageToken });
  }
}

πŸ”Ή Benefits of Using getPaginate

βœ… Reusability – No need to rewrite pagination logic for every model.
βœ… Consistency – All API responses follow the same format.
βœ… Performance – Uses Prisma’s optimized query execution.
βœ… Scalability – Easily integrates with any Prisma model.


πŸ”Ή Conclusion

By leveraging Prisma’s built-in pagination features with our getPaginate function, we’ve created a powerful and flexible way to handle pagination across different database models. This approach reduces redundant code while ensuring consistency in API responses.