Node.js Rest APIs example with Express, Sequelize & MySQL

bezkoder Node.js

Express is one of the most popular web frameworks for Node.js that supports routing, middleware, view system… Sequelize is a promise-based Node.js ORM that supports the dialects for Postgres, MySQL, SQL Server… In this tutorial, I will show you step by step to build Node.js Restful CRUD API using Express, Sequelize with MySQL database.

You should install MySQL in your machine first. The installation instructions can be found at Official MySQL installation manual.

Related Posts:
– Typescript: Node.js Typescript REST API with MySQL example
– Build Node.js Rest APIs with Express & MySQL (without Sequelize)
– Node.js: Upload/Import Excel file data into MySQL Database
– Node.js: Upload CSV file data into MySQL Database

Fullstack:
– Vue.js + Node.js + Express + MySQL example
– Vue.js + Node.js + Express + MongoDB example
– Angular 8 + Node.js Express + MySQL example
– Angular 10 + Node.js Express + MySQL example
– Angular 11 + Node.js Express + MySQL example
– Angular 12 + Node.js Express + MySQL example
– Angular 13 + Node.js Express + MySQL example
– Angular 14 + Node.js Express + MySQL example
– Angular 15 + Node.js Express + MySQL example
– Angular 16 + Node.js Express + MySQL example
– React + Node.js + Express + MySQL example
– React Redux + Node.js Express + MySQL example

Security: Node.js – JWT Authentication & Authorization example
Deployment:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Express and MySQL example – Docker Compose

Node.js Rest CRUD API overview

We will build Rest Apis that can create, retrieve, update, delete and find Tutorials by title.

First, we start with an Express web server. Next, we add configuration for MySQL database, create Tutorial model with Sequelize, write the controller. Then we define routes for handling all CRUD operations (including custom finder).

The following table shows overview of the Rest APIs that will be exported:

Methods Urls Actions
GET api/tutorials get all Tutorials
GET api/tutorials/:id get Tutorial by id
POST api/tutorials add new Tutorial
PUT api/tutorials/:id update Tutorial by id
DELETE api/tutorials/:id remove Tutorial by id
DELETE api/tutorials remove all Tutorials
GET api/tutorials/published find all published Tutorials
GET api/tutorials?title=[kw] find all Tutorials which title contains 'kw'

Finally, we’re gonna test the Rest Apis using Postman.

This is our project structure:

node-js-express-sequelize-mysql-example-project-structure

Demo Video

This is our Node.js Express Sequelize application demo running with MySQL database and test Rest Apis with Postman.

Create Node.js App

First, we create a folder:

$ mkdir nodejs-express-sequelize-mysql
$ cd nodejs-express-sequelize-mysql

Next, we initialize the Node.js App with a package.json file:

npm init

name: (nodejs-express-sequelize-mysql) 
version: (1.0.0) 
description: Node.js Rest Apis with Express, Sequelize & MySQL.
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, express, sequelize, mysql, rest, api
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

We need to install necessary modules: express, sequelize, mysql2 and cors.
Run the command:

npm install express sequelize mysql2 cors --save

The package.json file should look like this:

{
  "name": "nodejs-express-sequelize-mysql",
  "version": "1.0.0",
  "description": "Node.js Rest Apis with Express, Sequelize & MySQL",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "express",
    "rest",
    "api",
    "sequelize",
    "mysql"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "cors": "^2.8.5",
    "express": "^4.18.2",
    "mysql2": "^2.3.3",
    "sequelize": "^6.32.0"
  }
}

Setup Express web server

In the root folder, let’s create a new server.js file:

const express = require("express");
const cors = require("cors");

const app = express();

var corsOptions = {
  origin: "http://localhost:8081"
};

app.use(cors(corsOptions));

// parse requests of content-type - application/json
app.use(express.json());

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

// simple route
app.get("/", (req, res) => {
  res.json({ message: "Welcome to bezkoder application." });
});

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

What we do are:
– import express, and cors modules:

  • Express is for building the Rest apis
  • cors provides Express middleware to enable CORS with various options.

– create an Express app, then add body-parser (json and urlencoded) and cors middlewares using app.use() method. Notice that we set origin: http://localhost:8081.
– define a GET route which is simple for test.
– listen on port 8080 for incoming requests.

Now let’s run the app with command: node server.js.
Open your browser with url http://localhost:8080/, you will see:

node-js-express-sequelize-mysql-example-setup-server

Yeah, the first step is done. We’re gonna work with Sequelize in the next section.

Configure MySQL database & Sequelize

In the app folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for MySQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

  • max: maximum number of connection in pool
  • min: minimum number of connection in pool
  • idle: maximum time, in milliseconds, that a connection can be idle before being released
  • acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

We’re gonna initialize Sequelize in app/models folder that will contain model in the next step.

Now create app/models/index.js with the following code:

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: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

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

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

Don’t forget to call sync() method in server.js:

...
const app = express();
app.use(...);

const db = require("./app/models");
db.sequelize.sync()
  .then(() => {
    console.log("Synced db.");
  })
  .catch((err) => {
    console.log("Failed to sync db: " + err.message);
  });

...

In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:


db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Define the Sequelize Model

In models folder, create tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

This Sequelize Model represents tutorials table in MySQL database. These columns will be generated automatically: id, title, description, published, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

  • create a new Tutorial: create(object)
  • find a Tutorial by id: findByPk(id)
  • get all Tutorials: findAll()
  • update a Tutorial by id: update(data, where: { id: id })
  • remove a Tutorial: destroy(where: { id: id })
  • remove all Tutorials: destroy(where: {})
  • find all Tutorials by title: findAll({ where: { title: ... } })

These functions will be used in our Controller.

We can improve the example by adding Comments for each Tutorial. It is the One-to-Many Relationship and I write a tutorial for this at:
Sequelize Associations: One-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Create the Controller

Inside app/controllers folder, let’s create tutorial.controller.js with these CRUD functions:

  • create
  • findAll
  • findOne
  • update
  • delete
  • deleteAll
  • findAllPublished
const db = require("../models");
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;

// Create and Save a new Tutorial
exports.create = (req, res) => {
  
};

// Retrieve all Tutorials from the database.
exports.findAll = (req, res) => {
  
};

// Find a single Tutorial with an id
exports.findOne = (req, res) => {
  
};

// Update a Tutorial by the id in the request
exports.update = (req, res) => {
  
};

// Delete a Tutorial with the specified id in the request
exports.delete = (req, res) => {
  
};

// Delete all Tutorials from the database.
exports.deleteAll = (req, res) => {
  
};

// Find all published Tutorials
exports.findAllPublished = (req, res) => {
  
};

Let’s implement these functions.

Create a new object

Create and Save a new Tutorial:

exports.create = (req, res) => {
  // Validate request
  if (!req.body.title) {
    res.status(400).send({
      message: "Content can not be empty!"
    });
    return;
  }

  // Create a Tutorial
  const tutorial = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false
  };

  // Save Tutorial in the database
  Tutorial.create(tutorial)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the Tutorial."
      });
    });
};

Retrieve objects (with condition)

Retrieve all Tutorials/ find by title from the database:

exports.findAll = (req, res) => {
  const title = req.query.title;
  var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;

  Tutorial.findAll({ where: condition })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

We use req.query.title to get query string from the Request and consider it as condition for findAll() method.

Retrieve a single object

Find a single Tutorial with an id:

exports.findOne = (req, res) => {
  const id = req.params.id;

  Tutorial.findByPk(id)
    .then(data => {
      if (data) {
        res.send(data);
      } else {
        res.status(404).send({
          message: `Cannot find Tutorial with id=${id}.`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Error retrieving Tutorial with id=" + id
      });
    });
};

Update an object

Update a Tutorial identified by the id in the request:

exports.update = (req, res) => {
  const id = req.params.id;

  Tutorial.update(req.body, {
    where: { id: id }
  })
    .then(num => {
      if (num == 1) {
        res.send({
          message: "Tutorial was updated successfully."
        });
      } else {
        res.send({
          message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Error updating Tutorial with id=" + id
      });
    });
};

Delete an object

Delete a Tutorial with the specified id:

exports.delete = (req, res) => {
  const id = req.params.id;

  Tutorial.destroy({
    where: { id: id }
  })
    .then(num => {
      if (num == 1) {
        res.send({
          message: "Tutorial was deleted successfully!"
        });
      } else {
        res.send({
          message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Could not delete Tutorial with id=" + id
      });
    });
};

Delete all objects

Delete all Tutorials from the database:

exports.deleteAll = (req, res) => {
  Tutorial.destroy({
    where: {},
    truncate: false
  })
    .then(nums => {
      res.send({ message: `${nums} Tutorials were deleted successfully!` });
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while removing all tutorials."
      });
    });
};

Find all objects by condition

Find all Tutorials with published = true:

exports.findAllPublished = (req, res) => {
  Tutorial.findAll({ where: { published: true } })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

This controller can be modified a little to return pagination response:

{
    "totalItems": 8,
    "tutorials": [...],
    "totalPages": 3,
    "currentPage": 1
}

You can find more details at:
Server side Pagination in Node.js with Sequelize and MySQL

Define Routes

When a client sends request for an endpoint using HTTP request (GET, POST, PUT, DELETE), we need to determine how the server will response by setting up the routes.

These are our routes:

  • /api/tutorials: GET, POST, DELETE
  • /api/tutorials/:id: GET, PUT, DELETE
  • /api/tutorials/published: GET

Create a turorial.routes.js inside app/routes folder with content like this:

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

  var router = require("express").Router();

  // Create a new Tutorial
  router.post("/", tutorials.create);

  // Retrieve all Tutorials
  router.get("/", tutorials.findAll);

  // Retrieve all published Tutorials
  router.get("/published", tutorials.findAllPublished);

  // Retrieve a single Tutorial with id
  router.get("/:id", tutorials.findOne);

  // Update a Tutorial with id
  router.put("/:id", tutorials.update);

  // Delete a Tutorial with id
  router.delete("/:id", tutorials.delete);

  // Delete all Tutorials
  router.delete("/", tutorials.deleteAll);

  app.use('/api/tutorials', router);
};

You can see that we use a controller from /controllers/tutorial.controller.js.

We also need to include routes in server.js (right before app.listen()):

...

require("./app/routes/turorial.routes")(app);

// set port, listen for requests
const PORT = ...;
app.listen(...);

Test the APIs

Run our Node.js application with command: node server.js.
The console shows:

Server is running on port 8080.
Executing (default): DROP TABLE IF EXISTS `tutorials`;
Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `tutorials`
Drop and re-sync db.

Using Postman, we’re gonna test all the Apis above.

  1. Create a new Tutorial using POST /tutorials Api


    2. node-js-express-sequelize-mysql-example-demo-create-object

    After creating some new Tutorials, you can check MySQL table:

    mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  2 | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:16:08 | 2019-12-13 01:16:08 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         0 | 2019-12-13 01:16:24 | 2019-12-13 01:16:24 |
|  4 | Vue Tut #4        | Tut#4 Description |         0 | 2019-12-13 01:16:48 | 2019-12-13 01:16:48 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         0 | 2019-12-13 01:16:58 | 2019-12-13 01:16:58 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

  2. Retrieve all Tutorials using GET /tutorials Api


    3. node-js-express-sequelize-mysql-example-demo-retrieve-objects

  3. Retrieve a single Tutorial by id using GET /tutorials/:id Api


    4. node-js-express-sequelize-mysql-example-demo-get-single-object

  4. Update a Tutorial using PUT /tutorials/:id Api


    5. node-js-express-sequelize-mysql-example-demo-update-object

    Check tutorials table after some rows were updated:

    mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  2 | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:16:08 | 2019-12-13 01:16:08 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         1 | 2019-12-13 01:16:24 | 2019-12-13 01:22:51 |
|  4 | Vue Tut #4        | Tut#4 Description |         1 | 2019-12-13 01:16:48 | 2019-12-13 01:25:28 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         1 | 2019-12-13 01:16:58 | 2019-12-13 01:25:30 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

  5. Find all Tutorials which title contains ‘node’: GET /tutorials?title=node


    6. node-js-express-sequelize-mysql-example-demo-find-objects

  6. Find all published Tutorials using GET /tutorials/published Api


    7. node-js-express-sequelize-mysql-example-demo-find-active-objects

  7. Delete a Tutorial using DELETE /tutorials/:id Api


    8. node-js-express-sequelize-mysql-example-demo-delete-object

    Tutorial with id=2 was removed from tutorials table:

    mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         1 | 2019-12-13 01:16:24 | 2019-12-13 01:22:51 |
|  4 | Vue Tut #4        | Tut#4 Description |         1 | 2019-12-13 01:16:48 | 2019-12-13 01:25:28 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         1 | 2019-12-13 01:16:58 | 2019-12-13 01:25:30 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

  8. Delete all Tutorials using DELETE /tutorials Api


    9. node-js-express-sequelize-mysql-example-demo-delete-all-objects

    Now there are no rows in tutorials table:

    mysql> SELECT * FROM tutorials;
Empty set (0.00 sec)

You can use the Simple HTTP Client using Axios to check it.

axios-request-example-get-post-put-delete

Or: Simple HTTP Client using Fetch API

Conclusion

Today, we’ve learned how to create Node.js Rest Apis with an Express web server. We also know way to add configuration for MySQL database & Sequelize, create a Sequelize Model, write a controller and define routes for handling all CRUD operations.

You can find more interesting thing in the next tutorial:
– Server side Pagination in Node.js with Sequelize and MySQL
Return pagination data in response:

{
    "totalItems": 8,
    "tutorials": [...],
    "totalPages": 3,
    "currentPage": 1
}

– Deploying/Hosting Node.js app on Heroku with MySQL database

Or you can save Image to MySQL database:
Upload/store images in MySQL using Node.js, Express & Multer

Happy learning! See you again.

Further Reading

Upload Tutorial data from file to MySQL database table:

Fullstack CRUD Application:
– Vue.js + Node.js + Express + MySQL example
– Vue.js + Node.js + Express + MongoDB example
– Angular 8 + Node.js Express + MySQL example
– Angular 10 + Node.js Express + MySQL example
– Angular 11 + Node.js Express + MySQL example
– Angular 12 + Node.js Express + MySQL example
– Angular 13 + Node.js Express + MySQL example
– Angular 14 + Node.js Express + MySQL example
– Angular 15 + Node.js Express + MySQL example
– Angular 16 + Node.js Express + MySQL example
– React + Node.js + Express + MySQL example
– React Redux + Node.js Express + MySQL example

File Upload Rest API:
– Node.js Express File Upload Rest API example using Multer
– Google Cloud Storage with Node.js: File Upload example

Deployment:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Express and MySQL example – Docker Compose

Source code

You can find the complete source code for this example on Github.

If you want to add Comments for each Tutorial. It is the One-to-Many Association, there is a tutorial for that Relationship: Sequelize Associations: One-to-Many example – Node.js, MySQL

Or you can add Tags for each Tutorial and add Tutorials to Tag (Many-to-Many Relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Using SQL Server instead: Node.js CRUD example with SQL Server (MSSQL)

Typescript version: Node.js Typescript REST API with MySQL example

137 thoughts to “Node.js Rest APIs example with Express, Sequelize & MySQL”

  1. Hello, there is typo in article:
    turorial instead tutorial >>> ‘r’ insted ‘t’
    2x in article turorial.routes

    Thanks for this article – you help me understand a lot of backend.

  3. Thank you!!! I’ve worked all day trying to understand what was going on. This is great!

  6. Hello, this tutorial is good! But I have an error while creating the routes:

    TypeError: Router.use() requires a middleware function but got a Object
    at Function.use (C:\Users\Gabriel\Desktop\Remindy\node_modules\express\lib\router\index.js:458:13)
    at Function. (C:\Users\Gabriel\Desktop\Remindy\node_modules\express\lib\application.js:220:21)
    at Array.forEach ()
    at Function.use (C:\Users\Gabriel\Desktop\Remindy\node_modules\express\lib\application.js:217:7)
    at module.exports (C:\Users\Gabriel\Desktop\Remindy\app\routes\user.routes.js:28:7)
    at Object. (C:\Users\Gabriel\Desktop\Remindy\server.js:31:36)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)

    All the solutions I found on internet was only to exports the routers, but it didnt work for me, any ideas?

  7. Bezkoder, you have been extremely helpful to me. You have how to deploy to heroku, but how do I deploy this to any Linux/RedHat server to an existing URL? I also used your CRUD instructions for the front end. Obviously I have to change the mysql log in info in the back end app, but there are other things like relative links not resolving on the front end. I put “homepage”: “./” on the CRUD app, and it works within the app, but address bar URL is not the full path that includes the original url, so you can’t go back in history or refresh. Also, the connection to the mysql is refused, but running from within vcode on the same server, it works fine with the db. Any help is greatly appreciated. Thank you. :)

  8. Hi, thanks for the tutorial. I would like to clarify somethings. Like is there a reason why you code things a certain way? like are they common practices? Cause i am new to nodejs I realize that the file structure of your code differs slightly from most of the other codes that I have seen but they dont specifically touch on sequelize. Like forexample require(“./tutorial.model.js”)(sequelize, Sequelize). or even module.exports = (sequlize, Sequlize) => {}
    most code usually split the code up and then export it module.exports = funcName; Just wanted to know if this is a common practice or just your own style. Thanks and I appreciate the time taken you have spent regardless of mine, for replying all the questions asked. Thanks.

    1. My bad i think i mistook the code… haha got confused by the order of the files… Anyways, thanks for the tutorial.

  10. This is an excelent tutorial. Just one confusion is there.
    While creating tables, I see a sequelize with a small “s” and another with a capital “S”.
    What is the difference.

    1. Hi, if we use OOP language, we can say that one is for Sequelize object (instance) and one is for Sequelize class.

  12. Hi
    Please help,
    There is no body object in request in create record.
    I have Content-Type:application/json in my postman headers.
    Thankx

  13. Hi,
    I am a beginner so thank you so much for this tutorial. I don’t know what I did wrong but when I run “node server.js” and i try to make a request with postman I get this error :

    {
    “message”: “Content can not be empty!”
    }

    i am using : app.use(express.json()); in server.js insted of body-parser because they said its deprecated

    “body-parser”: “^1.19.0”,
    “cors”: “^2.8.5”,
    “express”: “^4.17.1”,
    “mysql2”: “^2.2.5”,
    “sequelize”: “^6.6.2”

    in the postman configuration i have ra enabled with json format
    need helpp please

  14. Wonderful tutorial! I actually added your website to my favorites list. Thanks a lot.

  16. I am getting a typeError that says Tutorial.getAll is not a function. Creating and saving a tutorial to the database works fine, but the rest of functionalities give the same typeError.

  18. Hi,
    I am a beginner so thank you so much for this tutorial. I don’t know what I did wrong but when I run “node server.js” I get this error :
    ReferenceError: req is not defined
    at Object. (C:\PII\horizon-virtual-academy\express-sequelize\app\controllers\user.controller.js:21:17)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
    at Module.load (internal/modules/cjs/loader.js:928:32)
    at Function.Module._load (internal/modules/cjs/loader.js:769:14)
    at Module.require (internal/modules/cjs/loader.js:952:19)
    at require (internal/modules/cjs/helpers.js:88:18)
    at module.exports (C:\PII\horizon-virtual-academy\express-sequelize\app\routes\user.routes.js:2:19)
    at Object. (C:\PII\horizon-virtual-academy\express-sequelize\server.js:31:36)
    at Module._compile (internal/modules/cjs/loader.js:1063:30)

    Could someone help me please ?

    1. look like you have problem with middleware of express, find more info at https://expressjs.com/en/guide/writing-middleware.html then check again file server.js and turorial.routes.js, may be there something syntax error or missing require library when init controller from route, finally you dont get anything from your code, just get source code of author and run :))))

  19. Hello Bezkoder,

    really nice tutorial. trying to run the vuejs and react front end to consume this web api
    how can i add another url in the corsOptions. I want to add http://localhost:8082

    var corsOptions = {
  origin: "http://localhost:8081"
};

    1. Hi, this is the way to allow multiple CORS domains:

      var allowedDomains = ['http://localhost:8081', 'http://localhost:8082'];
app.use(cors({
  origin: function (origin, callback) {
    // bypass the requests with no origin (like curl requests, mobile apps, etc )
    if (!origin) return callback(null, true);
 
    if (allowedDomains.indexOf(origin) === -1) {
      var msg = `This site ${origin} does not have an access. Only specific domains are allowed to access it.`;
      return callback(new Error(msg), false);
    }
    return callback(null, true);
  }
}));

  21. Thank you for this tutorial!
    this has been so helpful in a homework
    For a beginne in these technologies it was really helpful and explained well!
    Keep up the good work
    By the way, it would’ve been interesting if you added a part where you add Sorting functionality to this App.

  23. Running in Windows 10.
    Getting following Error:
    C:\nodejs-express-sequelize-mysql>node server.js
    Server is running on port 8080.
    Unhandled rejection SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:3306

    What command to start mysql server in windows?

    1. Had to zip download from MySQL and followed this to initialize and start my MySQL database.
      Successfully created the testdb after that using MySQL console. Tutorial worked without any issues after that.

  25. Thank you for the tutorial, I think it is really helpful. I been trying to create a nodejs express+ MySQL backend with angular. Everything almost works fine but i keep getting errors trying to fetch my already existing database for my CRUD. I don’t know if anyone came across the same issue. I followed everything unless i made a typo or something is up with my route or controller for CRUD operations.
    TypeError: Cannot read property ‘findAll’ of undefined
    at exports.findAll (C:\Users\chris\development\Javascript\fullstackJSapps\angular-express-node-mysql-fullstack-app\nodejs-backend-server\controllers\admin.controller.js:64:10)

    1. Nevermind, I fixed it. I had a typo where i forgot to initialize my user model objects for my CRUD operations in my dbconfig.js file.

  26. Hey Bezkoder,
    I’m an absolute beginner and I followed the tutorial step by step till this step: “Test the APIs
    Run our Node.js application with command: node server.js.” After this, I get tis error. I have literally spent the whole night trying to fix it but can’t find the reason. Please HELP!!!!!!!!!!

    Server is running on port 8080.
    (node:49788) UnhandledPromiseRejectionWarning: SequelizeAccessDeniedError: Access denied for user ”@’localhost’ (using password: YES)
    at ConnectionManager.connect (D:\nodejs-express-sequelize-mysql\node_modules\sequelize\lib\dialects\mysql\connection-manager.js:118:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    (node:49788) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `–unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
    (node:49788) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

  27. Hi thank you so much for doing this for us, just have one question, since I´m trying to implement this tutorial with the front end on angular 10 (https://bezkoder.com/angular-10-crud-app/) this root directory for node where I do create it with relation to the angular root folder? both are separately? ore one is inside the other?
    thanks in advance.

  29. This is very helpful and its works for my solutions. Thank you. I appreciate your simplicity

  31. Hi, Bezkoder! This is an excellent tutorial!
    There’s just a line of code that I really do not understand.
    This line: require(“./tutorial.model.js”)(sequelize, Sequelize)
    I don’t get it. What’s supose to do (sequelize, Sequelize), is this something related with JavaScript, with the node’s require function or is it because of sequelize. I’ve been checking the sequelize documentation but I didn’t see anything similar. :(

    I’d appreciate a lot if someone could explain me.

    1. Sorry!!! Forget it. I hadn’t seen how the tutorials model is defined.

      module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

      Now I understand is a JavaScript thing. lol

  32. Helloo.. thanks for this post.. its really help me a lot
    i’m new in website developing.. i wanna ask, how to upload the vue js + this node.js code into web server?
    thank you so much bezkoder!

  35. Hello Sir, I’m really thankful for this tutorial because I just recently learning Node.js, React.js, and MySQL individually and don’t have the idea how to connect them all. So, thank you to you, Sir!

    I just started with this tutorial to make node.js backend and follow all the steps above but I have errors like this:

    Server is running on port 8080.
    (node:16820) UnhandledPromiseRejectionWarning: SequelizeConnectionError: Unknown database ‘testdb’
    at ConnectionManager.connect (C:\Users\user\nodejs-express-sequelize-mysql\node_modules\sequelize\lib\dialects\mysql\connection-manager.js:126:17)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    (node:16820) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `–unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
    (node:16820) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate
    the Node.js process with a non-zero exit code.

    It seems that there is no testdb database, so, am I missed out some tutorial before this? or any suggestion from you Sir to resolve this error?

    Please Kindly Reply :)

      1. okay, so just an empty database then the title, description, and published will be created in localhost:8080/api/tutorials ? thank you sir!

  36. I just worked my way through it and it worked fine. Excellent tutorial. Thank you, Now to keep going and find out more about the Front end
    :)
    Cheers.

  37. One cannot find an easier well explained tutorial for connecting MySql with Node JS
    Great Work Man !!!👌👌👌👌👌

  38. Hello,
    I tried to combine two of your tutorials together Node.js Rest APIs example with Express, Sequelize & MySQL and Server side Pagination in Node.js with Sequelize & MySQL. The problem appears when I press edit and the next page doesn’t appear even thought the id of specific tutorial in URL changes. When i did your tutorials separably everything worked fine. That’s how it looks like https://i.imgur.com/zfM0v9f.png

    1. I’m sorry, I made a mistake with one of tutorial’s name I meant React + Node.js Express: User Authentication with JWT example and Server side Pagination in Node.js with Sequelize & MySQL

  39. Not able to find request body for http post method :
    Cannot read property ‘title’ of undefined
    at exports.create (F:\projects\sample1\controller\tutorial.controller.js:11:18)
    at Layer.handle [as handle_request] (F:\projects\sample1\node_modules\express\lib\router\layer.js:95:5)
    at next (F:\projects\sample1\node_modules\express\lib\router\route.js:137:13)
    at Route.dispatch (F:\projects\sample1\node_modules\express\lib\router\route.js:112:3)
    at Layer.handle [as handle_request] (F:\projects\sample1\node_modules\express\lib\router\layer.js:95:5)
    at F:\projects\sample1\node_modules\express\lib\router\index.js:281:22
    at Function.process_params (F:\projects\sample1\node_modules\express\lib\router\index.js:335:12)
    at next (F:\projects\sample1\node_modules\express\lib\router\index.js:275:10)
    at Function.handle (F:\projects\sample1\node_modules\express\lib\router\index.js:174:3)
    at router (F:\projects\sample1\node_modules\express\lib\router\index.js:47:12)

      1. Hi bezcoder, thanks for the tutorial.

        I am facing the exact same problem as adi with the same error message reported. I tried your suggestion but the error still persists. I noticed in your screenshot that you have 9 headers, but mine has only 8. Is the failure possibly due to a missing header, or is it due to other errors? Can you also screenshot your header so that I can check the 9 headers with my 8 headers? Thanks a lot!

        1. Hi, if you use Postman, in the Body tab, you only need to select raw and JSON.
          It will automatically change the Header :)

  40. Please Reply,
    I tried to coding follow you but it show this erorr.

    (node:25800) UnhandledPromiseRejectionWarning: SequelizeConnectionRefusedError: connect ECONNREFUSED 139.59.223.169:3306
    at ConnectionManager.connect (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:116:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async ConnectionManager._connect (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:318:24)
    at async /Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:250:32
    at async ConnectionManager.getConnection (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:280:7)
    at async /Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/sequelize.js:613:26
    at async MySQLQueryInterface.dropTable (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/dialects/abstract/query-interface.js:243:5)
    at async Function.drop (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/model.js:1388:12)
    at async Sequelize.drop (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/sequelize.js:849:33)
    at async Sequelize.sync (/Users/sparghetti/Projects/surveys-api/node_modules/sequelize/lib/sequelize.js:775:7)
    (Use `node –trace-warnings …` to show where the warning was created)
    (node:25800) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `–unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
    (node:25800) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

  41. Thanks for your tutorial,
    I just have a question: In the server.js file, how to use to routes instead of one

    require(“./app/routes/turorial.routes”)(app);

  42. Hii, first of all its a really helpful tutorial and easy to understand to a newbie like me.
    Just one query I have for you.
    Here you just have one table in the database but I have 9 tables in my project which are linked with each other via foreign keys.
    To get, update or delete information I have to join 2 or more tables and update all the fields . How can I do it using this code as a reference .
    Also if I update data in the front end will it reflect in my MySQL database. Please clear this doubt as well.
    Please help me with this.
    Thanks in advance :)

  43. hi, the content is awesome !
    anyway, how to deploy node app with sequelize mysql to heroku?

  45. It’s time to take care of a key point on our RESTful API: the security. We have our endpoints to manage users and tasks, but right now any client that knows where our API is running can access the endpoints. This is not safe, in the real world you’d want to control who has access to your API and you need to check with every request that it’s coming from a trusted source.

  46. Top notch work saving thousands of hours for thousands of people. Thanks!

  47. PLEASE REPLY. THANK YOU.
    Hello, ive followed the instructions step by step but keep getting this error and the api isnt responding on postman:

    (node:10716) [SEQUELIZE0004] DeprecationWarning: A boolean value was passed to options.operatorsAliases. This is a no-op with v5 and should be removed.
    Server is running on port 8080.
    (node:10716) UnhandledPromiseRejectionWarning: SequelizeAccessDeniedError: Access denied for user ‘root’@’localhost’ (using password: YES)
    at ConnectionManager.connect (C:\Users\Ecstasy-Net\nodejs-express-sequelize-mysql\node_modules\sequelize\lib\dialects\mysql\connection-manager.js:118:17) at processTicksAndRejections (internal/process/task_queues.js:97:5)
    (node:10716) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `–unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
    (node:10716) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

    1. Hi, you should create database with correct host, username, password, port. Then modify the configuration in the source code.

    2. in db.config.js file delete the line where it says ( “operatorsAliases”: false ), it’s version 5 so it’s depreciated, it out date in a way.

      1. Hello Sam,
        I’ve been struggling on the same issue because it was my very first time with mysql.
        First you need to install mysql-server on your computer.

        //installation
        shell> sudo apt install mysql-server
        //Start the service
        shell> sudo systemctl start mysql-server
        //Then to enter your mysql instance
        shell> sudo mysql
        //create a user
        mysql > CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
        //give to the newuser all PRIVILEGES
        mysql > GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
        //then flush
        mysql > FLUSH PRIVILEGES;
        //create the Database
        mysql > CREATE DATABASE ‘dbName’;
        //quit
        mysql > quit

        Now that you’ve created a user you can log in your mysql-server through a shell thanks to
        shell> mysql -u USERNAME -p
        or directly to the database with
        shell> mysql -h host -u user -p dbName

        Don’t forget to replace the variable in your db.config.js project’s file.
        Please for more information check https://dev.mysql.com/doc/
        Paulo

  48. I’ve been struggling with this for hours, and this tutorial finally helped me get it! I can’t thank you enough!!

  49. Very well explained. I would like to follow more tutorial by you. Thanks again for your explanation.

  50. Very great tutorial! but i have a problem, my api keep loading and never finished, anyone know how to solve this?
    nothing error from executing “nodemon app.js”

  51. Hi! Thank you so much for this!! Every time I try to run the POST request (the first step of the Testing APIs section), I get “message”: “Content can not be empty!”. I have ‘Content-Type’ ‘application/json’ set in my header tab. Do you have any tips? Thank you so much!

    1. hi, you have to put or “post” in some values/data in the “body” part too.

  53. Gettng Error : Unhandled rejection SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:3306

    1. Hi, please make sure that the database was running, then check your database configuration.

      1. Hi bezkoder, the tutorial is great but I also get access error and do not know what you mean by “check the database is running”. Should I be trying different port numbers? Stuck. Error below:
        PS C:\MyCode\nodejs-express-sequelize-mysql> node server.js
        node:events:353
        throw er; // Unhandled ‘error’ event
        ^

        Error: listen EACCES: permission denied 0.0.0.0:8080
        at Server.setupListenHandle [as _listen2] (node:net:1278:21)
        at listenInCluster (node:net:1343:12)
        at Server.listen (node:net:1429:7)
        at Function.listen (C:\MyCode\nodejs-express-sequelize-mysql\node_modules\express\lib\application.js:618:24)
        at Object. (C:\MyCode\nodejs-express-sequelize-mysql\server.js:33:5)
        at Module._compile (node:internal/modules/cjs/loader:1108:14)
        at Object.Module._extensions..js (node:internal/modules/cjs/loader:1137:10)
        at Module.load (node:internal/modules/cjs/loader:973:32)
        at Function.Module._load (node:internal/modules/cjs/loader:813:14)
        at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:76:12)
        Emitted ‘error’ event on Server instance at:
        at emitErrorNT (node:net:1322:8)
        at processTicksAndRejections (node:internal/process/task_queues:80:21) {
        code: ‘EACCES’,
        errno: -4092,
        syscall: ‘listen’,
        address: ‘0.0.0.0’,
        port: 8080
        }

  54. The tutorial is very good and helpful. I tried to use the client Web app to call the mySQL api sample. It shows below error. ( I use MySQL 8.0.20, Angular 8).
    :8080/api/tutorials:1 Failed to load resource: net::ERR_FAILED
    add:1 Access to XMLHttpRequest at ‘http://localhost:8080/api/tutorials’ from origin ‘http://localhost:4200’ has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: The ‘Access-Control-Allow-Origin’ header has a value ‘http://localhost:8081’ that is not equal to the supplied origin.
    add-tutorial.component.ts:35 HttpErrorResponse
    Thank you very much!

    :8080/api/tutorials:1 Failed to load resource: net::ERR_FAILED

    1. Sorry, all work! bezcoder…. thank you for you example lesson. Ну и по русски – спасибо.

        1. Figured it out – I missed adding the “require (“.app/routes/tutorial.routes”) (app)” addition to server.js so server.js didn’t even reference the routes js file we had added that defined how to route all api calls.

  58. Great tutorial! Very easy to follow for a rookie like me :)
    I have one question – I can’t seem to find where you point to your “tutorials” table in your MySQL connection? If I want it point to another table (like the table “clients”) where would I do that?

    Rue

      2. what if we want to add another table ?
        it is just add another line like that below with new table name ?

  60. Thank you so much for this tutorial.
    I’m waiting for new React tutorial, example about associate authentication jwt reactjs nodejs CRUD.

  62. Hi! I stumbled across an issue, i want to work with tables that have foreign keys, but I haven’t been able to incorporate sequelize documentation about fk’s or migrations. Any tips on doing so?

  63. THIS WAS REALLLLLLLLLLY AWESOME !!! I WAS CONFUSED HOW TO CONNECT VUE.JS WITH NODE.JS AND YOUR POST IS SIMPLE AND EFFICIENT…

    thanks :)

  64. Hello, first of all thanks for the awesome tutorial.
    However I am not being able to get the PUT(update using id) and Delete (by id) to work in Postman.
    I am getting the could not get any response message. However the get by id,get all ,Post and delete all work fine.

    1. Hi, please capture your Postman Header tab and Body tab when sending the HTTP requests :)

  65. Hello Bezkoder,
    thank you very much for your tutorial, however I have a problem when posting to create tutorial,
    the parameters “title” and “description are always “NULL”, others one are okay. I inserted directly in mysql too, it’s working. Any infos? Thanks in advance.

    1. Hi, maybe you forgot to set 'Content-Type': 'application/json' in the HTTP request header.

      1. I’m using Postman and I get this message: “message”: “Content can not be empty!”??

        1. You need to open the Header tab, and set the pair: 'Content-Type': 'application/json'.

  69. Hey man! I’m taking an effort to commend you for this amazing tutorial. I’m an iOS Engineer and I’m very much new to Node express. I first saw your Customer Node Express + Controller tutorial, but I need a Sequelizer, and then I found this. :)

    I hope you could continue this as a series and provide authentication stuff.

    Much love from PH.

    G

      1. Yup, I am so lost on how to connect this to the vue.js front end. I manage to get this working on a raspberry pi, but postman is not a library that’s supported on the OS.

  72. Got up to using postman, my backend seems to be working and is creating the table on mysql server but I cant seem to get a post request to work. I cant get any response. Have never used the app could you go into a bit more detail on getting it to work.

    1. Hi, you should set appropriate Header: Content-Type: application/json when sending POST request (Postman Header tab).

      1. Can you please add this in the original blog post. it took me a few minutes to sort out the issue.

  73. Thank a lot, would you like to use koa2 instead of express in next tutorial. Your tutorial is easy to understand for beginner!!

    1. Hi, I’ll write a tutorial for Koa2 when having time. Thanks for your suggestion :)

  74. Thanks, this worked wonders for me. Managed to get my own MVC codes up in a couple of hours.

  75. This tutorial shows me a basic & best way about MVC structure using Node.js Express and Sequelize. Thanks for sharing..

    1. Thank you for your comment. It is the motivation for me to make more tutorial like this to help people :)

      1. I am seeing a few other tutorials side-by-side and this is by far the best.

  79. Any chance of combining this with your express vue jwt authentication tutorial so only admins can edit or delete tutorials?

    1. Yes, you can do it by adding middlewares before controller functions when defining routes.

Comments are closed to reduce spam. If you have any question, please send me an email.