Node.js PostgreSQL CRUD Example

Tutorial: ” Node.js PostgreSQL CRUD Example – Express RestAPIs + Sequelize + PostgreSQL tutorial ”

In the tutorial, I will introduce step by step how to create a ‘ Node.js PostgreSQL CRUD Example – Express RestAPIs + Sequelize + PostgreSQL tutorial’ with a full-stack technologies: Express RestAPI Framework (Cors + Body-Parse) + Sequelize ORM + PostgreSQL database.

– I give you an Epic of the application, a fullstack excutive with overall architecture diagram.
– I guide step by step how to implement Nodejs RestAPI with Sequelize ORM to PostgreSQL database.
– Finally, I do an integrative testing from Postman Restclient to Nodejs RestAPIs application to PostgreSQL database.

Overview Architecture – Node.js PostgreSQL CRUD Example

Below is the architecture of the tutorial ‘Node.js PostgreSQL CRUD Example’:

To handling all POST/GET/PUT/DELETE RestAPI requests and do CRUD with PostgreSQL database, we create a backend web Node.js application with 4 main points:

• To handle CRUD RestAPI requests with Node.js, we use Express.js framework.
• To do CRUD operations with PostgreSQL database, we use Sequelize ORM queries.
• We define all RestAPI urls in router.js.
• We implement how to process each RestAPI requests in controller.js file.

Project Goal – Node.js PostgreSQL CRUD Example

After the tutorial, we will understand overall architecture and clearly picture how to create a full backend web restapis application with Node.js technology from abstract overview to specific helpful frameworks and details sourcecode for connecting all things in one application.

We will define 8 RestAPIs with POST/GET/PUT/DELETE methods for posting, fetching, updating, removing, pagination, filtering and sorting data from PostgreSQL database:

– For normally requests with POST/GET/PUT/DELETE methods, we create a first GROUP with 5 RestAPIs:

1. POST RestAPI /api/customers/create will handle the submit data from client to save in PostgreSQL database
2. GET RestAPI /api/customers/all will fetch all data from PostgreSQL database
3. GET RestAPI /api/customers/onebyid/:id will get a single data by primary key id
4. PUT RestAPI /api/customers/update/:id will update an existed record in PostgreSQL database
5. DELETE RestAPI /api/customers/delete/:id will delete an existed record in PostgreSQL which is associated with a primary key id

– For advanced purpose such as Filtering, Pagination and Sorting, we create the second RestAPIs group:

1. Filtering Request – GET RestAPI /api/customers/filteringbyage is used to fetch all records from PostgreSQL with a filtering by age
2. Pagination Request – GET RestAPI /api/customers/pagination is used to fetch data from PostgreSQL with pagination purpose.
3. Pagination Filtering and Sorting – GET RestAPI /api/customers/pagefiltersort is defined to fetch data from PostgreSQL with pagination, filtering by age and ordering by 2 fields firstname and lastname

Testcase 1 – Nodejs Express PostgreSQL POST Request – Node.js PostgreSQL CRUD Example

Check PostgreSQL’s records:

Testcase 2 – Nodejs Express PostgreSQL GET Request: get all data from PostgreSQL – Node.js PostgreSQL CRUD Example

Testcase 3 – Nodejs Express PostgreSQL GET Request: get one data record from PostgreSQL with a given id

Testcase 4 – Nodejs Express PostgreSQL UPDATE request – Node.js PostgreSQL CRUD Example

Testcase 5 – Nodejs Express PostgreSQL DELETE request: delete a record with a given id

Testcase 6 – Nodejs Express PostgreSQL Filtering request by a field

Testcase 7 – Nodejs Express PostgreSQL Pagination request

What does it mean? We had done a pagination request to fetch a second page page = 1 with a size of page is 7 (limit=7)

The RestAPI returns a json result with useful informantion as below:

1. totalItems describes the number of records in database
2. totalPages describes the total number of pages with requested limit
3. limit describes the number of items for a fetching page
4. currentPageNumber is the order number of requested page (currentPageNumber = page + 1)
5. currentPageSize is the size of the current page (currentPageSize <= limit)
6. customers is a dataset attached with the pagination request

Using Native PostgreSQL query with LIMIT statement to check the above result:

Testcase 8 - Nodejs Express PostgreSQL Pagination Filtering and Sorting request

What does it mean? - The above request had done with 3 proccessing steps:

1. Do the Filtering with age=23, and We just have 4 Customer items in database having age is 23 so returned totalItems is 4. The totalPages is 2 because of 2 reason:
- limit: 3
- and totalPages = Math.ceil(data.count / limit) = Math.ceil(4 / 3)

2. Do the pagination with offset = 0 (limit*page) and row_counts = 3:

3. Finally Do the Sorting by firstname with ascending order and lastname with descending order:

Create Nodejs Express Sequelize CRUD PostgreSQL Project - Node.js PostgreSQL CRUD Example

Before creating a Nodejs project, we need to confirm that the Nodejs and npm had been installed in your computer development by cmd: node -v and npm -v

If these commandlines are not recognized by command prompt, it means you need to install them by visit the https://nodejs.org/en/ site and download installed package and do the nodejs setup for development later.

Now starting development! Create a folder and named it as Nodejs-PostgreSQL-CRUD-Example, go inside the folder, open a cmd and initiate a Nodejs project by cmd npm init. After all, a package.json file is created as below content:

{
  "name": "nodejs-postgresql-crud-example",
  "version": "1.0.0",
  "description": "Nodejs PostgreSQL CRUD Example",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "crud",
    "postgresql",
    "example"
  ],
  "author": "https://loizenai.com",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "pg": "^8.3.2",
    "pg-hstore": "^2.3.3",
    "sequelize": "^6.3.4"
  }
}

Here is the project structure of Nodejs PostgreSQL CRUD Example:

- db.config.js is used to define PostgreSQL database configuration with Sequelize ORM
- customer.model.js is used to define a Sequelize model mapped with corresponding PostgreSQL database table schema.
- router.js is used to define all Nodejs Express RestAPI urls.
- controller.js is used to implement detail logic code to process each incoming request.
- server.js is used to implement a Nodejs Web server.

Install Nodejs Dependencies: Express, Cors, Body Parse, Sequelize, PostgreSQL - Node.js PostgreSQL CRUD Example

To development a 'Node.js PostgreSQL CRUD Example with Sequelize and Express RestAPIs', we need a set of packages to handle the full stack of the web backend proccessing, they includes Express framework, Cors, Body Parse, PostgreSQL packages and Sequelize ORM.

1. Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications.

   $ npm install express
   

2. CORS is a node.js package for providing a Connect/Express middleware that can be used to enable CORS with various options.

   $ npm install cors
   

3. Body-parser is the Node.js body parsing middleware. It is responsible for parsing incoming request bodies in a middleware before your handlers, available under the req.body property.

   $ npm install body-parser
   

4. pg pg-hstore package is PostgreSQL client for Node.js.

   npm install --save mysql2

5. Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

   $ npm install --save sequelize

We can install all the packages by one cmd:

$npm install --save express cors body-parser pg pg-hstore sequelize

Create Nodejs Sequelize Model Example - Node.js PostgreSQL CRUD Example

In the tutorial, 'Node.js Express Sequelize PostgreSQL CRUD RestAPIs Example', We need define a Sequelize ORM Model to represent a table in the database:

So here is the customer.model.js code:

/**
 * Copyright by https://loizenai.com
 * youtube loizenai
 */

module.exports = (sequelize, Sequelize) => {
	const Customer = sequelize.define('customer', {	
	  id: {
			type: Sequelize.INTEGER,
			autoIncrement: true,
			primaryKey: true
    },
	  firstname: {
			type: Sequelize.STRING
	  },
	  lastname: {
			type: Sequelize.STRING
  	},
	  address: {
			type: Sequelize.STRING
	  },
	  age: {
			type: Sequelize.INTEGER
    },
    copyrightby: {
      type: Sequelize.STRING,
      defaultValue: 'https://loizenai.com'
    }
	});
	
	return Customer;
}

We create a Sequelize Customer model with 6 attributes for mapping with all corresponding customers table's columns:

• id attribute is a primary key with Int type (in database)
• firstname attribute has Sequelize.STRING type mapping with the firstname column in customers table with varchar type
• lastname attribute has Sequelize.STRING> type mapping with the lastname column in customers table with varchar type
• address attribute has Sequelize.STRING type mapping with the lastname column in customers table with varchar type
• age attribute has Sequelize.INTEGER type mapping with the age column in customers table with int type
• copyrightby attribute has Sequelize.STRING type mapping with the copyrightby column in customers table with varchar type and having default value is https://loizenai.com

Sequelize CRUD Queries with PostgreSQL - Node.js PostgreSQL CRUD Example

In the tutorial, we do some CRUD operations from Node.js application with PostgreSQL database by using Sequelize ORM, so from now we need cleary understand some CRUD Sequelize methods which we will use later:

1. public static async create(values: object, options: object): Promise<Model>
   Builds a new model instance and calls save on it.

   Example code:

   Customer.create(customer).then(result => {    
       // send uploading message to client
       res.status(200).json({
           message: "Upload Successfully a Customer with id = " + result.id,
           customer: result,
       });
   });

2. public static async findAll(options: object): Promise<Array<Model>>
   Search for multiple instances.

   Example:

   Customer.findAll()
   	.then(customerInfos => {
   		res.status(200).json({
   			message: "Get all Customers' Infos Successfully!",
   			customers: customerInfos
   		});
   	})
   	. catch(error => {
   	});

3. public static async findByPk(param: number | string | Buffer, options: object): Promise<Model>
   Search for a single instance by its primary key._

   Example code:

   Customer.findByPk(customerId)
     .then(customer => {
   	  res.status(200).json({
   		  message: " Successfully Get a Customer with id = " + customerId,
   		  customers: customer
   	  });
     })
     . catch(error => {
   	// handling error here
     });

4. public static async findAndCountAll(options: object): Promise<{count: number, rows: Model[]}>

   Find all the rows matching your query, within a specified offset / limit, and get the total number of rows matching your query.

   Example code:

   Customer.findAndCountAll({ limit: limit, offset:offset })
     .then(data => {
   	const totalPages = Math.ceil(data.count / limit);
   	const response = {
   		// ...
   	  }
   	};
   	res.send(response);
     });  

5. public static async update(values: object, options: object): Promise<Array<number, number>>

   Update multiple instances that match the where options.

   Example code:

   Customer.update(updatedObject, {returning: true, where: {id: customerId}});

6. public static async destroy(options: object): Promise<number>
   Delete multiple instances, or set their deletedAt timestamp to the current time if paranoid is enabled.

   Example code:

   await customer.destroy();

7. async drop(options: object): Promise

   Drop the table represented by this Model

Create Node.js Express RestAPI Example - Node.js PostgreSQL CRUD Example

For building the Nodejs Express RestAPIs Example project with PostgreSQL, we do 4 step for development:

1. Create Express WEB Application Server
2. Define All RestAPI URLs in router.js
3. Configure PostgreSQL Database with Sequelize ORM
4. Implement All RESTAPIs in controllers.js file

Create Express Application Server - Node.js PostgreSQL CRUD Example

To implement an Express RestAPIs Application, firstly we need create a server with express app:

- server.js:

const express = require('express');
const app = express();
...
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

- For parsing body of requests, we need use body-parser dependency, add more code on server.js file:

...
var bodyParser = require('body-parser');
...
app.use(bodyParser.json());
...
const server = app.listen(8080, function () {
...
>

We define all RESTAPI URLs in a file router.js and then need attach it with the Express Application:

...
let router = require('./app/routers/router.js');
...
app.use('/', router);
...
const server = app.listen(8080, function () {
...

To enable CORS function, we need attach it with Express app:

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

Here is all coding in the server.js file:

const express = require('express');
const app = express();

var bodyParser = require('body-parser');
 
const db = require('./app/config/db.config.js');
  
// force: true will drop the table if it already exists
db.sequelize.sync({force: true}).then(() => {
  console.log('Drop and Resync with { force: true }');
}); 

let router = require('./app/routers/router.js');

const cors = require('cors')
const corsOptions = {
  origin: 'http://localhost:4200',
  optionsSuccessStatus: 200
}
app.use(cors(corsOptions));

app.use(bodyParser.json());
app.use('/', router);

// Create a Server
const server = app.listen(8080, function () {
 
  let host = server.address().address
  let port = server.address().port
 
  console.log("App listening at http://%s:%s", host, port); 
})

Define All Nodejs Express RestAPIs URLs in router.js

For making a router with Express framework, firstly we need get the router object from Express app. Then using it to define the GET/POST/DELETE/PUT requests:

let express = require('express');
let router = express.Router();
 
const customers = require('../controllers/controller.js');

router.post('/api/customers/create', customers.create);
router.get('/api/customers/all', customers.retrieveAllCustomers);
router.get('/api/customers/onebyid/:id', customers.getCustomerById);
router.get('/api/customers/filteringbyage', customers.filteringByAge);
router.get('/api/customers/pagination', customers.pagination);
router.get('/api/customers/pagefiltersort', customers.pagingfilteringsorting);
router.put('/api/customers/update/:id', customers.updateById);
router.delete('/api/customers/delete/:id', customers.deleteById);

module.exports = router;

Use the express.Router class to create modular, mountable route handlers. A Router instance is a complete middleware and routing system.

Configure PostgreSQL Database with Sequelize ORM - Node.js PostgreSQL CRUD Example

In the tutorial "Node.js PostgreSQL CRUD Example", we create a file env.js with an const Object to include all configured parameters for PostgreSQL database setup.

const env = {
  database: 'loizenaidb',
  username: 'postgres',
  password: '123',
  host: 'localhost',
  dialect: 'postgres',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

module.exports = env;

In the tutorial, 'Node.js Express Sequelize PostgreSQL CRUD RestAPIs Example', for the main setting between PostgreSQL database and Sequelize ORM, we define a file db.config.js as below code:

const env = require('./env.js');
 
const Sequelize = require('sequelize');
const sequelize = new Sequelize(env.database, env.username, env.password, {
  host: env.host,
  dialect: env.dialect,
  operatorsAliases: false,
 
  pool: {
    max: env.max,
    min: env.pool.min,
    acquire: env.pool.acquire,
    idle: env.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;
 
db.Customer = require('../models/customer.model.js')(sequelize, Sequelize);
 
module.exports = db;

Implement All RESTAPIs in controllers.js file

For handling Nodejs PostgreSQL CRUD RestAPIs' processing that been defined in router.js file, we implement all 8 working functions in controller.js file:

1. create = (req, res) function is used to handle a POST request at the endpoint /api/customers/create, save data to PostgreSQL database and return back a JSON message
2. retrieveAllCustomers = (req, res) function is used to handle a GET request at the endpoint /api/customers/all to fetch all records from PostgreSQL and return back to client in JSON format
3. getCustomerById = (req, res) function is used to handle a GET request at the endpoint /api/customers/onebyid/:id to get a specific record from PostgreSQL database with a given id and return back to client a JSON message
4. filteringByAge = (req, res) function is used to handle a GET request at the endpoint /api/customers/filteringbyage to filter all records in PostgreSQL by a given age value and return back all results to client in a Json message format
5. pagination = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagination with 2 query parameters limit and page to get a batch of records from PostgreSQL database like as a pagination query
6. pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by age and sorting by firstname and lastname from PostgreSQL database with 3 request query parameters limit, page, age
7. updateById = async (req, res) function is used to handle a PUT request at the endpoint /api/customers/update/:id to update a specific record from PostgreSQL database with a given id
8. deleteById = async (req, res) function is used to handle a DELETE request at the endpoint /api/customers/delete/:id to delete a specific record from PostgreSQL database with a given id from the path parameter

Implement Node.js Express CRUD RestAPIs: Post/Get/Put/Delete requests

Nodejs Express POST request

create = (req, res) function is used to handle a POST request at the endpoint /api/customers/create, save data to PostgreSQL database and return back a JSON message.

What will we do?

• Create a Customer object from a request's body data
• Use the Sequelize Model to save created object to PostgreSQL database
• Return back a JSON message to client side

Remember to use try-catch statement for handling any error if having unexpected exception.

exports.create = (req, res) => {
    let customer = {};

    try{
        // Building Customer object from upoading request's body
        customer.firstname = req.body.firstname;
        customer.lastname = req.body.lastname;
        customer.address = req.body.address;
        customer.age = req.body.age;
    
        // Save to MySQL database
        Customer.create(customer).then(result => {    
            // send uploading message to client
            res.status(200).json({
                message: "Upload Successfully a Customer with id = " + result.id,
                customer: result,
            });
        });
    }catch(error){
        res.status(500).json({
            message: "Fail!",
            error: error.message
        });
    }
}

Nodejs Express GET request

Now we implement fullstack from Nodejs Express GET request using Sequelize ORM to retrieve data from PostgreSQL database. We create 2 GET request methods:

• retrieveAllCustomers = (req, res) function is used to handle a GET request at the endpoint /api/customers/all to fetch all records from PostgreSQL and return back to client in JSON format
• getCustomerById = (req, res) function is used to handle a GET request at the endpoint /api/customers/onebyid/:id to get a specific record from PostgreSQL database with a given id and return back to client a JSON message

1. How to implement retrieveAllCustomers = (req, res) function? It's so simple

• Using Customer.findAll() function to fetch all records from PostgreSQL database
• Return a JSON message to client with needed information
• Do not forget to handle any unexpected error by using try-catch statement if having any exception throwed

exports.retrieveAllCustomers = (req, res) => {
    // find all Customer information from 
    Customer.findAll()
        .then(customerInfos => {
            res.status(200).json({
                message: "Get all Customers' Infos Successfully!",
                customers: customerInfos
            });
        })
        . catch(error => {
          // log on console
          console.log(error);

          res.status(500).json({
              message: "Error!",
              error: error
          });
        });
}

2. How to implement getCustomerById = (req, res) function? It's so simple

• Using Customer.findByPk(customerId) function to fetch a specific row from PostgreSQL database with a given customerId
• Return a JSON message to client with needed information
• Do not forget to handle any unexpected error by using try-catch statement if having any exception throwed

- Coding here:

exports.getCustomerById = (req, res) => {
  // find all Customer information from 
  let customerId = req.params.id;
  Customer.findByPk(customerId)
      .then(customer => {
          res.status(200).json({
              message: " Successfully Get a Customer with id = " + customerId,
              customers: customer
          });
      })
      . catch(error => {
        // log on console
        console.log(error);

        res.status(500).json({
            message: "Error!",
            error: error
        });
      });
}

Nodejs Express PUT request - Node.js PostgreSQL CRUD Example

Now we implement a fullstack solution: Nodejs Express RestAPI PUT request to get data from PostgreSQL using Sequelize ORM.

updateById = async (req, res) function is used to handle a PUT request at the endpoint /api/customers/update/:id to update a specific record from MySQL database with a given id

How to implement it? Just simple:

• Use the Sequelize Model to fetch a specific Customer with a given id from request parameter:
  customer = await Customer.findByPk(customerId)
• If existing a specific instance with the given id, we build a Customer object from the request's body data, then do an update operation to PostgreSQL database:

  let updatedObject = {
  	firstname: req.body.firstname,
  	lastname: req.body.lastname,
  	address: req.body.address,
  	age: req.body.age
  }
  let result = await Customer.update(updatedObject, {returning: true, where: {id: customerId}});

• Build a JSON message and return back to client side

  res.status(200).json({
  	message: "Update successfully a Customer with id = " + customerId,
  	customer: updatedObject,
  });

• Do not forget to handle any unexpected error by using try-catch statement if having any exception throwed

Coding:

exports.updateById = async (req, res) => {
    try{
        let customerId = req.params.id;
        let customer = await Customer.findByPk(customerId);
    
        if(!customer){
            // return a response to client
            res.status(404).json({
                message: "Not Found for updating a customer with id = " + customerId,
                customer: "",
                error: "404"
            });
        } else {    
            // update new change to database
            let updatedObject = {
                firstname: req.body.firstname,
                lastname: req.body.lastname,
                address: req.body.address,
                age: req.body.age
            }
            let result = await Customer.update(updatedObject, {returning: true, where: {id: customerId}});
            
            // return the response to client
            if(!result) {
                res.status(500).json({
                    message: "Error -> Can not update a customer with id = " + req.params.id,
                    error: "Can NOT Updated",
                });
            }

            res.status(200).json({
                message: "Update successfully a Customer with id = " + customerId,
                customer: updatedObject,
            });
        }
    } catch(error){
        res.status(500).json({
            message: "Error -> Can not update a customer with id = " + req.params.id,
            error: error.message
        });
    }
}

Nodejs Express DELETE request

deleteById = async (req, res) function is used to handle a DELETE request at the endpoint /api/customers/delete/:id to delete a specific record from PostgreSQL database with a given id from the path parameter.

How to implement it?

• Using the Sequelize Model with a function findByPk to check an existed of a specific record in PostgreSQL database with a given id
let customer = await Customer.findByPk(customerId);
• If does not exist, return a 404 NOT-FOUND message
• Otherwise, use a destroy() method to delete the existed record in PostgreSQL database with the given id then return back a successfully message to client side
• Do not forget to handle any unexpected error by using a try-catch statement

exports.deleteById = async (req, res) => {
    try{
        let customerId = req.params.id;
        let customer = await Customer.findByPk(customerId);

        if(!customer){
            res.status(404).json({
                message: "Does Not exist a Customer with id = " + customerId,
                error: "404",
            });
        } else {
            await customer.destroy();
            res.status(200).json({
                message: "Delete Successfully a Customer with id = " + customerId,
                customer: customer,
            });
        }
    } catch(error) {
        res.status(500).json({
            message: "Error -> Can NOT delete a customer with id = " + req.params.id,
            error: error.message,
        });
    }
}

Advance 1: Nodejs Sequelize Filtering PostgreSQL

Now we create an filtering restapi to get data from PostgreSQL database with some condition by Nodejs Express and Sequelize Queries:

filteringByAge = (req, res) function is used to handle a GET request at the endpoint /api/customers/filteringbyage to filter all records in PostgreSQL by a given age value and return back all results to client in a Json message format.

How to implement it? Just follow simple steps:

• Get an age value from request query for using to do the filter later
• Using the .findAll() method of Sequelize Model to retrieve all records from PostgreSQL database with a filter by age condition in where clause: where: {age: age}
• Build a Json message with informative fields and return it back to client side.
• Do not forget to handle any exception when processing by using try-catch statement!

Coding:

exports.filteringByAge = (req, res) => {
  let age = req.query.age;

    Customer.findAll({
                      attributes: ['id', 'firstname', 'lastname', 'age', 'address', 'copyrightby'],
                      where: {age: age}
                    })
          .then(results => {
            res.status(200).json({
                message: "Get all Customers with age = " + age,
                customers: results,
            });
          })
          . catch(error => {
              console.log(error);
              res.status(500).json({
                message: "Error!",
                error: error
              });
            });
}

Advance 2: Nodejs Sequelize Pagination PostgreSQL

pagination = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagination with 2 query parameters limit and page to get a batch of records from PostgreSQL database for the pagination query.

To do the pagination with PostgreSQL database by Sequelize ORM, we use the .findAndCountAll() method.

Signature of findAndCountAll() method:

public static async findAndCountAll(options: object): Promise<{count: number, rows: Model[]}>

.findAndCountAll(...) find all the rows matching your query, within a specified offset / limit, and get the total number of rows matching your query.

const offset = page ? page * limit : 0;

Simple steps to implement the pagination request:

• Get the page and limit parameters from the request query
• Calulate an offset from page and limit parameters
  const offset = page ? page * limit : 0;
• Do the pagination query to PostgreSQL database using the findAndCountAll() method of Sequelize model
• Construct a JSON message with a set of informative fields and return back to client side
• Remember to handle the un-expected error by using try-catch statement

Detail Coding:

exports.pagination = (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.limit);
  
    const offset = page ? page * limit : 0;
  
    Customer.findAndCountAll({ limit: limit, offset:offset })
      .then(data => {
        const totalPages = Math.ceil(data.count / limit);
        const response = {
          message: "Paginating is completed! Query parameters: page = " + page + ", limit = " + limit,
          data: {
              "copyrightby": "https://loizenai.com",
              "totalItems": data.count,
              "totalPages": totalPages,
              "limit": limit,
              "currentPageNumber": page + 1,
              "currentPageSize": data.rows.length,
              "customers": data.rows
          }
        };
        res.send(response);
      });  
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }    
}

Advance 3: Nodejs Sequelize Pagination Filtering Sorting with PostgreSQL - Node.js PostgreSQL CRUD Example

pagingfilteringsorting = (req, res) function is used to handle a GET request at the endpoint /api/customers/pagefiltersort to do an association quering operation: pagination, plus filtering by age and sorting by firstname and lastname from PostgreSQL database with 3 request query parameters limit, page, age.

We continue to use the method .findAndCountAll(...) to do the associated operation: pagination plus filtering and sorting query. So we add more 2 clauses inside .findAndCountAll(...) method:

• Where clause - using to filter by age field: where: {age: age}
• Order clause - using to sort rows by 2 fileds firstname with ascending direction and lastname with descending direction:

  
  order: [
    ['firstname', 'ASC'],
    ['lastname', 'DESC']
  ],
  

Straightforward steps to implement the Pagination + Filtering and Sorting function:

• Retrieve 3 parameters from incoming request query: limit, page for pagination and age for filtering.
• Calulate an offset for Sequelize pagining query later:
  const offset = page ? page * limit : 0;
• Do the Sequelize pagination filtering and sorting with .findAndCountAll() method:

  Customer.findAndCountAll({
                              attributes: ['id', 'firstname', 'lastname', 'age', 'address'],
                              where: {age: age}, 
                              order: [
                                ['firstname', 'ASC'],
                                ['lastname', 'DESC']
                              ],
                              limit: limit, 
                              offset:offset 
                            })

• Construct a Json message with informative fields and return back to client side:

  
  .then(data => {
    const totalPages = Math.ceil(data.count / limit);
    const response = {
      message: "Pagination Filtering Sorting request is completed! Query parameters: page = " + page + ", limit = " + limit + ", age = " + age,
      data: {
          "copyrightby": "https://loizenai.com",
          "totalItems": data.count,
          "totalPages": totalPages,
          "limit": limit,
          "age-filtering": age,
          "currentPageNumber": page + 1,
          "currentPageSize": data.rows.length,
          "customers": data.rows
      }
    };
    res.send(response);
  

• Do NOT forget to handle an un-expected errors with try-catch statement.

  try{
      //...
  }catch(error) {
      res.status(500).send({
        message: "Error -> Can NOT complete a paging request!",
        error: error.message,
      });
  }   

- Coding here:

exports.pagingfilteringsorting = (req, res) => {
  try{
    let page = parseInt(req.query.page);
    let limit = parseInt(req.query.limit);
    let age = parseInt(req.query.age);
  
    const offset = page ? page * limit : 0;

    console.log("offset = " + offset);
  
    Customer.findAndCountAll({
                                attributes: ['id', 'firstname', 'lastname', 'age', 'address'],
                                where: {age: age}, 
                                order: [
                                  ['firstname', 'ASC'],
                                  ['lastname', 'DESC']
                                ],
                                limit: limit, 
                                offset:offset 
                              })
      .then(data => {
        const totalPages = Math.ceil(data.count / limit);
        const response = {
          message: "Pagination Filtering Sorting request is completed! Query parameters: page = " + page + ", limit = " + limit + ", age = " + age,
          data: {
              "copyrightby": "https://loizenai.com",
              "totalItems": data.count,
              "totalPages": totalPages,
              "limit": limit,
              "age-filtering": age,
              "currentPageNumber": page + 1,
              "currentPageSize": data.rows.length,
              "customers": data.rows
          }
        };
        res.send(response);
      });  
  }catch(error) {
    res.status(500).send({
      message: "Error -> Can NOT complete a paging request!",
      error: error.message,
    });
  }      
}

Further Reading - Node.js PostgreSQL CRUD Example

Sourcecode - Node.js PostgreSQL CRUD Example

- Sourcecode for the tutorial 'Node.js PostgreSQL CRUD Example - Express RestAPIs + Sequelize + PostgreSQL tutorial':

Nodejs-PostgreSQL-CRUD-Example

- Github sourcecode:

Nodejs CRUD PostgreSQL Example - GITHUB