CRUD API Using Nodejs and Express

This is the third part of the node js tutorial series to create a restful API using the Express node.js module. I have shared node js with MySQL tutorials. This node js tutorial helps to create the rest API to the listing table, add a record, edit a record, and delete the record from the MySQL database. The rest API communicates with MySQL and updates data into the MySQL database.

I will create a rest API for all CRUD operations, The CRUD is an acronym for Create, Read, Update, and Delete operation. I will create HTTP POST, GET, PUT and DELETE type requests.

I am using the following dependency libraries in this nodejs project:

Express js: Express is a framework for building web applications on top of Node.js
MySQL: Use to create a connection with MySQL database and allow operations into table data.
body-parser: This nodejs module helps to read data from the ‘form’ element and attach it with the request.

Our main motto of this nodejs tutorial is to create Rest Api for CRUD operation using nodejs and express. The Node js Rest API details are as follows:

Route	Method	Type	Posted JSON	Description
/employees	GET	JSON	–	Delete particular employee records from database
/employees/{id}	GET	JSON	–	Get a single employee data
/employees	POST	JSON	`{"Name": "Rachel", "Address": "120 st park aevnue NY", "Country" : "America", "Phone" : "1234567451"}`	Insert new employee record into database
/employees	PUT	JSON	`{"Name": "Rachel1", "Address": "110 st park aevnue NY", "Country" : "America", "Phone" : "1234567451", "Id":1}`	Update employee record into database
/employees	DELETE	JSON	`{"Id" : 59}`	Delete particular employee records from the database

I am assuming you have read my previous node.js tutorial, so you know of package.json file. We will include these nodejs dependency modules into package.json file,

{
  "name": "node-restapi",
  "version": "1.0.0",
  "description": "",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": {
    "name": "Adam",
    "email": "restapi2example@gmail.com",
    "url": "https://restapiexample.com/"
  },
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.16.1",
    "express": "^4.14.1",
    "mysql": "^2.13.0"
  }
}

Save above package.json file and run the npm install command:

~/node-restapi$  npm install

Table of Contents

MySQL database & table creation

We will create 'dummy_db' name database in MySQL host. We will create 'employee' table into this database using below SQL query.

CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

Create node js rest api using Express and MySQL

let’s create nodejs rest API to access records from the database, add records to the table, and delete records from the MySQL database. It’s a very common crud operation for any application.

Step 1: Created main.js file into node js project and created dependency module instances.

var http = require("http");
var express = require('express');
var app = express();
var mysql      = require('mysql');
var bodyParser = require('body-parser');

Step 2: Create a MySQL connection in main.js file.

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'dummy_db'
});


connection.connect(function(err) {
  if (err) throw err
  console.log('You are now connected...')
})

Step 3: Now we will add body-parser the configuration like the below:

app.use( bodyParser.json() );       // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({     // to support URL-encoded bodies
  extended: true
}));

Create node.js Server

We will create node.js express server that will listen to our request on a particular port. I am running node server on 3000 port, you can change the port as per your port availability.

var server = app.listen(3000, "127.0.0.1", function () {

  var host = server.address().address
  var port = server.address().port

  console.log("Example app listening at http://%s:%s", host, port)

});

Node.js Rest API to fetch all records from MySQL Database Using Express

We will create a new GET type rest request to access all employee records from the MySQL database table. We will create a MySQL query to fetch data from the database and send JSON data to the client as a response object. We will test this rest endpoint using the browser or Postman.

//rest api to get all results
app.get('/employees', function (req, res) {
   console.log(req);
   connection.query('select * from employee', function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

res.end() method sends data to the client a JSON string through JSON.stringify() method.

Now you can access this request from a browser or postman, I am accessing GET type request from the browser using http://localhost:3000/employees, You should see all employee data in JSON format which was fetched from MySQL database.

Node.js Rest API to a single record from Mysql Database Using Express

We will create a new GET type rest request to access a single employee record from the MySQL database table. We will create a MySQL query to fetch the record of a particular employee and send JSON data to the client as a response object. We will test this rest endpoint using Postman/browser.

//rest api to get a single employee data
app.get('/employees/:id', function (req, res) {
   connection.query('select * from employee where id=?', [req.params.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

Now access http://localhost:3000/employees/1 rest API URL form the browser and you will get a single employee record from MySQL database whose id is 2, if you will not get then something happened wrong, maybe id does not exist in table or MySQL is not configured properly.

Node js Rest Api to Create New Record into MySQL Database

We have fetched records from the MySQL database using express, so the next step will create a new entry into the MySQL database table using rest api. The rest API would be a POST type because We will post some JSON data to the server.

//rest api to create a new record into mysql database
app.post('/employees', function (req, res) {
   var postData  = req.body;
   connection.query('INSERT INTO employee SET ?', postData, function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

I am testing the above rest call http://localhost:3000/employees/ using Postman and set JSON data to body, The JSON data is:

{"employee_name":"Adam","employee_salary":170750,"employee_age":30}

Node js Rest API to Update Record into MySQL Database

We will create a new Restful API using nodejs and express to update data into the MySQL database. We need the employee id of whom we want to update the record. We will create PUT type request to update the record in the MySQL database. We will post some JSON data to the server with the employee ID.

//rest api to update record into mysql database
app.put('/employees', function (req, res) {
   connection.query('UPDATE `employee` SET `employee_name`=?,`employee_salary`=?,`employee_age`=? where `id`=?', [req.body.employee_name,req.body.employee_salary, req.body.employee_age, req.body.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

As you can see, I have created an UPDATE MySQL query to update the record in the database and send JSON response to the client. You can test the above node js rest call http://localhost:3000/employees/ using Postman with a PUT type request. We need to post the following JSON data:

{"employee_name":"Adam12","employee_salary":170,"employee_age":32, "id" : 59}

PUT type Rest Request Testing Using Postman Rest Client

Node js Rest Api to Delete Record from MySQL Database Table

Finally, we will create a new node js rest API to create a DELETE Type request to remove employee records from the MySQL database table. We will pass employee ID as a parameter that we want to delete from the MySQL table.

//rest api to delete record from mysql database
app.delete('/employees', function (req, res) {
   console.log(req.body);
   connection.query('DELETE FROM `employee` WHERE `id`=?', [req.body.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end('Record has been deleted!');
	});
});

As you can see, I have created a Delete MySQL query to delete a record from the database and send a message response to the client. You can test the above node js rest call http://localhost:3000/employees/ using Postman with a DELETE type request. We need to post the following JSON data:

{"id" : 59}

DELETE type Rest API Testing Using Postman Client

The final `main.js` file code:

var http = require("http");
var express = require('express');
var app = express();
var mysql      = require('mysql');
var bodyParser = require('body-parser');

//start mysql connection
var connection = mysql.createConnection({
  host     : 'localhost', //mysql database host name
  user     : 'root', //mysql database user name
  password : '', //mysql database password
  database : 'dummy_db' //mysql database name
});

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

//start body-parser configuration
app.use( bodyParser.json() );       // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({     // to support URL-encoded bodies
  extended: true
}));
//end body-parser configuration

//create app server
var server = app.listen(3000,  "127.0.0.1", function () {

  var host = server.address().address
  var port = server.address().port

  console.log("Example app listening at http://%s:%s", host, port)

});

//rest api to get all results
app.get('/employees', function (req, res) {
   connection.query('select * from employee', function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

//rest api to get a single employee data
app.get('/employees/:id', function (req, res) {
   console.log(req);
   connection.query('select * from employee where id=?', [req.params.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

//rest api to create a new record into mysql database
app.post('/employees', function (req, res) {
   var postData  = req.body;
   connection.query('INSERT INTO employee SET ?', postData, function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

//rest api to update record into mysql database
app.put('/employees', function (req, res) {
   connection.query('UPDATE `employee` SET `employee_name`=?,`employee_salary`=?,`employee_age`=? where `id`=?', [req.body.employee_name,req.body.employee_salary, req.body.employee_age, req.body.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end(JSON.stringify(results));
	});
});

//rest api to delete record from mysql database
app.delete('/employees', function (req, res) {
   console.log(req.body);
   connection.query('DELETE FROM `employee` WHERE `id`=?', [req.body.id], function (error, results, fields) {
	  if (error) throw error;
	  res.end('Record has been deleted!');
	});
});

Conclusion

We have covered MySQL connection, body-parser configuration, and express js configuration with nodejs application. We have also created a CRUD rest API example that helps to create a new record into the database using express, update the record into MySQL using express, fetch all records from MySQL using rest API, and delete the record from MySQL using express node js.