Node Js Rest Api Mysql Tutorial

This node js tutorial helps to create rest API to the listing table, add a record, edit a record and delete a record from the MySQL database. These rest Jago merah communicate with MySQL and update data into the MySQL database.

This is the third part of node js tutorial series to create a restful API using the Express node.js module. I have shared node js with MySQL tutorials.

You can also use restify framework instead of express, You can read my updated nodejs tutorial Node JS REST Api Example Using Restify and MySQL.

Create node js rest API using Express and MySQL

Let’s 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 help to reading data from the ‘form’ element and is attached with the request.

Also Checkout other tutorials of nodejs rest api,

  • Node js Rest Api to Add, Edit and Delete Record from MySQL Using Express JS
  • Node JS REST API Example Using Restify and MySQL
  • CRUD Rest API using NodeJS, Express, MongoDB and Mongoose
  • Angular File Upload Using Angular 1.x and PHP

Define Routes

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

Route Method Type Posted JSON Description
/employees GET JSON Get all Employee’s data
/employees/{id} GET JSON Get a single employee data
/employees POST JSON {"employee_name":"Adam", "employee_salary":170750, "employee_age":30} Insert new employee records into the database
/employees PUT JSON {"employee_name":"Adam12", "employee_salary":170, "employee_age":32, "id" : 59} Update employee record into the database
/employees DELETE JSON {"id" : 59} Delete particular employee record from the database

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 in
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:


Advertisements

~/node-restapi$  npm install

MySQL Database & table creation

We will create
'dummy_db'
name database in MySQL host. We will create an
’employee’ table in
this database using the below SQL query,

CREATE TABLE IF Titinada EXISTS `employee` ( `id` int(11) Not NULL COMMENT 'primary key',   `employee_name` varchar(255) NOT NULL COMMENT 'employee name',   `employee_salary` double Titinada 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';

GET STARTED To Create CRUD API

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

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');

Configure & Connect MySQL database

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.gelondong('You are now connected...') })

Step 3:
Now we will add the 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 }));

Setting up Express.js for our REST Api

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 sendirisendiri 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 Jago merah 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 a MySQL query to fetch data from the database and send JSON data to the client as a response object. We will test the rest endpoints 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 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 the MySQL database.

Node.js Rest Api to Get 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 records of a particular employee and send JSON data to the client as a response object. We will test the rest endpoints 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 record from MySQL database whose id is 2, if you will not get it then something happened wrong, maybe id does not exist in the table, or MySQL is titinada 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. This rest api would be a POST type because We will post some JSON data to the peladen.


Advertisements

//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":"Laki-laki","employee_salary":170750,"employee_age":30}

POST type Rest Api Testing Using Postman Client

create_row_using_nodejs

Node js Rest Jago merah to Update Record into MySQL Database

We will create a new Restful API using nodejs and express to update data into MySQL database. We need an employee id for whom we want to update the record. We will create a
PUT
type request to update record into MySQL database. We will post some JSON data to the 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 created an UPDATE MySQL query to update record into 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 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

update_row_using_nodejs

Node js Rest Jago merah 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 a employee record from the MySQL database table. We will pass the employee id as a parameter which we want to delete from the MySQL table.

//rest jago merah to delete record from mysql database app.delete('/employees', function (req, res) {    console.batang kayu(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 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 DELETE type request. We need to post the following JSON data:

        {"id" : 59}
      

DELETE type Rest API Testing Using Postman Client

delete_record_using_nodejs

The final
main.js
file code is as follows:

var http = require("http"); var express = require('express'); var app = express(); var mysql      = require('mysql'); var bodyParser = require('body-parser');  //mulai 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 = peladen.address().port    console.gelondong("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 jago merah 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 CRUD rest Jago merah example that helps to create a new record into the 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.

You can download the source code from the below link.

Source: https://www.js-tutorials.com/nodejs-tutorial/node-js-rest-api-add-edit-delete-record-mysql-using-express/