Create Restful API with nodejs and mysql database

    Dec 15, 2016       by Pankaj Kumar
API-in-Nodejs-with-mysql-db.jpg

Today we are going to create a demo to create API in nodejs with mysql databse using express framework with better structure.

Tools required:

Lets begin with following steps:

Open your terminal and kindly follow the following steps

  1. Create a Folder name node-mysql - mkdir node-mysql

  2. Navigate to the root of your newly created folder - cd node-mysql

  3. Create a package.json file - npm init
    Package.json is a file that gives the necessary information to npm which allows it to identify the project as well as handle the project's dependencies.
    npm init will prompt you to enter some information such as the app name, description, version, author, keyword and also ask if what you see is what you like.

Folder Structure:

 
DAO
--audioDAO.js
--userDAO.js
Models
--Audio.js
--User.js
node_modules
-----
------Packagaes installed by npm command is saved in node_modules.
------ `npm installed Packagaes` is used to install any packagae
-------
Routes
--user.js
Services
--user.js
Utilities
--config.js
--cred.js
--environment.js
--dbConfig.js
--template.js
--util.js
package-lock.json
package.json
server.js
 

 

In above folder we can see DAO folder,  where we will perform entire mysql db query related task below that we have Models where we have models for defining the schema of the db tables, then node_modules which is created while installing node required packages. Next we have Routes & Services about which we will discuss later in thid demo. Below that we have Utitlities folder where we have defined the configuration related stuff and common method which will be used throught the app and then package-lock.json which generated automatically by npm while installing packages and then package.json for storing the installed package related   info and basic info of our application. At last we have server.js about which we will discuss in detail later

Lets have a look on our package.json file with needed package listed in it and basic detail of our app.

 

 
{
"name": "NodeJS",
"version": "1.0.0",
"description": "NodeJS Project",
"main": "server.js",
"author": "Suraj Roy",
"dependencies": {
"async": "^1.5.2",
"body-parser": "^1.15.2",
"express": "^4.14.0",
"express-mysql-session": "^1.2.1",
"md5": "^2.2.1",
"memory": "0.0.3",
"multer": "^1.3.0",
"mustache": "^2.3.0",
"mysql": "^2.13.0",
"nodemailer": "^4.0.1",
"save": "^2.3.1"
},
"devDependencies": {
"nodemon": "^1.11.0"
}
}
 

 

In the above file we can see that the required packages are listed with its version.

In the next step, We have a file named server.js which performs perform the task i.e., accepts the input requests for parsing, routing, middleware set up and routing related things. So have a look on code inside it.

 

 
let app = require('express')(),
express = require('express'),
server = require('http').Server(app),
mustache = require('mustache') 
bodyParser = require('body-parser'); 
/* Basic packages included above in our app */
require('./Utilities/dbConfig'); // db connection
 
let userRoute = require('./Routes/user'),
util = require('./Utilities/util'),
config = require("./Utilities/config").config;
 
app.use(bodyParser.json()); // for parsing input data
app.use(bodyParser.urlencoded({ extended: false })); // for parsing input data
 
  
app.use(function(err, req, res, next) { // middleware of our app
return res.send({
"errorCode": util.statusCode.FOUR_ZERO_ZERO,
"errorMessage": util.statusMessage.SOMETHING_WENT_WRONG
});
});
 
//app.use('/', webRoute);
app.use('/user', userRoute); // routing
 
/*first API to check if server is running*/
app.get('/', function(req, res) {
res.send('hello, world!');
});
 
server.listen(config.NODE_SERVER_PORT.port,function(){ //server starting over port defined in config
    console.log('Server running on port 3000');
});
 

 

In the above we can see the basic package included at the top, Below that db connection is called in this page, Then we have code for data parsing and middleware of the app. At the bottom section we have set the routing of the app and a method which will run over http://localhost:3000/ to check whether app is working or not at initial stage and at last we have started the server.

In nodejs app, request is first handled by middleware in our server.js then it goes to the route section and then service section where query related task performs and returns the reponse to route section to serve at the reqeust.

So next have a look on route section(Routes/user.js):

 
/* User Sign Up. */
router.post('/signup', (req, res) => {
    userService.signup(req.body, (data) => {
        res.send(data);
    });
});
 

 

Above we can see at method for signup task which is accepting the reqeust from server.js and passes it to the service part and received the request data and returns back. So next lets have a look over file(Services/user.js).

 
let signup = (data, callback) => {
async.auto({
checkUserExistsinDB: (cb) => {
if (!data.email) {
cb(null, { "statusCode": util.statusCode.BAD_REQUEST, "statusMessage": util.statusMessage.PARAMS_MISSING })
return;
}
var criteria = {
email: data.email
}
userDAO.getUsers(criteria, (err, dbData) => {
if (err) { console.log(err)
cb(null, { "statusCode": util.statusCode.INTERNAL_SERVER_ERROR, "statusMessage": util.statusMessage.DB_ERROR})
return;
}

 

if (dbData && dbData.length) {
cb(null, { "statusCode": util.statusCode.BAD_REQUEST, "statusMessage": util.statusMessage.USER_EXISTS});
} else {
cb(null);
}
});
},
createUserinDB: ['checkUserExistsinDB', (cb, functionData) => {
if (functionData && functionData.checkUserExistsinDB) {
cb(null, functionData.checkUserExistsinDB);
return;
}

 

let token = util.generateToken();
let customerData = {
"first_name": data.fullName.split(' ')[0],
"last_name": data.fullName.substr(data.fullName.indexOf(" ") + 1),
"password": util.encryptData(data.password),
"email": data.email,
"token": token,
"user_type": 1,
"created_At": util.getMysqlDate(new Date().toISOString()),
"status": 1
}
//console.log(customerData);
let cData = {
"first_name": data.fullName.split(' ')[0],
"last_name": data.fullName.substr(data.fullName.indexOf(" ") + 1),
"email": data.email,
"token": token,
"status": 1
}
//console.log(cData);
userDAO.createUser(customerData, (err, dbData) => {
if (err) {
cb(null, { "statusCode": util.statusCode.INTERNAL_SERVER_ERROR, "statusMessage": util.statusMessage.DB_ERROR })
return;
}

cb(null, { "statusCode": util.statusCode.OK, "statusMessage": util.statusMessage.USER_ADDED,"result":cData});

});

}]
}, (err, response) => {
callback(response.createUserinDB);
});
}
 

 

This way we can create API in nodejs with mysql database. Lets have a look on  the first API created with it over postman.

postman-screenshot

 

This way we can create API in nodejs with mysql databse. You can download detailed zipped demo code from here with postman export where you can easily find the API request and its response. 

 

node with mysql

WHAT'S NEW

image
JavaScript Complete Guide in Hindi(Free Tutorial)

Click to Watch
CATEGORIES
INTERVIEW QUESTIONS