In this guide, we’ll explore two main types of pagination techniques, offset pagination and cursor pagination, using Prisma. We’ll also cover how to sort data using Prisma’s orderBy feature.

1. Offset Pagination

Offset pagination is a straightforward technique where you specify the number of records to skip and the number to take. This is useful when you want to retrieve a subset of records without worrying about specific starting points.

Create a file named offset.ts:

offset.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const main = async () => {
  // Offset pagination
  const offsetData = await prisma.post.findMany({
    skip: 5, // Skip the first 5 records
    take: 2, // Take the next 2 records
  });

  console.log("Offset Pagination Result:", offsetData);
};

main();

In this example:

skip: 5 skips the first five records.
take: 2 retrieves the next two records after skipping.
Limitation: For large datasets, offset pagination can slow down since the database has to process all records up to the offset point before retrieving the specified records.

1. Cursor-Based Pagination

Cursor-based pagination is more efficient for large datasets. Instead of skipping a specific number of records, it starts from a known record (defined by a unique cursor) and retrieves the next set of records.

Create a file named cursor.ts:

cursor.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const main = async () => {
  // Cursor-based pagination
  const cursorData = await prisma.post.findMany({
    cursor: {
      id: 15, // Start from the record with id 15
    },
    skip: 5, // Skip 5 records after the starting point
    take: 2, // Take the next 2 records
  });

  console.log("Cursor Pagination Result:", cursorData);
};

main();

In this example:

cursor: { id: 15 } specifies that the pagination should start from the record with id: 15.
skip: 5 skips the next five records after the starting point.
take: 2 retrieves the following two records.
Difference Between Offset and Cursor Pagination
Offset: Skips a specified number of records from the beginning.
Cursor: Starts from a specific record (the cursor) and skips a certain number of records from there.

1. Sorting with orderBy

Prisma’s orderBy feature allows you to sort results in ascending or descending order. You can apply sorting to fields like id, title, content, published, authorName, createdAt, or updatedAt.

Create a file named sort.ts:

sort.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const main = async () => {
  // Sorting data in ascending order by title
  const sortData = await prisma.post.findMany({
    orderBy: {
      title: 'asc', // Sort by title in ascending order
    },
  });

  console.log("Sorted Data:", sortData);
};

main();

In this example, title: 'asc' sorts the records by the title field in ascending order. Change 'asc' to 'desc' for descending order.

Sorting with Conditions

You can combine sorting with conditional filters to retrieve only specific records, as shown below.

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const main = async () => {
  const sortData = await prisma.post.findMany({
    orderBy: {
      id: 'asc', // Sort by id in ascending order
    },
    where: {
      title: 'Title 1', // Filter to records where title is 'Title 1'
    },
  });

  console.log("Conditionally Sorted Data:", sortData);
};

main();

This code will retrieve all posts with title equal to 'Title 1' and sort them by id in ascending order.

1. Combining Pagination and Sorting

You can combine pagination and sorting for more refined queries. Here’s an example where we apply both sorting and offset pagination.

offset_sort_pagination.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const main = async () => {
  const sortAndPaginateData = await prisma.post.findMany({
    orderBy: {
      id: 'asc', // Sort by id in ascending order
    },
    skip: 3, // Skip the first 3 records
    take: 2, // Take the next 2 records
    where: {
      title: 'Title 1', // Filter to records where title is 'Title 1'
    },
  });

  console.log("Sorted and Paginated Data:", sortAndPaginateData);
};

main();

This query retrieves records where the title is 'Title 1', sorts them by id in ascending order, skips the first three records, and takes the next two.

These examples demonstrate how to implement pagination (offset and cursor-based) and sorting using Prisma. Combining these techniques enables more efficient data retrieval, especially when working with large datasets.