Pagination

When your database has many records, returning all of them at once can be slow and use too much memory. Pagination lets you return a few records at a time.

The frontend will send query parameters to tell us which “page” they want:

• page — which page number (1, 2, 3…).
• limit — how many users per page (5, 10, 20…).

For example: /users?page=2&limit=5 means “give me page 2, with 5 users per page” (users 6–10).

Converting Page/Limit to SQL

To implement pagination, convert the page and limit into SQL commands:

• LIMIT — how many rows to return.
• OFFSET — how many rows to skip.

The maths

• If page=1, limit=5 → show users 1–5 → LIMIT 5 OFFSET 0 (skip 0, take 5).
• If page=2, limit=5 → show users 6–10 → LIMIT 5 OFFSET 5 (skip 5, take 5).
• If page=3, limit=5 → show users 11–15 → LIMIT 5 OFFSET 10 (skip 10, take 5).

Formula: offset = (page - 1) * limit

Pagination Endpoint

Update your “Get all users” endpoint to support pagination.

Key concepts:

• Default values: If the frontend doesn’t send page or limit, use defaults (page 1, limit 10).
• Multiple placeholders: This query uses two placeholders for LIMIT and OFFSET.
• Parameter order: The array [limit, offset] matches the SQL order LIMIT ? OFFSET ?.

Example (Express + MySQL pool — TypeScript):

// Get users with pagination: /users?page=1&limit=5
app.get("/users", async (req, res) => {
  try {
    // Get pagination parameters with defaults if not provided
    const page = Number(req.query.page) || 1;
    const limit = Number(req.query.limit) || 10;

    // Calculate offset
    const offset = (page - 1) * limit;

    const [rows] = await pool.execute(
      "SELECT id, username, email FROM users LIMIT ? OFFSET ?",
      [limit.toString(), offset.toString()]
    );
    const users = rows as User[]; // Type the paginated results

    res.json(users);
  } catch (error) {
    console.error("Database error:", error);
    res.status(500).json({
      error: "Failed to fetch users",
    });
  }
});

How it works

• /users?page=1&limit=5 gets the first 5 users (OFFSET 0, LIMIT 5).
• /users?page=2&limit=5 gets users 6–10 (OFFSET 5, LIMIT 5).
• Uses LIMIT + new OFFSET concept.
• Default values: page 1, limit 10.
• /users — First 10 users (default).
• /users?page=2 — Users 11–20.
• /users?limit=5 — First 5 users.
• /users?page=3&limit=5 — Users 11–15.

Repo link