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.

Contents

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

Application overview

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

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

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

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

Our project structure will be like:

Create Node.js application

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

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

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

npm init

name: (nodejs-express-mysql) 
version: (1.0.0) 
description: Never miss a thing in Life. Take notes quickly. Organize and keep track of all your notes.
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, express, mysql, restapi
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

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

npm install express mysql body-parser --save

The package.json file should look like this:

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

Setup Express web server

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

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

const app = express();

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

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

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

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

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

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

Create MySQL table

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

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

Configure & Connect to MySQL database

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

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

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

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

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

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

module.exports = connection;

Define the Model

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

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

This is the content inside customer.model.js:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

module.exports = Customer;

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

We use database connection query() method to execute MySQL script: INSERT, SELECT, UPDATE, DELETE. You can find more details about mysql module at: https://www.npmjs.com/package/mysql.

Define Routes

When a client sends request for an endpoint using HTTP request (GET, POST, PUT, DELETE), we need to determine how the server will reponse. It’s why we’re gonna setup the routes.

These are routes we define:

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

Create a routes folder inside app folder with content like this:

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

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

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

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

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

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

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

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

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

...

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

app.listen(...);

Create the Controller

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

create
findAll
findOne
update
delete
deleteAll

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

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

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

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

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

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

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

Let’s implement these functions.

Create a new object

Create and Save a new Customer:

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

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

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

Retrieve all objects

Retrieve all Customers from the database:

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

Retrieve a single object

Find a single Customer with a customerId:

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

Update an object

Update a Customer identified by the customerId in the request:

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

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

Delete an object

Delete a Customer with the specified customerId in the request:

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

Delete all objects

Delete all Customers from the database:

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

Test the APIs

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

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

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

Create a new Customer using POST /customers Api

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

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

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

Retrieve all Customers using GET /customers Api

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

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

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

Update a Customer using PUT /customers/:customerId Api

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

Check customers table after a row was updated:

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

Delete a Customer using DELETE /customers/:customerId Api

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

Customer with id=2 was removed from customers table:

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

Delete all Customers using DELETE /customers Api

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

Now there are no rows in customers table:

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

Source code

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

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

Happy learning! See you again.

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

prowebkarawang says:
October 4, 2019 at 10:36 am
The tutorial is concise and clear and very helpful, thanks.
1. bezkoder says:
  October 6, 2019 at 6:38 am
  Hi, thank you 🙂
  Hope it help you.
Federico says:
October 24, 2019 at 2:21 am
You’re really awesome bro!
1. bezkoder says:
  October 24, 2019 at 3:18 am
  Hi, thank you for your comment! ?
  Hope this help.
gus abas says:
October 27, 2019 at 12:24 pm
i have a problem when i try, the message is “cannot find module ‘..controllers/customer.contorller.js”
please help
1. bezkoder says:
  October 27, 2019 at 1:43 pm
  Hi, there is a typo: contorller. It should be customer.controller.js.

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

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

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

bezkoder says:
October 29, 2019 at 2:47 pm
Hi, please show me more details about your HTTP POST request.

golavo says:
November 7, 2019 at 9:46 am
Very helpful, thanks for this tutorial.

Build Node.js Rest APIs with Express & MySQL

Application overview

Create Node.js application

Setup Express web server

Create MySQL table

Configure & Connect to MySQL database

Define the Model

Define Routes

Create the Controller

Create a new object

Retrieve all objects

Retrieve a single object

Update an object

Delete an object

Delete all objects

Test the APIs

Source code

Conclusion

Further Reading

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

Leave a Reply Cancel reply

Search this site

Application overview

Create Node.js application

Setup Express web server

Create MySQL table

Configure & Connect to MySQL database

Define the Model

Define Routes

Create the Controller

Create a new object

Retrieve all objects

Retrieve a single object

Update an object

Delete an object

Delete all objects

Test the APIs

Source code

Conclusion

Further Reading

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

Leave a Reply Cancel reply

Follow us

Search this site