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
- Goal
- Create Nodejs Project
- Install Express, Cors, Body Parse, MySQL, Sequelize
- Create Sequelize Model
- Sequelize Queries to do CRUD operations with MySQL
- How to build Nodejs Express RestAPIs
- Implement Nodejs Express POST request with MySQL using Sequelize ORM
- Implement Nodejs Express GET request
- Implement Nodejs Express Sequelize MySQL PUT request
- Implement Nodejs Express DELETE request
- Node.js Express Filtering RestAPIs with Sequelize and MySQL
- Nodejs Express Pagination RestAPI from MySQL database with Sequelize
- Nodejs Pagination Filtering and Sorting RestAPI with MySQL database using Sequelize ORM
- Further Reading
- Sourcecode
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:
- a POST RestAPI
/api/customers/create
will handle the submit data from client to save in MySQL database - a GET RestAPI
/api/customers/all
will fetch all data from MySQL - a GET RestAPI
/api/customers/onebyid/:id
will get a single data by primary keyid
- a PUT RestAPI
/api/customers/update/:id
will update an existed record in MySQL - a DELETE RestAPI
/api/customers/delete/:id
will delete an existed record in MySQL which is associated with a primary keyid
– For advanced purpose such as Filtering, pagination and Sorting, we create the second RestAPIs group:
- Filtering Request – a GET RestAPI
/api/customers/filteringbyage
is used to fetch all records from MySQL with a filtering byage
- Pagination Request – a GET RestAPI
/api/customers/pagination
is used to fetch data from MySQL with pagination purpose. - Pagination Filtering and Sorting – a GET RestAPI
/api/customers/pagefiltersort
is defined to fetch data from MySQL with pagination, filtering byage
and ordering by 2 fieldsfirstname
andlastname
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:
totalItems
describes the number of records in databasetotalPages
describes the total number of pages with requested limitlimit
describes the number of items for a fetching pagecurrentPageNumber
is the order number of requested page (currentPageNumber = page + 1
)currentPageSize
is the size of the current page (currentPageSize <= limit
)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:

- a file
db.config.js
is used to define MySQL database configuration with Sequelize ORM - a file
customer.model.js
is used to define a Sequelize model mapped with corresponding MySQL database table schema. - a file
router.js
is used to define all Express RestAPI URLs - a file
controller.js
is used to implement detail logic code to process each incoming request - 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.
- 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
- 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
- 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
mysql-2
package is MySQL client for Node.js with focus on performance.npm install --save mysql2
- 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:
id
attribute is a primary key withInt
type (in database)firstname
attribute hasSequelize.STRING
type mapping with thefirstname
column incustomers
table withvarchar
typelastname
attribute hasSequelize.STRING
type mapping with thelastname
column incustomers
table withvarchar
typeaddress
attribute hasSequelize.STRING
type mapping with thelastname
column incustomers
table withvarchar
typeage
attribute hasSequelize.INTEGER
type mapping with theage
column incustomers
table withint
typecopyrightby
attribute hasSequelize.STRING
type mapping with thecopyrightby
column incustomers
table withvarchar
type and having default value ishttps://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:
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, }); });
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 => { });
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 });
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); });
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}});
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();
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:
- Create Express WEB Application Server
- Define All RestAPI URLs in
router.js
- Configure MySQL Database with Sequelize ORM
- 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:
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 messageretrieveAllCustomers = (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 formatgetCustomerById = (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 messagefilteringByAge = (req, res)
function is used to handle a GET request at the endpoint/api/customers/filteringbyage
to filter all records in MySQL by a givenage
value and return back all results to client in a Json message formatpagination = (req, res)
function is used to handle a GET request at the endpoint/api/customers/pagination
with 2 query parameterslimit
andpage
to get a batch of records from MySQL database like as a pagination querypagingfilteringsorting = (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 byage
and sorting byfirstname
andlastname
from MYSQL database with 3 request query parameterslimit
,page
,age
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 givenid
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 givenid
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 formatgetCustomerById = (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 givencustomerId
- 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 givenid
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 givenid
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 byage
condition inwhere
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
andlimit
parameters from the request query - Calulate an
offset
frompage
andlimit
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 andlastname
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 andage
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
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: