https://jinalshahblog.wordpress.com/2016/10/06/rest-api-using-node-js-and-mysql/


Hello Readers,

Do you know creating RESTful Api using node.js and Express Template is as easy as making tea or coffee?? seriously!!! So in this tutorial we will see how to create a basic RESTful api using node.js with Mysql as Database. From this article I am starting series for creating To-Do application in angular 2 which will use node.js as a back end.it is part 1 of the series. We will start by creating crud(Create,Read,Update,Delete)operation in node.js.our api will accept GET,POST,PUT,DELETE requests. Before starting with creating api in node.js below are the steps for setting up the environment. You will also require to install Mysql Server either locally or on a remote machine. you can get it easily with xamp or wamp.

Setting up the environment:

         cmd> npm install express -generator -g

            screenshot-21

  • Install express template of node.js

         cmd> express

here in this example i am using express template for node.js. above code creates a sample node.js project ready for us.

screenshot-24

  • Install dependency for mysql

         cmd> npm install mysql –save

As i am using mysql as my database so i need to install dependency of mysql in to my project.by writing above command it will add dependecy to package.json file.

Screenshot (25).png

  • Install Cors

        cmd> npm install cors –save

As we know CORS(cross -origin resourece sharing)  is most important while creating api.above code will install dependecy of cors in to package.json file.

Screenshot (26).png

  • Install all the dependency of package.json

    cmd> npm install

Screenshot (27).png

Setting up  table in mysql:

script file for table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE IF NOT EXISTS `task` (
 `Id` varchar(50) NOT NULL,
 `Title` varchar(500) DEFAULT NULL,
 `Status` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
-- Dumping data for table `task`
 --
 
INSERT INTO `task` (`Id`, `Title`, `Status`) VALUES
 ('1', 'Go to Market tomorrow', 'done'),
 ('2', 'Email to manager', 'pending'),
 ('3', 'Push code to GitHub', 'done'),
 ('4', 'Go For Running', 'done'),
 ('5', 'Go to Movie', 'pending');

 

Creating api using node.js

Now as we are done with environment setup and database, we should require a file which can connect to the database. So let’s create a file called dbconnection.js and inside the file we will store the information to connect with the database. In below example I am connection to local database which created above. You can connect to remote database by simply changing your host, username and password.

create dbconnection.js

1
2
3
4
5
6
7
8
9
10
var mysql=require('mysql');
 var connection=mysql.createPool({
 
host:'localhost',
 user:'root',
 password:'',
 database:'demo'
 
});
 module.exports=connection;

Building Task model

create Task.js file inside the models folder as shown below.

screenshot-30

As we are done with setting up the connection, now we will create the model for Task. Create a folder called models and inside that folder create Task.js file. Our Task model contains five methods getAllTasks, getTaskById, addTask, updateTask and deleteTask. First we need to include the dbconnection module which we created earlier in our Task model.

Task.js

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
var db=require('../dbconnection'); //reference of dbconnection.js
 
var Task={
 
getAllTasks:function(callback){
 
return db.query("Select * from task",callback);
 
},
 getTaskById:function(id,callback){
 
return db.query("select * from task where Id=?",[id],callback);
 },
 addTask:function(Task,callback){
 return db.query("Insert into task values(?,?,?)",[Task.Id,Task.Title,Task.Status],callback);
 },
 deleteTask:function(id,callback){
  return db.query("delete from task where Id=?",[id],callback);
 },
 updateTask:function(id,Task,callback){
  return db.query("update task set Title=?,Status=? where Id=?",[Task.Title,Task.Status,id],callback);
 }
 
};
 module.exports=Task;

Setting up the Routes:

We had created the dbconnection and task model but without setting up the routes we can’t really do anything with what we created so far.Each route is an http method either GET,PUT,POST,DELETE. With a specific url end point.

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
router.get('/:id?',function(req,res,next){
 
if(req.params.id){
 
Task.getTaskById(req.params.id,function(err,rows){
 
if(err)
  {
  res.json(err);
  }
  else{
  res.json(rows);
  }
  });
 }
 else{
 
Task.getAllTasks(function(err,rows){
 
if(err)
  {
  res.json(err);
  }
  else
  {
  res.json(rows);
  }
 
 });
 }
 });

Now let’s understand what we did? We just created the router.get method,which will be executed when user request for HTTP GET method.we can call route.get method with or without parameter i.e. parameter id is optional. We can create the optional parameter by simply adding ‘ ? ‘ as postfix. So first it will check whether the id is passed or not. If  id is passed then it will call the Task.getTaskById method which is created previously in Task.js model otherwise it will call Task.getAllTasks method.

1
2
3
4
5
6
7
8
9
10
11
12
router.post('/',function(req,res,next){
 
Task.addTask(req.body,function(err,count){
  if(err)
  {
  res.json(err);
  }
  else{
  res.json(req.body);//or return count for 1 & 0
  }
  });
 });

 

It is executed when user request for HTTP POST method. It will call the Task.addTask method and passed the data as req.body parameter. It will return the task object on successful insertion or return error message if insertion failed.

Over all Tasks.js

Here in routing file we must required to include Task.js which is created previously inside the models folder.

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
var express = require('express');
 var router = express.Router();
 var Task=require('../models/Task');
 
router.get('/:id?',function(req,res,next){
 
if(req.params.id){
 
Task.getTaskById(req.params.id,function(err,rows){
 
if(err)
  {
  res.json(err);
  }
  else{
  res.json(rows);
  }
  });
 }
 else{
 
Task.getAllTasks(function(err,rows){
 
if(err)
  {
  res.json(err);
  }
  else
  {
  res.json(rows);
  }
 
 });
 }
 });
 router.post('/',function(req,res,next){
 
Task.addTask(req.body,function(err,count){
  if(err)
  {
  res.json(err);
  }
  else{
  res.json(req.body);//or return count for 1 & 0
  }
  });
 });
 router.delete('/:id',function(req,res,next){
 
Task.deleteTask(req.params.id,function(err,count){
 
if(err)
  {
  res.json(err);
  }
  else
  {
  res.json(count);
  }
 
});
 });
 router.put('/:id',function(req,res,next){
 
Task.updateTask(req.params.id,req.body,function(err,rows){
 
if(err)
  {
  res.json(err);
  }
  else
  {
  res.json(rows);
  }
  });
 });
 module.exports=router;

 

Setting up the app.js:

This is the main entry point of node.js application. When user request any method first it will be redirected to app.js then from the app.js it will be redirected to requested routes.so one can say it is configuration file.

We need to set few line in app.js. which is following.

  • var cors=require(‘cors’);
  • var Tasks=require(‘./routes/Tasks’);
  • use(cors());
  • use(‘/Tasks’,Tasks);

after including this lines your app.js will look like these.

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
var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');
var cors=require('cors');
var routes = require('./routes/index');
var users = require('./routes/users');
var Tasks=require('./routes/Tasks');
var app = express();
 
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');
 
// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(cors());
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
 
app.use('/', routes);
app.use('/users', users);
app.use('/Tasks',Tasks);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
var err = new Error('Not Found');
err.status = 404;
next(err);
});
 
// error handlers
 
// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: err
});
});
}
 
// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
res.status(err.status || 500);
res.render('error', {
message: err.message,
error: {}
});
});
module.exports = app;

Done we are all set to run these newly created RESTful Api.

npm start

Screenshot (33).png

 

Following table summarized the routes we will be using.

PathRequest Type
http://localhost:3000/TasksGET
http://localhost:3000/Tasks/1GET
http://localhost:3000/Tasks/1DELETE
http://localhost:3000/TasksPOST (pass data in body)
http://localhost:3000/Tasks/1PUT (pass data in body)

To test api I am using REST Client tool of Mozilla Firefox or you can use postman in Google chrome.

http://localhost:3000/Tasks

Screenshot (34).png

http://localhost:3000/Tasks/1

gettaskbyid

Click here to download Demo

Conclusion:

We have seen how simple it is to create a RESTful API using Node.js, Express and MySQL. Gyus isn’t it easy? You can download the full source code on github from above link.

Hope it will helpful to you!! If it helped you to understand basic node.js REST Api then please share and comment on it.

Thanks!!

이 게시물을...