Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Node Rest api using hapi and mysql

NodeRestful

Create Node Restful APIs with MySQL Database

Install Node JS and MySQL Software, create a database and import SQL file.

Go to terminal or command line.

Execute following commands to run this application.

Start Project


$ cd NodeRestful

$ npm install

$ npm start

Open your browser

http://localhost:8000/helloworld

Tutorial: Create a RESTful API using NodeJS and MySQL Database

##GET

http://localhost:8000/users
http://localhost:8000/user/user_id

##POST

http://localhost:8000/messages
http://localhost:8000/signup
http://localhost:8000/sendMessage
-- phpMyAdmin SQL Dump
-- version 4.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 06, 2017 at 03:09 AM
-- Server version: 10.1.19-MariaDB
-- PHP Version: 5.6.24
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `database_name`
--
-- --------------------------------------------------------
--
-- Table structure for table `messages`
--
CREATE TABLE `messages` (
`mid` int(11) NOT NULL,
`message` text,
`uid_fk` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `messages`
--
INSERT INTO `messages` (`mid`, `message`, `uid_fk`) VALUES
(1, 'Make people fall in love with your ideas.', 1),
(2, 'The Social Network Script http://www.thewallscript.com.', 1),
(3, 'The Oauth Login http://www.oauthlogin.com', 1),
(4, 'Digital Marketing https://www.balloonnetworks.com/', 2);
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`uid` int(11) NOT NULL,
`username` varchar(50) DEFAULT NULL,
`password` varchar(200) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`uid`, `username`, `password`, `email`) VALUES
(1, 'Dhaval Patel', '$2a$10$yDPkHJJFORyk7I2OWHj6Seq5KFc7reyD7iygpvLnOBo0tQTslZcW.', 'mdhaval_patel@yahoo.com'),
(2, 'patel', '$2a$10$20gRVeNMjsR935tT/TVe2.yAVQh5cLm17XEh7jZ50dQ9lK0nzkn/i', 'mdhaval_patel@yahoo.com');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
ADD PRIMARY KEY (`mid`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`uid`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
MODIFY `mid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
{
"name": "noderestfuljsonapi",
"version": "1.0.0",
"description": "Node Restful",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "node server.js"
},
"keywords": [
"node",
"restful"
],
"author": "Dhaval Patel",
"license": "ISC",
"dependencies": {
"bcrypt": "^1.0.2",
"hapi": "^16.1.0",
"joi": "^10.2.1",
"mysql": "^2.13.0"
},
"devDependencies": {}
}
'use strict';
const Hapi = require('hapi');
const MySQL = require('mysql');
const Joi = require('joi');
const Bcrypt = require('bcrypt');
// Create a server with a host and port
const server = new Hapi.Server();
const connection = MySQL.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'database_name'
});
server.connection({
host: 'localhost',
port: 8000
});
connection.connect();
server.route({
method: 'GET',
path: '/helloworld',
handler: function (request, reply) {
return reply('hello world1');
}
});
// Add the route
server.route({
method: 'GET',
path: '/users',
handler: function (request, reply) {
connection.query('SELECT uid, username FROM users', function (error, results, fields) {
if (error) throw error;
console.log(results);
reply(results);
});
}
});
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;
console.log(results);
reply(results);
});
},
config: {
validate: {
params: {
uid: Joi.number().integer()
}
}
}
});
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;
var salt = Bcrypt.genSaltSync();
var encryptedPassword = Bcrypt.hashSync(password, salt);
var orgPassword = Bcrypt.compareSync(password, encryptedPassword);
connection.query('INSERT INTO users (username,email,password) VALUES ("' + username + '","' + email + '","' + encryptedPassword + '")', function (error, results, fields) {
if (error) throw error;
console.log(results);
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}$/)
}
}
}
});
server.route({
method: 'POST',
path: '/sendMessage',
handler: function (request, reply) {
const uid = request.payload.uid;
const message = request.payload.message;
connection.query('INSERT INTO messages (message,uid_fk) VALUES ("' + message + '","' + uid + '")', function (error, results, fields) {
if (error) throw error;
console.log(results);
reply(results);
});
},
config: {
validate: {
payload: {
uid: Joi.number().integer(),
message: [Joi.string(), Joi.number()]
}
}
}
});
server.route({
method: 'POST',
path: '/messages',
handler: function (request, reply) {
const uid = request.payload.uid;
console.log(uid);
connection.query('SELECT * FROM messages WHERE uid_fk = "' + uid + '"', function (error, results, fields) {
if (error) throw error;
console.log(results);
reply(results);
});
},
config: {
validate: {
payload: {
uid: Joi.number().integer()
}
}
}
});
server.route({
method: 'DELETE',
path: '/message/{uid}/{mid}',
handler: function (request, reply) {
const uid = request.params.uid;
const mid = request.params.mid;
console.log(uid + "---" + 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()
}
}
}
});
// Start the server
server.start((err) => {
if (err) {
throw err;
}
console.log('Server running at:', server.info.uri);
});
@ianobre

This comment has been minimized.

Copy link

@ianobre ianobre commented Oct 1, 2020

thank you so much for this file! Save my life!
Just adjust the Bcrypt to use this package: const Bcrypt = require('bcrypt-nodejs'); the Bcrypt packare return many errors

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment