Node JS REST API Example Using Restify and MySQL

Restify is a very popular nodejs restful framework to create rest API. This node js tutorial help to create CRUD functionality for employee, Like we can add employee, edit employee, view employee data, and delete employee using restapi. I will use MySQL as a database for the employee table.

The node.js and MySQL both are open source so you can create rest calls for mobile, web applications free of cost without any investment. You don’t need to purchase a license for the database and server.

I have already covered Simple Hello Nodejs tutorial, Add,edit and delete Record using mysql and nodejs and Simple Rest api example using Nodejs, ExpressJS and MySQL. I am not covering some basics of node js like how to create nodejs?, what is package.json?, How to install npm. I am assuming you guys aware about these things, now lets go new things of nodejs.

We will cover following functionality into this node js tutorial:

• How to create database and table into mysql server
• How to add routing with nodejs application
• Created CRUD operation using REST API and MySQL

Simple Example of Rest API Using Nodejs, Restify with MySQL

Restify is popular and handy rest framework to create restful api. I am using MySQL to create database and table.I will use acceptParser,queryParser and bodyParser plugin for parsing rest api json data.

I am using following files and folder

I am creating a folder 'nodejs-restify-restapi-example'.This is our nodejs project name.

package.json: This file will have all nodejs dependencies module for this example.
config.js: This file will use for database connection parameters and object for mysql.
main.js: This file will use to create nodejs application server and routes url.
node_modules folder: This folder will contains all nodejs packages.

Create MySQL Database and Table Structure for Customer Table

I am using MySQL server for store user registration information into MySQL table.We will create 'test' name database into MySQL server.We will create 'employee' table into 'test' mysql database using below script.

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT 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',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='datatable demo table' AUTO_INCREMENT=3 ;

This node.js rest API tutorial help to get all records, add a record, edit a record and delete a record from the MySQL database using restful API. These rest API communicate with MySQL and update data into the MySQL database using HTTP rest call. I will use HTTP POST, GET, PUT and DELETE type requests for various rest API calls.

We will use following dependency libraries in this nodejs project:

• Restify js : restify is a rest framework for building web applications on top of Node.js
• MySQL : This nodejs module help to create connection with MySQL database and allow SQL queries with table

Create Package.json file

We will create package.json file for this rest api nodejs application into root path of node js application 'nodejs-restify-restapi-example'.This file define what libraries or modules will be use in this nodejs project.You need to add below code into package.json file.

{
  "name": "nodejs-restapi",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "mysql": "^2.14.1",
    "restify": "^5.2.0"
  }
}

lets install node js module using npm command, now open cmd window and go to path of your node application, like d:/nodejs-restify-restapi-example and run below example.

d:/nodejs-restify-restapi-example> npm install

Above command will install all dependency node js modules into node_modules folder.

The Node js Rest API details are as follows:

Route	Method	Type	Posted JSON	Description
/employees	GET	JSON	–	Get all employees data
/employees/{id}	GET	JSON	–	Get a single employee data
/employees	POST	JSON	{"employee_name": "Rachel", "employee_age": "34", "employee_salary" : "23421"}	Insert new employee record into database
/employees	PUT	JSON	{"employee_name": "Rachel", "employee_age": "34", "employee_salary" : "23421", "id":21}	Update employee record into database
/employees	DELETE	JSON	{"id" : 59}	Delete particular employee record from database

Also, Checkout other tutorials of nodejs rest api,

• CRUD Rest API using NodeJS, Express, MongoDB and Mongoose
• Angular File Upload Using Angular 1.x and PHP

Create MySQL Connection with Nodejs

MySQL is very popular opensource relational database.We will create MySQL database connection into config.js file The config.js file location would be d:\nodejs-restify-restapi-example\config.js. We will add following code into this file.

'use strict'

var mysql = require('mysql');

module.exports = {
    name: 'rest-api',
    hostname : 'http://localhost',
    version: '0.0.1',
    env: process.env.NODE_ENV || 'development',
    port: process.env.PORT || 3000,
    db: {
        get : mysql.createConnection({
			host     : 'localhost',
			user     : 'root',
			password : '',
			database : 'test1'
		})
    }
}

I am using restrict JavaScript mode in this file and exported the MySQL module. I have passed the database username, password, name, and hostname. I will use this MySQL connection object into main.js file for CRUD rest API operation.

Create Node.js Rest Api using Restify and MySQL

We have created structure of the nodejs project, Now we will create a main.js file in the root of nodejs project.The index.js file location will d:\nodejs-restify-restapi-example\main.js.

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

	const config  = require('./config'),
      restify = require('restify'),
      mysql      = require('mysql')

I have also included config.js file which has mysql connection object.

Step 2: Get MySQL connection object tomain.js file.

var connection = config.db.get;

Step 3: We will add some restify plugin for json data parser and configuration like below,

server.use(restify.plugins.acceptParser(server.acceptable));
server.use(restify.plugins.queryParser());
server.use(restify.plugins.bodyParser());

Create node.js Server

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

/**
 * Initialize Server
 */
const server = restify.createServer({
    name    : config.name,
    version : config.version,
    url : config.hostname
});
server.listen(3001, function () {
  console.log('%s listening at %s', server.name, server.url);
});

Node.js Rest Api to fetch all record from MySQL Database Using Restify

We will create a GET Rest Request to access all employee records from the MySQL database table. We will use MySQL query to fetch data from the employee table and send JSON data to the client as a response object.

//rest api to get all results
server.get('/employees', function (req, res) {
   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 access http://localhost:3001/employee rest API URL from the browser and you will get all employees record from the MySQL database.

Rest Api to get single record from MySQL Database Using Node.js

We will create a GET type rest request to access a single employee record from the MySQL database table. We will use MySQL query to fetch records of a particular employee and send JSON data to the client as a response object.

//rest api to get a single employee data
server.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));
	});
});

Rest Api to Create New Record into MySQL Using Node.js and Restify

We will create a new Rest API to create a new employee data entry into the MySQL database table using node.js. I will create a POST type Rest request because We will post some JSON data to the node server.

//rest api to create a new record into mysql database
server.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 Using Node JS

We will create a new PUT type Restful api request using nodejs and restify to update data into the MySQL database.We need to pass employee id which will use to updated record into table.

//rest api to update record into mysql database
server.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));
	});
});

Node js Restful Api to Delete Record from MySQL Database

We will create a new DELETE Type rest Api request using node js 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
server.delete('/employees/:id', function (req, res) {
   connection.query('DELETE FROM `employee` WHERE `id`=?', [req.params.id], function (error, results, fields) {
    if (error) throw error;
    res.end('Record has been deleted!');
  });
});

Conclusion

We have created simple nodejs application and installed nodejs module dependencies using npm installer. We have also connected MySQL using node js. Create Rest API to get all employees, get single employee record, create new employee entry, update employee data and delete employee data from MySQL database using nodejs restify framework.

You can download source code from below link.