NodeJS RESTfull APIs with Express and MySQL

TechArise Team

7 months ago

RESTful web APIs are typically loosely based on HTTP methods to access resources via URL-encoded parameters and the use of JSON or XML to transmit data. RESTful Web services are one way of providing interoperability between computer systems on the Internet. Rest API helps to communicate between the client app and the server application. REST is an architecture style for designing networked applications. A REST API defines a bunch of functions which developers can perform requests and receive responses via HTTP protocol such as GET and POST.

In this tutorial, you will learn to how create CRUD (create, read, update, delete) operation RESTful APIs NodeJS with Express and MySQL. This is a very simple example, you can just copy-paste, and change according to your requirements.

Also Read : Build Simple REST API with PHP and MySQL

Before started to implement the NodeJS RESTfull APIs with Express and MySQL, look files structure:

nodejs-express-mysql-restfull-api
- application
  - config
    - db.config.js
  - models
    - news.model.js
  - controllers
    - news.controller.js
  - routes
    - routes.js
- node_modules
- .env
- index.js
- package.json
- package-lock.json
- README.md

Understanding REST API

REST provides a block of HTTP methods which are used to alter the data. The following are common HTTP methods:

GET — is used for reading and retrieving data.
POST — is used for inserting data.
PUT/PATCH — is used for updating data.
DELETE — is used for deleting data.

Step 1: Create MySQL Database and Table

For this tutorial, you need a MySQL database with the following table:

  CREATE TABLE `news` (
    `id` int(11) NOT NULL,
    `title` text NOT NULL,
    `description` text NOT NULL,
    `status` int(1) NOT NULL,
    `publish_date` varchar(12) DEFAULT NULL,
    `created_date` varchar(12) DEFAULT NULL,
    `modified_date` varchar(12) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  INSERT INTO `news` (`id`, `title`, `description`, `status`, `publish_date`, `created_date`, `modified_date`) VALUES
(1, 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.', 1, '1634369809', '1634369809', '1634369809');


  ALTER TABLE `news`
    ADD PRIMARY KEY (`id`);

  ALTER TABLE `news`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
  COMMIT;

CREATE TABLE `news` (

`id` int(11) NOT NULL,

`title` text NOT NULL,

`description` text NOT NULL,

`status` int(1) NOT NULL,

`publish_date` varchar(12) DEFAULT NULL,

`created_date` varchar(12) DEFAULT NULL,

`modified_date` varchar(12) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `news` (`id`, `title`, `description`, `status`, `publish_date`, `created_date`, `modified_date`) VALUES

(1, 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.', 1, '1634369809', '1634369809', '1634369809');

ALTER TABLE `news`

ADD PRIMARY KEY (`id`);

ALTER TABLE `news`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

COMMIT;

Set Up and Run a Simple Node Server

Let’s build our web server. Create a file named index.js

    const express = require("express");
    const dotenv = require('dotenv');
    const app = express();
    // dotenv config
    dotenv.config();
    // ContentTypeParser: parse requests of content-type - application/json
    app.use(express.json());

    // ContentTypeParser: parse requests of content-type - application/x-www-form-urlencoded
    app.use(express.urlencoded({ extended: true }));

    // create route
    app.get("/", (req, res) => {
      res.json({ message: "TechArise - NodeJS RESTfull APIs with Express and MySQL" });
    });

    require("./application/routes/routes.js")(app);

    // set port, listen 
    const PORT = Number(process.env.PORT || 3000);


    app.listen(PORT, () => {
      console.log(`Server is running on PORT ${PORT}.`);
    });

const express = require("express");

const dotenv = require('dotenv');

const app = express();

// dotenv config

dotenv.config();

// ContentTypeParser: parse requests of content-type - application/json

app.use(express.json());

// ContentTypeParser: parse requests of content-type - application/x-www-form-urlencoded

app.use(express.urlencoded({ extended: true }));

// create route

app.get("/", (req, res) => {

res.json({ message: "TechArise - NodeJS RESTfull APIs with Express and MySQL" });

});

require("./application/routes/routes.js")(app);

// set port, listen

const PORT = Number(process.env.PORT || 3000);

app.listen(PORT, () => {

console.log(`Server is running on PORT ${PORT}.`);

});

Step 2: Create file named .env

loads environment variables

  # DB Configurations
  HOST=localhost
  DB_USER=root
  DB_PASS=
  DB_DATABASE=newsapi_DB

  # local runtime configs
  PORT=9090
  SECRET_JWT=supersecret

# DB Configurations

HOST=localhost

DB_USER=root

DB_PASS=

DB_DATABASE=newsapi_DB

# local runtime configs

PORT=9090

SECRET_JWT=supersecret

Step 3: Connect to Database file named db.config.js inside “application/config/” folder.

The code below shows the database credentials

const mysql = require("mysql");
const dotenv = require('dotenv');
dotenv.config();

// create MySQL Connection
var connection = mysql.createPool({
  host: process.env.HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_DATABASE
});

module.exports = connection;

const mysql = require("mysql");

const dotenv = require('dotenv');

dotenv.config();

// create MySQL Connection

var connection = mysql.createPool({

host: process.env.HOST,

user: process.env.DB_USER,

password: process.env.DB_PASS,

database: process.env.DB_DATABASE

});

module.exports = connection;

Step 4: Create models

Create a models file named news.model.js inside “application/models/” folder.

The News class handles the CRUD process
constructor — called an object
Add News Record in database.
Update News Record in databae.
News all Records from database.
get News single Record from database.
delete News Record from database.

    const sql = require("../config/db.config.js");
   
    // constructor
    class News {
      constructor(news) {
        this.title = news.title;
        this.description = news.description;
        this.status = news.status;
        this.publish_date = news.publish_date;
        this.created_date = news.created_date;
        this.modified_date = news.modified_date;
      }
      // create new news
      static create(newNews, result) {
        sql.query("INSERT INTO news SET ?", newNews, (err, res) => {
          if (err) {
            console.log("error: ", err);
            result(err, null);
            return;
          }

          console.log("created news: ", { id: res.insertId, ...newNews });
          result(null, { id: res.insertId, ...newNews });
        });
      }
      // get single news
      static findById(newsId, result) {
        sql.query(`SELECT * FROM news WHERE id = ${newsId}`, (err, res) => {
          if (err) {
            console.log("error: ", err);
            result(err, null);
            return;
          }

          if (res.length) {
            console.log("found news: ", res[0]);
            result(null, res[0]);
            return;
          }

          // not found news with the id
          result({ kind: "not_found" }, null);
        });
      }
      // get all news
      static getAll(result) {
        sql.query("SELECT * FROM news", (err, res) => {
          if (err) {
            console.log("error: ", err);
            result(null, err);
            return;
          }

          console.log("news: ", res);
          result(null, res);
        });
      }
      // update news
      static updateById(id, news, result) {
        sql.query(
          "UPDATE news SET title = ?, description = ?, status = ?, publish_date = ?, created_date = ?, modified_date = ? WHERE id = ?",
          [news.title, news.description, news.status, publish_date.address, news.created_date, news.modified_date, id],
          (err, res) => {
            if (err) {
              console.log("error: ", err);
              result(null, err);
              return;
            }

            if (res.affectedRows == 0) {
              // not found news with the id
              result({ kind: "not_found" }, null);
              return;
            }

            console.log("updated news: ", { id: id, ...news });
            result(null, { id: id, ...news });
          }
        );
      }
      // delete news
      static remove(id, result) {
        sql.query("DELETE FROM news WHERE id = ?", id, (err, res) => {
          if (err) {
            console.log("error: ", err);
            result(null, err);
            return;
          }

          if (res.affectedRows == 0) {
            // not found news with the id
            result({ kind: "not_found" }, null);
            return;
          }

          console.log("deleted news with id: ", id);
          result(null, res);
        });
      }
      // delete all news
      static removeAll(result) {
        sql.query("DELETE FROM news", (err, res) => {
          if (err) {
            console.log("error: ", err);
            result(null, err);
            return;
          }

          console.log(`deleted ${res.affectedRows} news`);
          result(null, res);
        });
      }
    }
    module.exports = News;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

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

// constructor

class News {

constructor(news) {

this.title = news.title;

this.description = news.description;

this.status = news.status;

this.publish_date = news.publish_date;

this.created_date = news.created_date;

this.modified_date = news.modified_date;

}

// create new news

static create(newNews, result) {

sql.query("INSERT INTO news SET ?", newNews, (err, res) => {

if (err) {

console.log("error: ", err);

result(err, null);

return;

}

console.log("created news: ", { id: res.insertId, ...newNews });

result(null, { id: res.insertId, ...newNews });

});

}

// get single news

static findById(newsId, result) {

sql.query(`SELECT * FROM news WHERE id = ${newsId}`, (err, res) => {

if (err) {

console.log("error: ", err);

result(err, null);

return;

}

if (res.length) {

console.log("found news: ", res[0]);

result(null, res[0]);

return;

}

// not found news with the id

result({ kind: "not_found" }, null);

});

}

// get all news

static getAll(result) {

sql.query("SELECT * FROM news", (err, res) => {

if (err) {

console.log("error: ", err);

result(null, err);

return;

}

console.log("news: ", res);

result(null, res);

});

}

// update news

static updateById(id, news, result) {

sql.query(

"UPDATE news SET title = ?, description = ?, status = ?, publish_date = ?, created_date = ?, modified_date = ? WHERE id = ?",

[news.title, news.description, news.status, publish_date.address, news.created_date, news.modified_date, id],

(err, res) => {

if (err) {

console.log("error: ", err);

result(null, err);

return;

}

if (res.affectedRows == 0) {

// not found news with the id

result({ kind: "not_found" }, null);

return;

}

console.log("updated news: ", { id: id, ...news });

result(null, { id: id, ...news });

}

);

}

// delete news

static remove(id, result) {

sql.query("DELETE FROM news WHERE id = ?", id, (err, res) => {

if (err) {

console.log("error: ", err);

result(null, err);

return;

}

if (res.affectedRows == 0) {

// not found news with the id

result({ kind: "not_found" }, null);

return;

}

console.log("deleted news with id: ", id);

result(null, res);

});

}

// delete all news

static removeAll(result) {

sql.query("DELETE FROM news", (err, res) => {

if (err) {

console.log("error: ", err);

result(null, err);

return;

}

console.log(`deleted ${res.affectedRows} news`);

result(null, res);

});

}

module.exports = News;

Step 5: Create controllers

Create a controllers file named news.controller.js inside “application/controllers/” folder.

    // include news model
    const News = require("../models/news.model.js");

    // Add and Save a new News method
    exports.create = (req, res) => {
      // Validate request
      if (!req.body) {
        res.status(400).send({
          message: "Error: Content can not be empty!"
        });
      }

      // Create a News
      const news = new News({
        title: req.body.title,
        description: req.body.description,
        status: req.body.status,
        publish_date: req.body.publish_date,
        created_date: req.body.created_date,
        modified_date: req.body.modified_date
      });

      // Save News in the database
      News.create(news, (err, data) => {
        if (err)
          res.status(500).send({
            message:
              err.message || "Error while creating news."
          });
        else res.send(data);
      });
    };

    // get all News from the database.
    exports.findAll = (req, res) => {
      News.getAll((err, data) => {
        if (err)
          res.status(500).send({
            message:
              err.message || "Error while retrieving news."
          });
        else res.send(data);
      });
    };

    // Find a single News with a newsId
    exports.findOne = (req, res) => {
      News.findById(req.params.newsId, (err, data) => {
        if (err) {
          if (err.kind === "not_found") {
            res.status(404).send({
              message: `News record not found ID: ${req.params.newsId}.`
            });
          } else {
            res.status(500).send({
              message: "Error retrieving News ID " + req.params.newsId
            });
          }
        } else res.send(data);
      });
    };

    // Update a News records 
    exports.update = (req, res) => {
      // Validate Request
      if (!req.body) {
        res.status(400).send({
          message: "Error: Content can not be empty!"
        });
      }

      console.log(req.body);

      News.updateById(
        req.params.newsId,
        new News(req.body),
        (err, data) => {
          if (err) {
            if (err.kind === "not_found") {
              res.status(404).send({
                message: `News record not found ID: ${req.params.newsId}.`
              });
            } else {
              res.status(500).send({
                message: "Error: updating News ID: " + req.params.newsId
              });
            }
          } else res.send(data);
        }
      );
    };

    // Delete a News with the specified newsId in the request
    exports.delete = (req, res) => {
      News.remove(req.params.newsId, (err, data) => {
        if (err) {
          if (err.kind === "not_found") {
            res.status(404).send({
              message: `News record not found ID: ${req.params.newsId}.`
            });
          } else {
            res.status(500).send({
              message: "Could not delete News ID: " + req.params.newsId
            });
          }
        } else res.send({ message: `News was deleted successfully!` });
      });
    };

    // Delete all News from the database.
    exports.deleteAll = (req, res) => {
      News.removeAll((err, data) => {
        if (err)
          res.status(500).send({
            message:
              err.message || "Error occurred while removing all news."
          });
        else res.send({ message: `Delete all News successfully!` });
      });
    };

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

// include news model

const News = require("../models/news.model.js");

// Add and Save a new News method

exports.create = (req, res) => {

// Validate request

if (!req.body) {

res.status(400).send({

message: "Error: Content can not be empty!"

});

}

// Create a News

const news = new News({

title: req.body.title,

description: req.body.description,

status: req.body.status,

publish_date: req.body.publish_date,

created_date: req.body.created_date,

modified_date: req.body.modified_date

});

// Save News in the database

News.create(news, (err, data) => {

if (err)

res.status(500).send({

message:

err.message || "Error while creating news."

});

else res.send(data);

});

};

// get all News from the database.

exports.findAll = (req, res) => {

News.getAll((err, data) => {

if (err)

res.status(500).send({

message:

err.message || "Error while retrieving news."

});

else res.send(data);

});

};

// Find a single News with a newsId

exports.findOne = (req, res) => {

News.findById(req.params.newsId, (err, data) => {

if (err) {

if (err.kind === "not_found") {

res.status(404).send({

message: `News record not found ID: ${req.params.newsId}.`

});

} else {

res.status(500).send({

message: "Error retrieving News ID " + req.params.newsId

});

}

} else res.send(data);

});

};

// Update a News records

exports.update = (req, res) => {

// Validate Request

if (!req.body) {

res.status(400).send({

message: "Error: Content can not be empty!"

});

}

console.log(req.body);

News.updateById(

req.params.newsId,

new News(req.body),

(err, data) => {

if (err) {

if (err.kind === "not_found") {

res.status(404).send({

message: `News record not found ID: ${req.params.newsId}.`

});

} else {

res.status(500).send({

message: "Error: updating News ID: " + req.params.newsId

});

}

} else res.send(data);

}

);

};

// Delete a News with the specified newsId in the request

exports.delete = (req, res) => {

News.remove(req.params.newsId, (err, data) => {

if (err) {

if (err.kind === "not_found") {

res.status(404).send({

message: `News record not found ID: ${req.params.newsId}.`

});

} else {

res.status(500).send({

message: "Could not delete News ID: " + req.params.newsId

});

}

} else res.send({ message: `News was deleted successfully!` });

});

};

// Delete all News from the database.

exports.deleteAll = (req, res) => {

News.removeAll((err, data) => {

if (err)

res.status(500).send({

message:

err.message || "Error occurred while removing all news."

});

else res.send({ message: `Delete all News successfully!` });

});

};

Step 6: Create routes

Create a routes file named routes.js inside “application/routes/” folder.

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

  // Add a new news in database
  app.post("/news/create", news.create);

  // Fetch all News for database
  app.get("/news", news.findAll);

  // Fetch data a single News form database
  app.get("/news/:newsId", news.findOne);

  // Update a News records
  app.put("/news/update/:newsId", news.update);

  // Delete a News records
  app.delete("/news/delete/:newsId", news.delete);

  // Delete All News records
  app.delete("/news/delete", news.deleteAll);
};

module.exports = app => {

const news = require("../controllers/news.controller.js");

// Add a new news in database

app.post("/news/create", news.create);

// Fetch all News for database

app.get("/news", news.findAll);

// Fetch data a single News form database

app.get("/news/:newsId", news.findOne);

// Update a News records

app.put("/news/update/:newsId", news.update);

// Delete a News records

app.delete("/news/delete/:newsId", news.delete);

// Delete All News records

app.delete("/news/delete", news.deleteAll);

};

Run Application

Now we will use Postman to run RESTful APIs.

POST Method: Save News in the database

  // POST Method
  http://localhost:9090/news/create

  // Body JSON
  {
    "title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "status" : 1,
    "publish_date": "1634369809",
    "created_date": "1634369809",
    "modified_date": "1634369809"
  }

// POST Method

http://localhost:9090/news/create

// Body JSON

{

"title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"status" : 1,

"publish_date": "1634369809",

"created_date": "1634369809",

"modified_date": "1634369809"

}

GET method: Find a single News with a newsId.

  // GET Method
  http://localhost:9090/news/1
  
  // Response JSON
  {
    "title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "status" : 1,
    "publish_date": "1634369809",
    "created_date": "1634369809",
    "modified_date": "1634369809"
  }

// GET Method

http://localhost:9090/news/1

// Response JSON

{

"title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"status" : 1,

"publish_date": "1634369809",

"created_date": "1634369809",

"modified_date": "1634369809"

}

GET method: Get all News from the database.

  // GET Method
  http://localhost:9090/news
  
  // Response JSON
  [
    {
      "title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
      "description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
      "status" : 1,
      "publish_date": "1634369809",
      "created_date": "1634369809",
      "modified_date": "1634369809"
    }
  ]

// GET Method

http://localhost:9090/news

// Response JSON

[

{

"title": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"status" : 1,

"publish_date": "1634369809",

"created_date": "1634369809",

"modified_date": "1634369809"

}

]

PUT Method: Update a News records

  // PUT Method
  http://localhost:9090/news/update/1
  
  // Body JSON
  {
    "title": "update_Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "description": "update_Lorem Ipsum is simply dummy text of the printing and typesetting industry.",
    "status" : 1,
    "publish_date": "1634369801",
    "created_date": "1634369801",
    "modified_date": "1634369801"
  }

// PUT Method

http://localhost:9090/news/update/1

// Body JSON

{

"title": "update_Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"description": "update_Lorem Ipsum is simply dummy text of the printing and typesetting industry.",

"status" : 1,

"publish_date": "1634369801",

"created_date": "1634369801",

"modified_date": "1634369801"

}

DELETE Method: Delete a News with the specified newsId in the request

  // DELETE Method
  http://localhost:9090/news/delete/1
  // Message
  {
    "message": "News was deleted successfully!"
  }

// DELETE Method

http://localhost:9090/news/delete/1

// Message

{

"message": "News was deleted successfully!"

}

DELETE Method: Delete all News from the database.

  // DELETE Method
  http://localhost:9090/news/delete
  // Message
  {
    "message": "Delete all News successfully!"
  }

// DELETE Method

http://localhost:9090/news/delete

// Message

{

"message": "Delete all News successfully!"

}

Also Read : Build Simple RESTful API using Python and MySQL

Download