Create a RESTful API using Node and Express with MySQL Database
Wall Script
Wall Script
Sunday, April 19, 2020

Create a RESTful API using Node and Express with MySQL Database

Node Express web framework is a best solution to create RESTful APIs in quick time. Previously we published some concepts with different technologies like PHP and Java. This article will explain to you a more simple way to use external plugins to enrich your project APIs. Here you will find to create GET and POST requests with request payload validations to protect the endpoints.

Node Express MySQL RESTful


We have already discussed how to create RESTful APIs using PHP and Java in my previous tutorials.

You have to install NodeJs and MySQL softwares. You will find the SQL file in Git repository.

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


Initialize NodeJS
Create a project folder and initialize Node project.
$npm init

This will generate a package.json with product information. Give starting file as src/index.js.
About to write to /Users/SrinivasTamada/Desktop/projects/Node-Express-MySQL-Restful/package.json:

{
"name": "node-express-mysql-restful",
"version": "1.0.0",
"description": "Node express MySQL restful api",
"main": "src/index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"repository": {
"type": "git",
"url": "git+https://github.com/srinivastamada/Node-Express-MySQL-Restful.git"
},
"keywords": [
"Node",
"Express",
"MySQL",
"Restful"
],
"author": "Srinivas Tamada",
"license": "ISC",
"bugs": {
"url": "https://github.com/srinivastamada/Node-Express-MySQL-Restful/issues"
},
"homepage": "https://github.com/srinivastamada/Node-Express-MySQL-Restful#readme"
}


Is this OK? (yes) y

Install Dependancies
You have to following Node dependencies to improve the project.

Express
Express is a web application framework, that provides restful endpoints.
npm install express

MySQL
This helps you connect MySQL database. Install XAMPP for MySQL database.
npm install mysql

Nodemon
Nodemon is a tool that helps to Node application continuously.
npm install nodemon

.gitignore
Create an ignore file for Git and exclude node_modules.
/node_modules

.prettierrc
Prettier configuration file. Here I have enabled singleQuote value true
{
"singleQuote": true
}

Project Structure
Create a folder src and create two blank JavaScript files index.js and queries.js
Node Express MySQL RESTful

index.js - Create Node Server
Create a Node Express server and it runs a port 3030. Here app.get method create a GET endpoint with /hello
const express = require('express');
const PORT = process.env.PORT || 3030;
const app = express();

app.get('/hello', (req, res, next) => {
  res.send('Hello');
});

app.listen(PORT, () => {
console.log('Server is running on PORT:', PORT);
});

Start Server
npm run dev

Run at http://localhost:3030/hello
Open the browser and hit the url and you will find the following message.
Node Express MySQL RESTful

Working with MySQL
Install MySQL database and create the users and messages table. If you are using XAMPP, start the MySQL server.
Node Express MySQL RESTful

queries.js
This file contains all of the querie methods and export the methods. Using MySQL connection getting all of the users data with promise resolve.
const mysql = require('mysql');

const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node',
});

const users = async () => {
const promise = new Promise((resolve, reject) => {
connection.query('SELECT uid, username FROM users', function (
error,
data,
fields
) {
if (error) {
  reject(err);
} else {
  resolve(data);
}
});
});
return promise;
};

module.exports = {
users,
};

index.js
Import the queries.js and connect with /users GET restful endpoint.
const express = require('express');
const queries = require('./queries');
const PORT = process.env.PORT || 3030;
const app = express();

app.get('/hello', (req, res, next) => {
res.send('Hello');
});

app.get('/users', async (req, res, next) => {
try{
  queries.users().then(data=>{
  res.json(data);
})
} catch(err){
  next(err)
}
});

app.listen(PORT, () => {
console.log('Server is running on PORT:', PORT);
});

http://localhost:3030/users
You will access all the users information from the database.
Node Express MySQL RESTful

Request Params - http://localhost:3030/user/2

index.js
Express is allowing to access request parameters with routes. Here create an user endpoint with user id.
app.get('/user/:uid', async (req, res, next) => {
const uid = req.params.uid;
try{
queries.user(uid).then(data=>{
res.json(data);
})
} catch(err){
next(err)
}
});

queries.js
Create an another method to get the particular user details based on the user id.
const user = async (uid) => {
const promise = new Promise((resolve, reject) => {
connection.query(
'SELECT uid, username, email FROM users WHERE uid = "' + uid + '"',
function (error, data, fields) {
if (error) {
  reject(err);
} else {
  resolve(data);
}
}
);
});
return promise;
};

POST Restful Calls
Need few more plugins to complete the POST requests.

body-parse
Request body JSON parsing middleware.
npm install body-parser

bcrypt
This plugin helps to encrypt the user password in secure way.
npm install bcrypt


index.js - /signup
Create POST method and pass the req.body to queries.signup method.
const express = require('express');
const queries = require('./queries');
const PORT = process.env.PORT || 3030;
const app = express();

/* JSON body parse*/
const bodyParser = require('body-parser')
app.use(bodyParser.urlencoded({ extended: true }))
app.use(bodyParser.json())


app.get('/users', async (req, res, next) => {
...
...
});

app.get('/user/:uid', async (req, res, next) => {
...
...
});

app.post('/signup', async (req, res, next) => {
const postData = req.body;
try{
queries.signup(postData).then(data=>{
res.json({'status': 'success'});
})
} catch(err){
next(err)
}
});

app.listen(PORT, () => {
console.log('Server is running on PORT:', PORT);
});


queries.js -signup
Here assigning the postData values and encrypting the password with hash value. Insert statement insert the values into the database.
const mysql = require('mysql');
const bcrypt = require('bcrypt');
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node',
});

const users = async () => {
...
...
};

const user = async (uid) => {
...
...
};

const signup = async (postData) => {
const username = postData.username;
const email = postData.email;
const password = postData.password;

const salt = bcrypt.genSaltSync();
const encryptedPassword = bcrypt.hashSync(password, salt);
const promise = new Promise((resolve, reject) => {
connection.query(
'INSERT INTO users (username,email,password) VALUES ("' +
username +
'","' +
email +
'","' +
encryptedPassword +
'")',
function (error, data, fields) {
if (error) {
reject(err);
} else {
resolve(data);
}
}
);
});
return promise;
};

module.exports = {
users,
user,
signup,
};

You need a postman application to test the POST request. Submit a JSON request with raw.
Node Express MySQL RESTful

Request Body Validation
You have to apply request validations to protect the RESTful endpoints.

Express Validation
This is middleware validation and it can use JOI validation rules.
npm install express-validation

index.js - validation
You can apply validations with Joi methods.
const express = require('express');
const queries = require('./queries');
const PORT = process.env.PORT || 3030;
const app = express();

/* JSON body parse*/
const bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json());

/* Request validation */
const { validate, ValidationError, Joi } = require('express-validation');


app.get('/users', async (req, res, next) => {
try {
queries.users().then((data) => {
res.json(data);
});
} catch (err) {
res.json(err);
}
});

/* ui validation*/
const uidValidation = {
params: Joi.object({
uid: Joi.number().integer().required(),
}),
};

app.get('/user/:uid', validate(uidValidation), async (req, res, next) => {
const uid = req.params.uid;
try {
queries.user(uid).then((data) => {
res.json(data);
});
} catch (err) {
res.json(err);
}
});

/* Signup validation*/
const signupValidation = {
body: Joi.object({
email: Joi.string().email().required(),
username: Joi.string().min(3).max(20).required(),
password: Joi.string().regex(/^[a-zA-Z0-9]{8,30}$/).required(),
}),
};

app.post('/signup', validate(signupValidation), async (req, res, next) => {
const postData = req.body;
try {
queries.signup(postData).then((data) => {
res.json({ status: 'success' });
});
} catch (err) {
res.json(err);
}
});

/* Validation error status*/
app.use(function (err, req, res, next) {
if (err instanceof ValidationError) {
return res.status(err.statusCode).json(err);
}
return res.status(500).json(err);
});

app.listen(PORT, () => {
console.log('Server is running on PORT:', PORT);
});

Email validation error
Node Express MySQL RESTful

Note: You will find more endpoints in the GIT repository.

CORS on ExpressJS - Cross Domain
If you are deploy this on different domain, you have to allow origins to connect cross domain. You can restrict the Access-Control-Allow-Origin value with your application domain. Here * means allowing all access point like Mobile, Desktop etc.
/* CORS cross domain
* Replace * with your domain
*/
app.use(function(req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
next();
});
web notification

2 comments:

  1. Hi Srinivas,

    I observed few things can imporve from your aricle.I'll list down here

    1. You should have used some ORM, writing queries are not secure
    2. Regarding folder structure you should have divided into biz, controllers and util folders
    3. Docker file managemnt
    4. .env file missing

    "scripts": {
    "start": "if [$NODE_ENV == 'production']; then node app.js; else nodemon app.js; fi"
    }



    ReplyDelete
  2. hi sir..for performance wise.. is this good enough to handle many request.

    ReplyDelete

mailxengine Youtueb channel
Make in India
X