Create a RESTful API using NodeJS and MySQL Database
Wall Script
Wall Script
Sunday, February 05, 2017

Create a RESTful API using NodeJS and MySQL Database

These days, there are no projects that do not have a REST API for creating professional web services. REST ( Representational State Transfer) consists set of guidelines to build a web service, which can be used to modify or view specific information on the web without performing any server side operations. Using RESTful APIs saves huge amount of time. Twitter, Facebook, Google and thousands of companies use REST APIs. This is because REST is the most efficient and widespread standard in the creation of APIs for Internet services.

Create RESTful API NodeJS Mysql


We have already discussed how to create RESTful APIs using PHP and Java in my previous tutorials. Let’s see how to create RESTful API usig Node for processing HTTP requests. With this API created, you can just use it with any application that submits HTTP request. It is the best way to work especially if you are building any mobile application.


You have to install NodeJs and MySQL softwares, create following tables or download sql file from my Git repository and import.

Users Table
User table contains all the users registration details.
CREATE TABLE `users` (
`uid` int(11) AUTO_INCREMENT,
`username` varchar(50),
`password` varchar(200),
`email` varchar(200),
PRIMARY KEY (`uid`)
);

Messages Table
This table contains all of the user messages.
CREATE TABLE `messages` (
`mid` int(11) AUTO_INCREMENT,
`message` text,
`uid_fk` int(11),
PRIMARY KEY (`mid`)
);

Create RESTful API NodeJS user Mysql

Setup Node Restful Project

Go to Terminal or Command Line, create a project folder.
$ mkdir nodeRestful

$ cd nodeRestful

Initialize your node project, this will create a package.json file.
$ node init

Install hapi.js plugin for Restful APIs.
$ npm install hapi --save

Create a Node Hapi Server

server.js
Open text editor, copy following code. Here const (JavaScript ES6) is a identifier won’t be reassigned, if you want you can use var.
'use strict';
const Hapi = require('hapi');

// Create a server with a host and port
const server = new Hapi.Server();
server.connection({
    host: 'localhost',
    port: 8000
});

// Add the route
server.route({
    method: 'GET',
    path:'/helloworld',
    handler: function (request, reply) {
    return reply('hello world');
}
});

server.start((err) => {
   if (err) {
     throw err;
   }
  console.log('Server running at:', server.info.uri);
});

Run Project
$ sudo npm start

Open your browser and launch the following URL, you will find "hello world" result.
http://localhost:8000/helloworld

Database Connection

MySQL Database
Install MySQL package for Node using node package manager(NPM).
npm install mysql --save

server.js
Modify the server.js and include MySQL connection code. Here you have to modify the MySQL database name, host, username and password.
'use strict';
const Hapi = require('hapi');
const MySQL = require('mysql');

// Create a server with a host and port
const server = new Hapi.Server();

const connection = MySQL.createConnection({
     host: 'localhost',
     user: 'username',
     password: 'password',
     database: 'database_name'
});

server.connection({
    host: 'localhost',
    port: 8000
});

connection.connect();

.....
.....
.....


Get Users Details
Here is the route for users data. Download poster extension for Chrome browser, this will help you to test these Restful APIs.
http://localhost:8000/users
server.route({
    method: 'GET',
    path: '/users',
    handler: function (request, reply) {
       connection.query('SELECT uid, username, email FROM users',
       function (error, results, fields) {
       if (error) throw error;

       reply(results);
    });
  }
});

Create RESTful API NodeJS user Mysql

Working with Parameters

Parameter Validations
For valid inputs, install Joi package for Node.
npm install joi --save

Username validation minimum value 3 characters and maximum 30.
username: Joi.string().alphanum().min(3).max(30).required()

Password regular expression min 8 and max 30
password: Joi.string().regex(/^[a-zA-Z0-9]{8,30}$/)

Message validation supports both strings and numbers.
message: [Joi.string(), Joi.number()]

Birth year validation
birthyear: Joi.number().integer().min(1900).max(2013)

Email validation
email: Joi.string().email()

Get User Data
Here getting the user data based on uid value, Joi.number().integer() validating the user id input.
http://localhost:8000/user/1
const Joi = require('joi');

server.route({
    method: 'GET',
    path: '/user/{uid}',
    handler: function (request, reply) {
    const uid = request.params.uid;

    connection.query('SELECT uid, username, email FROM users WHERE uid = "' + uid + '"',
    function (error, results, fields) {
       if (error) throw error;

       reply(results);
    });
    },
   config: {
       validate: {
        params: {
        uid: Joi.number().integer()
       }
  }
}
});

Create RESTful API NodeJS user Mysql

POST
Get user message details, post parameters works with payload.
http://localhost:8000/messages
server.route({
    method: 'POST',
    path: '/messages',
    handler: function (request, reply) {

    const uid = request.payload.uid;
    connection.query('SELECT * FROM messages WHERE uid_fk = "' + uid + '"',
    function (error, results, fields) {
        if (error) throw error;

        reply(results);
    });
},
config: {
    validate: {
    payload: {
    uid: Joi.number().integer()
}
}

}
});

Create RESTful API NodeJS user Mysql

Delete
Delete user message data based on user and message ids.
http://localhost:8000/message/1/3
server.route({
    method: 'DELETE',
    path: '/message/{uid}/{mid}',
    handler: function (request, reply) {
    const uid = request.params.uid;
    const mid = request.params.mid;
    connection.query('DELETE FROM messages WHERE uid_fk = "' + uid + '"AND 
    mid = "' + mid + '"',
    function (error, result, fields) {
       if (error) throw error;

       if (result.affectedRows) {
           reply(true);
       } else {
           reply(false);
       }
});
},
config: {
     validate: {
     params: {
       uid: Joi.number().integer(),
       mid: Joi.number().integer()
      }
     }
}
});

Create RESTful API NodeJS user Mysql

Encryption
We need to encrypt user password, bcrypt package will provide you the salt encryption code.
$ npm install bcrypt --save

Create RESTful API NodeJS user Mysql


POST User Signup
User signup with encrypted user password.
server.route({
    method: 'POST',
    path: '/signup',
    handler: function (request, reply) {
    const username = request.payload.username;
    const email = request.payload.email;
    const password = request.payload.password;

    //Encryption
    var salt = Bcrypt.genSaltSync();
    var encryptedPassword = Bcrypt.hashSync(password, salt);

    //Decrypt
    var orgPassword = Bcrypt.compareSync(password, encryptedPassword);

    connection.query('INSERT INTO users (username,email,passcode) VALUES 
    ("' + username + '","' + email + '","' + encryptedPassword + '")',
    function (error, results, fields) {
        if (error) throw error;

        reply(results);
    });
},
config: {
      validate: {
       payload: {
          username: Joi.string().alphanum().min(3).max(30).required(),
          email: Joi.string().email(),
          password: Joi.string().regex(/^[a-zA-Z0-9]{8,30}$/)
       }
    }
}
});

Create RESTful API NodeJS user Mysql

Create RESTful API NodeJS user Mysql

web notification

22 comments:

  1. Amazing article

    Why hapi over expressjs

    ReplyDelete
  2. my result

    {"statusCode":404,"error":"Not Found"}

    ReplyDelete
  3. It's simple, effective and easy like always you do Mr. Tamada! Thanks

    ReplyDelete
  4. Thanks for this useful article...

    Need 2 mods Mr. Tamada:

    http://localhost:8000/helloword ---> http://localhost:8000/helloworld

    and

    when install bcrypt have an error, may be you need to do this:
    npm install --save bcryptjs && npm uninstall --save bcrypt
    finally the require must be:
    const Bcrypt = require('bcryptjs');

    Best regards!

    ReplyDelete
  5. This Article Very Useful my college study
    Thank you

    ReplyDelete
  6. great tutorial, very usefull for me..

    ReplyDelete
  7. why did you use tapi over express js?

    ReplyDelete
  8. Nice tutorial !
    As several people already asked here why did you choose toy use 'hapi' and not 'express'?
    What are the advantages of hapi ?

    ReplyDelete
  9. hi
    how can i add header here facing 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:3000' is therefore not allowed access.
    can you pls help on this

    ReplyDelete
  10. Good one.Learnt node.js application creation.How do we know the package name for a functionality.

    ReplyDelete
  11. Great tutorial, very easy to undestand!

    ReplyDelete
  12. Very nice... need to close db connection?

    ReplyDelete
  13. My bro youre really expert, thank you very very much ;)

    ReplyDelete
  14. I copied your code from Github and installed all the packages mentioned in the tutorial but I get the error code "Error: connect ECONNREFUSED" when running npm start on the command line. The server starts up but I get this error code.

    ReplyDelete

mailxengine Youtueb channel
Make in India
X