Pagination

1. Offset and Limit Pagination

For medium / small databases.

offset: Determines the starting point.
limit: Determines the number of items to get per call.

server.ts


import { eq, asc } from 'drizzle-orm';
 
// Only these limit numbers are accepted to prevent large data calls
const limitNumberLs = [
    10, 20, 50, 100
]
 
// /cards?offset=100&limit=20
app.get('/cards', (req: Request, res: Response) => {
 
    // Assume offset refers to the data ID
    const offset: number = +req.query.offset || 0;
    let limit: number    = +req.query.limit  || 10;
 
    // Limit the max limit number
    if(!limitNumberLs.includes(limit)){
        limit = 10
    }
 
    // Assume Using "drizzle"
    const data = await db.query.card.findMany({
	    limit: limit,
        offset: offset,
        orderBy: [asc(card.id)]
    });
 
    res.json(data);
});

2. Cursor-based pagination

For large databases, query data faster and prevent slow offsets.

cursor: Refers to the data ID.

server.ts


import { gt, asc } from 'drizzle-orm';
 
// Only these limit numbers are accepted to prevent large data calls
const limitNumberLs = [
    10, 20, 50, 100
]
 
// /cards?cursor=778&limit=20
app.get('/cards', (req: Request, res: Response) => {
 
    // Assume cursor refers to the data ID
    const cursor: number = +req.query.cursor || 0;
    let limit: number    = +req.query.limit  || 10;
 
    // Limit the max limit number
    if(!limitNumberLs.includes(limit)){
        limit = 10
    }
 
    // Assume Using "drizzle"
    const data = await db.query.card.findMany({
        where: gt(card.id, cursor),
	    limit: limit,
        orderBy: [asc(card.id)]
    });
 
    res.json(data);
});

3. Page-Based Pagination

For medium / small databases.

page: Determines the starting page.
limit / size: Determines the number of items to get per call.

server.ts


import { eq, asc } from 'drizzle-orm';
 
// Only these limit numbers are accepted to prevent large data calls
const limitNumberLs = [
    10, 20, 50, 100
]
 
// /cards?page=1&limit=20
app.get('/cards', (req: Request, res: Response) => {
 
    // Assume offset refers to the data ID
    const page: number = +req.query.page  || 1;
    let limit: number  = +req.query.limit || 10;
 
    // Limit the max limit number
    if(!limitNumberLs.includes(limit)){
        limit = 10
    }
 
    // Assume Using "drizzle"
    const data = await db.query.card.findMany({
	    limit: limit,
        offset: page * limit,
        orderBy: [asc(card.id)]
    });
 
    res.json(data);
});

4. Page Token Pagination

Add your params as an encrypted token from { id: 10 } to MTAwCg== (e.g. Base64)

nextPageToken: Determines the token for the next page.

Let’s implement the token with Cursor-based pagination.

server.ts


import { encode, decode } from 'js-base64';
import { eq, asc } from 'drizzle-orm';
 
// Let's say `eyBpZDogMTE0IH0=` = `{ id: 114 }`
// /cards?nextPage=eyBpZDogMTE0IH0=
app.get('/cards', (req: Request, res: Response) => {
 
    const nextPage: string = req.query.nextPage || "";
    let cursor = 0;
 
    try {
        if(nextPage !== ""){
            const decodedToken = JSON.parse(nextPage);
            !!decodedToken["id"] && (cursor = decodedToken["id"])
        }
    } catch (error) {
        cursor = 0
    }
 
    const limit = 10
 
    // Assume Using "drizzle"
    const data = await db.query.card.findMany({
        where: gt(card.id, cursor),
	    limit: limit,
        orderBy: [asc(card.id)]
    });
 
    res.json({
        data,
        previousPageToken: nextPage,
        nextPageToken: encode(JSON.stringify({ id: cursor + limit }))
    });
});

In Advanced, you may include more data in your token (e.g. limit, conditions) for filtering.

Samples:


{
    "id": 56,
    "limit": 50
}