Node.js Express Pagination with PostgreSQL example

In previous post, we’ve know how to build Node.js Rest CRUD Apis. This tutorial will show you how to make Node.js Express Pagination with PostgreSQL using Sequelize.

Related Post:
Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize
– Dockerize: Docker Compose Nodejs and Postgres example

More Practice:
Node.js JWT Authentication with PostgreSQL example

Clients for this Server:
React with Material-UI / React with react-table v7
Angular 8 / Angular 10 / Angular 11 / Angular 12 / Angular 13 / Angular 14
Vue with Bootstrap / Vuetify


Server side Pagination

One of the most important things to make a website friendly is the response time, and pagination comes for this reason. For example, this bezkoder.com website has hundreds of tutorials, and we don’t want to see all of them at once. Pagination means displaying a small number of all, by a page.

If there are only few pages, we can fetch all items and paginate on the client side. It gives us benefit of faster subsequent page loads.

But in case we have large number of pages, make paging on client side will make our client app download all the data at first which might not be needed. So let the server do the work.

Server side pagination is better for:

  • Large data set
  • Faster initial page load
  • Accessibility for those not running JavaScript
  • Complex view business logic

Node.js Pagination with PostgreSQL & Express overview

Assume that we have tutorials table in database like this:

node-js-pagination-postgresql-express-example-database-table

Node.js Express Rest API Server will exports API for pagination (with/without filter), here are some url samples:

  • /api/tutorials?page=1&size=5
  • /api/tutorials?size=5: using default value for page
  • /api/tutorials?title=data&page=1&size=3: pagination & filter by title containing ‘data’
  • /api/tutorials/published?page=2: pagination & filter by ‘published’ status

This is structure of the result that we want to get from the APIs:

{
    "totalItems": 32,
    "tutorials": [...],
    "totalPages": 8,
    "currentPage": 1
}

Read Tutorials with default page index (0) and page size (3):

node-js-pagination-postgresql-express-example-default-paging

Indicate page index = 10 but not specify size (default: 3) for total 32 items:

  • page_0: 3 items
  • page_9: 3 items
  • page_10: 2 items

node-js-pagination-postgresql-express-example-default-page-size

Indicate size = 4 but not specify page index (default: 0):

node-js-pagination-postgresql-express-example-page-size

For page index = 1 and page size = 4 (in total 32 items):

node-js-pagination-postgresql-express-example-paging-with-size

Pagination and filter by title that contains a string:

node-js-pagination-postgresql-express-example-paging-filter

Pagination and filter by published status:

node-js-pagination-postgresql-express-example-paging-status

Node.js Sequelize for Pagination

To help us deal with this situation, Sequelize provides way to implement pagination with offset and limit properties in the query object that we pass to query methods.

  • offset: quantity of items to skip
  • limit: quantity of items to fetch

For example, there are total 8 items.
{ offset: 3 }: skip first 3 items, fetch 5 remaining items.
{ limit: 2 }: fetch first 2 items.
{ offset: 3, limit: 2 }: skip first 3 items, fetch 4th and 5th items.

We’re gonna use findAll() and findAndCountAll() methods which accept the pagination information above for paging.

Sequelize findAll

This is how we use findAll() with limit and offset properties:

model.findAll({
  limit,
  offset,
  where: {}, // conditions
});

Now try it to query our tutorials data:

model.findAll({
  limit: 2,
  offset: 3,
  where: {}, // conditions
});

The result:

[
    {
        "id": 4,
        "title": "bezkoder Tut#4",
        "description": "Tut#4 Description",
        "published": false,
        "createdAt": "2020-11-27T09:40:19.568Z",
        "updatedAt": "2020-11-27T09:40:19.568Z"
    },
    {
        "id": 5,
        "title": "bezkoder Tut#5",
        "description": "Tut#5 Description",
        "published": true,
        "createdAt": "2020-11-27T09:40:31.305Z",
        "updatedAt": "2020-11-27T09:40:31.305Z"
    }
]

With where condition:

model.findAll({
  limit: 2,
  offset: 1,
  where: { title: { [Op.like]: `%agi%` } }, // conditions
});

Look back to the table, there are several items which title contain ‘agi’: 15,18,19,23,25,26.
The result:

[
    {
        "id": 18,
        "title": "bezkoder Paging Tut#18",
        "description": "Tut#18 Description",
        "published": true,
        "createdAt": "2020-11-27T09:57:28.812Z",
        "updatedAt": "2020-11-27T09:57:28.812Z"
    },
    {
        "id": 19,
        "title": "bezkoder Paging Tut#19",
        "description": "Tut#19 Description",
        "published": true,
        "createdAt": "2020-11-27T09:57:31.191Z",
        "updatedAt": "2020-11-27T09:57:31.191Z"
    }
]

You can see that the response skips item with id=15 at position 0 because we set the offset=1.
The information seems not enough when working with large data. We don’t know the total number of items that match the query, the result also shows a piece for which data we need.

That’s the reason why findAndCountAll() comes to us.

Sequelize findAndCountAll

findAndCountAll() is more useful for paging than findAll(). It is because it also gets the total number of rows matching the query.

model.findAndCountAll({
  limit,
  offset,
  where: {}, // conditions
});

Query our tutorials data:

model.findAndCountAll({
  limit: 2,
  offset: 3,
  where: {}, // conditions
});

Instead of getting only a JSON array, we have count and rows in the result:

{
    "count": 32,
    "rows": [
        {
            "id": 4,
            "title": "bezkoder Tut#4",
            "description": "Tut#4 Description",
            "published": false,
            "createdAt": "2020-11-27T09:40:19.568Z",
            "updatedAt": "2020-11-27T09:40:19.568Z"
        },
        {
            "id": 5,
            "title": "bezkoder Tut#5",
            "description": "Tut#5 Description",
            "published": true,
            "createdAt": "2020-11-27T09:40:31.305Z",
            "updatedAt": "2020-11-27T09:40:31.305Z"
        }
    ]
}

With where condition:

model.findAndCountAll({
  limit: 2,
  offset: 1,
  where: { title: { [Op.like]: `%agi%` } }, // conditions
});

The result:

{
    "count": 32,
    "rows": [
        {
            "id": 18,
            "title": "bezkoder Paging Tut#18",
            "description": "Tut#18 Description",
            "published": true,
            "createdAt": "2020-11-27T09:57:28.812Z",
            "updatedAt": "2020-11-27T09:57:28.812Z"
        },
        {
            "id": 19,
            "title": "bezkoder Paging Tut#19",
            "description": "Tut#19 Description",
            "published": true,
            "createdAt": "2020-11-27T09:57:31.191Z",
            "updatedAt": "2020-11-27T09:57:31.191Z"
        }
    ]
}

Setup Node.js Pagination with PostgreSQL App

We need to install necessary modules: express, sequelize, pg, pg-hstore.
Run the command:

npm install express sequelize pg pg-hstore --save

You can follow step by step, or get source code in this post:
Node.js Express & PostgreSQL: CRUD Rest APIs example with Sequelize

The Node.js Express Project contains structure that we only need to add some changes to make the pagination work well.

node-js-pagination-postgresql-express-example-project-structure

Or you can get the new Github source code at the end of this tutorial.

Configure PostgreSQL database & Sequelize

In the app folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "postgres",
  PASSWORD: "123",
  DB: "testdb",
  dialect: "postgres",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for PostgreSQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

  • max: maximum number of connection in pool
  • min: minimum number of connection in pool
  • idle: maximum time, in milliseconds, that a connection can be idle before being released
  • acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

We’re gonna initialize Sequelize in app/models folder that will contain model in the next step.

Now create app/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

Don’t forget to call sync() method in server.js:

...
const app = express();
app.use(...);

const db = require("./app/models");
db.sequelize.sync();

...

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:


db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Create Data Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in PostgreSQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them. Now you can easily use following methods with pagination:

  • get all Tutorials: findAll({ limit, offset })
  • find all Tutorials by title: findAll({ where: { title: ... }, limit, offset })
  • find and count all Tutorials: findAndCountAll({ limit, offset })
  • find and count all Tutorials by title: findAndCountAll({ where: { title: ... }, limit, offset })

In the Controller, we only use findAndCountAll({ where, limit, offset }) because it is enough for all we need.

Controller with Pagination

Earlier on the section above, we’ve known the specific properties in a query object that we pass to findAndCountAll() method: offset and limit. But the API receives page and size.

Here is way to calculate:

  • limit = size
  • offset = page * size

Notice that we start counting from page 0.

For example, if we want get page number 5 (page=5) and on each page there is 8 records (size=8), the calculations will look like this:

  • limit = size = 8
  • offset = page * size = 5 * 8 = 40

So on page 5, there are records from 40 to 47.

Generally, in the HTTP request URLs, paging parameters are optional. So if our Rest API supports pagination, we should provide default values to make paging work even when Client does not specify these parameters.

const getPagination = (page, size) => {
  const limit = size ? +size : 3;
  const offset = page ? page * limit : 0;

  return { limit, offset };
};

By default, 3 Tutorials will be fetched from database in page index 0.

The response data will contains count and rows:

{
    "count": 32,
    "rows": [
        {
            ...
        },
        {
            ...
        }
    ]
}

We need to get the result with the structure:

{
    "totalItems": 32,
    "tutorials": [...],
    "totalPages": 8,
    "currentPage": 1
}

So, let’s write the function to map default response to desired structure:

const getPagingData = (data, page, limit) => {
  const { count: totalItems, rows: tutorials } = data;
  const currentPage = page ? +page : 0;
  const totalPages = Math.ceil(totalItems / limit);

  return { totalItems, tutorials, totalPages, currentPage };
};

Now the code in tutorial.controller.js will look like this:

const db = require("../models");
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;

const getPagination = ...;
const getPagingData = ...;

// Retrieve all Tutorials from the database.
exports.findAll = (req, res) => {
  const { page, size, title } = req.query;
  var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;

  const { limit, offset } = getPagination(page, size);

  Tutorial.findAndCountAll({ where: condition, limit, offset })
    .then(data => {
      const response = getPagingData(data, page, limit);
      res.send(response);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

// find all published Tutorial
exports.findAllPublished = (req, res) => {
  const { page, size } = req.query;
  const { limit, offset } = getPagination(page, size);

  Tutorial.findAndCountAll({ where: { published: true }, limit, offset })
    .then(data => {
      const response = getPagingData(data, page, limit);
      res.send(response);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

// other CRUD functions

Create Node.js Express API layer

The final part is to use the Controller methods in tutorial.routes.js with Express Router.
There are 2 important endpoints: /api/tutorials/ and /api/tutorials/published.

module.exports = app => {
  const tutorials = require("../controllers/tutorial.controller.js");

  var router = require("express").Router();

  // Retrieve all Tutorials
  router.get("/", tutorials.findAll);

  // Retrieve all published Tutorials
  router.get("/published", tutorials.findAllPublished);

  ...

  app.use('/api/tutorials', router);
};

Conclusion

In this post, we have learned how to create Node.js Pagination Rest Api with Express, Sequelize and PostgreSQL database. We also see that Sequelize supports a great way to make pagination and filter methods without need of boilerplate code.

React Pagination Client that works with this Server:
React Table Pagination using react-table v7
React Pagination with API using Material-UI

react-pagination-with-api-material-ui-change-page

Angular Client working with this server:
Angular 8 Pagination example
Angular 10 Pagination example
Angular 11 Pagination example
Angular 12 Pagination example
Angular 13 Pagination example
Angular 14 Pagination example

Or Vue Client:
Vue Pagination example (Bootstrap)
Vuetify Pagination example

Happy learning! See you again.

Further Reading

Fullstack with Angular:
Server side Pagination with Node.js and Angular

server-side-pagination-node-js-angular-ui-change-size

Fullstack CRUD App:
Vue.js + Node.js + Express + PostgreSQL example
Angular 8 + Node.js Express + PostgreSQL example
Angular 10 + Node.js Express + PostgreSQL example
Angular 11 + Node.js Express + PostgreSQL example
Angular 12 + Node.js Express + PostgreSQL example
Angular 13 + Node.js Express + PostgreSQL example
Angular 14 + Node.js Express + PostgreSQL example
React + Node.js + Express + PostgreSQL example

Security: Node.js JWT Authentication & Authorization with PostgreSQL example

Source Code

You can find the complete source code for this tutorial on Github.

Dockerize: Docker Compose Nodejs and Postgres example

3 thoughts to “Node.js Express Pagination with PostgreSQL example”

  1. I have an example with the next scenario. I am trying to query by month but it is needed to evaluate if I send month or not from the front.

    I have used:
    let conditionMonth = search ? models.WorkDays.sequelize.Sequelize.literal (
    EXTRACT(MONTH from workday ) = ${ search } ) : null

    But it is not working to evaluate if I send that data from the front or not. Any idea to include where condition or not depending if the data is sent?

    Thank you

  2. Pls help me, whent the req.query,title is undefined. I can’t get all tutorila. That execute sql: WHERE null.
    I don’t know

Comments are closed to reduce spam. If you have any question, please send me an email.