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.

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.

Node RestfulDownload this project on Github

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

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

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

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()
}
}

}
});

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()
}
}
}
});

Encryption

We need to encrypt user password, bcrypt package will provide you the salt encryption code.

$ npm install bcrypt --save

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}$/)
}
}
}
});