Reading Time: 12 minutes

13,256 Views

Node Js is a runtime environment which provides to compile and execute javascript based applications. Nowadays there are several applications which using nodejs, express, sequelize orm and all to make application execution much faster.

Inside this article we will see RESTful APIs development using Node Express Sequelize ORM CRUD APIs with MySQL.

APIs can be build in various technologies but making in Node & Express is something different which makes development much easier and execution is faster in comparison of others.

Learn More –

Let’s get started.

Application Directory Structure

We need to create a best directory structure to make RESTful APIs along with code manageable environment. Well managed folder hierarchy of development is a good technique to reduce work effort and much understandable to understand the things.

/nodejs-express-apis
  /app
    /config
      db.config.js
      env.js
    /controller
      book.controller.js
    /model
      book.model.js
    /route
      book.route.js
  /node_modules
  package.json
  server.js

We will have 2 folders at root i.e /app & /node_modules.

In /app folder we will have the followings –

/config directory which stores database details and environment variables like database for production and development.
/controller directory which holds the functional files. These files are responsible to manage the data from models and responses.
/model directory stores database table schemes i.e each model have their table structure and will handle database related operations with that.
/route directory keeps application or restful apis routes configuration.

server.js is the main file which executes application and provides the listening port of requests. package.json file keep tracks of application dependencies. node_modules folder contain package dependent files which we installs via NPM.

Node Application Setup & Configuration

Now, we have a folder structure for node application. Open that folder in terminal or command prompt.

Step #1 – Create package.json file

First thing first we need to create a package.json file which keeps track of all installed dependencies of node application.

We have two ways to generate it. First by default values and second by going through series of questions and generate package.json file.

So, here we will prefer auto generate with default values.

Back to terminal and type command

$ npm init -y

{
  "name": "nodejs-express-apis",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Great ! package.json created.

Step #2 – Package Installations

In terms of this RESTful application, we will install packages as express, sequelize, mysql2, nodemon, body-parser. About each package you can find several article over google.

Here, you can find some details about these packages –

express – Web application framework for node application
sequelize – It’s node js ORM for postgres, mysql etc.
mysql2 – Database driver for application
nodemon – It’s tool which restarts node development server automatically when it listen any changes.
body-parser – It’s a middleware to accept body parameter values to methods

To install all these packages, back to terminal and type the command as –

$ npm install express sequelize nodemon mysql2 body-parser

+ nodemon@2.0.4
+ mysql2@2.2.5
+ express@4.17.1
+ sequelize@6.3.5
+ body-parser@1.19.0
added 199 packages from 178 contributors and audited 201 packages in 33.305s

10 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities

When we see package.json file again, then we should see it’s updated code –

{
  "name": "nodejs-express-apis",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql2": "^2.2.5",
    "nodemon": "^2.0.4",
    "sequelize": "^6.3.5"
  }
}

Successfully, we have installed the needed packages inside application.

Development – RESTful APIs CRUD Operation

Open project into any of your code editor. Here, I have preferred VS code.

VS Code of Node RESTful APIs with Sequelize ORM

Open /app/config/env.js – Database details

const env = {
  database: "node_express_apis",
  username: "root",
  password: "root",
  host: "localhost",
  dialect: "mysql",
};

module.exports = env;

Open /app/config/db.config.js – Database connection

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

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

//Models
db.book = require("../model/book.model.js")(sequelize, Sequelize);

module.exports = db;

Open /app/model/book.model.js – Application model i.e table schema of database. When we run application this will creates a database table.

module.exports = (sequelize, Sequelize) => {
  const Book = sequelize.define("book", {
    name: {
      type: Sequelize.STRING(100),
    },
    price: {
      type: Sequelize.INTEGER,
    },
    language: {
      type: Sequelize.STRING(100),
    },
    total_pages: {
      type: Sequelize.INTEGER,
    },
  }, {
      timestamps: false
  });

  return Book;
};

Open /app/controller/book.controller.js

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

// Post a Book
exports.create = (req, res) => {
  // Save to MySQL database
  Book.create({
    name: req.body.name,
    price: req.body.price,
    language: req.body.language,
    total_pages: req.body.total_pages,
  }).then((book) => {
    res.status(200).json({
      status: true,
      message: "Book created successfully",
    });
  });
};

// Get all books
exports.findAll = (req, res) => {
  Book.findAll().then((books) => {
    // Send all books as response
    res.status(200).json({
      status: true,
      data: books,
    });
  });
};

// Find a book by Id
exports.findByPk = (req, res) => {
  Book.findByPk(req.params.bookId).then((book) => {
    res.status(200).json({
      status: true,
      data: book,
    });
  });
};

// Update a book
exports.update = (req, res) => {
  const id = req.params.bookId;
  Book.update(
    {
      name: req.body.name,
      price: req.body.price,
      language: req.body.language,
      total_pages: req.body.total_pages,
    },
    { where: { id: req.params.bookId } }
  ).then(() => {
    res.status(200).json({
        status: true,
        message: "Book updated successfully with id = " + id
    });
  });
};

// Delete a book by Id
exports.delete = (req, res) => {
  const id = req.params.bookId;
  Book.destroy({
    where: { id: id },
  }).then(() => {
    res.status(200).json({
        status: true,
        message: "Book deleted successfully with id = " + id
    });
  });
};

Open /app/route/book.route.js

module.exports = function (app) {
  
  const book = require("../controller/book.controller.js");

  // Create a new book
  app.post("/api/book", book.create);

  // Retrieve all book
  app.get("/api/books", book.findAll);

  // Retrieve a single book by Id
  app.get("/api/books/:bookId", book.findByPk);

  // Update a book with Id
  app.put("/api/books/:bookId", book.update);

  // Delete a book with Id
  app.delete("/api/books/:bookId", book.delete);
};

Open server.js

var express = require("express");
var app = express();
var bodyParser = require("body-parser");
app.use(bodyParser.json());

// include database config file
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 }");
});

// include application routes
require("./app/route/book.route.js")(app);

// Create & Listen Server
var server = app.listen(8081, function () {
  var host = server.address().address;
  var port = server.address().port;

  console.log("Application request listening at http://%s:%s", host, port);
});

Finally, we have done all development. Next, we need to start server and check APIs. To check working APIs I will use Postman i.e a Chrome extension.

RESTful APIs Testing at Postman

Starting development by command –

$ nodemon

Nodemon Start Development Server Node Js

Here, we have APIs routes what we have configured.

CREATE – http://localhost:8081/api/book POST
LIST ALL – http://localhost:8081/api/books GET
SINGLE DATA – http://localhost:8081/api/books/1 GET
DELETE – http://localhost:8081/api/books/2 DELETE
UPDATE – http://localhost:8081/api/books/2 PUT

Check these images how we have tested.

CREATE BOOK API

LIST ALL BOOKS API

SINGLE BOOK DETAIL API

UPDATE BOOK DETAILS API

DELETE BOOK DETAIL API

We hope this article helped you to learn Node Express Sequelize ORM CRUD APIs with MySQL in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.