This is third part of nodejs tutorial series to create restful api using Express node.js module.I have shared node js with mysql tutorials.This node js tutorial help to create rest api to listing table, add record, edit record and delete record from MySQL database. These rest api communicate with MySQL and update data into mysql database.

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

I am using 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 connection with MySQL database and allow operations into table data.
• body-parser : This nodejs module help to reading data from the 'form' element and attached with request.

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

I am assuming you have read my both previous node.js tutorial, so you have knowledge 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": "[email protected]",
    "url": "http://www.js-tutorials.com/"
  },
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.16.1",
    "express": "^4.14.1",
    "mysql": "^2.13.0"
  }
}

save above package.json file and run npm install command:

~/node-restapi$  npm install

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 database, add record into table and delete record from mysql database.Its 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: Created 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 configuration like 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 our request on particular port.I am running noder server on 3000 port, you can change 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 record from MySQL Database Using Express

We will create a new GET type rest request to access all employee records from MySQL database table.We will create MySQL query to fetch data from database and send json data to client as response object.We will test this rest end points using 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 send data to client a json string through JSON.stringify() method.

Now you can access this get request from browser or postman,I am accessing GET type request from 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 MySQL database table.We will create MySQL query to fetch record of particular employee and send json data to client as response object.We will test this rest end points 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 browser and you will get a single employee records from MySQL database which id is 2, if you will not get then something happened wrong, may be id is not exist in table or MySQL is not configured properly.

Node js Rest Api to Create New Record into MySQL Database

We have fetched record from MySQL database using express, so next step will create a new entry into MySQL database table using rest api. This rest api would be a POST type because We will post some JSON data to 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 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}

POST type Rest API Testing Using Postman Client

Node js Rest Api to Update Record into MySQL Database

We will create new Restful api using nodejs and express to update data into MySQL database.We need employee id whom we want to update record.We will create PUT type request to update record into MySQL database.We will post some JSON data to server with 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 create UPDATE MySQL query to update record into database and send json response to client.You can test above node js rest call http://localhost:3000/employees/ using postman with PUT type request.We need to post 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 frpm MySQL Database Table

Finally we will create new node js rest api to create DELETE Type request to remove employee record from MySQL database table.We will pass employee id as a parameters which we want to delete from 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 create Delete MySQL query to delete record from database and send message response to client.You can test above node js rest call http://localhost:3000/employees/ using postman with DELETE type request.We need to post 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,express js configuration with nodejs application.We have also created CRUD rest api example that help to create a new record into database using express, update record into MySQL using express, fetch all records from MySQL using rest api and delete record from MySQL using express node js.

The post CRUD API Using Nodejs and Express appeared first on Rest Api Example.