Node.js, Express and MySQL Restful CRUD API Tutorial

In this tutorial, we’ll learn how to develop a RESTful CRUD (Create, Retrieve, Update, Delete) API with Node.jsExpress, and MySQL database.
In this tutorial, we are going to build a simple todo management application. We will build Rest APIs for creating, listing, editing and deleting a Todo.
Express is one of the most popular web frameworks for node.js. It is built on top of node.js HTTP module and adds support for routing, middleware, view system, etc. It is very simple and minimal, unlike other frameworks that try to do way too much, thereby reducing the flexibility for developers to have their own design choices.

Setup MySQL database

Make sure that you have installed the MySQL database in your machine.
Use the following command to create a database:
create database demo;
Use below SQL script to create todos table in 'demo' database:
CREATE TABLE `todos` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

Tools and technologies used

  1. Node.js (npm)
  2. Express
  3. Body-parser
  4. MySQL
  5. Visual studio code IDE

Development steps

  1. Creating an application
  2. Install dependencies
  3. Setting up the webserver
  4. Defining Routes using Express
  5. Developing the Restful APIs
  6. Testing our APIs
  7. Conclusion
  8. Source code on GitHub repository

1. Creating an application

  1. Open a terminal and create a new folder for the application.
$ mkdir node-express-mysql-crud-restapi
  1. Initialize the application with a package.json file
Go to the root folder of your application and type npm init to initialize your app with a package.json file.
$ cd node-express-mysql-crud-restapi
$ npm init
Here is the complete package.json file:
{
  "name": "node-express-mysql-crud-restapi",
  "version": "1.0.0",
  "description": "Todo App",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Express",
    "RestAPI",
    "MySQL",
    "Todos"
  ],
  "author": "javaguides.net",
  "license": "MIT"
}
Note that I’ve specified a file named main.js as the entry point of our application. We’ll create a main.js file in the next section.

2. Install dependencies

We will need express, mysql and body-parser modules in our application. Let’s install them by typing the following command -
$ npm install express body-parser mysql --save
We have used --save option to save all the dependencies in the package.json file. The final package.json file looks like this -
{
  "name": "node-express-mysql-crud-restapi",
  "version": "1.0.0",
  "description": "Todo App",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Express",
    "RestAPI",
    "MySQL",
    "Todos"
  ],
  "author": "javaguides.net",
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

3. Setting up the webserver

Let’s now create the main entry point of our application. Create a new file named main.js in the root folder of the application with the following contents -
const express = require('express');
const bodyParser = require('body-parser');

// create express app
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }))

// parse application/json
app.use(bodyParser.json())

// define a simple route
app.get('/', (req, res) => {
    res.json({"message": "Welcome to Todo app"});
});

// listen for requests
app.listen(4000, () => {
    console.log("Server is listening on port 4000");
});
Let’s now run the server and go to http://localhost:4000 to access the route we just defined.
$ npm install
$ node main.js
 
Server is listening on port 4000

4. Defining Routes using Express

Create a new folder called routes inside the app folder with the following command:

$ mkdir app/routes
$ cd app/routes
Now, create a new file called todo.routes.js inside app/routes folder with the following contents:
module.exports = (app) => {
    const todos = require('../controllers/todo.controller.js');

    // Create a new todo
    app.post('/todos', todos.create);

    // Retrieve all todos
    app.get('/todos', todos.findAll);

    // Retrieve a single todo by id
    app.get('/todos/:id', todos.findOne);

    // Update a Todo with id
    app.put('/todos/:id', todos.update);

    // Delete a Todo by id
    app.delete('/todos/:id', todos.delete);
}
Note that We have added a require statement for the todo.controller.js file. We’ll define the controller file in the next section. The controller will contain methods for handling all the CRUD operations.
Before defining the controller, let’s first include the routes in main.js. Add the following require statement before app.listen() line inside server.js file.
// Require Notes routes
require('./app/routes/note.routes.js')(app);
Here is the complete main.js file:
const express = require('express');
const bodyParser = require('body-parser');

// create express app
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }))

// parse application/json
app.use(bodyParser.json())

// define a simple route
app.get('/', (req, res) => {
    res.json({"message": "Welcome to Todo app"});
});

require('./app/routes/todo.routes.js')(app);

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

5. Developing the Restful APIs

Create a new folder called controllers inside the app folder, then create a new file called todo.controller.js inside app/controllers folder.

Connect to MySQL Database

Inside todo.controller.js file adds below MySQL database connection code:
const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
})

Create a Todo

// Create and Save a new Todo
exports.create = (req, res) => {
    // Validate request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    var params = req.body;
    console.log(params);

    connection.query("INSERT INTO todos SET ? ", params,
        function (error, results, fields) {
            if (error) throw error;
            return res.send({
                data: results,
                message: 'New todo has been created successfully.'
            });
        });
};

Retrieve all Todos

// Retrieve and return all todos from the database.
exports.findAll = (req, res) => {
    connection.query('select * from todos',
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Retrieve a single Todo by Id

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

    connection.query('select * from todos where Id=?',
        [req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Update a Todo

/ Update a todo identified by the id in the request
exports.update = (req, res) => {
    // Validate Request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    console.log(req.params.id);
    console.log(req.body.description);
    connection.query('UPDATE `todos` SET `name`=?,`description`=? where `id`=?',
        [req.body.name, req.body.description, req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Delete a Todo

// Delete a todo with the specified id in the request
exports.delete = (req, res) => {
    console.log(req.body);
    connection.query('DELETE FROM `todos` WHERE `Id`=?', 
        [req.body.id], function (error, results, fields) {
            if (error) throw error;
            res.end('Record has been deleted!');
    });
};

Complete code - todo.controller.js

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
})

// Create and Save a new Todo
exports.create = (req, res) => {
    // Validate request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    var params = req.body;
    console.log(params);

    connection.query("INSERT INTO todos SET ? ", params,
        function (error, results, fields) {
            if (error) throw error;
            return res.send({
                data: results,
                message: 'New todo has been created successfully.'
            });
        });
};

// Retrieve and return all todos from the database.
exports.findAll = (req, res) => {
    connection.query('select * from todos',
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

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

    connection.query('select * from todos where Id=?',
        [req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

// Update a todo identified by the id in the request
exports.update = (req, res) => {
    // Validate Request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    console.log(req.params.id);
    console.log(req.body.description);
    connection.query('UPDATE `todos` SET `name`=?,`description`=? where `id`=?',
        [req.body.name, req.body.description, req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

// Delete a todo with the specified id in the request
exports.delete = (req, res) => {
    console.log(req.body);
    connection.query('DELETE FROM `todos` WHERE `Id`=?', 
        [req.body.id], function (error, results, fields) {
            if (error) throw error;
            res.end('Record has been deleted!');
    });
};

6. Testing our APIs

Let’s now test all the APIs one by one using postman.

Create a new Todo using POST /todos API


Retrieving all Todos using GET /todos API


Retrieving a single Todo using GET /todos/:id API

Updating a Todo using PUT /todos/:id API

Deleting a Todo using DELETE /todos/:id API

7. Conclusion

In this tutorial, we have developed a RESTful CRUD (Create, Retrieve, Update, Delete) API with Node.js, Express, and MySQL database.

8. Source code on GitHub repository

Working on it...

Comments

  1. Enroll in the leading Autocad institute in Delhi and Noida.High Technologies Solutions believes in quality training and provide innovative friendly environment.We also provide live projects, Assignments, free demo class and placement assistance. Call at 9311002620.
    autocad training institute in Delhi
    autocad training institute in Noida

    ReplyDelete
  2. Thanks for this blog are more informative contents step by step. I here attached my site would you see this blog.

    7 tips to start a career in digital marketing

    “Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

    we have offered to the advanced syllabus course digital marketing for available join now.

    more details click the link now.

    https://www.webdschool.com/digital-marketing-course-in-chennai.html

    ReplyDelete
  3. Amazing articles useful information.

    Web designing trends in 2020

    When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, WhatsApp, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.

    we have offered to the advanced syllabus course web design and development for available join now.

    more details click the link now.

    https://www.webdschool.com/web-development-course-in-chennai.html

    ReplyDelete
  4. This has been very helpful. I wish you had an additional one on how to add support for authentication!

    ReplyDelete

Post a Comment

Leave Comment