Build Node.js Rest APIs with Express & MySQL

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.

Related Posts:
– Node.js Rest APIs example with Express & MySQL (including 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
– React + 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 & MySQL Associations:
– One-to-Many Relationship example
– Many-to-Many Relationship example


Application overview

We will build Rest Apis for creating, retrieving, updating, deleting and searching Tutorials.

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

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 Node.js Rest API works with Client in one of these posts:

Our project structure will be like:

nodejs-rest-api-express-mysql-project-structure-new

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 cors.
Run the command:

npm install express mysql cors --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": {
    "cors": "^2.8.5",
    "express": "^4.18.1",
    "mysql": "^2.18.1"
  }
}

Setup Express web server

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

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." });
});

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

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

– 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:

nodejs-rest-api-express-mysql-setup-server

Create MySQL table

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

CREATE TABLE IF NOT EXISTS `tutorials` (
  id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  description varchar(255),
  published 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 tutorial.model.js. We’re gonna define constructor for Tutorial object here, and use the database connection above to write CRUD functions:

  • create a new Tutorial
  • find a Tutorial by id
  • get all Tutorials
  • get all published Tutorials
  • update a Tutorial by id
  • remove a Tutorial
  • remove all Tutorials

This is the content inside tutorial.model.js:

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

// constructor
const Tutorial = function(tutorial) {
  this.title = tutorial.title;
  this.description = tutorial.description;
  this.published = tutorial.published;
};

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

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

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

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

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

Tutorial.getAll = (title, result) => {
  let query = "SELECT * FROM tutorials";

  if (title) {
    query += ` WHERE title LIKE '%${title}%'`;
  }

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

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

Tutorial.getAllPublished = result => {
  sql.query("SELECT * FROM tutorials WHERE published=true", (err, res) => {
    if (err) {
      console.log("error: ", err);
      result(null, err);
      return;
    }

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

Tutorial.updateById = (id, tutorial, result) => {
  sql.query(
    "UPDATE tutorials SET title = ?, description = ?, published = ? WHERE id = ?",
    [tutorial.title, tutorial.description, tutorial.published, id],
    (err, res) => {
      if (err) {
        console.log("error: ", err);
        result(null, err);
        return;
      }

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

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

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

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

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

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

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

module.exports = Tutorial;

Tutorial model is simple, it contains fields: title, description & published.

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 response. It’s why we’re gonna setup the routes.

These are routes we define:

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

Create a routes folder inside app folder, then create tutorial.routes.js file 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. 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/tutorial.routes.js")(app);

app.listen(...);

Create the Controller

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

  • create
  • findAll
  • findOne
  • findAllPublished
  • update
  • delete
  • deleteAll
const Tutorial = require("../models/tutorial.model.js");

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

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

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

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

// Update a Tutorial identified 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) => {
  
};

Let’s implement these functions.

Create a new object

Create and Save a new Tutorial:

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

  // Create a Tutorial
  const tutorial = new Tutorial({
    title: req.body.title,
    description: req.body.description,
    published: req.body.published || false
  });

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

Retrieve objects

Retrieve all Tutorials from the database (with or without condition):

// Retrieve all Tutorials from the database (with condition).
exports.findAll = (req, res) => {
  const title = req.query.title;

  Tutorial.getAll(title, (err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    else res.send(data);
  });
};

exports.findAllPublished = (req, res) => {
  Tutorial.getAllPublished((err, data) => {
    if (err)
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    else res.send(data);
  });
};

Retrieve a single object

Find a single Tutorial by the id:

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

Update an object

Update a Tutorial identified by the id in the request:

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

  console.log(req.body);

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

Delete an object

Delete a Tutorial with the specified id in the request:

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

Delete all objects

Delete all Tutorials from the database:

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

Test the APIs

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

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

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

  1. Create a new Tutorial using POST /tutorials Api


    2. nodejs-rest-api-express-mysql-create-tutorial

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

    mysql> SELECT * FROM tutorials;
+----+---------------------------+-------------------+-----------+
| id | title                     | description       | published |
+----+---------------------------+-------------------+-----------+
|  1 | Node.js Basics            | Tut#1 Description |         0 |
|  2 | Rest APIs                 | Tut#2 Description |         0 |
|  3 | Node Rest APIs            | Tut#3 Description |         0 |
|  4 | MySQL database            | Tut#4 Description |         0 |
|  5 | Node Rest Apis with MySQL | Tut#5 Description |         0 |
+----+---------------------------+-------------------+-----------+

  2. Retrieve all Tutorials using GET /tutorials Api


    3. nodejs-rest-api-express-mysql-retrieve-tutorial

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


    4. nodejs-rest-api-express-mysql-retrieve-one-tutorial

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


    5. nodejs-rest-api-express-mysql-update-tutorial

    Check tutorials table after some rows are updated:

    mysql> SELECT * FROM tutorials;
+----+---------------------------------+-------------------+-----------+
| id | title                           | description       | published |
+----+---------------------------------+-------------------+-----------+
|  1 | Node.js Basics                  | Tut#1 Description |         0 |
|  2 | Rest APIs                       | Tut#2 Description |         0 |
|  3 | Node Rest APIs (updated)        | Tut#3 Desc (new)  |         1 |
|  4 | MySQL database                  | Tut#4 Description |         0 |
|  5 | Node Rest Apis with MySQL (new) | Tut#5 Desc (new)  |         1 |
+----+---------------------------------+-------------------+-----------+

  5. Find Tutorials by title using GET /tutorials Api


    6. nodejs-rest-api-express-mysql-search-tutorial

  6. Find all published Tutorials


    7. nodejs-rest-api-express-mysql-find-tutorial

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


    8. nodejs-rest-api-express-mysql-delete-id-tutorial

    Tutorial with id=4 was removed from tutorials table:

    mysql> SELECT * FROM tutorials;
+----+---------------------------------+-------------------+-----------+
| id | title                           | description       | published |
+----+---------------------------------+-------------------+-----------+
|  1 | Node.js Basics                  | Tut#1 Description |         0 |
|  2 | Rest APIs                       | Tut#2 Description |         0 |
|  3 | Node Rest APIs (updated)        | Tut#3 Desc (new)  |         1 |
|  5 | Node Rest Apis with MySQL (new) | Tut#5 Desc (new)  |         1 |
+----+---------------------------------+-------------------+-----------+

  8. Delete all Tutorials using DELETE /tutorials Api


    9. nodejs-rest-api-express-mysql-delete-tutorial

    Now there are no rows in tutorials table:

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

You can also test this Node.js Rest API with Client in one of these posts:

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 and finder methods.

You can find more interesting thing in the next tutorial:
– Deploying/Hosting Node.js app on Heroku with MySQL database
– Dockerize Node.js Express and MySQL example – Docker Compose

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.

Further Reading

– Upload/store images in MySQL using Node.js, Express & Multer
– Node.js Express File Upload Rest API example using Multer

Source code

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

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

  1. Thank you so much for the tutorial
    it is very helpfull for me as beginner to have some practical project to learning by doing

  2. It’s a nice tutorial. Thank you!
    However, I’m working on a similar project and I need to use sqlite instead of mySQL and I was wondering if it’ll work and what modules i can use ?

  7. I got this Error
    Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

    SOLUTION that helped me was upgrading mysql to mysql2

    npm install mysql2

  10. Your walkthrough helped me immensely with creating this quote of the day app:
    quot3d[dot]herokuapp[dot]com
    Your style of writing is perfect for the way I like to learn; and your screen grabs of the different steps save so much time. Thanks for all the effort you’ve put in to share your knowledge.

  13. Greetings! I am getting the error:
    {
    code: ‘ER_NOT_SUPPORTED_AUTH_MODE’,
    errno: 1251,
    sqlMessage: ‘Client does not support authentication protocol requested by server; consider upgrading MySql client’,
    sqlState: ‘08004’,
    fatal: true
    }

    1. If I use express.urlencoded I still get a warning:
      “body-parser deprecated undefined extended: provide extended option server.js:9:17”

      If I ignore this and run a create call from postman I get
      “SyntaxError: Unexpected token t in JSON at position 70    at JSON.parse (<anonymous>)    at parse (C:\Users\Gino\Development\playground\nodejs-express-mysql\node_modules\body-parser\lib\types\json.js:89:19)    at C:\Users\Gino\Development\playground\nodejs-express-mysql\node_modules\body-parser\lib\read.js:121:18…..”

  17. it’s a very helpful article.
    I request you please create another article for user registration and login with express validator & Token in express with MySQL.

    Thanks

  18. require(“../node_mysql/routes/customer.routes.js”)(app)

    What does this line do?

  19. The problem that i posted few minute ago “can not get / customers” is resolved but i am facing other problem when i request URL in postman only sending request is coming no response is return so please help me to resolve this issue

  23. How to achieve multiple table join in this example, I know we can use sequalize but I want to understand more in this example

  24. I saw your sequalize tutorial very nice, I want to do the same in this example, I mean joining multiple table, how to achieve the association concept here. I know I can use sequalize but I want understand more in the boilerplate example

  25. Thank you very much. It’s one of the most clear tutorial on this subject I ever found.
    So would you have an advice on an article clearly explaining how to add a security layer on this, like with token ….

    TY

  26. {“message”:”ER_BAD_NULL_ERROR: Column ’email’ cannot be null”}
    while post using postman

  28. Nice tutorial, thanks.
    Just FYI, small bug/typo in model section:
    Should be “result(err, null)” instead of “result(null, err)”

    1. This is really a great tutorial! But indeed, only with

      result(err, null);

      I get a correct (wanted) error response when trying to remove all customers: I did set a constraint in the database, where the customer id is a foreign key in another table with valid data. So it is not possible in the DB to delete the (referenced) customer only. But the API reports “all customers deleted successfully” 🙂 until I changed this line as you suggested.

  29. works like a charm, well explained and thanks for sharing …..keep up the good work and hope to see of more tutorials using react + nodejs, angular + nodejs with real-world examples and with authentication + authorization

  30. postman error bro
    {
    “message”: “ER_BAD_NULL_ERROR: Column ‘content’ cannot be null”
    }

    error status: 500 internet server error

    1. Hi, kindly make sure that you have Content-Type: application/json in the HTTP request header 🙂

  33. are “insertId” and “affectedRows” a method from sql ? i am still confused , where did the method come from.

  35. Thanks for your post. It was really helpful. Please help me with customer login with your rest api format

  36. 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)

    1. 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

  39. 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.

    1. Hi, yes you can 🙂

      It looks like:

      exports.findAll = async (req, res, next) => {
  const { ok, result, error } = await Customer.getAll();
  if (!ok) {
    next(error)
  } 
  return res.send(result);
};

  44. 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!

  46. 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?

  47. 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

  48. 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
    }

    1. Hi, you need to stop the service which is using port 3000, or change the app server port.

  49. 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

  50. 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
    }

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

      1. 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.

        1. 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.

  51. Nice tutorial, especially for anyone doing an intro to REST APIs in NodeJS. Simple and straight to the point. Well done

  52. Great tutorial and really appreciated. CRUD applications are real-world problems many tutorials can seem to overlook. This was perfect.

  53. 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?

    2. go to MySQL Installer > Reconfigure MySQL Server > Authentication Method and select “Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)”

  55. 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

      1. 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

  58. 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

    1. hhmm..may be it should be result, not results here
      Customer.create = (newCustomer, result)

  59. 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’
    }

    1. Hi, please check the database configuration in the code (db.config.js). It should match your PC MySQL database configuration.

  60. 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

  61. I will recommend to all my friends and colleagues. Thank you very much for this tutorial!

  62. 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,

  63. 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>

  65. 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

  66. 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!

  67. 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

  68. 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

    1. Hi, because we put server.js in the root folder, so the directory should be “./app/routes/customer.routes.js”. 🙂

  69. 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)

      1. 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.

        1. 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.

  70. 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 ?

    1. 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);

      1. 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();

          1. Thank you for explaining. So the two `app` are different.
            can `module.exports = app => {` be interpreted as `module.exports = (app) => {`?

        2. or, is it equivalent to this?
          const routeFunction = require(“./app/routes/customer.routes.js”);
          app.use(‘/’, routeFunction);

          1. 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);

    2. i am also doubtful at this line, and one more thing is
      if
      require(“./app/routes/customer.routes.js”)(app);
      require(“./app/routes/order.routes.js”)(app);

      data from order.routes.js is not displaying.
      why ? and how to declare multiple routes?

  72. 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!

  73. 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..

  74. 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.

    1. 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.

        1. Hey bezkoder,
          You have any tutorial for promise and promise chain concept using node

  75. Great tutorial. I am not even Node developer but i got the Job done. Thanks A lots man

  79. i got a problem when i get a customer by id
    “message”: ” Error retrieving customer with id 2″
    please help

  81. 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.

    1. Hi, did you set appropriate Header: Content-Type: application/json when sending POST request?

      1. in postman, next to the “graphQL”, by default it says “text”. make clic that “Text” optioon and shoose “Json”. I had the same mistake and it fixed it. note that in the screenshot it already says “JSON” so you have to make it look like that on your postman

  82. 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?

  85. 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?

        1. 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.

      2. 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.

        1. 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 🙂

          1. 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.

    2. Yes, you can make many-to-many relationship with table structure as Sequelize. But you will need to write more boilerplate.

  87. 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

    2. You have to set headers for content-type: application/json & content-type: x-www-form-urlencoded

        1. Maybe you forgot to set headers for content-type: application/json & content-type: x-www-form-urlencoded on REST Client requests 🙂

          2. Hi, for example, with Postman, you can see Header tab (besides Body tab) where we set header for HTTP request 🙂

  89. 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)

      1. 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”
        }

  90. i have a problem when i try, the message is “cannot find module ‘..controllers/customer.contorller.js”
    please help

        2. 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);

      2. Thank you very much, this was very helpful. From now on my project will be structured in this fashion

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