In partnership with
Pagination is a technique that breaks down large datasets into smaller chunks called pages.
Learning pagination makes your API more efficient.
And, BIG bonus…
makes you a better developer.
Today, you will learn how to design an API with pagination and how to use one.
There are two main types of pagination:
offset-based
cursor-based
Let’s start with the simpler one.
Offset-Based Pagination
Using YouTube as an example:
In each “page”, we want to display 20 videos.
In offset-based pagination:
limit determines the page size
offset determines the page number
Let’s get videos for page one.
In the backend, the SQL query probably looks something like this:
This returns 20 videos with data like title, thumbnail, video URL, etc.
To query page two, add an offset:
-- Page 2
SELECT
*
FROM
VideoPool
ORDER BY
uploaded_at DESC, id DESC
LIMIT
20
OFFSET
20; --hereThis returns the next 20 videos.
This is how the math works:
To use that API in the frontend:
Send a request with the page size and page number you need.
For example, to get the third page with 20 items on each page:
This approach is simple to understand and easy to implement.
But there are downsides:
The larger the offset, the slower the query.
If database rows are added/removed frequently, offset-based pagination will break.
Cursor-based pagination solves these problems.
Cursor-Based Pagination
Cursor-based pagination also uses LIMIT to determine the page size.
BUT, instead of a page number, it uses a cursor to track your position in the table.
It can only go to the next or the previous page.
Let's query page one again.
In the backend, the first query looks identical:
SELECT *
FROM VideoPool
ORDER BY uploaded_at DESC, id DESC
LIMIT 20;To query the next page, however, we need something new called a cursor (or a key)
A unique pointer to the last item on the current page.
What’s unique and can identify a row in the database?
The ID. But, in this case, ID is not enough because we are sorting by both ID and uploaded_at. So let’s use a combination of both to create a cursor.
Why do we need the cursor?
With the cursor, we know what the last item on page one is, plus, the table is sorted, right?
So, we can get page two items with a query that says, “give me 20 items after the last item on page one.”
-- Page 2
SELECT
*
FROM
VideoPool
WHERE
(
(uploaded_at < '2077-03-30 12:15:00')
OR
(uploaded_at = '2077-03-30 12:15:00' AND id < 49)
)
ORDER BY
uploaded_at DESC, id DESC
LIMIT
20;The entire flow works like this:
Frontend gets page one
Backend reads the database and returns the data with a cursor.
The cursor is usually encoded.
Frontend gets page two with the cursor
Backend decodes the cursor, returns page two items, and sends back the new cursor.
Why bother doing this?
It's faster than using offset because we can skip to items we want (assuming the cursor column is indexed).
It's more consistent. Even if the table changes, with the key, we can still accurately get the next page items.
Of course, there are downsides.
You can't skip directly to the page you want. You only really know what the next/previous page is.
Since you don’t know how many pages are left, you can’t show page numbers.
This is more difficult to implement.
That’s almost everything you need to know about pagination.
Fee from Anime Coders
