Create REST API in Node.js, MySQL, Express

This tutorial is a quick guide to Node.js, the Express framework, and MySQL database, focusing on the fundamental REST routes and basic database interaction. You’ll build a simple API boilerplate that can then be used as the foundation for any app.

You should have a basic understanding of REST APIs and CRUD operations, plus basic JavaScript knowledge.

Setup the Database & Table in MySQL

Create Database “node_api”

Create Table:

CREATE TABLE `employees` (

`id`BIGINT(20) NOT NULL AUTO_INCREMENT,

`emp_name`VARCHAR(50) NOT NULL,

`emp_sal`INT(11) NULL DEFAULT ‘0’,

`emp_image`VARCHAR(50) NULL DEFAULT NULL,

`created_date`TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

INDEX `Index 1` (`id`)

) COLLATE=‘utf8_general_ci’ ENGINE=InnoDB AUTO_INCREMENT=5;

Above query will create table in your database.

Insert some data in your database:

INSERT INTO `employees` (`id`, `emp_name`, `emp_sal`,`emp_image`, `created_date`) VALUE(1, “Inderjeet”, 45000,”http://example.com/image.png”, ‘2017-05-18 22:17:48’);

INSERT INTO `employees` (`id`, `emp_name`, `emp_sal`,`emp_image`, `created_date`) VALUE( 2, “Rohit”, 33000, “https://image.com/image.png”, ‘2017-08-04 10:17:44’);

Environment Setup:

Installing Node.js
Installing Express Generator

Open Command prompt to run your commands:

cmd> npm install –g express–generator

Create Express Application using below command:

express node_api

node_express

Change Directory to node_api

cd node_api

Run below command

npm install

node_install

After running the above commands step by step, you will see the below directory structure:

node_directory_structure

Now install node packages open command line and navigate to application root directory and run below commands one by one.

npm install body–parser —save

npm install express —save

npm install mysql —save

node_packages

After installing all Node packages package.json will look like below.

{
 "name": "node-api",
 "version": "0.0.0",
 "private": true,
 "scripts": {
 "start": "node ./bin/www"
 },
 "dependencies": {
 "body-parser": "^1.18.2",
 "cookie-parser": "~1.4.3",
 "debug": "~2.6.9",
 "express": "^4.15.5",
 "jade": "~1.11.0",
 "morgan": "~1.9.0",
 "mysql": "^2.14.1",
 "serve-favicon": "~2.4.5"
 }
}

Setup Server

Copy below code into server.js and save it into root folder of our application.

// Pulling all installed packages
var http = require('http'),
 express = require('express'),
 mysql = require('mysql')
 parser = require('body-parser');
 
// Database Connection
var connection = mysql.createConnection({
 host : 'localhost',
 user : 'root',
 password : 'password', //Your Database User Password
 database : 'node_api'
});
try {
 connection.connect();
 
} catch(e) {
 console.log('Database Connetion failed:' + e);
}
 
 
// Setup an App with express, configure app to use body parser and assign port to our app.
var app = express();
app.use(parser.json());
app.use(parser.urlencoded({ extended: true }));
app.set('port', process.env.PORT || 5000);//Port Number
 
// Set default route
app.get('/', function (req, res) {
 res.send('<html><body><h1>Welcome to Node API</h1></body></html>');
});
 
// Create server
http.createServer(app).listen(app.get('port'), function(){
 console.log('Server listening on port ' + app.get('port'));
});

Run below command on the root directory of our application to start server

node server.js

To test the API’s we will use Postman Chrome Extension

You can check the default rout using below URL:

http://localhost:5000/

Response:

Employees List:

app.get('/employees_list', function (req,res) {
 connection.query('SELECT * from employees', function(err, rows, fields) {
 if (!err){
 var response = [];
 response.push({'result' : 'success'});
 if (rows.length != 0) {
 response.push({'data' : rows});
 } else {
 response.push({'msg' : 'No Result Found'});
 }
 
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 } else {
 res.status(400).send(err);
 }
 });
});

Employee List API URL:

http://localhost:5000/employees_list

Response:

Add Employee:

Code:

app.post('/employees/add', function (req,res) {
 var response = [];
 
 if (
 typeof req.body.name !== 'undefined' && 
 typeof req.body.sal !== 'undefined' && 
 typeof req.body.imageUrl !== 'undefined'
 ) {
 var name = req.body.name, sal = req.body.sal, imageUrl = req.body.imageUrl;
 
 connection.query('INSERT INTO employees (emp_name, emp_sal, emp_image) VALUES (?, ?, ?)', 
 [name, sal, imageUrl], 
 function(err, result) {
 if (!err){
 
 if (result.affectedRows != 0) {
 response.push({'result' : 'success'});
 } else {
 response.push({'msg' : 'No Result Found'});
 }
 
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 } else {
 res.status(400).send(err);
 }
 });
 
 } else {
 response.push({'result' : 'error', 'msg' : 'Please fill required details'});
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 }
});

Access URL:

http://localhost:5000/employees/add

Response:

node_postman_employees_add

View Single Employee:

Code:

app.get('/employees/:id', function (req,res) {
 var id = req.params.id;
 
 connection.query('SELECT * from employees where id = ?', [id], function(err, rows, fields) {
 if (!err){
 var response = [];
 
 if (rows.length != 0) {
 response.push({'result' : 'success', 'data' : rows});
 } else {
 response.push({'result' : 'error', 'msg' : 'No Results Found'});
 }
 
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 } else {
 res.status(400).send(err);
 }
 });
});

Access URL:

http://localhost:5000/employees/1

Response:

node_postman_employees_view

Edit Employee:

Code:

 app.post('/employees/edit/:id', function (req,res) {
 var id = req.params.id, response = [];
 
 if (
 typeof req.body.name !== 'undefined' && 
 typeof req.body.sal !== 'undefined' && 
 typeof req.body.imageUrl !== 'undefined'
 ) {
 var name = req.body.name, sal = req.body.sal, imageUrl = req.body.imageUrl;
 
 connection.query('UPDATE employees SET emp_name = ?, emp_sal = ?, emp_image = ? WHERE id = ?', 
 [name, sal, imageUrl, id], 
 function(err, result) {
 if (!err){
 
 if (result.affectedRows != 0) {
 response.push({'result' : 'success'});
 } else {
 response.push({'msg' : 'No Result Found'});
 }
 
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 } else {
 res.status(400).send(err);
 }
 });
 
 } else {
 response.push({'result' : 'error', 'msg' : 'Please fill required details'});
 res.setHeader('Content-Type', 'application/json');
 res.send(200, JSON.stringify(response));
 }
});

Access URL:

http://localhost:5000/employees/edit/1

Response:

node_postman_employees_edit

Delete Employee:

Code:

app.delete('/employees/delete/:id', function (req,res) {
 var id = req.params.id;
 
 connection.query('DELETE FROM employees WHERE id = ?', [id], function(err, result) {
 if (!err){
 var response = [];
 
 if (result.affectedRows != 0) {
 response.push({'result' : 'success'});
 } else {
 response.push({'msg' : 'No Result Found'});
 }
 
 res.setHeader('Content-Type', 'application/json');
 res.status(200).send(JSON.stringify(response));
 } else {
 res.status(400).send(err);
 }
 });
});

Access URL:

http://localhost:5000/employees/delete/2

Response:

node_postman_employees_delete

That’s all! This was the complete CURD operation.

Source code is also available on Github.

Do comment for any query or suggestion.

THANKS.

#Learn #Develop #Share

First solve the problem. Then, write the codes.

Create REST API in Node.js, MySQL, Express

Setup Server

Leave a comment Cancel reply

Create REST API in Node.js, MySQL, Express

Setup Server

Share this:

Related

Leave a comment Cancel reply