⇦ Back

WDX-180

Web Development X

Pagination

Managing Large Amounts of Data

Every application works perfectly with 5 records.

The real test begins with 5,000.

Our CMS currently displays every product:

  SELECT *
  FROM products

This works until:

Today we solve that problem with pagination.

Learning Objectives

By the end of this lesson, students will be able to:

Part 1 — The Problem

Imagine:

  Products Table

  1
  2
  3
  ...
  9999

Current query:

  SELECT *
  FROM products

Returns:

  9999 rows

Bad idea.

Problems Without Pagination

Performance

Database:

  Returns 9999 records

Server:

  Processes 9999 records

Browser:

  Renders 9999 rows

Everyone loses.

User Experience

Imagine Amazon showing:

  1,000,000 products

on one page.

Users would scroll until retirement.

What Is Pagination?

Pagination divides data into pages.

Example:

  Page 1

  1-10

  Page 2

  11-20

  Page 3

  21-30

Visualization:

  flowchart LR

  A[Database]

  A --> B[Page 1]

  A --> C[Page 2]

  A --> D[Page 3]

Part 2 — SQL LIMIT

SQLite supports:

  LIMIT

Example:

  SELECT *
  FROM products
  LIMIT 10;

Result:

  Only 10 records

OFFSET

OFFSET tells SQLite:

  Skip rows

Example:

  SELECT *
  FROM products
  LIMIT 10
  OFFSET 20;

Meaning:

  Skip first 20 rows

  Return next 10 rows

Visualization:

  Rows

  1
  2
  3
  ...
  20 ← skipped

  21 ← start
  22
  23
  ...
  30

Part 3 — Understanding Page Calculations

Assume:

  10 items per page

Page 1

  OFFSET = 0

Page 2

  OFFSET = 10

Page 3

  OFFSET = 20

Formula:

  (page - 1) * limit

Example:

  (3 - 1) * 10

  20

Part 4 — Reading Query Parameters

URL:

  /products?page=3

Express:

  const page = Number(req.query.page) || 1;

Result:

  page === 3

Part 5 — Building Pagination

First, let’s update our db/setup.js seeding script to add 30 more dummy products and be able to test our pagination:

  // Let's create 30 rows to test pagination:
  for (let i = 1; i <= 30; i++) {
    db.exec(`
      INSERT INTO products (name, description, price)
      VALUES
      ('Product ${i}', 'Description for product ${i}', ${(i * 10).toFixed(2)});
      `);
  }

  console.log('Database created successfully.');

Route:

  router.get('/', (req, res) => {

      const page = Number(req.query.page) || 1;
      const limit = 10;
      const offset = (page - 1) * limit;

      const stmt = db.prepare(`
          SELECT *
          FROM products
          ORDER BY id DESC
          LIMIT ?
          OFFSET ?
      `);

      const products = stmt.all(limit, offset);

      res.render('products/list',
          {
            // ...
            products,
            page
          }
      );

  });

What Happens?

Page 1:

  LIMIT 10
  OFFSET 0

Page 2:

  LIMIT 10
  OFFSET 10

Page 3:

  LIMIT 10
  OFFSET 20

Part 6 — Total Record Count

Pagination requires knowing:

  How many records exist?

Query:

  SELECT COUNT(*) AS total
  FROM products

Repository:

  function count() {

      const stmt = db.prepare(`
          SELECT COUNT(*) AS total
          FROM products
      `);

      return stmt.get().total;

  }

Example:

  150

products.

Part 7 — Total Pages

Formula:

  totalRecords / pageSize

Not quite.

Problem:

  151 / 10

  15.1 pages

Impossible.

Use:

  Math.ceil(totalRecords / limit)

Example:

  Math.ceil(151 / 10)

  16

Perfect.

Part 8 — Generating Page Numbers

Route:

  // routes/products.js
  const productRepository = require("../db/productRepository");
  // ...
  router.get("/", (req,res)=>{

    const totalRecords = productRepository.count();
    const totalPages = Math.ceil(totalRecords / limit);
    // ...
  
    // Pass `totalPages` to the product list template:
    res.render('products/list',
        {
          // ...
          products,
          page,
          totalPages
        }
    );

  });

Part 9 — Rendering Pagination Links

View: (/views/products/list.ejs)

  <nav>
    <% for ( let i = 1; i <= totalPages; i++ ) { %>
      <a href="/products?page=<%= i %>">
        <%= i %>
      </a>
    <% } %>
  </nav>

Result:

  1 2 3 4 5 6 7

Highlight Current Page

  <% if(i === page) { %>

  <strong>
      <%= i %>
  </strong>

  <% } else { %>

  <a href="/products?page=<%= i %>">
      <%= i %>
  </a>

  <% } %>

Result:

  1 2 [3] 4 5 6

Part 10 — Previous and Next Links

Update: views/products/list.ejs:

Previous:

  <% if (page > 1) { %>
    <a href="/products?page=<%= page - 1 %>">Previous</a>
  <% } %>

Next:

  <% if (page < totalPages) { %>
    <a href="/products?page=<%= page + 1 %>">Next</a>
  <% } %>

Result:

  Previous

  1 2 3 4 5

  Next

Part 11 — Validation

Bad URL:

  /products?page=-999

Bad URL:

  /products?page=banana

Validation:

  let page = Number(req.query.page);

  if (
      !Number.isInteger(page)
      || page < 1
  ) {
      page = 1;
  }

Page Too Large

Example:

  /products?page=999999

Fix:

  if (page > totalPages) {
      page = totalPages;
  }

Part 12 — Extracting Repository Functions

Repository:

  function findPage( page, limit ) {

      const offset = (page - 1) * limit;
      const stmt = db.prepare(`
          SELECT *
          FROM products
          ORDER BY id DESC
          LIMIT ?
          OFFSET ?
      `);

      return stmt.all(
          limit,
          offset
      );

  }

Route:

  const products = productRepository.findPage( page, limit );

Cleaner.

Part 13 — Offset Pagination Limitations

Offset pagination is simple.

But it has drawbacks.

Imagine:

  1 million rows

Page:

  100,000

requires:

  OFFSET 999990

SQLite must still skip nearly a million rows.

Expensive.

Cursor Pagination

Large applications often use:

  Cursor Pagination

instead.

Example:

  /products?after=120

SQL:

  SELECT *
  FROM products
  WHERE id > 120
  LIMIT 10

Used by:

We’ll stick with OFFSET pagination for now because it’s easier to learn.

Challenge: Try to implement the Cursor Pagination algorithm.

Part 14 — Improving User Experience

Show:

  <p>
  Showing page
  <%= page %>
  of
  <%= totalPages %>
  </p>

Example:

  Showing page 3 of 15

Show totals:

  <p>
  Total Products:
  <%= totalRecords %>
  </p>

Users appreciate context.

Common Beginner Mistakes

Forgetting ORDER BY

❌ Bad:

  SELECT *
  FROM products
  LIMIT 10

Results may appear in unexpected order.

✅ Always:

  ORDER BY id DESC

or another explicit column.

Trusting Query Parameters

Bad:

  const page = req.query.page;

Validate everything.

Using OFFSET Without LIMIT

❌ Bad:

  OFFSET 10

Always pair with LIMIT.

Calculating Total Pages Incorrectly

❌ Bad:

  total / limit

✅ Use:

  Math.ceil(...)

Bonus Challenge

Allow users to choose:

  10 per page
  25 per page
  50 per page
  100 per page

Example:

  /products?page=2&limit=25

Validate the limit against an allowed list:

  const allowed = [
      10,
      25,
      50,
      100
  ];

Never trust arbitrary limits from users.

Otherwise someone eventually discovers:

  /products?limit=999999999

and your server begins reconsidering its life choices.

Key Takeaways

Today you learned:

Most CRUD applications eventually need pagination. It’s one of those features that seems trivial until you discover your database contains 500,000 rows and your browser tab has become a space heater.

⚠️ A large part of the content of this module was created using Generative AI (ChatGPT). The synthetic (AI-generated) content was reviewed and curated by Kostas Minaidis.

Project maintained by in-tech-gration Hosted on GitHub Pages — Theme by mattgraham