How to implement Node.js Rest Apis with Express, Sequelize pagination & MySQL

Code With Travel

·

Follow

·

Dec 8, 2021

3 min read

—

In this tutorial we will learn Node.js Rest Apis with Express, Sequelize pagination & MySQL. For this we will use express , cors , mysql2 , sequelize.

MySQL2 project is a continuation of MySQL-Native. Protocol parser code was rewritten from scratch and api changed to match popular mysqljs/mysql. MySQL2 team is working together with mysqljs/mysql team to factor out shared code and move it under mysqljs organisation.

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

Let’s start.

Make folder using following command.

mkdir backendmysql
cd backendmysql
npm init -y

Install following module.

npm install express cors mysql2 sequelize

So your package.json will look like following:

{
  "name": "js-express-sequelize-mysql",
  "version": "1.0.0",
  "description": "Node.js Rest Apis with Express, Sequelize pagination & MySQL.",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "node",
    "js",
    "express",
    "sequelize",
    "pagination",
    "mysql",
    "rest",
    "api"
  ],
  "author": "stemword",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.1.0",
    "sequelize": "^5.21.12"
  }
}

Create server.js file.

const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const app = express();
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
const db = require("./app/models");
app.get("/", (req, res) => {
  res.json({ message: "Welcome to application." });
});
require("./app/routes/turorial.routes")(app);
// set port, listen for requests
const PORT = 4568;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

Create folder models and create index.js

const dbConfig = require("../config/db.config.js");
const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: 0,
  define: {
    "timestamps": false
  },
  freezeTableName: true,
  logging:true,
  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});
const db = {};
db.Sequelize = Sequelize;
db.expenseuser = require("./expenseuser.model.js")(sequelize,Sequelize);
module.exports = db;

create file expenseuser.model.js.

module.exports = (sequelize,Sequelize) => {
    const ExpenseName = sequelize.define("r_expense_user", {
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      phone: {
        type: Sequelize.STRING
      },
      address: {
        type: Sequelize.STRING
      },
      token: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.BOOLEAN
      },
      type: {
        type: Sequelize.BOOLEAN
      },
      created: {
        type: Sequelize.DATE
      },
    },{tableName : 'r_expense_user'});
  
    return ExpenseName;
  };

Create config folder and create db.config.js .

module.exports = {
  HOST: "127.0.0.1",
  USER: "root",
  PASSWORD: "root",
  DB: "testexpense",
  dialect: "mysql",
  freezeTableName: true,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

Create controllers folder and expenseuser.controller.js .

const db = require("../models");
const ExpenseUser = db.expenseuser;
const Op = db.Sequelize.Op;
// Retrieve all Tutorials from the database.
exports.findAll = (req, res) => {
  const { page, size, title } = req.query;
  var condition = title ? { name: { [Op.like]: `%${title}%` } } : null;
const { limit, offset } = getPagination(page, size);
ExpenseUser.findAndCountAll({
    where: condition,
    limit,
    offset,
    attributes: ["id", "name",'address','created'],
  })
    .then((data) => {
      const response = getPagingData(data, page, limit);
      res.send(response);
    })
    .catch((err) => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials.",
      });
    });
};
const getPagination = (page, size) => {
  const limit = size ? +size : 1;
  const offset = page ? page * limit : 0;
return { limit, offset };
};
const getPagingData = (data, page, limit) => {
  const { count: totalItems, rows: tutorials } = data;
  const currentPage = page ? +page : 0;
  const totalPages = Math.ceil(totalItems / limit);
return { totalItems, tutorials, totalPages, currentPage };
};

Create routes folder and create turorial.routes.js

module.exports = (app) => {
  const expense_user = require("../controllers/expenseuser.controller.js");
  var router = require("express").Router();
  router.get("/parties", expense_user.findAll);
  app.use("/api", router);
  app.use("*", (req, res) => res.status(404).json({ error: "not found" }));
};

Now go to postman

URL : http://localhost:4568/api/parties?page=0&size=1

{"totalItems":2,"tutorials":[{"id":1,"name":"party","address":"111","created":"2021-08-27T04:54:18.000Z"}],"totalPages":2,"currentPage":0}

You can check source code here. https://github.com/stemword/node-js-sequelize-pagination-mysql

You can also checkout YouTube channel : https://www.youtube.com/channel/UCOWT2JvRnSMVSboxvccZBFQ