Node.js Express Sequelize MySQL CRUD RestAPIs Example – Node.js Post/Get/Put/Delete Request

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

Overview Architecture – Node.js Express Sequelize MySQL CRUD RestAPIs Example

Below is the architecture of the tutorial ‘Node.js Express Sequelize MySQL CRUD RestAPIs Example’:

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

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

Video Debug – Node.js Express Sequelize MySQL CRUD RestAPIs Example

In the video, I guide you step by step how to get & import sourcecode to Visual Code Studio from the site loizenjava.com and running as debug mode to check the living values of variables when having any incomming request:

Goal

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 MySQL database:

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

1. a POST RestAPI /api/customers/create will handle the submit data from client to save in MySQL database
2. a GET RestAPI /api/customers/all will fetch all data from MySQL
3. a GET RestAPI /api/customers/onebyid/:id will get a single data by primary key id
4. a PUT RestAPI /api/customers/update/:id will update an existed record in MySQL
5. a DELETE RestAPI /api/customers/delete/:id will delete an existed record in MySQL 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 – a GET RestAPI /api/customers/filteringbyage is used to fetch all records from MySQL with a filtering by age
2. Pagination Request – a GET RestAPI /api/customers/pagination is used to fetch data from MySQL with pagination purpose.
3. Pagination Filtering and Sorting – a GET RestAPI /api/customers/pagefiltersort is defined to fetch data from MySQL with pagination, filtering by age and ordering by 2 fields firstname and lastname

Testcase 1 – Nodejs Express POST Request

Check MySQL’s records:

Testcase 2 – Nodejs Express.js GET request all data from MySQL

Testcase 3 – Nodejs Express GET requestt: a data via primary key id

Testcase 4 – Nodejs Express Update Request

Testcase 5 – Nodejs Express Delete Request: a Customer by ID

Testcase 6 – Nodejs Express Filtering Request: Customers by age

The request returns a result set with a list of 4 included Customers having age = 23.

Check the database again:

Testcase 7 – Nodejs Express Do 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 MySQL query with LIMIT statement to check the above result:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

offset = limit*page = 7*1 = 7
row_count = limit

Do the LIMIT MySQL query:

Testcase 8 - Nodejs Express Do the 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 Project

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-Express-RestAPIs-MySQL, 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-express-restapis-mysql",
  "version": "1.0.0",
  "description": "Nodejs Create RestAPIs with MySQL database - RestAPI Post/Get/Delete/Put examples",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "repository": {
    "type": "git",
    "url": "https://github.com/loizenjava"
  },
  "keywords": [
    "nodejs",
    "restapis",
    "mysql",
    "tutorials"
  ],
  "author": "https://loizenjava.com",
  "license": "ISC",
  "dependencies": {
    "body-parse": "^0.1.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.1.0",
    "sequelize": "^6.3.3"
  }
}

All npm packages contain a file, usually in the project root, called package. json - this file holds various metadata relevant to the project. This file is used to give information to npm that allows it to identify the project as well as handle the project's dependencies. - via https://nodejs.org/

For coding Editor, we use friendly Visual Studio Code to write code and debug Nodejs project. For the tutorial, We will create a project with below structure:

1. a file db.config.js is used to define MySQL database configuration with Sequelize ORM
2. a file customer.model.js is used to define a Sequelize model mapped with corresponding MySQL database table schema.
3. a file router.js is used to define all Express RestAPI URLs
4. a file controller.js is used to implement detail logic code to process each incoming request
5. a file server.js is used to implement a Nodejs Web server

Install Express, Cors, Body Parse, MySQL, Sequelize

To development a 'Node.js Express Sequelize MySQL CRUD RestAPIs Example', we need a set of packages to handle the full stack of the web backend proccessing, they includes Express framework, Cors, Body Parse, MySQL 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. mysql-2 package is MySQL client for Node.js with focus on performance.

   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 mysql2 sequelize

Create Nodejs Sequelize Model

In the tutorial, 'Node.js Express Sequelize MySQL 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:

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://loizenjava.com'
    }
	});
	
	return Customer;
}

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

1. id attribute is a primary key with Int type (in database)
2. firstname attribute has Sequelize.STRING type mapping with the firstname column in customers table with varchar type
3. lastname attribute has Sequelize.STRING type mapping with the lastname column in customers table with varchar type
4. address attribute has Sequelize.STRING type mapping with the lastname column in customers table with varchar type
5. age attribute has Sequelize.INTEGER type mapping with the age column in customers table with int type
6. copyrightby attribute has Sequelize.STRING type mapping with the copyrightby column in customers table with varchar type and having default value is https://loizenjava.com

Sequelize Queries to do CRUD operations with MySQL

In the tutorial, we do some CRUD operations from Node.js application with MySQL 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

How to build Nodejs Express RestAPIs

For building the Nodejs RestAPIs project we do 4 step for development:

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

Create Express Application Server

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 MySQL Database with Sequelize ORM

Firstly, we create a file env.js with an const Object to include all configured parameters for MySQL database setup.

const env = {
  database: 'loizenjavadb',
  username: 'root',
  password: '12345',
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

module.exports = env;

In the tutorial, 'Node.js Express Sequelize MySQL CRUD RestAPIs Example', for the main setting between MySQL 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 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 MySQL 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 MySQL 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 MySQL 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 MySQL 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 MySQL 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 MYSQL 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 MySQL 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 MySQL database with a given id from the path parameter

Implement Nodejs Express POST request with MySQL using Sequelize

create = (req, res) function is used to handle a POST request at the endpoint /api/customers/create, save data to MySQL 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 MySQL 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
        });
    }
}

Implement FullStack Nodejs Express Sequelize MySQL GET API Request

Now we implement fullstack from Nodejs Express GET request using Sequelize ORM to retrieve data from MySQL 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 MySQL 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 MySQL 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 MySQL 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 MySQL 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
        });
      });
}

Implement Nodejs Express Sequelize MySQL PUT request

Now we implement a fullstack solution: Nodejs Express RestAPI PUT request to get data from MySQL 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 MySQL 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
        });
    }
}

Implement Nodejs Express DELETE request with Sequelize ORM to MySQL

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 MySQL 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 MySQL 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 MySQL 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,
        });
    }
}

Node.js Express Filtering RestAPI from MySQL using Sequelize ORM

Now we create an filtering restapi to get data from MySQL 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 MySQL 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 MySQL 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
              });
            });
}

Nodejs Express Pagination RestAPI with MySQL database using Sequelize OMR

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 MySQL database for the pagination query.

To do the pagination with MySQL 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 MySQL 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://loizenjava.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,
    });
  }    
}

Nodejs Express Pagination Filtering and Sorting RestAPI with MySQL using Sequelize

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 MYSQL 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://loizenjava.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://loizenjava.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

1. Sequelize Model and Operations
2. Express Router

Sourcecode

Clearly and running sourcecode for the tutorial: "Node.js Express Sequelize MySQL CRUD RestAPIs Example":

• How to build Express RestAPIs
• Implement POST request
• Implement GET request
• Implement PUT request
• Implement DELETE request
• Node.js Filtering RestAPI
• Nodejs Pagination RestAPI
• Nodejs Pagination Filtering and Sorting RestAPI

Nodejs RestAPIs Express Sequelize MySQL

- GitHub Sourcecode:

Nodejs RestAPIs Express Sequelize MySQL - GitHub Sourcecode