Creating a RESTful API with Express js, Node js/MySQl

Do you want to create a RESTful API with NodeJS/Express Js framework? Here is the tutorial, by reading this tutorial you can create our own REST API application server. Nowadays, it’s quite common to create web and mobile apps that consume data from APIs. This is providing easy decoupling, client or server can be developed by different teams or same.

we are going to use a very popular web framework called Express to create REST APIs.

What is Express js?

Express is a minimalist web framework that was highly inspired by the Sinatra framework from the Ruby language. With this module, you can create anything from small applications to large, complex ones. This framework allows you to build APIs and also to create simple websites.

Here is main advantages of Express:
1. Easily integrate with template engines
2. No database or persistence data storage, you can integrate with any object-relational mapping (ORM) or object data mappers (ODMs).
3. flexible and Robust routing.
4. Minimalist code
5. sophisticated middlewares concept.
6. A huge list of third-party middlewares to integrate

NOTE: You have to install NodeJs and MySQL software.

Database and table

Name your database whatever you want, and run below shown SQL it will create task table.

-- 
-- Table structure for `tasks`
-- 
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `task` varchar(200) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);
ALTER TABLE `tasks` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Insert some sample data into the tasks table.

INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, 'Find bugs', 1, '2016-04-10 23:50:40'),
(2, 'Review code', 1, '2016-04-10 23:50:40'),
(3, 'Fix bugs', 1, '2016-04-10 23:50:40'),
(4, 'Refactor Code', 1, '2016-04-10 23:50:40'),
(5, 'Push to prod', 1, '2016-04-10 23:50:50');

Setup Node Restful Project

Go to Terminal or Command Line, create a project folder.

$ mkdir express-node-rest-project
$ cd express-node-rest-project

Initialize your node project with below npm command, which will create a file called package.json and it will ask few questions about the project if you want to avoid questions, use — yes flag.

$ npm init --yes

Now install express js framework and MySQL driver with NPM.

$ npm install express --save
$ npm install mysql --save
$ npm install body-parser --save

Here is my final package.json file.

{
  "name": "express-node-rest-project",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.15.2",
    "mysql": "^2.13.0"
  }
}

We are going to implement following API calls –

Method URL Action
GET /todos Retrieve all todos
GET /todos/search/bug Search for todos with ‘bug’ in their name
GET /todo/1 Retrieve todo with id == 1
POST /todo Add a new todo
PUT /todo Update todo with id == 1
DELETE /todo Delete todo with id == 1

Create Express Server

Create server.js file, Open it with editor, copy following code. Please read the comments for better understanding.

const express = require('express');
const app = express();
const bodyParser = require('body-parser');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));


// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});

// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(8080, function () {
    console.log('Node app is running on port 8080');
});

Express server is ready, you can start your server with node server.js command, to see output point your browser to http://localhost:8080.

Node MySQL – Database Connection

Update your server.js file with MySQL connection code. Here you have to modify the MySQL database name, host, username and password.

const express = require('express');
const app = express();
const mysql = require('mysql');
...
....
....

// connection configurations
const mc = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_task_demo'
});

// connect to database
mc.connect();

// default route
app.get('/', function (req, res) {
.....
.....
.....

Implementing the API calls with express js

Now that we have our express server up and running with the database connection, we need to manage todos in the database.

Getting the Todos list – We are going to create a new route so that when a user hits /todos, it will return a list of all todos in JSON format. Update your server.js with below code.

// Retrieve all todos 
app.get('/todos', function (req, res) {
    mc.query('SELECT * FROM tasks', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos list.' });
    });
});

This function simply return all todos information as you can see in this query, to call this API use this URL http://localhost:8080/todos.

Getting single todo – We are going to create a new route so that when a user hits /todo/{id}, it will return a todo in JSON format.

// Retrieve todo with id 
app.get('/todo/:id', function (req, res) {

    let task_id = req.params.id;

    if (!task_id) {
        return res.status(400).send({ error: true, message: 'Please provide task_id' });
    }

    mc.query('SELECT * FROM tasks where id=?', task_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'Todos list.' });
    });

});

This function checks record of given id and returns if found anything, to call this API use this URL http://localhost:8080/todo/1.

Find todo by name – We are going to create a new route so that when a user hits /todos/search/{Query}, it will return a list of all matched todos in JSON format.

// Search for todos with ‘bug’ in their name
app.get('/todos/search/:keyword', function (req, res) {
    let keyword = req.params.keyword;
    mc.query("SELECT * FROM tasks WHERE task LIKE ? ", ['%' + keyword + '%'], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos search list.' });
    });
});

This function search in database for your given query, to call this API use this URL http://localhost:8080/todos/search/bug

Add todo – We are going to create a new route so that when a user sends a post request to /todo with required data, app will add a new record to the database.

// Add a new todo  
app.post('/todo', function (req, res) {

    let task = req.body.task;

    if (!task) {
        return res.status(400).send({ error:true, message: 'Please provide task' });
    }

    mc.query("INSERT INTO tasks SET ? ", { task: task }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New task has been created successfully.' });
    });
});

This API accepts post request and insert submitted data in your database. To call this API use this URL http://localhost:8080/todo

Delete Task – We are going to create a new route so that when a user sends a delete request to /todo/{id}, the app will delete a record from the database.

//  Delete todo
app.delete('/todo', function (req, res) {

    let task_id = req.body.task_id;

    if (!task_id) {
        return res.status(400).send({ error: true, message: 'Please provide task_id' });
    }
    mc.query('DELETE FROM tasks WHERE id = ?', [task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});	

Update Task – We are going to create a new route so that when a user sends a put request to /todo/{id} with required data, the app will update a record based on match parameter in the database.

//  Update todo with id
app.put('/todo', function (req, res) {

    let task_id = req.body.task_id;
    let task = req.body.task;

    if (!task_id || !task) {
        return res.status(400).send({ error: task, message: 'Please provide task and task_id' });
    }

    mc.query("UPDATE tasks SET task = ? WHERE id = ?", [task, task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});

This API accept put request and updates submitted data in your database. To call this API use this URL http://localhost:8080/todo/{id}

Here is the complete server.js file.

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const mysql = require('mysql');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));

// connection configurations
const mc = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'node_task_demo'
});

// connect to database
mc.connect();

// default route
app.get('/', function (req, res) {
    return res.send({ error: true, message: 'hello' })
});

// Retrieve all todos 
app.get('/todos', function (req, res) {
    mc.query('SELECT * FROM tasks', function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos list.' });
    });
});

// Search for todos with ‘bug’ in their name
app.get('/todos/search/:keyword', function (req, res) {
    let keyword = req.params.keyword;
    mc.query("SELECT * FROM tasks WHERE task LIKE ? ", ['%' + keyword + '%'], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Todos search list.' });
    });
});

// Retrieve todo with id 
app.get('/todo/:id', function (req, res) {

    let task_id = req.params.id;

    mc.query('SELECT * FROM tasks where id=?', task_id, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results[0], message: 'Todos list.' });
    });

});

// Add a new todo  
app.post('/todo', function (req, res) {

    let task = req.body.task;

    if (!task) {
        return res.status(400).send({ error:true, message: 'Please provide task' });
    }

    mc.query("INSERT INTO tasks SET ? ", { task: task }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New task has been created successfully.' });
    });
});

//  Update todo with id
app.put('/todo', function (req, res) {

    let task_id = req.body.task_id;
    let task = req.body.task;

    if (!task_id || !task) {
        return res.status(400).send({ error: task, message: 'Please provide task and task_id' });
    }

    mc.query("UPDATE tasks SET task = ? WHERE id = ?", [task, task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });
});

//  Delete todo
app.delete('/todo/:id', function (req, res) {

    let task_id = req.params.id;

    mc.query('DELETE FROM tasks WHERE id = ?', [task_id], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Task has been updated successfully.' });
    });

});

// all other requests redirect to 404
app.all("*", function (req, res, next) {
    return res.send('page not found');
    next();
});

// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(8080, function () {
    console.log('Node app is running on port 8080');
});

// allows "grunt dev" to create a development server with livereload
module.exports = app;

I hope you like this Post, Please feel free to comment below, your suggestion and problems if you face - we are here to solve your problems.

DMCA.com Protection Status