0

Building a Restful CRUD API with Node.js, Express and MySQL

In this tutorial we will see how to Building a Restful CRUD API using Node.js, Express and MySQL. 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.

 

Install node.js (https://nodejs.org/en/)

 

Install express generator

Use the application generator tool, express-generator, to quickly create an application skeleton.

The express-generator package installs the express command-line tool. Use the following command to do so:

npm install express -generator -g

express generator

 

Install express template of node.js

express

express

The app structure created by the generator is just one of many ways to structure Express apps. Feel free to use this structure or modify it to best suit your needs.

 

Install dependency for mysql

npm install mysql –save

mysql

 

Install Cors

npm install cors –save

cros

 

Install all the dependency of package.json

npm install

npm

 

Setting up  table in mysql:

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

var mysql=require('mysql');
var connection=mysql.createPool({ 
  host:'localhost',
  user:'root',
  password:'',
  database:'demo'
});
module.exports=connection;

 

Building Task model

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.

//reference of dbconnection.js
var db=require('../dbconnection'); 
 
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

Here in routing file we must required to include Task.js which is created previously inside the models folder. 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.

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;

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.

 

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.

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

npm start

 

Following table summarized the routes we will be using.

Path Request Type
http://localhost:3000/Tasks GET
http://localhost:3000/Tasks/1 GET
http://localhost:3000/Tasks/1 DELETE
http://localhost:3000/Tasks POST (pass data in body)
http://localhost:3000/Tasks/1 PUT (pass data in body)

 

 

Prakash S

Prakash S

I would like to introduce myself as a Software professional opting for the career in software industry. I'm Prakash S, a MCA graduate and trained as industry level practice for Software technology. Basically I am a PHP Developer but now days exploring more in HTML5, CSS, AngularJS and jQuery libraries.