Learn Rest API using Express.

js and MySQL
DB
Published Jul 19, 2018Last updated Jun 24, 2019

[Link] using MySQL DB Tutorial

Tools:
Text Editor:
 Visual Studio Code
[Link]
 Installation:
[Link]
Creating first [Link] (Express) API:
Create a folder for your app
  $ mkdir firtApp
On terminal/commandline go to the folder created from previous step
 $ cd firstApp
Initialize node project :
 $ npm init
To avoid question use flags;
 $ npm init –yes
Install express framework, mySql, and body-parser
 $ npm install express –save (Express Framework)
 $ npm install mysql –save (for connecting to DB)
 $ npm install body-parser
o This is a [Link] middleware for handling JSON, Raw, Text and URL encoded
form data.
Server setup:
nodemon will help us to keep track of changes to our application by watching
changed files and automatically restart the server.
$ npm install --save-dev nodemon
Create a file [Link]
 $ touch [Link]
 Open [Link] file and type code below:
var express = require('express'),
app = express(),
port = [Link] || 3000;
[Link](port);
[Link]('todo list RESTful API server started on: ' + port);
 this will run your app on the port you have provided when you start the server
MySQL DB Setup:
Prerequisite:
Install mySQL Community Edition
Create a DB:
CREATE DATABASE mydb;
Create table:
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
);

ALTER TABLE `tasks` ADD PRIMARY KEY (`id`);

ALTER TABLE `tasks` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Populate the table with sample data:
INSERT INTO `tasks` (`id`, `task`, `status`, `created_at`) VALUES
(1, 'Find bugs', 1, '2016-04-10 [Link]'),
(2, 'Review code', 1, '2016-04-10 [Link]'),
(3, 'Fix bugs', 1, '2016-04-10 [Link]'),
(4, 'Refactor Code', 1, '2016-04-10 [Link]'),
(5, 'Push to prod', 1, '2016-04-10 [Link]');
Create new folder named app and inside app create additional folders named
controller, model, routes.
 Inside controller create new file named [Link]
 Inside model create new file named [Link]
  Inside routes create new file named [Link]
Structure would look like below diagram:

Setting up the routes

Routing refers to determining how an application responds to a client request for a
specific endpoint, which is a URI (or path) and a specific HTTP request method (GET,
POST, PUT,PATCH,DELETE)
Each of our routes has different route handler functions, which are executed when the
route is matched.
Below we have defined two basic routes
 /tasks
 /tasks/taskId
 with different methods
‘/tasks’ has to methods(‘GET’ and ‘POST’), while ‘/tasks/taskId’ has GET, PUT
and DELETE.
As you can see, we required the controller so each of the routes methods can call it’s
respective handler function.
To do this, open the [Link] file in the route folder and paste the code snippet
below into
'use strict';
[Link] = function(app) {
var todoList = require('../controllers/todoListController');

// todoList Routes
[Link]('/tasks')
.get(todoList.list_all_tasks)
.post(todoList.create_a_task);

[Link]('/tasks/:taskId')
.get(todoList.read_a_task)
.put(todoList.update_a_task)
.delete(todoList.delete_a_task);
};
Let’s create a db connection wrapper, this will allow you to create connection on db
which stored on a single file and can be reuse by other modules.
To do this create a new file name [Link] under models folder
$ touch [Link]
Paste the code snippet below:
'user strict';

var mysql = require('mysql');

//local mysql db connection
var connection = [Link]({
host : 'localhost',
user : 'root',
password : '',
database : 'mydb'
});

[Link](function(err) {
if (err) throw err;
});

[Link] = connection;
Setting up model:
We can now reuse our db instance to other modules
Now create are Task object and provide function like creating new task, get all task, get
task by id, update task by id and delete task by id.
Open [Link] and paste code snippet below:
'user strict';
var sql = require('./[Link]');

//Task object constructor

var Task = function(task){
[Link] = [Link];
[Link] = [Link];
this.created_at = new Date();
};
[Link] = function (newTask, result) {
[Link]("INSERT INTO tasks set ?", newTask, function (err, res) {

if(err) {
[Link]("error: ", err);
result(err, null);
}
else{
[Link]([Link]);
result(null, [Link]);
}
});
};
[Link] = function (taskId, result) {
[Link]("Select task from tasks where id = ? ", taskId, function
(err, res) {
if(err) {
[Link]("error: ", err);
result(err, null);
}
else{
result(null, res);

}
});
};
[Link] = function (result) {
[Link]("Select * from tasks", function (err, res) {

if(err) {
 [Link]("error: ", err);
result(null, err);
}
else{
[Link]('tasks : ', res);

result(null, res);
}
});
};
[Link] = function(id, task, result){
[Link]("UPDATE tasks SET task = ? WHERE id = ?", [[Link], id],
function (err, res) {
if(err) {
[Link]("error: ", err);
result(null, err);
}
else{
result(null, res);
}
});
};
[Link] = function(id, result){
[Link]("DELETE FROM tasks WHERE id = ?", [id], function (err, res) {

if(err) {
[Link]("error: ", err);
result(null, err);
}
else{

result(null, res);
}
});
};

[Link]= Task;
In here we require [Link] to import our connection instance to mysql db and perform
mysql queries.
Setting up the controller
Open [Link] file with your text edito and let’s deep dive into coding.
In this controller, we would be writing five(5) different functions namely: list_all_tasks,
create_a_task, read_a_task, update_a_task, delete_a_task. We will export each of the
functions for us to use in our routes.
Each of these functions uses different Task methods we created previously in
[Link] such as getTaskById, getAllTask, updateById, createTask and remove.
Open [Link] and paste code snippet below
'use strict';

var Task = require('../model/[Link]');

exports.list_all_tasks = function(req, res) {

[Link](function(err, task) {

[Link]('controller')
if (err)
[Link](err);
 [Link]('res', task);
[Link](task);
});
};

exports.create_a_task = function(req, res) {

var new_task = new Task([Link]);

//handles null error

if(!new_task.task || !new_task.status){

[Link](400).send({ error:true, message: 'Please provide

task/status' });

}
else{

[Link](new_task, function(err, task) {

if (err)
[Link](err);
[Link](task);
});
}
};

exports.read_a_task = function(req, res) {

[Link]([Link], function(err, task) {
if (err)
[Link](err);
[Link](task);
});
};

exports.update_a_task = function(req, res) {

[Link]([Link], new Task([Link]), function(err, task) {
if (err)
[Link](err);
[Link](task);
});
};

exports.delete_a_task = function(req, res) {

[Link]( [Link], function(err, task) {

if (err)
[Link](err);
[Link]({ message: 'Task successfully deleted' });
});
};
Earlier on, we had a minimal code for our server to be up and running in the [Link]
file.
In this section we will be connecting our handlers(controllers), database, the created
 models, body parser and the created routes together.
Open the [Link] file created awhile ago and follow the following steps to put
everything together.
Essentially, you will be replacing the code in your [Link] with the code snippet from
this section
 use bodyParser Parse incoming request bodies in a middleware before your
handlers, available under the [Link] property.
It exposes various factories to create middlewares. All middlewares will populate
the [Link] with the parsed body, or an empty object ({}) if there was no
body to parse (or an error was returned).
 Register our created routes in the server
On [Link] would have code below:
const express = require('express'),
app = express(),
bodyParser = require('body-parser');
port = [Link] || 3000;

const mysql = require('mysql');

// connection configurations
const mc = [Link]({
host: 'localhost',
user: 'root',
password: '',
database: 'mydb'
});

// connect to database
[Link]();

[Link](port);

[Link]('API server started on: ' + port);

[Link]([Link]({ extended: true }));

[Link]([Link]());

var routes = require('./app/routes/approutes'); //importing route

routes(app); //register the route
Start mySQL db sever
Start [Link]
$ nodemon sever
– This will start server and any changes made to the code will restart the server
Or
$ node [Link]
 This will start [Link]
Testing via Postman
Prerequisite:
Install Postman
[Link]
59773-1848618340.1531359773
Now that everything is now connected, let’s test each of the routes and the respective
methods.
Open your postman and type:
 1.
[Link] in the enter request URL section and press enter.

2. On the same address, change the method to POST, click body and select “raw”
and on the drop
menu choose application/json.

on body
provide task details:
i.e:
{
“task”:”create repo”,
“status”:”1”
}
After this, click on send button.
This should give you a response 200 ok