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 example: Build a full-stack CRUD Application

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:

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.

1. Create a new Customer using POST /customers Api




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

2. Retrieve all Customers using GET /customers Api




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




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




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

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




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

6. Delete all Customers using DELETE /customers Api




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

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

• Express.js Routing
• https://www.npmjs.com/package/express
• https://www.npmjs.com/package/body-parser
• https://www.npmjs.com/package/mysql