Creating Web API Using Node.js And MySQL

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 a series for creating To-Do applications in Angular 2 which will use node.js as a back-end. It is part 1 of the series. We will start by performing the CRUD (Create,Read,Update,Delete) operation in node.js. Our API will accept GET, POST, PUT, and DELETE requests.

Before starting with creating API in node.js, below are the steps for setting up the environment. You will also need to install MySQL Server either locally or on a remote machine. You can get it easily with XAMPP or WAMP.

Setting up the environment

• Install node.js.
• Install Express generator.
  
  cmd> npm install express -generator -g
  
  
  
  
• Install Express Template of node.js.
  
  

  cmd> express

  
  Here, in this example, I am using Express template for node.js. The above code creates a sample node.js project ready for us.
  
  
  
  
  
• Install dependency for MySQL.
  
  cmd> npm install mysql --save
  
  As I am using MySQL as my database, so I need to install dependencies of MySQL into my project. By writing the above command, we will add the dependency to package.json file.
  
  
  
  
• Install Cors
  
  cmd> npm install cors --save
  
  As we know, CORS (cross -origin resource sharing) is important while creating API, the above code will install the dependency of CORS into package.json file.
  
  
  
  
• Install all the dependency of package.json
  
  cmd> npm install
  
  
  

Setting up the table in mysql

script file
for table[code language = "sql"]
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');
[/code]

Creating the API using node.js

Now, as we are done with environment setup and database, we need 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 connected to the local database which was created above. You can connect to a remote database by simply changing your host, username, and password.

create dbconnection.js

[code language = "javascript"]
var mysql = require('mysql');
var connection = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'demo'
});
module.exports = connection;
[/code]

Building Task model

Create Task.js file inside the models folder, as shown following.



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

[code language = "javascript"]
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;
[/code]

Setting up the Routes

Although, we have 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, or DELETE, with a specific URL end-point.

[code language = "javascript"]
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);
            }
        });
    }
});
[/code]

Now, let’s understand what we did. We just created the router.get method, which will be executed when user requests 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.

[code language = "javascript"]
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
        }
    });
});
[/code]

It is executed when user requests for HTTP POST method. It will call the Task.AddTask method and pass 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 need to include Task.js which is created previously inside the models folder.

[code language = "javascript"]
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;
[/code]

Setting up the app.js

This is the main entry point of node.js application. When user requests 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 a configuration file.

We need to set a few lines in app.js, as following.

• var cors=require('cors');
• var Tasks=require('./routes/Tasks');
• app.use(cors());
• app.use('/Tasks',Tasks);

After including these lines, your app.js will look like these.

[code language = ”javascript”]
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;
[/code]

Done  -- we are all set to run this newly created RESTful API.

npm start



The following table summarizes the routes we will be using and the method that will be called.

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)

To test the API, I am using REST Client tool of Mozilla Firefox. You can use POSTMAN in Google chrome.

http://localhost:3000/Tasks



http://localhost:3000/Tasks/1



Click here to download the Demo.Want to host RESTful API on Heroku Server? Have a look at my post Hosting Web API On Heroku Server.

Conclusion

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

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

Thanks!!