How to Create RESTful API Using Node.JS, Express and MySQL
Today we’ll learn to create a RESTful API using Node.js, Express 3 and MySQL. Before starting with node js (Official site) tutorial and creating an API we need to learn about what is node js. So, Nodejs is JavaScript rapid framework which uses an event-driven model that makes it lightweight and faster. Now a days, Node.js is widely used for App development.
The Express 4 is Node framework that provides robust set of features to develop mobile and web applications. It is used to perform different actions based on URL and HTTP Method.
Read More:
Create a RESTful Web Service API with Slim
We are going to use latest nodejs version v6.10.2. Make sure you have installed Node.JS and let’s get to it!
RESTful API Using Node.JS, Express and MySQL with Example
We are going to build an API for a Shop that will:
- Handle CRUD for an item (Eg. Products)
- Return JSON data
- Use the proper API Endpoints (
URL) and HTTP methods (POST,GETandDELETE)
|
1
2
3
4
5
|
server.js
package.json
node_modules/
|
To create new MySQL database open http://127.0.0.1/phpmyadmin in your browser (you can also use MySQL client) and create a database node_shop in MySQL.
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE DATABASE node_shop;
CREATE TABLE `nd_products` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`product_name` VARCHAR(50) NOT NULL,
`product_price` INT(11) NULL DEFAULT '0',
`product_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;
|
It will create nd_products databse table which we will use later in this tutorial. Now, Insert some test data into database table by executing following queries.
|
1
2
3
4
5
|
INSERT INTO `nd_products` (`id`, `product_name`, `product_price`, `product_image`, `created_date`) VALUES (2, 'Lenovo B40', 4300, 'http://example.com/img_product_2.png', '2017-05-18 22:16:57');
INSERT INTO `nd_products` (`id`, `product_name`, `product_price`, `product_image`, `created_date`) VALUES (3, 'XYON 350', 65000, 'http://example.com/img_product_3242.png', '2017-05-18 22:17:48');
INSERT INTO `nd_products` (`id`, `product_name`, `product_price`, `product_image`, `created_date`) VALUES (4, 'SPARK 654 GOLD', 9900, 'http://example.com/img_product_3442.png', '2017-05-18 22:17:48');
|
To install node packages open command line and navigate to application root directory and run below commands one by one.
|
1
2
3
4
5
6
7
|
$ npm install
$ npm install body-parser --save
$ npm install express --save
$ npm install mysql --save
|
npm will create node_modules folder and download all the packages into it.
After installing all Node packages package.json will look like below.
|
1
2
3
4
5
6
7
8
9
10
|
{
"name": "phone-book",
"version": "0.0.1",
"private": true,
"dependencies": {
"body-parser": "^1.17.2",
"express": "^4.15.3",
"mysql": "^2.5.4"
}
}
|
Copy below code into server.js and save it into root folder of our application.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
// Basic Setup
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 : 'root',
database : 'node_shop'
});
try {
connection.connect();
} catch(e) {
console.log('Database Connetion failed:' + e);
}
// Setup express
var app = express();
app.use(parser.json());
app.use(parser.urlencoded({ extended: true }));
app.set('port', process.env.PORT || 5000);
// Set default route
app.get('/', function (req, res) {
res.send('<html><body><p>Welcome to sShop App</p></body></html>');
});
// Create server
http.createServer(app).listen(app.get('port'), function(){
console.log('Server listening on port ' + app.get('port'));
});
|
Basic Setup: Pull the packages installed by npm and define them in our app so we can use it later.
Setup express: Setup an App with express, configure app to use body parser and assign port to our app.
Create server: Create server that will listen specified port that we defined earlier.
Database Connection: Configuration related to our database connection. You can replace your credentials with our credentials.
It’s time to start server. Run below command on the root directory of our application
|
1
2
3
|
node server.js
|
We will use Postman Chrome Extension to test our App. You can install the extension from here.
This endpoint will add product information into database. All the fields are mandatory in request.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
// Endpoint: http://127.0.0.1:5000/product/add
app.post('/product/add', function (req,res) {
var response = [];
if (
typeof req.body.name !== 'undefined' &&
typeof req.body.price !== 'undefined' &&
typeof req.body.imageUrl !== 'undefined'
) {
var name = req.body.name, price = req.body.price, imageUrl = req.body.imageUrl;
connection.query('INSERT INTO nd_products (product_name, product_price, product_image) VALUES (?, ?, ?)',
[name, price, 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));
}
});
|
Response:
|
1
2
3
4
5
|
[
{
"result": "success"
}
]
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
// Endpoint: http://127.0.0.1:5000/product/{:product id}
app.get('/product/:id', function (req,res) {
var id = req.params.id;
connection.query('SELECT * from nd_products 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);
}
});
});
|
It will search a product record having id set by user from nd_products database table and if it found the record than it will send product details data back into JSON response. If product could not found in database table than will show message “No Records Found” with result as error.
We are sending response information in JSON format. This is standard for an API and will help the App developer to use our data.
Response:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[
{
"result": "success",
"data": [
{
"id": 5,
"product_name": "ZYON 8990",
"product_price": 4000,
"product_image": "https://example.com/image.png ",
"created_date": "2017-05-18T16:47:48.000Z"
}
]
}
]
|
Postman Output
An Edit Single Product endpoint will be used to edit specific product exists in ‘nd_products’ database table. The parameters Product name, price and image URL need to be passes in POST method with our request. Server will make sure that no any parameters is not missing in request. If any of these parameter could not found than an error response with “Please fill required information” will be sent in response in JSON format.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
// Endpoint: http://127.0.0.1:5000/product/edit/{:product id}
app.post('/product/edit/:id', function (req,res) {
var id = req.params.id, response = [];
if (
typeof req.body.name !== 'undefined' &&
typeof req.body.price !== 'undefined' &&
typeof req.body.imageUrl !== 'undefined'
) {
var name = req.body.name, price = req.body.price, imageUrl = req.body.imageUrl;
connection.query('UPDATE nd_products SET product_name = ?, product_price = ?, product_image = ? WHERE id = ?',
[name, price, 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 information'});
res.setHeader('Content-Type', 'application/json');
res.send(200, JSON.stringify(response));
}
});
|
Now, let’s send request to our application using Postman, Appropriate response will get back in response from our application.
Response:
|
1
2
3
4
5
6
7
|
[
{
"result": "success"
}
]
|
Postman Output
In this route, we are searching a product in our nd_products database table having product id set in request, if product is exists in database table than delete that particular product.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
// Endpoint: http://127.0.0.1:5000/product/delete/{:product id}
app.delete('/product/delete/:id', function (req,res) {
var id = req.params.id;
connection.query('DELETE FROM nd_products 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);
}
});
});
|
If any error occurred in database operation during request, we will send response with 400 HTTP code and error description.
Response:
|
1
2
3
4
5
6
7
|
[
{
"result": "success"
}
]
|
Postman Output
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
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 : '123456',
database : 'node_shop'
});
try {
connection.connect();
} catch(e) {
console.log('Database Connetion failed:' + e);
}
var app = express();
app.use(parser.json());
app.use(parser.urlencoded({ extended: true }));
app.set('port', process.env.PORT || 5000);
app.get('/', function (req, res) {
res.send('<html><body><p>Welcome to sShop 0.1</p></body></html>');
});
app.get('/get-owner', function (req,res) {
connection.query('SELECT * from nd_shop', 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);
}
});
});
app.post('/product/edit/:id', function (req,res) {
var id = req.params.id, response = [];
if (
typeof req.body.name !== 'undefined' &&
typeof req.body.price !== 'undefined' &&
typeof req.body.imageUrl !== 'undefined'
) {
var name = req.body.name, price = req.body.price, imageUrl = req.body.imageUrl;
connection.query('UPDATE nd_products SET product_name = ?, product_price = ?, product_image = ? WHERE id = ?',
[name, price, 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));
}
});
app.delete('/product/delete/:id', function (req,res) {
var id = req.params.id;
connection.query('DELETE FROM nd_products 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);
}
});
});
app.get('/product/:id', function (req,res) {
var id = req.params.id;
connection.query('SELECT * from nd_products 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);
}
});
});
http.createServer(app).listen(app.get('port'), function(){
console.log('Server listening on port ' + app.get('port'));
});
|
That’s all:) Our basic CRUD operation REST API is ready to use. I will share more node js tutorials soon.
Do comment or contact me at for any kind of help during development.
Read More:
Create a RESTful Web Service API with Slim
How to Integrate PayUMoney Payment gateway in PHP
How To Integrate Stripe Payment Gateway Using PHP and JavaScript
sFileTypeChecker – JQuery Plugin To Verify Input File before uploading
sSwitch – JQuery Toggle Button Plugin For Sliding Toggle Switches
Composer – Dependency manager for PHP
One thought on “How to Create RESTful API Using Node.JS, Express and MySQL”