# Pagination Concepts Made Easy with SQL Query and React/Next JS
What is Pagination? (skip if you already know)
Pagination means dividing the contents/document into discrete pages. It is a user interface design pattern.
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)
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1762582598152/a0216f39-f97f-4a17-a723-2b5bbf9e121c.webp align="center")
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:
```sql
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
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1762581923365/2a01f898-6f90-44a0-93f5-5a6a0caca968.webp align="center")
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`
```sql
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?**
```javascript
// 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.
```javascript
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.
```javascript
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.
```javascript
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.
```javascript
"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([]);
const [cursor, setCursor] = useState(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 (
{data.map((post) => (
{post.title}
{post.description}
))}
{hasNextPage && (
)}
);
}
```
**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
| Feature | Offset Pagination | Cursor Pagination |
| --- | --- | --- |
| Speed | Slower for large data | Faster and consistent |
| Data stability | Can skip or duplicate data | Reliable order |
| Implementation | Easier | Slightly complex |
| Best for | Static data | Dynamic feeds (e.g. social media, posts) |
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.