You just need 2 patterns of implementing pagination as dev’s while starting out:

1. Off-Set based pagination — (recommended if small data)

2. Cursor based pagination — (recommended if you have large data)

Offset-based pagination

In offset-based pagination, we use SQL’s LIMIT and OFFSET to fetch only a specific chunk of records.

page_size = how many items to show per page
page_number = which page to fetch (e.g., if page_size = 10, then page 3 returns items 21–30)

The client (frontend) sends these values to the backend, like:

/posts?page_size=3&page_number=2

The backend reads those parameters and runs a query such as:

SELECT * FROM posts LIMIT 3 OFFSET 3;

Advantage:

• It allows the clients to view the total number of pages.

• It allows clients to jump to a specific page by passing the page number.

Disadvantage:

• The database looks up for (offset + limit) number of records before discarding the unwanted ones and returning the remaining.
  Hence, the query time increases drastically as the offset increase

• If an item in a previous page is deleted, data will shift forward, causing some results to be skipped.

Cursor Based Pagination

In cursor-based pagination, instead of using page numbers, we use a cursor, a unique pointer that refers to a specific record in the database.

The client sends a cursor (like a record ID) and a page_size to fetch data that comes after or before that record.

GET /api/users?cursor=12345&page_size=10

SELECT * FROM users
WHERE id <= 12345
ORDER BY id DESC
LIMIT 11;  -- (page_size + 1)

The backend fetches page_size + 1 records.
If there’s an extra record, it means there’s another page, and that extra record’s ID becomes the next_cursor, we don’t return the extra record (11th record) but we verify that another page/record is still available, store the next_cursor value and hasNextPage named boolean value.

What do we return?

// json return
{
    data: [{...data}],
    next_cursor: (11th record id) or null if no another record
    hasNextPage: True/ False (according to condition),
}

Advantage:

• Since we are fetching from a stable reference point, the addition or deletion of record will not affect the pagination window.

• The database jumps directly to the record without iterating through the unwanted data. Hence, making it more efficient. ——most important——

  Disadvantage:

• The cursor pagination doesn’t allow clients to jump to a specific page.

• Limited sort features. If the requirement is to sort based on a non-unique column (E.g. first name), it will be challenging to implement using cursor pagination. Concatenating multiple columns to get a unique key leads to slower time complexity.

  Encoded Cursor

• The encoded cursor suggests returning an encoded base64 string regardless of the underlying pagination solution.

  When using offset pagination, we encode the page_number and total_page into a base64 string and return it as a cursor to the clients.

    response: {
        // "page=3|offset=20|total_pages=30"
        next_cursor: "dcjadfaXMDdQTQ"
    }
  

  This allows the server to implement different underlying pagination solutions while providing a consistent interface to the API consumers.

    request: {
        cursor: "dcjadfaXMDdQTQ",
        page_size: 10
    }
  

Code Implementation in NextJS:

Implementing Cursor-Based Pagination in Next.js + Drizzle ORM

Pagination helps load data efficiently without fetching everything at once.
While offset-based pagination (using page and limit) is simple, it can become inefficient with large datasets.

That’s where cursor-based pagination comes in — it’s faster, more stable, and widely used in APIs like Twitter or GitHub.

What Is Cursor-Based Pagination?

Instead of asking:

“Give me page 2”

We say:

“Give me the next 3 items after this record (cursor).”

Each response includes:

• data → list of results

• next_cursor → the next record’s ID

• hasNextPage → whether more data exists

This makes it more reliable when data is constantly changing.

Backend — Next.js API Route + Drizzle ORM

Let’s start with the backend logic.

We’ll handle both initial and subsequent requests from the frontend.
When no cursor is provided → fetch the first batch.
When cursor exists → fetch results after that cursor.

import { posts } from "@/db/schema";
import db from "@/lib/drizzle";
import { desc, lte } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";

export async function GET(req: NextRequest) {
  const { searchParams } = new URL(req.url);

  const cursor = Number(searchParams.get("cursor")) || null;
  const limit = Number(searchParams.get("page_size")) || 3;

  // Fetch one extra to check if more results exist
  const effectiveLimit = limit + 1;

  let hasNextPage = false;
  let next_cursor = null;

  const data = await db
    .select()
    .from(posts)
    .where(cursor ? lte(posts.id, cursor) : undefined)
    .orderBy(desc(posts.id))
    .limit(effectiveLimit);

  // If we got more data than limit, that means there’s a next page
  if (data.length > limit) {
    hasNextPage = true;
    next_cursor = data[data.length - 1].id;
    data.pop(); // remove the extra fetched item
  }

  return NextResponse.json({
    data,
    next_cursor,
    hasNextPage,
    message: "Fetched successfully",
  });
}

Backend logic summary:

• Fetch (limit + 1) rows.

• If extra data exists → hasNextPage = true.

• Remove the extra record and send the remaining ones.

• Return next_cursor for the next request.

Frontend — React (Next.js Client Component)

The frontend keeps track of:

• data

• cursor

• hasNextPage

• loading state

Each time we fetch, we append new results to the existing list.

"use client";

import axios from "axios";
import { useEffect, useState } from "react";

interface Post {
  id: number;
  title: string;
  description: string;
}

export default function Page() {
  const [data, setData] = useState<Post[]>([]);
  const [cursor, setCursor] = useState<number | null>(null);
  const [hasNextPage, setHasNextPage] = useState(false);
  const [loading, setLoading] = useState(false);

  async function fetchPosts(cursor: number | null, page_size = 3) {
    try {
      setLoading(true);
      const res = await axios.get(`/api/fetchPosts?cursor=${cursor}&page_size=${page_size}`);
      setData((prev) => [...prev, ...res.data.data]);
      setCursor(res.data.next_cursor);
      setHasNextPage(res.data.hasNextPage);
    } finally {
      setLoading(false);
    }
  }

  useEffect(() => {
    fetchPosts(null);
  }, []);

  return (
    <div>
      {data.map((post) => (
        <div key={post.id} className="bg-gray-100 p-3 mb-2 rounded-md">
          <h2 className="font-semibold text-lg">{post.title}</h2>
          <p className="text-sm text-gray-700">{post.description}</p>
        </div>
      ))}

      {hasNextPage && (
        <button
          disabled={loading}
          onClick={() => fetchPosts(cursor)}
          className="bg-blue-500 text-white px-4 py-2 rounded-md"
        >
          {loading ? "Loading..." : "Load More"}
        </button>
      )}
    </div>
  );
}

Frontend logic summary:

• On first render → fetch initial data.

• On “Load More” click → send the last cursor.

• Append new results to the state.

• Hide the button if there are no more pages.

Why Cursor Pagination Is Better

Cursor pagination is ideal for real-time apps like feeds, dashboards, or infinite scrolls.

Final Thoughts

Cursor-based pagination gives you both performance and consistency.
It ensures you always fetch the right data even when your dataset changes frequently.

If you’re building a product with constantly updating data — like posts, comments, or stock market lists —
this approach will scale much better than traditional offset-based pagination.