nodejs-rest-api-express-mysql-feature-image

Build Node.js Rest APIs with Express & MySQL

Last modified: September 19, 2020 bezkoder Node.js

Express is one of the most popular web frameworks for Node.js that supports routing, middleware, view system… This tutorial will guide you through the steps of building Node.js Restful CRUD API using Express and interacting with MySQL database.

Before reading the tutorial, please install MySQL in your machine. The installation instructions can be found at Official MySQL installation manual.

Fullstack:
– Vue.js + Node.js + Express + MySQL
– Angular 8 + Node.js + Express + MySQL
– React + Node.js + Express + MySQL

Node.js & MySQL Associations:
– One-to-Many Relationship example

Contents

Application overview
Create Node.js application
Setup Express web server
Create MySQL table
Configure & Connect to MySQL database
Define the Model
Define Routes
Create the Controller
Test the APIs
Conclusion
Further Reading
Source code

Application overview

We will build Rest Apis for creating, retrieving, updating & deleting Customers.

First, we start with an Express web server. Next, we add configuration for MySQL database, create Customer model, write the controller. Then we define routes for handling all CRUD operations:

Methods	Urls	Actions
GET	/customers	get all Customers
GET	/customers/42	get Customer with id=42
POST	/customers	add new Customer
PUT	/customers/42	update Customer with id=42
DELETE	/customers/42	remove Customer with id=42
DELETE	/customers	remove all Customers

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

Our project structure will be like:

Create Node.js application

Open terminal/console, then create a folder for our application:

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

Initialize the Node.js application with a package.json file:

npm init

name: (nodejs-express-mysql) 
version: (1.0.0) 
description: Node.js Restful CRUD API with Node.js, Express and MySQL
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, express, mysql, restapi
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

Next, we need to install necessary modules: express, mysql and body-parser.
Run the command:

npm install express mysql body-parser --save

The package.json file should look like this:

{
  "name": "nodejs-express-mysql",
  "version": "1.0.0",
  "description": "Node.js Restful CRUD API with Node.js, Express and MySQL",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "express",
    "mysql",
    "restapi"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.17.1"
  }
}

Setup Express web server

Now, in the root folder, we create a new file named server.js:

const express = require("express");
const bodyParser = require("body-parser");

const app = express();

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

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

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

// set port, listen for requests
app.listen(3000, () => {
  console.log("Server is running on port 3000.");
});

What we do are:
– import express and body-parser modules. Express is for building the Rest apis, and body-parser helps to parse the request and create the req.body object that we will need to access in our routes.
– create an Express app, then add body-parser middlewares using app.use() method.
– define a GET route which is simple for test.
– listen on port 3000 for incoming requests.

Now we can run the app with command: node server.js.
Open your browser, enter the url http://localhost:3000/, you will see:

Create MySQL table

Before connecting Node.js Application with MySQL, we need a table first.
So run the SQL script below to create customers table:

CREATE TABLE IF NOT EXISTS `customers` (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  email varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  active BOOLEAN DEFAULT false
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Configure & Connect to MySQL database

We’re gonna have a separate folder for configuration. Let’s create config folder in the app folder, under application root folder, then create db.config.js file inside that config folder with content like this:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb"
};

Now create a database connection that uses configuration above.
The file for connection is db.js, we put it in app/models folder that will contain model in the next step.

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

// Create a connection to the database
const connection = mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB
});

// open the MySQL connection
connection.connect(error => {
  if (error) throw error;
  console.log("Successfully connected to the database.");
});

module.exports = connection;

Define the Model

In models folder, create a file called customer.model.js. We’re gonna define constructor for Customer object here, and use the database connection above to write CRUD functions:

create a new Customer
find a Customer by id
get all Customers
update a Customer by id
remove a Customer
remove all Customers

This is the content inside customer.model.js:

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

// constructor
const Customer = function(customer) {
  this.email = customer.email;
  this.name = customer.name;
  this.active = customer.active;
};

Customer.create = (newCustomer, result) => {
  sql.query("INSERT INTO customers SET ?", newCustomer, (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(err, null);
      return;
    }

    console.log("created customer: ", { id: res.insertId, ...newCustomer });
    result(null, { id: res.insertId, ...newCustomer });
  });
};

Customer.findById = (customerId, result) => {
  sql.query(`SELECT * FROM customers WHERE id = ${customerId}`, (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(err, null);
      return;
    }

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

    // not found Customer with the id
    result({ kind: "not_found" }, null);
  });
};

Customer.getAll = result => {
  sql.query("SELECT * FROM customers", (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(null, err);
      return;
    }

    console.log("customers: ", res);
    result(null, res);
  });
};

Customer.updateById = (id, customer, result) => {
  sql.query(
    "UPDATE customers SET email = ?, name = ?, active = ? WHERE id = ?",
    [customer.email, customer.name, customer.active, id],
    (err, res) => {
      if (err) {
        console.log("error: ", err);
        result(null, err);
        return;
      }

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

      console.log("updated customer: ", { id: id, ...customer });
      result(null, { id: id, ...customer });
    }
  );
};

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

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

    console.log("deleted customer with id: ", id);
    result(null, res);
  });
};

Customer.removeAll = result => {
  sql.query("DELETE FROM customers", (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(null, err);
      return;
    }

    console.log(`deleted ${res.affectedRows} customers`);
    result(null, res);
  });
};

module.exports = Customer;

Customer model is simple, it contains fields: email, name & active.

We use database connection query() method to execute MySQL script: INSERT, SELECT, UPDATE, DELETE. You can find more details about mysql module at: https://www.npmjs.com/package/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 reponse. It’s why we’re gonna setup the routes.

These are routes we define:

/customers: GET, POST, DELETE
/customers/:customerId: GET, PUT, DELETE

Create a routes folder inside app folder, then create customer.routes.js file with content like this:

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

  // Create a new Customer
  app.post("/customers", customers.create);

  // Retrieve all Customers
  app.get("/customers", customers.findAll);

  // Retrieve a single Customer with customerId
  app.get("/customers/:customerId", customers.findOne);

  // Update a Customer with customerId
  app.put("/customers/:customerId", customers.update);

  // Delete a Customer with customerId
  app.delete("/customers/:customerId", customers.delete);

  // Create a new Customer
  app.delete("/customers", customers.deleteAll);
};

You can see that we use a controller from /controllers/customer.controller.js. It contains methods for handling CRUD operations and will be created in the next step.

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

...

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

app.listen(...);

Create the Controller

Now we create a controllers folder inside app folder, then we have a file named customer.controller.js. Our controller will be written inside this with CRUD functions:

create
findAll
findOne
update
delete
deleteAll

const Customer = require("../models/customer.model.js");

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

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

// Find a single Customer with a customerId
exports.findOne = (req, res) => {
  
};

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

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

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

Let’s implement these functions.

Create a new object

Create and Save a new Customer:

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

  // Create a Customer
  const customer = new Customer({
    email: req.body.email,
    name: req.body.name,
    active: req.body.active
  });

  // Save Customer in the database
  Customer.create(customer, (err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while creating the Customer."
      });
    else res.send(data);
  });
};

Retrieve all objects

Retrieve all Customers from the database:

exports.findAll = (req, res) => {
  Customer.getAll((err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving customers."
      });
    else res.send(data);
  });
};

Retrieve a single object

Find a single Customer with a customerId:

exports.findOne = (req, res) => {
  Customer.findById(req.params.customerId, (err, data) => {
    if (err) {
      if (err.kind === "not_found") {
        res.status(404).send({
          message: `Not found Customer with id ${req.params.customerId}.`
        });
      } else {
        res.status(500).send({
          message: "Error retrieving Customer with id " + req.params.customerId
        });
      }
    } else res.send(data);
  });
};

Update an object

Update a Customer identified by the customerId in the request:

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

  Customer.updateById(
    req.params.customerId,
    new Customer(req.body),
    (err, data) => {
      if (err) {
        if (err.kind === "not_found") {
          res.status(404).send({
            message: `Not found Customer with id ${req.params.customerId}.`
          });
        } else {
          res.status(500).send({
            message: "Error updating Customer with id " + req.params.customerId
          });
        }
      } else res.send(data);
    }
  );
};

Delete an object

Delete a Customer with the specified customerId in the request:

exports.delete = (req, res) => {
  Customer.remove(req.params.customerId, (err, data) => {
    if (err) {
      if (err.kind === "not_found") {
        res.status(404).send({
          message: `Not found Customer with id ${req.params.customerId}.`
        });
      } else {
        res.status(500).send({
          message: "Could not delete Customer with id " + req.params.customerId
        });
      }
    } else res.send({ message: `Customer was deleted successfully!` });
  });
};

Delete all objects

Delete all Customers from the database:

exports.deleteAll = (req, res) => {
  Customer.removeAll((err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while removing all customers."
      });
    else res.send({ message: `All Customers were deleted successfully!` });
  });
};

Test the APIs

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

Server is running on port 3000.
Successfully connected to the database.

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

Create a new Customer using POST /customers Api

nodejs-rest-api-express-mysql-test-create

After creating some new Customers, we can check MySQL table:

mysql> SELECT * FROM customers;
+----+--------------------+--------+--------+
| id | email              | name   | active |
+----+--------------------+--------+--------+
|  1 | bezkoder@gmail.com | zKoder |      1 |
|  2 | jack123@gmail.com  | Jack   |      0 |
|  3 | drhelen@gmail.com  | Helen  |      0 |
+----+--------------------+--------+--------+

Retrieve all Customers using GET /customers Api

nodejs-rest-api-express-mysql-test-retrieve-all

Retrieve a single Customer by id using GET /customers/:customerId Api

nodejs-rest-api-express-mysql-test-retrieve-one

Update a Customer using PUT /customers/:customerId Api

nodejs-rest-api-express-mysql-test-update

Check customers table after a row was updated:

mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | bezkoder@gmail.com | zKoder   |      1 |
|  2 | jack123@gmail.com  | Jack     |      0 |
|  3 | drhelen@gmail.com  | Dr.Helen |      1 |
+----+--------------------+----------+--------+

Delete a Customer using DELETE /customers/:customerId Api

nodejs-rest-api-express-mysql-test-delete-one

Customer with id=2 was removed from customers table:

mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | bezkoder@gmail.com | zKoder   |      1 |
|  3 | drhelen@gmail.com  | Dr.Helen |      1 |
+----+--------------------+----------+--------+

Delete all Customers using DELETE /customers Api

nodejs-rest-api-express-mysql-test-delete-all

Now there are no rows in customers table:

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

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, create a model, write a controller and define routes for handling all CRUD operations.

You can find more interesting thing in the next tutorial:
Deploying/Hosting Node.js app on Heroku with MySQL database

If you want to use Sequelize to reduce boilerplate code, there is a post for this:
Node.js Rest APIs example with Express, Sequelize & MySQL

Happy learning! See you again.

Source code

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

137 thoughts to “Build Node.js Rest APIs with Express & MySQL”

prowebkarawang says:
October 4, 2019 at 10:36 am
The tutorial is concise and clear and very helpful, thanks.
Reply
1. bezkoder says:
  October 6, 2019 at 6:38 am
  Hi, thank you 🙂
  Hope it help you.
  Reply
  1. Suju says:
    September 9, 2020 at 10:48 am
    Very helpful, thanks bro.
    Reply
  2. Lesego says:
    September 18, 2020 at 8:55 am
    Thank you very much, this was very helpful. From now on my project will be structured in this fashion
    Reply
2. KIRANDEEP KAUR says:
  August 4, 2020 at 12:24 am
  where to create the object as I am learning don’t have much knowledge
  Reply
Federico says:
October 24, 2019 at 2:21 am
You’re really awesome bro!
Reply
1. bezkoder says:
  October 24, 2019 at 3:18 am
  Hi, thank you for your comment! ?
  Hope this help.
  Reply
gus abas says:
October 27, 2019 at 12:24 pm
i have a problem when i try, the message is “cannot find module ‘..controllers/customer.contorller.js”
please help
Reply
1. bezkoder says:
  October 27, 2019 at 1:43 pm
  Hi, there is a typo: contorller. It should be customer.controller.js.
  Reply
  1. fobb says:
    April 9, 2020 at 3:11 pm
    I still get error of reading req.body
    Reply
    1. bezkoder says:
      April 10, 2020 at 12:16 pm
      Make sure that you set 'Content-Type': 'application/json' for HTTP request Header.
      Reply
      1. Pankaj says:
        April 22, 2020 at 10:55 am
        Cannot POST /customers when testing api
      2. bezkoder says:
        April 22, 2020 at 10:56 pm
        Maybe you forgot to add Content Type to HTTP header.
    2. Junior says:
      December 16, 2020 at 4:14 pm
      I was wrong to declare the body-parser after the routes. Try this:
      const app = express();
      app.use(bodyParser.urlencoded({ extended: true }));
      app.use(bodyParser.json());
      require(“./app/routes/customer.routes.js”)(app);
      Reply

I got a problem when i post a request to save new customers

_http_outgoing.js:470
    throw new ERR_HTTP_HEADERS_SENT('set');
    ^

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at ServerResponse.setHeader (_http_outgoing.js:470:11)
    at ServerResponse.header (D:\NodeJS\webtuthien\node_modules\express\lib\response.js:771:10)
    at ServerResponse.send (D:\NodeJS\webtuthien\node_modules\express\lib\response.js:170:12)
    at done (D:\NodeJS\webtuthien\node_modules\express\lib\response.js:1008:10)
    at Immediate. (D:\NodeJS\webtuthien\node_modules\express-handlebars\lib\utils.js:26:13)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)

bezkoder says:
October 29, 2019 at 2:47 pm
Hi, please show me more details about your HTTP POST request.
Reply
1. Bhargavi Balusu says:
  June 4, 2020 at 4:48 am
  Hi,
  What are the headers you have used. because i tried to do POST, but got this message,
  {
  “message”: “ER_BAD_NULL_ERROR: Column ‘username’ cannot be null”
  }
  Reply
  1. bezkoder says:
    June 4, 2020 at 1:22 pm
    Hi, it is Content-type: application/json 🙂
    Reply

golavo says:
November 7, 2019 at 9:46 am
Very helpful, thanks for this tutorial.
Reply
Axel says:
December 31, 2019 at 4:51 am
Hello
im dealing with another issue and can’t figure it out
Server is running on port 3000.
Successfully connected to the database.
error: Error: ER_BAD_NULL_ERROR: Column ’email’ cannot be null
at Query.Sequence._packetToError (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket. (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/Connection.js:91:28)
at Socket. (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/Connection.js:525:10)
at Socket.emit (events.js:305:20)
at addChunk (_stream_readable.js:341:12)
——————–
at Protocol._enqueue (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/home/axel/nodejs-express-mysql/node_modules/mysql/lib/Connection.js:201:25)
at Function.Customer.create (/home/axel/nodejs-express-mysql/app/models/customer.model.js:11:7)
at exports.create (/home/axel/nodejs-express-mysql/app/controllers/customer.controller.js:20:12)
at Layer.handle [as handle_request] (/home/axel/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at next (/home/axel/nodejs-express-mysql/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/home/axel/nodejs-express-mysql/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/home/axel/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at /home/axel/nodejs-express-mysql/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/home/axel/nodejs-express-mysql/node_modules/express/lib/router/index.js:335:12) {
code: ‘ER_BAD_NULL_ERROR’,
errno: 1048,
sqlMessage: “Column ’email’ cannot be null”,
sqlState: ‘23000’,
index: 0,
sql: ‘INSERT INTO customers SET `email` = NULL, `name` = NULL, `active` = NULL’
}
Thanks
Reply
1. bezkoder says:
  January 1, 2020 at 1:12 am
  Hi, please tell me which HTTP request along with payload you sent to the server.
  Reply
2. Rosapink says:
  January 25, 2020 at 6:30 am
  You have to set headers for content-type: application/json & content-type: x-www-form-urlencoded
  Reply
  1. Deni says:
    January 30, 2020 at 1:17 am
    my problem same with Axel, how to fix it? please…
    Reply
    1. bezkoder says:
      January 30, 2020 at 8:12 am
      Maybe you forgot to set headers for content-type: application/json & content-type: x-www-form-urlencoded on REST Client requests 🙂
      Reply
      1. hope says:
        February 5, 2020 at 6:55 pm
        Where do I set headers? I’m confused
      2. bezkoder says:
        February 6, 2020 at 2:20 am
        Hi, for example, with Postman, you can see Header tab (besides Body tab) where we set header for HTTP request 🙂
  2. Yasir says:
    April 27, 2020 at 10:22 am
    Thanks r million
    Reply
Roberta says:
January 13, 2020 at 8:42 am
Thank You for the tutorial! Helped a lot!
Reply
1. Roberta says:
  January 14, 2020 at 12:59 pm
  For making many to many relationship need to use Sequelize or can make it without it?
  Reply
2. bezkoder says:
  January 16, 2020 at 7:58 am
  Yes, you can make many-to-many relationship with table structure as Sequelize. But you will need to write more boilerplate.
  Reply
Nick says:
February 6, 2020 at 9:53 am
Thank you for the tutorial, it really helps me a lot! I’m still a beginner so there are some parts I don’t understand.
Can you please explain to me what the “result” function does in the models? And why does it need two parameters?
Reply
1. bezkoder says:
  February 6, 2020 at 10:06 am
  Hi, one for data, and one for error data 🙂
  Reply
  1. Nick says:
    February 6, 2020 at 10:12 am
    Where do the data go from there? Are they linked to the controller?
    Thanks in advance!
    Reply
    1. bezkoder says:
      February 7, 2020 at 3:48 pm
      Yeah, they are linked to the controller.
      For example:
      Customer.getAll = result => { ... result(null, err); ... };
      Now look at functions in controller:
      Customer.getAll((err, data) => { ... })
      You can see that we use (err, data) => { ... } as result.
      The result parameter of getAll() is a function.
      In other words, we pass a function to getAll() as parameter.
      Reply
      1. Nick says:
        February 10, 2020 at 7:27 am
        Thanks for the explanation! 🙂
      2. bezkoder says:
        February 10, 2020 at 10:03 am
        You’re welcome 🙂
  2. NewBie says:
    May 25, 2020 at 6:55 pm
    coming from OOP where all variables are strictly defined for certain type, for example `DbConnection x`, it’s very hard to adapt to Node’s free-style arbitrary declaration and what it implies. Do you have any articles to help jump-start?
    Thank you very much in advanced.
    Reply
    1. bezkoder says:
      May 26, 2020 at 4:11 am
      Hi, thanks for your suggestion.
      Currently I don’t have much time to write many tutorials that you and other people need (although I really want to do).
      I will try to work for new Typescript style in the future 🙂
      Reply
      1. Santhosh sandy says:
        October 25, 2020 at 6:37 am
        Firstly Thank you for all your beautiful tutorials. If you got time, Please try to post a Node.js, MySQL RestApi CRUD app using typescript.
Jo says:
February 9, 2020 at 6:57 pm
Thanks Bezkoder – great tutorial, super helpful!
Reply
sami says:
February 17, 2020 at 10:03 am
Great tutorial. Can we add user authentication layer with jwt to this customers module?
Reply
1. bezkoder says:
  February 17, 2020 at 11:14 pm
  Hi, you can find how to implement authentication at the post:
  Node.js & JWT – Token Based Authentication & Authorization example
  Reply
abn says:
February 24, 2020 at 9:40 am
The tutorial is concise and clear and very helpful, thanks.
yet If there another table ‘Product’ so that a customer has one or many product
and I want to return a customer with its products, how could I proceed?
Reply
1. bezkoder says:
  February 24, 2020 at 10:45 am
  Hi, I will write a tutorial for your suggestion when having time 🙂
  Reply
CoolKat says:
March 15, 2020 at 2:12 am
I cloned the project from GitHub, and created the MySQL table exactly like it was done in the tutorial, but when I try to post a new customer using Postman, I receive the error:
{
“message”: “ER_BAD_NULL_ERROR: Column ’email’ cannot be null”
}
If I manually put data into MySQL and perform a GET request, I get the data correct data, so something seems to not be working with POST.
Reply
1. bezkoder says:
  March 17, 2020 at 2:24 am
  Hi, did you set appropriate Header: Content-Type: application/json when sending POST request?
  Reply
2. anjana says:
  June 30, 2020 at 10:45 am
  Did you find solution?
  Reply
Vadim says:
March 17, 2020 at 9:36 am
Great tutorial! Exactly what I’ve been searching for. Thank you!
Reply
Ebadullah says:
March 23, 2020 at 6:08 pm
i got a problem when i get a customer by id
“message”: ” Error retrieving customer with id 2″
please help
Reply
1. bezkoder says:
  March 24, 2020 at 3:00 am
  Hi, please make sure that there is a Customer with id=2.
  Reply
minato says:
March 26, 2020 at 6:52 pm
Thank you for this Node CRUD with MySQL tutorial, very inspiring!
Reply
Febrian says:
March 29, 2020 at 8:18 am
Great tutorial and very helpful. thank you 🙂
Reply
Kayode says:
March 31, 2020 at 1:53 pm
Very straight forward and helpful. Thank you.
Reply
Iron Man says:
April 4, 2020 at 4:45 am
Great tutorial. I am not even Node developer but i got the Job done. Thanks A lots man
Reply
Rohit Nikam (Rowhit) says:
April 8, 2020 at 6:05 am
This is very nice and clean and neat tutorial, thank you!
but i have one error, After run node server.js command it shows:
Server is running on port no 3000
Successfully connected to the database!
Using Postman, i test the create new Customers APIs it gives me error in response:
{
“message”: “ER_NO_DB_ERROR: No database selected”
}
Please kindly share your response.
Reply
1. Rohit Nikam (Rowhit) says:
  April 8, 2020 at 6:52 am
  In node mysql, the properties are called database, not db.
  // Create a connection to the database
```
const connection = mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  db: dbConfig.DB
});
```
  So, change your config to:
```
// Create a connection to the database
const connection = mysql.createConnection({
  host: dbConfig.HOST,
  user: dbConfig.USER,
  password: dbConfig.PASSWORD,
  database: dbConfig.DB   //This was changd
});
```
  So sorry it’s my mistake, and thanks again for this tutorial.
  Reply
  1. bezkoder says:
    April 8, 2020 at 10:24 am
    Yeah, I’m so happy to hear that 🙂
    Reply
    1. Rohit Nikam (Rowhit) says:
      April 9, 2020 at 11:45 am
      Hey bezkoder,
      You have any tutorial for promise and promise chain concept using node
      Reply
      1. bezkoder says:
        April 11, 2020 at 2:44 am
        Hi, I will write them when having time 🙂
Therese says:
April 9, 2020 at 11:49 am
Hi! Thanks for a very useful tutorial. Im currently creating a React.js project and i am going to use this tutorial connecting to the client side. Right now i have one frontend folder and this backend folder (this tutorial) but are unsure the best way to connect those two? Thankful for any help..
Reply
1. bezkoder says:
  April 10, 2020 at 1:35 pm
  Hi, you can find the fullstack example that uses React as frontend aand Node.js Express as backend at the post:
  React + Node.js + Express + MySQL example: Build a CRUD App
  Reply
Bruder says:
April 22, 2020 at 9:57 pm
Very clear, easy to implement step by step. This is one of the best Node.js with MySQL tutorials I’ve ever seen.
Thank you!
Reply
Emil Mammadov says:
April 23, 2020 at 4:25 pm
Thank you very much. That was the best.
Reply
Nixon Augustine says:
April 29, 2020 at 11:13 am
Hi, Wonderful tutorial. Really Appreciate the time you took to write the Tutorial.
I have doubt
require(“./app/routes/customer.routes.js”)(app);
is this statement correct ? why is require not assigned to a variable. And (app) is mentioend at the end ?
Reply
1. bezkoder says:
  April 29, 2020 at 11:46 pm
  Hi, you can see that in app/routes/customer.routes.js, we export a function:
```
module.exports = app => {
  ...
}

// or:
const func = app => {
  ...
}

module.exports = func;
```
  So the require("./app/routes/customer.routes.js")(app) is equivalent to:
```
const routeFunction = require("./app/routes/customer.routes.js");
routeFunction(app);
```
  Reply
  1. NewBie says:
    May 23, 2020 at 11:40 pm
    I’m a newbie, still don’t get it. routeFunction() takes parameter?
    my understanding is require() is the combination of declaration and instantiation of a class, for example
    myObj app = myObj();
    Reply
    1. bezkoder says:
      May 23, 2020 at 11:49 pm
      A function is an object, so it can be a function 🙂
      Reply
      1. NewBie says:
        May 25, 2020 at 5:33 pm
        Thank you for explaining. So the two `app` are different.
        can `module.exports = app => {` be interpreted as `module.exports = (app) => {`?
      2. bezkoder says:
        May 26, 2020 at 4:09 am
        Yeah, they are the same 🙂
    2. NewBie says:
      May 23, 2020 at 11:55 pm
      or, is it equivalent to this?
      const routeFunction = require(“./app/routes/customer.routes.js”);
      app.use(‘/’, routeFunction);
      Reply
      1. bezkoder says:
        May 24, 2020 at 1:37 am
        Ah no, you can see that, in ./app/routes/customer.routes.js we export the function:
        module.exports = app => { app.post("/customers", customers.create); app.get("/customers", customers.findAll); app.get("/customers/:customerId", customers.findOne); app.put("/customers/:customerId", customers.update); app.delete("/customers/:customerId", customers.delete); app.delete("/customers", customers.deleteAll); };
        So, the way we use it is equivalent to:
        const routeFunction = require(“./app/routes/customer.routes.js”); routeFunction(app);
        Then the result will be:
        app.post("/customers", customers.create); app.get("/customers", customers.findAll); app.get("/customers/:customerId", customers.findOne); app.put("/customers/:customerId", customers.update); app.delete("/customers/:customerId", customers.delete); app.delete("/customers", customers.deleteAll);
Nixon Augustine says:
April 29, 2020 at 11:39 am
This is an error I get. Instead of Customer ,I have user, but the other code is the same. Where have i made the mistake.
TypeError: User.viewAll is not a function at exports.viewAll (E:\touchtraks\api\v1\controller\user.controller.js:4:7) at Layer.handle [as handle_request] (E:\touchtraks\api\node_modules\express\lib\router\layer.js:95:5) at next (E:\touchtraks\api\node_modules\express\lib\router\route.js:137:13) at Route.dispatch (E:\touchtraks\api\node_modules\express\lib\router\route.js:112:3) at Layer.handle [as handle_request] (E:\touchtraks\api\node_modules\express\lib\router\layer.js:95:5) at E:\touchtraks\api\node_modules\express\lib\router\index.js:281:22 at Function.process_params (E:\touchtraks\api\node_modules\express\lib\router\index.js:335:12) at next (E:\touchtraks\api\node_modules\express\lib\router\index.js:275:10) at urlencodedParser (E:\touchtraks\api\node_modules\body-parser\lib\types\urlencoded.js:82:7) at Layer.handle [as handle_request] (E:\touchtraks\api\node_modules\express\lib\router\layer.js:95:5)
Reply
1. bezkoder says:
  April 29, 2020 at 11:42 pm
  Hi, please show your code here 🙂
  Reply
  1. Nixon Augustine says:
    April 30, 2020 at 5:01 am
    Hi, Really Appreciate you answering my previous question. I have a question, which might soud too basic.
    It would be great if you could answer it .
    Can you explain the flow of control from the time an api request hits the server.
    From Server.js -> where does the control go and what does it check.
    Thanks in advance.
    Reply
    1. bezkoder says:
      April 30, 2020 at 12:01 pm
      You can follow this flow:
      server.js -> routes -> controller -> model.
      Reply
  2. Nixon Augustine says:
    April 30, 2020 at 5:35 am
    I got the error fixed.
    Reply
    1. Yunus Emre says:
      August 22, 2020 at 10:56 pm
      Hey Nixon. Just a quick reminder. When you fix the problem, always share the steps that you have take in. You never know how it might help to someone in future.
      For anyone who is having the same problem, just add “module.exports.Course = Course;” at the end of course.model.js file. We need to export objects, classes to be able reference and use in in other files.
      Reply
      1. Yunus Emre says:
        August 22, 2020 at 11:03 pm
        Sorry just add “module.exports = Course”
ahmed hlaimi says:
May 2, 2020 at 12:01 am
hello awsome tuto bu u have one mistake in require(“./app/routes/customer.routes.js”)(app);
u should delete app from path like this :
require(“./routes/customer.routes.js”)(app); at server.js
Reply
1. bezkoder says:
  May 2, 2020 at 12:22 am
  Hi, because we put server.js in the root folder, so the directory should be “./app/routes/customer.routes.js”. 🙂
  Reply
2. sarah says:
  November 2, 2020 at 7:14 pm
  Hi, i removed /app and it worked for me
  Reply
Karl says:
May 3, 2020 at 2:10 pm
Hi bezkoder,
thks for this tutorial. I m also new to react and try to use this tutorial to train myself but I cant move forward. Can you tell me where and how I should run the mysql query to create the table CUSTOMER?
“So run the SQL script below to create customers table:”
I’ve done it in the root directory but got some errors
Thks in advance
Reply
1. bezkoder says:
  May 4, 2020 at 2:14 am
  Hi, you can use MySQL Workbench or MySQL Client to run MySQL command.
  Reply
Harsh says:
May 6, 2020 at 6:18 pm
Hey there is a mistake in the code .
// Instead of this
Customer.findById = (customerId, result) => {
sql.query(`SELECT * FROM customers WHERE id = ${customerId}`, (err, res) => {
…..}
// Please correct it to
sql.query(`SELECT * FROM customers WHERE id = “${customerId}”`, (err, res) => {
…..}
// add the double quotes in where clause
Thanks for the tutorial though
Cheers!
Reply
Bella says:
May 11, 2020 at 6:19 am
Wow, this is really nice. This is my first workable project with js and it’s so impressive. The next step, how can I link it with my UI? Do you have any nice lesson to suggest? Cheers
Reply
1. Sam says:
  May 14, 2020 at 6:19 pm
  https://bezkoder.com/react-node-express-mysql/
  Here you go
  Reply
kkwaxy says:
May 17, 2020 at 7:28 pm
Thanks a lot. It’s been very helpful for me. i’m newer on nodejs
Reply
david arias says:
May 19, 2020 at 5:06 am
Buenas noches Koder:
Me podrías apoyar con este error… Ya probé varias opciones e investigué otras opciones y nada.
O si puedes dar otra opción. Por favor.
————————————————————————————————————————————————-
require(“./app/routes/customer.routes”)(app)
^
TypeError: require(…) is not a function
at Object. (C:\Cursos\KODER\node-express-mysql\server.js:19:40)
at Module._compile (internal/modules/cjs/loader.js:1185:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1205:10)
at Module.load (internal/modules/cjs/loader.js:1034:32)
at Function.Module._load (internal/modules/cjs/loader.js:923:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:71:12)
at internal/main/run_main_module.js:17:47
PS C:\Cursos\KODER\node-express-mysql>
Reply
1. bezkoder says:
  May 19, 2020 at 9:46 am
  Hi, please show me your code in ./app/routes/customer.routes.
  Reply
Msukma says:
May 20, 2020 at 5:27 am
Hai there, thank for this great tutorial,
I have issue, on showing html when using view template engine (ejs ), on this scenario 1.parsing url with params or more than 1 level url name, but but work well when showing 1 level url
—————————————————————-
app.get(‘/home:params’,function(req,res){
res.render(‘home’)
})
or..
app.get(‘/home/sub’,function(req,res){
res.render(‘home’)
})
thanks,
Reply
hia says:
May 23, 2020 at 11:45 pm
I will recommend to all my friends and colleagues. Thank you very much for this tutorial!
Reply
Michele Bridi says:
June 5, 2020 at 8:01 am
hello, sorry for the ignorance but I would like to know how to create the db, if I have not misunderstood you explain how to connect and do the rest but I do not know how to access / create the db thanks
Reply
Percy says:
June 5, 2020 at 10:37 am
Im getting this error”No database selected”
error: Error: ER_NO_DB_ERROR: No database selected
at Query.Sequence._packetToError (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Query.ErrorPacket (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
at Protocol._parsePacket (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket. (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\Connection.js:88:28)
at Socket. (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:310:20)
at addChunk (_stream_readable.js:286:12)
——————–
at Protocol._enqueue (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Connection.query (C:\Users\master\BackEnd\DeclareApp\code\node_modules\mysql\lib\Connection.js:198:25)
at Function.User.getAll (C:\Users\master\BackEnd\DeclareApp\code\app\models\user.model.js:47:7)
at exports.findAll (C:\Users\master\BackEnd\DeclareApp\code\app\controllers\user.controller.js:34:10)
at Layer.handle [as handle_request] (C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\layer.js:95:5)
at next (C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\route.js:137:13)
at Route.dispatch (C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\layer.js:95:5)
at C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\index.js:281:22
at Function.process_params (C:\Users\master\BackEnd\DeclareApp\code\node_modules\express\lib\router\index.js:335:12) {
code: ‘ER_NO_DB_ERROR’,
errno: 1046,
sqlMessage: ‘No database selected’,
sqlState: ‘3D000’,
index: 0,
sql: ‘SELECT * FROM user’
}
Reply
1. bezkoder says:
  June 7, 2020 at 1:20 am
  Hi, please check the database configuration in the code (db.config.js). It should match your PC MySQL database configuration.
  Reply
Edward says:
June 12, 2020 at 9:35 am
Hi, Great Tutorial.
When testing the APIs with postman, I try and create a new customer. It successfully creates the customer and console logs the created customer but then throws the error:
ReferenceError: result is not defined
at Query. (****/customer.model.js:19:9)
```
This is the code im using to create the customer in the database:
Customer.create = (newCustomer, results) => {
    sql.query("INSERT INTO customers SET ?", newCustomer, (err, res) => {
        if (err) {
          console.log("error: ", err);
          result(err, null);
          return;
        }
    
        console.log("created customer: ", { id: res.insertId, ...newCustomer });
        result(null, { id: res.insertId, ...newCustomer });
    });
}
```
Any help would be greatly appreciated
Reply
1. 324234 says:
  June 27, 2020 at 5:29 pm
  hhmm..may be it should be result, not results here
  Customer.create = (newCustomer, result)
  Reply
2. NewBie says:
  July 18, 2020 at 5:50 pm
  you have `results` and `result`.
  Reply
Naveen Gupta says:
June 13, 2020 at 1:24 am
Thanks, explained so well.
Reply

hello, i need to know, if i want to get some customers, not all.

thanks a lot

Hi, if you want to make pagination, please read this tutorial:
Server side Pagination in Node.js with Sequelize & MySQL

Hello bezkoder, i am very new in api rest. i need get some customer for name and not for id.

this is for id in typescript

public async getOne (req:Request,res:Response): Promise{
        const { id } = req.params;
        const habitante = await pool.query('SELECT * FROM habitantes WHERE id = ?', [id]);
        if (habitante.length>0)
        {
            return res.json(habitante[0]);
        }
        console.log(habitante);
        res.status(404).json({ text:'El habitante '+ req.params.id + ' no existe'});
    }

and the routes is

this.router.get('/:id', HabitantesController.getOne);

this works perfectly

i need something like that

public async getSome (req:Request,res:Response): Promise{
        const { termino } = req.params;
        const habitantes = await pool.query('SELECT * FROM habitantes WHERE primerNombre like %?%', [termino]);
        if (habitantes.length>0)
        {
            return res.json(habitantes);
        }
                console.log(habitantes); 
        res.status(404).json({ text:'No exiten habitantes que coincidan con este termino: '+ req.params.termino });
    }

habitantesroutes.ts

this.router.get('/:termino', HabitantesController.getSome);

this doesn’t work but always the Api consider the parameter ‘termino’ as if it were the id

help me please.

I do not know if I explain very well, my English is very basic.

regards

mamta says:
June 27, 2020 at 1:49 pm
Hello sir, here is my code of server.js
const express = require(“express”);
const bodyParser = require(“body-parser”);
const app = express();
// parse requests of content-type: application/json
app.use(bodyParser.json());
// parse requests of content-type: application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }));
// simple route
app.get(“/”, (req, res) => {
res.json({ message: “Welcome to bezkoder application.” });
});
require(“./app/routes/customer.routes.js”)(app);
// set port, listen for requests
app.listen(3000, () => {
console.log(“Server is running on port 3000.”);
});
created customer: { id: 3, email: undefined, name: undefined }
where is the problem
Reply
maky says:
June 28, 2020 at 2:38 am
Thank you very much for this tutorial!
Reply
nitu says:
July 11, 2020 at 5:25 pm
Server is running on port 3000.
D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Parser.js:437
throw err; // Rethrow non-MySQL errors
^
Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Handshake.Sequence._packetToError (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Handshake.ErrorPacket (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
at Protocol._parsePacket (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket. (D:\nodejs-express-mysql\node_modules\mysql\lib\Connection.js:88:28)
at Socket. (D:\nodejs-express-mysql\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
——————–
at Protocol._enqueue (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (D:\nodejs-express-mysql\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at Connection.connect (D:\nodejs-express-mysql\node_modules\mysql\lib\Connection.js:116:18)
at Object. (D:\nodejs-express-mysql\app\models\db.js:13:12)
at Module._compile (internal/modules/cjs/loader.js:778:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
at Module.load (internal/modules/cjs/loader.js:653:32)
at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
at Function.Module._load (internal/modules/cjs/loader.js:585:3)
at Module.require (internal/modules/cjs/loader.js:692:17)
i got this error…can anyone help me?
Reply
1. desperate_student says:
  August 20, 2020 at 9:18 pm
  I found this solution and it worked for me : https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server
  Reply
2. Philip says:
  December 10, 2020 at 3:42 pm
  go to MySQL Installer > Reconfigure MySQL Server > Authentication Method and select “Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)”
  Reply
Vincent F says:
July 13, 2020 at 1:01 pm
Great tutorial and really appreciated. CRUD applications are real-world problems many tutorials can seem to overlook. This was perfect.
Reply
Harry Karani says:
July 17, 2020 at 7:19 am
Nice tutorial, especially for anyone doing an intro to REST APIs in NodeJS. Simple and straight to the point. Well done
Reply
Shivani says:
July 25, 2020 at 11:04 am
I have the following error. It will be a great help if you can it as soon as possible.
C:\Program Files\nodejs\nodejs-express-mysql>node server.js
Server is running on port 3000.
C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Parser.js:437
throw err; // Rethrow non-MySQL errors
^
Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@’localhost’ (using password: YES)
at Handshake.Sequence._packetToError (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Handshake.ErrorPacket (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
at Protocol._parsePacket (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket. (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\Connection.js:88:28)
at Socket. (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (_stream_readable.js:295:12)
——————–
at Protocol._enqueue (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at Connection.connect (C:\Program Files\nodejs\nodejs-express-mysql\app\node_modules\mysql\lib\Connection.js:116:18)
at Object. (C:\Program Files\nodejs\nodejs-express-mysql\app\models\db.js:13:12)
at Module._compile (internal/modules/cjs/loader.js:1138:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1158:10)
at Module.load (internal/modules/cjs/loader.js:986:32)
at Function.Module._load (internal/modules/cjs/loader.js:879:14)
at Module.require (internal/modules/cjs/loader.js:1026:19)
at require (internal/modules/cjs/helpers.js:72:18) {
code: ‘ER_ACCESS_DENIED_ERROR’,
errno: 1045,
sqlMessage: “Access denied for user ‘root’@’localhost’ (using password: YES)”,
sqlState: ‘28000’,
fatal: true
}
Reply
1. bezkoder says:
  July 25, 2020 at 3:15 pm
  Hi, you should create database with correct host, username, password, port. Then modify the configuration in the source code.
  Reply
  1. Shivani says:
    July 27, 2020 at 4:43 am
    also one query customers is one table here but in my case i have to join 3 tables and then put that data in kendro grid then how can i put that query in this code. please help.
    how can i do it.
    Reply
    1. Shivani says:
      July 27, 2020 at 4:53 am
      And there are three join queries for each section on the front end. It will be a great help if you could provide entire solution as you have join on your website. Thanks in Advance.
      Reply
awanti says:
July 25, 2020 at 5:03 pm
hello…….in my console it is just showing Server running on port 3000, but it is not showing Successfully connected database….and while sending requests on postman it shows that request cannot be send ….please give reply..please suggest something to work properly
Reply
Shivani says:
July 30, 2020 at 2:53 am
how to solve the following error . please help it is urgent.
events.js:292
throw er; // Unhandled ‘error’ event
^
Error: listen EADDRINUSE: address already in use :::3000
at Server.setupListenHandle [as _listen2] (net.js:1313:16)
at listenInCluster (net.js:1361:12)
at Server.listen (net.js:1447:7)
at Function.listen (C:\Program Files\nodejs\nodejs-express-mysql\node_modules\express\lib\application.js:618:24)
at Object. (C:\Program Files\nodejs\nodejs-express-mysql\server.js:19:5)
at Module._compile (internal/modules/cjs/loader.js:1138:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1158:10)
at Module.load (internal/modules/cjs/loader.js:986:32)
at Function.Module._load (internal/modules/cjs/loader.js:879:14)
at Function.executeUserEntryPoint [as runMain] (internal/modules/run_main.js:71:12)
Emitted ‘error’ event on Server instance at:
at emitErrorNT (net.js:1340:8)
at processTicksAndRejections (internal/process/task_queues.js:84:21) {
code: ‘EADDRINUSE’,
errno: ‘EADDRINUSE’,
syscall: ‘listen’,
address: ‘::’,
port: 3000
}
Reply
1. bezkoder says:
  July 30, 2020 at 2:55 am
  Hi, you need to stop the service which is using port 3000, or change the app server port.
  Reply
  1. Shivani says:
    July 30, 2020 at 11:41 am
    okay thank you so much
    Reply
Mapi says:
August 5, 2020 at 3:56 pm
Hi,
thank you for your clear and simple tutorial. Im new to javascript and node.js.
Can you explain why you didn’t work with the ES6 syntax?
e.g. customer:
```
class Customer{
  constructor(name, email, active) {
    this.name= name;
    this.email= email;
    this.active=active;
  }
}
```
I know object oriented languages like Java – is your way the ‘recommended’ way in javascript? Or should I work with this ES6 syntax for better design?
Thank you
Reply
Hunor says:
August 12, 2020 at 7:06 pm
I was wondering why the routing was implemented via passing the app object into the exported routing function instead of using the Router object that express provides.
Is it because it’s one less line of code per route?
Reply
Orholt says:
August 14, 2020 at 10:43 am
Hi, I had problem with removing item from my MySQL DB, can someone help me?
I posted the issue on StackOverflow but only answer don’t seems to work:
https://stackoverflow.com/questions/63400833/node-js-api-cant-delete-from-database
Reply

Hello,
excellent article. It was very helpful to me. I have a question, because in the models, the errors are returned where the data should go. for example:

Customer.getAll = result => {
   sql.query ("SELECT * FROM customers", (err, res) => {
     if (err) {
       console.log ("error:", err);
       result (null, err);
       return;
     }

     console.log ("customers:", res);
     result (null, res);
   });
};

Instead of:

if (err) {
       result (err, null);
       return;
     }

Thanks for the article!

Abel Heredia Bravo says:
August 16, 2020 at 7:54 am
Excelent !!!!
Reply
Dragon says:
August 21, 2020 at 2:34 am
WIth this controller-model solution, is it possible to handle async and wait process?
Reply
Khashaa says:
August 27, 2020 at 7:11 am
It was freakin helpful for me!
Reply
kodingspot says:
September 8, 2020 at 5:06 pm
is beautiful if you using new Promise() on the model….
Reply
REST says:
September 11, 2020 at 3:26 pm
This tutorial is incomplete. After you write:
“npm install express mysql body-parser –save”
You never generate any files. Therefore all the controller-files are missing. Other people have had problems with this in above comments. The app-folder is not present for example.
If you are going to make a step by step tutorial, please make it step by step and don’t miss out on os-commands.
Reply
kirad says:
September 15, 2020 at 1:53 pm
Great
Reply
jahangir says:
October 7, 2020 at 11:41 am
how to generate package-lock.json file ?
Reply
1. bezkoder says:
  October 7, 2020 at 1:40 pm
  Hi, please run the command: npm install
  Reply
Bisht says:
October 8, 2020 at 6:48 am
Hello,
I am getting this error can you help.
TypeError: sql.query is not a function
at Function.Customer.create (/home/bisht/nodejs-express-mysql/app/models/customer.model.js:11:7)
at exports.create (/home/bisht/nodejs-express-mysql/app/controllers/customer.controller.js:20:14)
at Layer.handle [as handle_request] (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at next (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at /home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:335:12)
at next (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:275:10)
at urlencodedParser (/home/bisht/nodejs-express-mysql/node_modules/body-parser/lib/types/urlencoded.js:82:7)
TypeError: sql.query is not a function
at Function.Customer.getAll (/home/bisht/nodejs-express-mysql/app/models/customer.model.js:43:7)
at exports.findAll (/home/bisht/nodejs-express-mysql/app/controllers/customer.controller.js:32:14)
at Layer.handle [as handle_request] (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at next (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/route.js:137:13)
at Route.dispatch (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/route.js:112:3)
at Layer.handle [as handle_request] (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/layer.js:95:5)
at /home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:281:22
at Function.process_params (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:335:12)
at next (/home/bisht/nodejs-express-mysql/node_modules/express/lib/router/index.js:275:10)
at urlencodedParser (/home/bisht/nodejs-express-mysql/node_modules/body-parser/lib/types/urlencoded.js:82:7)
Reply
1. Ghanshyam Sharma says:
  November 2, 2020 at 8:17 am
  Hello,
  For this you need to add sql module *npm install mysql* and after include *var mysql = require(‘mysql’);*
  in your server.js or index.js
  Thanks
  Reply
Aviroop Sanyal says:
November 12, 2020 at 6:07 am
Thanks for your post. It was really helpful. Please help me with customer login with your rest api format
Reply
1. bezkoder says:
  November 12, 2020 at 8:20 am
  Hi, you can find the instruction at:
  Node.js Express: JWT example | Token Based Authentication & Authorization
  Reply
Manasa says:
November 19, 2020 at 6:46 am
Thank you.Helped me a lot as a beginner.
Reply
Taufik Muharrom says:
December 11, 2020 at 7:18 am
are “insertId” and “affectedRows” a method from sql ? i am still confused , where did the method come from.
Reply
1. bezkoder says:
  December 11, 2020 at 2:13 pm
  Hi, they came from response data of MySQL query 🙂
  Reply
Ajay says:
December 28, 2020 at 6:05 am
thank you
Reply
Shubham Verma says:
January 4, 2021 at 2:02 pm
Thank You so much… (Very neat and precise explanation)
Reply

Build Node.js Rest APIs with Express & MySQL

Application overview

Create Node.js application

Setup Express web server

Create MySQL table

Configure & Connect to MySQL database

Define the Model

Define Routes

Create the Controller

Create a new object

Retrieve all objects

Retrieve a single object

Update an object

Delete an object

Delete all objects

Test the APIs

Conclusion

Further Reading

Source code

137 thoughts to “Build Node.js Rest APIs with Express & MySQL”

Leave a Reply Cancel reply

Tools

Application overview

Create Node.js application

Setup Express web server

Create MySQL table

Configure & Connect to MySQL database

Define the Model

Define Routes

Create the Controller

Create a new object

Retrieve all objects

Retrieve a single object

Update an object

Delete an object

Delete all objects

Test the APIs

Conclusion

Further Reading

Source code

137 thoughts to “Build Node.js Rest APIs with Express & MySQL”

Leave a Reply Cancel reply

Follow us

Tools