Server-Side Pagination in Remix: Offset and Cursor-based Pagination

Introduction

Pagination is essential for managing large datasets, and Remix offers a straightforward approach to server-side pagination. By utilizing query parameters (params), Remix automatically reloads your page when these parameters change, enabling efficient data fetching. This ensures a seamless user experience, as content can be dynamically updated based on the current page without requiring manual refreshes.

In this tutorial, we will use Remix and Prisma for server-side pagination using both offset based pagination as well as cursor-based pagination. Additionally, we will discuss advantages and disadvantages of both the approaches to help you decide, which method to use as per your requirement.

Offset-Based Pagination

Offset- Based Pagination is a pretty straight-forward approach to where we just skip certain number of records and fetch the result from the database. We simply skip pageSize * pageNumber number of rows. You can figure out the problem with this approach. Let's say we need to retrieve the 10th page in the dataset, then we have to fetch and skip the first 9 pages which can be a costly query to our database. This can become inefficient with large datasets as we have to skip a lot of rows making it less suitable for scenarios with extensive pagination requirements.

Implementation

First, we have to setup Prisma in our remix project. You can follow the Prisma guide to get started with Prisma.

In our Prisma schema file schema.prisma , let's define our model. For this example, we'll use a simple Post model.

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  createdAt DateTime @default(now())
}

After defining our schema, we need to run npx prisma migrate dev to create the database and the Post table.

Now, coming to our remix file, let's create a loader function to fetch the data from the server.

// app/.server/prisma.ts

export const prisma = new PrismaClient();

// app/routes/posts.jsx
import { json, useLoaderData } from 'remix';
import {prisma} from '~/.server/prisma';
import { useSearchParams } from 'react-router-dom';

export const shouldRevalidate:ShouldRevalidateFunction = ({formAction, nextUrl, currentUrl, defaultShouldRevalidate}:ShouldRevalidateFunctionArgs)=>{
  if(!formAction && currentUrl.pathname === nextUrl.pathname) return false;
  return defaultShouldRevalidate;
}

export const loader = async ({ request }) => {
  const url = new URL(request.url);
  const page = parseInt(url.searchParams.get('page') || '1', 10);
  const pageSize = 10;
  const offset = (page - 1) * pageSize;

  const posts = await prisma.post.findMany({
    skip: offset,
    take: pageSize,
  });

  const totalPosts = await prisma.post.count();

  return json({ posts, totalPosts, page, pageSize });
};

export default function Posts() {
  const { posts, totalPosts, page, pageSize } = useLoaderData();
  const [searchParams, setSearchParams] = useSearchParams();

  const totalPages = Math.ceil(totalPosts / pageSize);

  const handlePageChange = (newPage) => {
    searchParams.set('page', newPage);
    setSearchParams(searchParams);
  };

  return (
    <div>
      {posts.map(post => (
        <div key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
        </div>
      ))}
      <div className="pagination-controls">
        {page > 1 && (
          <button onClick={() => handlePageChange(page - 
        1)}>Previous</button>
        )}
        {page < totalPages && (
          <button onClick={() => handlePageChange(page + 
         1)}>Next</button>
        )}
      </div>
    </div>
  );
}

In our loader function, we get page and pageSize query from our URL and pass that to our database call. Using search params can be a great way to handle state rather than explicitly using useState. This makes sure that the loader function is automatically called every time the query changes, so we don't have to worry about revalidation on page changes. Also, we have exported revalidate function to make sure the loader is only called when the page or pageSize changes.

Also Read: Implementing Bit Field Role-Based Access Control (RBAC)

Cursor-based pagination

Cursor-based pagination is another technique that is particularly useful for pagination through large datasets efficiently. It minimizes the load on the database by only querying relevant rows beyond the cursor position. Instead of skipping fixed number of records, which can be really costly, we use a unique identifier (often a primary key like id) to mark our position in the dataset. This way we don't have to fetch the data we don't need. For example: If I want the 4th page and I know what the last id on the 3rd page is, I can simply fetch the records after that. This becomes far more efficient that skipping data of the first three pages to get that 4th page.

Implementation

// app/routes/posts.jsx
import { json, useLoaderData } from 'remix';
import {prisma} from '~/.server/prisma/;
import { useSearchParams } from 'react-router-dom';

export const shouldRevalidate:ShouldRevalidateFunction = ({formAction, nextUrl, currentUrl, defaultShouldRevalidate}:ShouldRevalidateFunctionArgs)=>{
  if(!formAction && currentUrl.pathname === nextUrl.pathname) return false;
  return defaultShouldRevalidate;
}

export const loader = async ({ request }) => {
  const url = new URL(request.url);
  const cursor = url.searchParams.get('cursor');
  const pageSize = 10;

  const posts = await prisma.post.findMany({
    take: pageSize,
    skip: cursor ? 1 : 0, // Skip the cursor itself if present
    cursor: cursor ? { id: parseInt(cursor, 10) } : undefined,
    orderBy: { id: 'asc' },
  });

  // Get the next cursor
  const nextCursor = posts.length === pageSize ? posts[posts.length - 1].id : null;

  return json({ posts, nextCursor, pageSize });
};

export default function Posts() {
  const { posts, nextCursor } = useLoaderData();
  const [searchParams, setSearchParams] = useSearchParams();

  const handleLoadMore = () => {
    searchParams.set('cursor', nextCursor);
    setSearchParams(searchParams);
  };

  return (
    <div>
      {posts.map(post => (
        <div key={post.id}>
          <h2>{post.title}</h2>
          <p>{post.content}</p>
        </div>
      ))}
      {nextCursor && (
        <button onClick={handleLoadMore}>Load More</button>
      )}
    </div>
  );
}

In our loader function, we pass the cursor to our query parameters and to tell our database to where to start to get the data.

Advantages and Disadvantages

Offset-Based Pagination

Advantages:

• Simple to implement and understand.
• Works well for small datasets

Disadvantages:

• Performance can degrade for large datasets because the database has to skip many rows.
• Susceptible to issues when rows are inserted or deleted while paginating.

Cursor-Based Pagination

Advantages:

• More efficient for large datasets as it avoids the need to skip a large number of rows
• Handles inserts and deletes more gracefully without missing or duplicating records.

Disadvantages:

• Page jumping may be harder to implement which is sometimes a necessity.

Conclusion

This way, we can implement offset and cursor-based pagination with Remix and Prisma with both approaches having its advantages and disadvantages. We can use any of these approaches for pagination based on our requirements, or maybe combine them both where we use cursor-based pagination where suitable and offset based pagination where page jumping is required. By strategically choosing between these methods, we can optimize the performance and user experience of our application.

Thank you for reading this article. See you in the next one.