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

Build Node.js Rest APIs with Express & MySQL

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)
Deploying/Hosting Node.js app on Heroku with MySQL database
Node.js – JWT Authentication & Authorization example

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


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:

MethodsUrlsActions
GET/customersget all Customers
GET/customers/42get Customer with id=42
POST/customersadd new Customer
PUT/customers/42update Customer with id=42
DELETE/customers/42remove Customer with id=42
DELETE/customersremove all Customers

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

Our project structure will be like:

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

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:

nodejs-rest-api-express-mysql-run-server

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.

  1. Create a new Customer using POST /customers Api

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

  3. Retrieve all Customers using GET /customers Api

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

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

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

  7. Update a Customer using PUT /customers/:customerId Api

  8. 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 |
    +----+--------------------+----------+--------+
    

  9. Delete a Customer using DELETE /customers/:customerId Api

  10. 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 |
    +----+--------------------+----------+--------+
    

  11. Delete all Customers using DELETE /customers Api

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

Further Reading

Source code

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

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

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

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

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

          1. Hi, for example, with Postman, you can see Header tab (besides Body tab) where we set header for HTTP request πŸ™‚

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

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

      1. 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 πŸ™‚

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

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

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

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

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

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

  10. 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) => {`?

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

  12. 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”. πŸ™‚

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *