Example of node.js rest API for express, serialize and MySQL

Time:2021-11-28

This article is translated fromNode.js Rest APIs example with Express, Sequelize & MySQL

Latest revision: September 29, 2020bezkoder Node.js

ExpressNode.js is one of the most popular web frameworks, supporting routing, middleware, view systemSequelizeIt is a promise based node.js orm. It supports Postgres, MySQL and SQL Server dialects. In this tutorial, I will show you how to use express, serialize, and MySQL databases to build the node.js restful crud API.

You should first install MySQL on the machine. Installation instructions can be found at:
Official MySQL installation manual

Related articles:

Full stack:

Security:Node.js – JWT Authentication & Authorization example
Deployment:Deploying/Hosting Node.js app on Heroku with MySQL database

Node.js rest crud API overview

We will build rest APIs, which can create, retrieve, update, delete and find tutorials by title.
First, let’s start with the express web server. Next, we add a configuration for the MySQL database and create it using serializeTutorialModel, write controller. We then define a route to handle all CRUD operations, including custom finders.

The following table outlines 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 will test rest APIs using postman.

This is our project structure:
[image upload failed… (image-5f8879-1604969151628)]

Sample video

This is a demonstration of our node.js express serialize application running with MySQL database, and test rest APIs through postman.

Create node.js application

First, we create a directory:

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

Next, we use the package.json file to initialize the node.js app:

npm init

name: (nodejs-express-sequelize-mysql) 
version: (1.0.0) 
description: Node.js Rest Apis with Express, Sequelize & MySQL.
entry point: (index.js) server.js
test command: 
git repository: 
keywords: nodejs, express, sequelize, mysql, rest, api
author: bezkoder
license: (ISC)

Is this ok? (yes) yes

We need to install the necessary modules:expresssequelizemysql2andbody-parser
Run command:

npm install express sequelize mysql2 body-parser cors --save

The package.json file should look like this:

{
  "name": "nodejs-express-sequelize-mysql",
  "version": "1.0.0",
  "description": "Node.js Rest Apis with Express, Sequelize & MySQL",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "nodejs",
    "express",
    "rest",
    "api",
    "sequelize",
    "mysql"
  ],
  "author": "bezkoder",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "mysql2": "^2.0.2",
    "sequelize": "^5.21.2"
  }
}

Build express web server

Under the root directory, create a newserver.jsFile:

const express = require("express");
const bodyParser = require("body-parser");
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(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
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

What we need to do is:

  • Importexpressbody-parserandcorsmodular:

    • Express is used to build rest APIs
    • body-parserHelps resolve requests and createreq.bodyobject
    • corsExpress middleware is provided to give CORS multiple choices.
  • Create an express application and useapp.use()Method additionbody-parserandcorsMiddleware. Please note that we set origin:http:// localhost:8081

  • Define an easy to test get route.

  • Listen for incoming requests on port 8080.

Now run the application with the following command:node server.js
Use URLhttp:// localhost:8080/Open the browser and you will see:

Example of node.js rest API for express, serialize and MySQL

http://localhost:8080

Yes, the first step has been completed. In the next section, we will work with sequenize.

Configure MySQL database and serialize it

In the app folder, we create a separate config folder and configure it using the db.config.js file, as shown below:

module.exports = {
  HOST: "localhost",
  USER: "root",
  PASSWORD: "123456",
  DB: "testdb",
  dialect: "mysql",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

The first five parameters are used for MySQL connection.

poolIs optional and will be used for the serialize connection pool configuration:

-Max: the maximum number of connections in the pool
-Min: the minimum number of connections in the pool
-Idle: the maximum time (in milliseconds) that a connection can be idle before it is released
-Acquire: the maximum time (in milliseconds) that the pool will attempt to acquire a connection before throwing an error

For more details, visitAPI Reference for the Sequelize constructor

Initialize serialize

We will include the of the model in the next stepapp/modelsInitialize serialize in folder.

Now, use the following code to createapp/models/index.js

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

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

Don’t forget to be hereserver.jsCall insync()method

...
const app = express();
app.use(...);

const db = require("./app/models");
db.sequelize.sync();

...

During development, you may need to delete existing tables and resynchronize the database. Just use force: true, as shown in the following code:

db.sequelize.sync({ force: true }).then(() => {
  console.log("Drop and re-sync db.");
});

Define the serialize model

In the models folder, create the tutorial.model.js file like this:

module.exports = (sequelize, Sequelize) => {
  const Tutorial = sequelize.define("tutorial", {
    title: {
      type: Sequelize.STRING
    },
    description: {
      type: Sequelize.STRING
    },
    published: {
      type: Sequelize.BOOLEAN
    }
  });

  return Tutorial;
};

The serialize model represents the data in the MySQL databasetutorialsWatch. These columns will be generated automatically: ID, title, description, published, createdat, updatedat

After initializing serialize, we do not need to write crud functions. Serialize supports all these functions:

  • Create a new tutorial: create (object)
  • Find through ID tutorial: findbypk (ID)
  • Get all tutorials: findall()
  • Through ID: update (data, where: {ID: ID})
  • Delete tutorial: Destroy (where: {ID: ID})
  • Delete all tutorials: Destroy (where: {})
  • Find all tutorials by Title: findall ({where: {Title:…}})

These functions will be used in our controller.

We can improve the example by adding comments to each tutorial. This is a one to many relationship, for which I have written a tutorial:
Sequelize Associations: One-to-Many example – Node.js, MySQL

Alternatively, you can label each tutorial and add the tutorial to the label (many to many relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL

Create controller

stayapp/controllersFolder, let’s create tutorial.controller.js using the following crud function:

  • create
  • findAll
  • findOne
  • update
  • delete
  • deleteAll
  • findAllPublised
const db = require("../models");
const Tutorial = db.tutorials;
const Op = db.Sequelize.Op;

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

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

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

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

// Find all published Tutorials
exports.findAllPublished = (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.title) {
    res.status(400).send({
      message: "Content can not be empty!"
    });
    return;
  }

  // Create a Tutorial
  const tutorial = {
    title: req.body.title,
    description: req.body.description,
    published: req.body.published ? req.body.published : false
  };

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

Retrieve object (conditional)

Retrieve all tutorials from the database / find by Title:

exports.findAll = (req, res) => {
  const title = req.query.title;
  var condition = title ? { title: { [Op.like]: `%${title}%` } } : null;

  Tutorial.findAll({ where: condition })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

We usereq.query.titleGet the query string from request and use it asfindAll()Conditions of the method.

Retrieve a single object

Find a single tutorial with ID:

exports.findOne = (req, res) => {
  const id = req.params.id;

  Tutorial.findByPk(id)
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message: "Error retrieving Tutorial withTutorial was updated successfully."
        });
      } else {
        res.send({
          message: `Cannot update Tutorial with id=${id}. Maybe Tutorial was not found or req.body is empty!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Error updating Tutorial withTutorial was deleted successfully!"
        });
      } else {
        res.send({
          message: `Cannot delete Tutorial with id=${id}. Maybe Tutorial was not found!`
        });
      }
    })
    .catch(err => {
      res.status(500).send({
        message: "Could not delete Tutorial withSome error occurred while removing all tutorials."
      });
    });
};

Find all objects by criteria

Find allpublished = trueTutorials for:

exports.findAllPublished = (req, res) => {
  Tutorial.findAll({ where: { published: true } })
    .then(data => {
      res.send(data);
    })
    .catch(err => {
      res.status(500).send({
        message:
          err.message || "Some error occurred while retrieving tutorials."
      });
    });
};

You can make some modifications to the controller to return a paging response:

{
    "totalItems": 8,
    "tutorials": [...],
    "totalPages": 3,
    "currentPage": 1
}

You can find more details at:
Server side Pagination in Node.js with Sequelize and MySQL

Define route

When the client uses HTTP requests (get, post, put, delete) to send requests to the endpoint, we need to set the route to determine the response mode of the server.

These are our routes:

  • /api/tutorials:GET,POST,DELETE
  • /api/tutorials/:id:GET,PUT,DELETE
  • /api/tutorials/published:GET
    stayapp/routesCreate a folderturorial.routes.js, which reads as follows:
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 it/controllers/tutorial.controller.jsController in.

We also need toserver.jsInclude routes inapp.listen()Before):

...

require("./app/routes/turorial.routes")(app);

// set port, listen for requests
const PORT = ...;
app.listen(...);

Test API

Run our node.js application with the following command:node server.js
Console display:

Server is running on port 8080.
Executing (default): DROP TABLE IF EXISTS `tutorials`;
Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `description` VARCHAR(255), `published` TINYINT(1), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `tutorials`
Drop and re-sync db.

Using postman, we will test all of the above APIs.

1. UsePOST/tutorialsAPI create a new tutorial

Example of node.js rest API for express, serialize and MySQL

Create a new Tutorial

After creating some new tutorials, you can check the MySQL tables:

mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  2 | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:16:08 | 2019-12-13 01:16:08 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         0 | 2019-12-13 01:16:24 | 2019-12-13 01:16:24 |
|  4 | Vue Tut #4        | Tut#4 Description |         0 | 2019-12-13 01:16:48 | 2019-12-13 01:16:48 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         0 | 2019-12-13 01:16:58 | 2019-12-13 01:16:58 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

2. UseGET /tutorialsAPI retrieves all tutorials

Example of node.js rest API for express, serialize and MySQL

Retrieve all Tutorials

3. UseGET /tutorials/:idAPI queries a single tutorial by ID

Example of node.js rest API for express, serialize and MySQL

Retrieve a single Tutorial by id

4. Update tutorials with ` put / tutorials /: ID ‘API

Example of node.js rest API for express, serialize and MySQL

Update a Tutorial

After updating some rows, checktutorialsTable:

mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  2 | JS: Node Tut #2   | Tut#2 Description |         0 | 2019-12-13 01:16:08 | 2019-12-13 01:16:08 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         1 | 2019-12-13 01:16:24 | 2019-12-13 01:22:51 |
|  4 | Vue Tut #4        | Tut#4 Description |         1 | 2019-12-13 01:16:48 | 2019-12-13 01:25:28 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         1 | 2019-12-13 01:16:58 | 2019-12-13 01:25:30 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

5. UseGET /tutorials?title=nodeFind all tutorials whose title contains’ node ‘

Example of node.js rest API for express, serialize and MySQL

Find all Tutorials which title contains ‘node’

6. UseGET /tutorials/publishedAPI queries all published tutorials

Example of node.js rest API for express, serialize and MySQL

Find all published Tutorials

7. Use ‘Delete / tutorials /: IDAPI delete a tutorial! [Delete a Tutorial]( https://upload-images.jianshu.io/upload_images/15949040-921d6d926ec1ec72?imageMogr2/auto -Orient / strip% 7cimageview2 / 2 / w / 1240) tutorial with id = 2 has been downloaded fromTutorials ` delete from table:

mysql> select * from tutorials;
+----+-------------------+-------------------+-----------+---------------------+---------------------+
| id | title             | description       | published | createdAt           | updatedAt           |
+----+-------------------+-------------------+-----------+---------------------+---------------------+
|  1 | JS: Node Tut #1   | Tut#1 Description |         0 | 2019-12-13 01:13:57 | 2019-12-13 01:13:57 |
|  3 | JS: Vue Tut #3    | Tut#3 Description |         1 | 2019-12-13 01:16:24 | 2019-12-13 01:22:51 |
|  4 | Vue Tut #4        | Tut#4 Description |         1 | 2019-12-13 01:16:48 | 2019-12-13 01:25:28 |
|  5 | Node & Vue Tut #5 | Tut#5 Description |         1 | 2019-12-13 01:16:58 | 2019-12-13 01:25:30 |
+----+-------------------+-------------------+-----------+---------------------+---------------------+

8. Use ‘Delete / tutorials’API delete all tutorials! [Delete all Tutorials]( https://upload-images.jianshu.io/upload_images/15949040-e3fc6ce85574b9bc?imageMogr2/auto -Orient / strip% 7cimageview2 / 2 / w / 1240) is now inTutorials ` there is no row of data in the table:

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

conclusion

Today, we have learned how to create node.js rest APIs using the express web server. We also know how to add a MySQL database and serialize configuration, create a serialize model, write a controller, and define a route to handle all CRUD operations.

You can find more interesting things in the next tutorial:

{
    "totalItems": 8,
    "tutorials": [...],
    "totalPages": 3,
    "currentPage": 1
}

Happy study! bye.

Further reading

Upload tutorial data from file to MySQL database table:
Node.js: Upload Excel file data into MySQL Database
Node.js: Upload CSV file data into MySQL Database

source code

You canGithubThe complete source code for this example was found on.

If you want to add comments for each tutorial. It is a one to many association, and there is a tutorial on the relationship:Sequelize Associations: One-to-Many example – Node.js, MySQL

Alternatively, you can label each tutorial and add the tutorial to the label (many to many relationship):
Sequelize Many-to-Many Association example with Node.js & MySQL