Create a pagination API with Express
Tags: pagination, express
Published: 2024-06-27 04:14:00

---
title: Create a pagination API with Express
published: true
date: 2024-06-24 08:12:00 UTC
tags: Pagination,Express
canonical_url: https://blog.stackpuz.com/create-a-pagination-api-with-express/
---

![Pagination API with Express](https://blog.stackpuz.com/media/posts/5/cover.jpg)

Splitting larger content into distinct pages is known as pagination. This approach significantly enhances the user experience and speeds up the loading of web pages. This example will demonstrate how to create a pagination API using Express and use MySQL as a database.

## Prerequisites 

- Node.js
- MySQL

## Setup project 

Setting up the Node.js project dependencies.

```
npm install express mysql2
```

Create a testing database named "example" and run the [database.sql](https://github.com/StackPuz/Example-Pagination-Express/blob/main/database.sql) file to import the table and data.

## Project structure 

```
├─ config.js
├─ index.js
└─ public
   └─ index.html
```

## Project files 

### config.js 

This file contains the database connection information.

```javascript
module.exports = {
  host: 'localhost',
  database: 'example',
  user: 'root',
  password: ''
}
```

### index.js 

This file is the main entry point for the Express application. It will create and setup the Express server. Because this API only has one routing URL, we will include it and the handler function in this file.

```javascript
const express = require('express')
const mysql = require('mysql')
const config = require('./config')

let con = mysql.createConnection({
  host: config.host,
  database: config.database,
  user: config.user,
  password: config.password
})

let app = express()
app.use(express.static('public'))
app.get('/api/products', (req, res) => {
  let page = req.query.page || 1
  let size = parseInt(req.query.size) || 10
  let order = mysql.raw(req.query.order || 'id')
  let direction = mysql.raw(req.query.direction || 'asc')
  let offset = (page - 1) * size
  con.query('select * from product order by ? ? limit ? offset ?', [order, direction, size, offset], (error, result) => {
    res.send(result)
  })
})
app.listen(8000)
```

- `mysql.createConnection()` will create the database connection.  
- `express.static('public')` will serve the static resource inside the public folder. (We used to serve index.html as a default page)  
- We utilize the query string to get `page, size, order, direction` information to create the paginated data by using the `limit` and `offset` of the SQL query.  

### index.html 

Instead of entering the URL manually to test our API, we used this file to create links for easier testing.

```html
<!DOCTYPE html>
<head>
</head>
<body>
    <ul>
        <li><a target="_blank" href="/api/products">Default</a></li>
        <li><a target="_blank" href="/api/products?page=2">Page 2</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25">Page 2 and Size 25</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name">Page 2 and Size 25 and Order by name</a></li>
        <li><a target="_blank" href="/api/products?page=2&size=25&order=name&direction=desc">Page 2 and Size 25 and Order by name descending</a></li>
    </ul>
</body>
</html>
```

## Run project 

```
node index.js
```

Open the web browser and goto http://localhost:8000

You will find this test page.

![test page](https://blog.stackpuz.com/media/posts/5/index.PNG)

## Testing 

### Testing without any parameters 

Click the "Default" link, and it will open the URL `http://localhost:8000/api/products`

![default test](https://blog.stackpuz.com/media/posts/5/default.PNG)

The API will return paginated data with default parameters (page = 1 and size = 10).

### Page index test 

Click the "Page 2" link, and it will open the URL `http://localhost:8000/api/products?page=2`

![page index test](https://blog.stackpuz.com/media/posts/5/page-test-3.PNG)

The API will return paginated data on the second page, starting with product id 11

### Page size test 

Click the "Page 2 and Size 25" link, and it will open the URL `http://localhost:8000/api/products?page=2&size=25`

![page size test](https://blog.stackpuz.com/media/posts/5/size-test.PNG)

The API will return paginated data on the second page by starting with product id 26 because the page size is 25.

### Order test 

Click the "Page 2 and Size 25 and Order by name" link, and it will open the URL `http://localhost:8000/api/products?page=2&size=25&order=name`

![order test](https://blog.stackpuz.com/media/posts/5/order-test.PNG)

The API will return paginated data on the second page, but the product order is based on the product name.

### Descending order test 

Click the "Page 2 and Size 25 and Order by name descending" link, and it will open the URL `http://localhost:8000/api/products?page=2&size=25&order=name&direction=desc`

![descending order test](https://blog.stackpuz.com/media/posts/5/order-desc-test.PNG)

The API will return paginated data on the second page, but the product order is based on the product name in descending order.

## Conclusion 

In this article, you have learned how to create and setup the Express server in order to implement the pagination API. The pagination approach will enhance the user experience and speed up your Express API. If you like the article, please share it.

Source code: [https://github.com/stackpuz/Example-Pagination-Express](https://github.com/stackpuz/Example-Pagination-Express)

Create a CRUD Web App in Minutes: [https://stackpuz.com](https://stackpuz.com)