CRUD operations using Nodejs,MySQL and Restify

RestAPIExample Team Rest API Example

Restify is very popular nodejs restful framework to create rest api. This nodejs tutorial help to create CRUD operations for customer module ,We will create rest api to add customer, edit customer, view customer data and delete customer using restapi. I will use MySQL as database for customer table.

The node.js and MySQL both are open source so you don’t worry to purchase license for database and server.

I have already covered Create Rest API Using Node.js, MySQL and Express and now create nodejs rest api using restify.

nodejs-mysql-express

We will cover following functionality into this node js tutorial:

  • How to create database and table into MySQL server
  • How to create MySQL database connection with nodejs
  • 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

I am creating a folder 'nodejs-restify-restapi-example'. This is our nodejs application name.I am using MySQL, acceptParser, queryParser and bodyParser plugin for parsing rest api json data.

I am using following files and folder

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 when we will use npm install command.

Create MySQL Database and Customer Table

We will create 'test' name database in MySQL host server and created a 'customer' table into this database using below SQL query,

CREATE TABLE IF NOT EXISTS `customer` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `Country` varchar(100) NOT NULL,
  `Phone` int(10) NOT NULL,
  `Created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I will use HTTP POST, GET, PUT and DELETE type request for various rest api call.

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

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.

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 : 'test'
		})
    }
}

I have passed 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 4001 port, you can change port as per your port availability.

/**
 * Initialize Server
 */
const server = restify.createServer({
    name    : config.name,
    version : config.version,
    url : config.hostname
});
server.listen(4001, 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 customer records from MySQL database table.We will use MySQL query to fetch data from customer table and send json data to client as response object.

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

Now access http://localhost:4001/customer rest api url form browser and you will get a all customers record from 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 customer record from MySQL database table.We will use MySQL query to fetch record of particular customer and send json data to client as response object.

//rest api to get a single customer data
server.get('/customer/:id', function (req, res) {
   connection.query('select * from customer 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 customer data entry into MySQL database table using node.js. I will create POST type Rest request because We will post some JSON data to node server.

//rest api to create a new record into mysql database
server.post('/customer', function (req, res) {
   var postData  = req.body;
   connection.query('INSERT INTO customer 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 new PUT type Restful api request using nodejs and restify to update data into MySQL database.We need to pass customer id which will use to updated record into table.

//rest api to update record into mysql database
server.put('/customers', function (req, res) {
   connection.query('UPDATE `customer` SET `Name`=?,`Address`=?,`Country`=?,`Phone`=? where `Id`=?', [req.body.Name,req.body.Address, req.body.Country, req.body.Phone, 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 new DELETE Type rest Api request using node js to remove customer record from MySQL database table.We will pass customer id as a parameters which we want to delete from MySQL table.

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

Conclusion

This tutorial help to create CRUD operations using nodejs and restify along with MySQL database.We have also use npm to install nodejs module dependencies.Create HTTP call to get all customer,get single customer record,create new customer entry,update customer data and delete customer data from MySQL database using nodejs and restify framework.