The Background
For one of my home projects, I require an API service for my Angular SPA to interact with. I could’ve opted for C++ or Java but the turnaround on writing code in nodeJS is so much quicker, not to mention its efficiency.
The project I’m working on is a project task manager. There are dozens out there already but I need to brush up on my Angular and nodeJS knowledge anyway and figured this would be a good opportunity to do that. Plus I only want it locally, on my local network server, along with the SPA. Then my finished project will provide me with exactly what I need. Nothing less, nothing more.
I wanted to be able to store a bunch of data and access it when and where I need to. So my first port of call was to figure out what kind of data I wanted to store.
Well, it’s a project task manager, with emphasis on estimated task durations, deadlines and order of importance and with that in mind, here’s what I decided I need:
- Project name
- Project deadline
- Project description
- Project color*
- Task name
- Task deadline
- Task description
- Task estimated duration
- Task color*
- Task priority, normal | low | high
*The tasks and projects will have an assigned color because I intend on showing them in a bar graph, one of those horizontal ones with the cute colors.
Next, I wanted to use that information to design a database to store all of the values in. I will call the database TaskMan, because, why not. I could’ve drawn up an entity-relationship diagram but truth be told, when it’s a project for myself, I just open up a text editor, figure out what I need and then go for a loose design from there. It’ll only be myself using it so I’m not too bothered about having the perfect database, I’m not a database administrator, after all. I do, however, write all of my database creation code in a text file and save it as .sql just because thats wise. I used to use phpMyAdmin for managing MySQL but times have changed, my friends, times have changed.
RESTful
I won’t go into a great deal of detail with this because there’s a wealth of information about it online already. But REST refers to REpresentational State Transfer. As in, the state is transferred with each request, as opposed to being stored by the server. So the server remains stateless, it does not keep sessions for each request etc.
There’s four main components to using a RESTful API:
- Create – POST
- Read – GET
- Update – PUT
- Delete – DELETE
Commonly referred to as CRUD, these refer to the types of HTTP request that one can make. I make use of these here.
The Database
Alright, I’m just going to dump some tables here which details the database tables I decided to create, and then I’ll include the creation SQL for them also.
This is the
| Field Name | Field Type | Length | Description |
| id | INT | NOT NULL AUTO_INCREMENT PRIMARY KEY | |
| name | VARCHAR | 56 | NOT NULL UNIQUE |
| description | TEXT | ||
| start_date | DATETIME | ||
| deadline | DATETIME | ||
| completed | BOOLEAN / TINYINT | ||
| color | VARCHAR | 7 | |
| priority_id | INT | 3 | FOREIGN KEY |
And this is the almost identical
| Field Name | Field Type | Length | Description |
| id | INT | NOT NULL AUTO_INCREMENT PRIMARY KEY | |
| project_id | INT | NOT NULL FOREIGN KEY | |
| name | VARCHAR | 56 | NOT NULL UNIQUE |
| description | TEXT | ||
| start_date | DATETIME | ||
| deadline | DATETIME | ||
| estimated_duration | INT | 3 | |
| completed | BOOLEAN / TINYINT | ||
| priority_id | INT | 3 | FOREIGN KEY |
Finally, this table is the
| Field Name | Field Type | Length | Description |
| id | INT | NOT NULL AUTO_INCREMENT PRIMARY KEY | |
| name | VARCHAR | 20 | |
| color | VARCHAR | 7 |
I could definitely get some normalisation in the works here but like I said, not a DBA and its for a home project only, so, I’m happy with that. I decided to add a color record to the
Following is the MySQL code to create the above tables and database:
CREATE DATABASE TaskMan;
CREATE TABLE priority(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
color VARCHAR(7),
CONSTRAINT priority_pk PRIMARY KEY(id)
);
CREATE TABLE projects(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(56) NOT NULL UNIQUE,
description TEXT,
start_date DATETIME,
deadline DATETIME,
completed BOOLEAN,
color VARCHAR(7),
priority_id INT(3),
CONSTRAINT projects_pk PRIMARY KEY (id),
FOREIGN KEY (priority_id) REFERENCES priority(id) ON DELETE SET NULL
);
CREATE TABLE tasks(
id INT NOT NULL AUTO_INCREMENT,
project_id INT NOT NULL,
name VARCHAR(56) NOT NULL,
description TEXT,
start_date DATETIME,
deadline DATETIME,
estimated_duration INT(3),
completed BOOLEAN,
priority_id INT(3),
CONSTRAINT tasks_pk PRIMARY KEY(id),
FOREIGN KEY(priority_id) REFERENCES priority(id) ON DELETE SET NULL,
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
);
Now, you can either log in to MySQL via terminal or, if you have phpMyAdmin set up, you can log in there and paste it straight into the SQL code executor that they have. I use MySQL via the terminal, although I definitely use phpMyAdmin for quickly inserting and examining data. The order of execution is important because of the
As a side note, ON DELETE SET NULL means set the tasks.priority_id to NULL if a priority that is referenced is deleted. ON DELETE CASCADE means delete this record if the projects.id reference is deleted. It just keeps the database tidy.
The Project
That’s the database set up, at this point I began my nodeJS project in WebStorm. I just titled it TaskMan and created an empty project. From there, I opened the terminal within the WebStorm IDE and ran a couple of commands. The first being
Next I ran some more npm commands to install some node modules to the project:
Express will let me get a server up and running really easily, mysql is, well, for mysql database interaction and moment is a fantastic library which, in their own words, allows you to “Parse, validate, manipulate, and display dates and times in JavaScript”. Morgan is a HTTP error logger I am using for development; the -D parameter is the same as –save-dev, just shorter. So the library won’t be included in production. Finally, http-errors just makes life easier when dealing with http errors, I use it to create a 404 Not Found error.
Alright, with all those dependencies installed, time to churn out some code. I created an app.js file in the root directory of my project. In this file, I included morgan, express and http-errors. At first, I just set up express to listen on port 3000 and return a typical “Hello, world” to ensure everything is working so far:
let createError = require('http-errors');
let express = require('express');
let logger = require('morgan');
let app = express();
app.use(function(req, res, next) {
res.send('Hello, world!');
});
app.listen(3000, "0.0.0.0");
Running this code, I directed my browser to http://localhost:3000 and saw “Hello, world!” as expected. So now express is up and running, my journey continued!
I wanted to ensure I could handle urlencoded and json data, and as such I implemented the built-in middleware provided by the Express library to do so. The following middleware are based on the body-parser library. I also implemented the morgan logger and http-errors at this point.
So below
let createError = require('http-errors');
let express = require('express');
let logger = require('morgan');
let app = express();
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404, 'rip'));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500).send(err.message);
});
app.listen(3000, "0.0.0.0");
The http-errors library in action:
app.use(function(req, res, next) {
next(createError(404, 'rip'));
});
This piece of code takes all incoming requests that reach it and creates a 404 error, with the help of the http-errors library, and passes that to the
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500).send(err.message);
});
At this point, all I will ever get from this code, no matter what calls I make to what endpoints, is 404 errors. This is because of the call to
So the final piece of code to add to app.js is something to handle my requests. I created a new directory in the root of my project called controllers, and in this directory I created an index.js file to handle some controlling. It doesn’t do anything just yet but let’s implement that in app.js for now so we can close off that file and not return to it for a while.
let createError = require('http-errors');
let express = require('express');
let logger = require('morgan');
let controllers = require('./controllers');
let app = express();
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(controllers);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404, 'rip'));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500).send(err.message);
});
app.listen(3000, "0.0.0.0");
Here, I created a controllers constant and told the express app to use whatever code is in that directory to process incoming requests. If that code cannot process requests, an error is thrown. I am yet to update the rest of the code to use http-errors, though, so instead, it simply sets an appropriate status and returns an appropriate message, as opposed to creating a new http error with the library and passing it along the handler chain.
Controllers
The controllers directory provides a controller for each endpoint in the API. So if I make a call to
I had two endpoints in mind, one for the projects and one for the tasks, and so I created a controller for each of those endpoints. Now lets take a look at index.js inside the controllers directory:
let express = require('express'),
router = express.Router(),
projectsController = require('./projects_controller'),
tasksController = require('./tasks_controller');
router.use('/projects', projectsController);
router.use('/tasks', tasksController);
router.get('/', function (req, res) {
res.status(403).send("403 Access Forbidden");
});
module.exports = router;
The code here is relatively simple. I define a handle to the express module so I can access the router component, I define my controllers and then I tell the router to use the appropriate controller for the relative endpoints. I also add some code to return a 403 Access Forbidden error in the event that the directory is accessed directly.
I won’t go through both controllers because they are somewhat similar, so let’s just take a look at projects_controller.js:
let express = require('express'),
router = express.Router(),
projectsModel = require('../models/projects_model'),
general = require('../helpers/general');
router.post('/', createNewProject);
router.get('/', getAllProjects);
router.get('/:projectId', getProjectById);
router.delete('/:projectId', deleteProject);
router.delete('/', deleteProject);
router.put('/:projectId', updateProject);
function getProjectById(req, res) {
let required = ['projectId'],
params = req.params;
if (!general.checkIfObjectContains(params, required)) {
res.status(400).send("Missing Parameter");
} else {
projectsModel.getProjectById(params)
.then(data => {
if (data.toString() !== '')
res.status(200).send({data: data});
else
res.status(404).send('404 Not Found');
})
.catch(
// Log the rejection reason
(err) => {
console.log(err);
});
}
}
function getAllProjects(req, res) {
let required = [],
params = req.params;
if (!general.checkIfObjectContains(params, required)) {
res.status(400).send("Missing Parameter");
} else {
projectsModel.getAllProjects(params)
.then(data => {
if (data !== null)
res.status(200).send({data: data});
else
res.status(404).send('404 Not Found');
})
.catch(
// Log the rejection reason
(err) => {
console.log(err);
});
}
}
function createNewProject(req, res) {
let required = ['name', 'description', 'start_date', 'deadline', 'color', 'priority_id'],
params = req.body;
if (!general.checkIfObjectContains(params, required)) {
res.status(400).send({data:"Missing Parameter"});
} else {
projectsModel.newProject(params)
.then(data => {
if (data !== null && data.affectedRows > 0) {
res.setHeader('Location', '/projects/' + data.insertId);
res.status(201).send(null);
}
else {
res.status(200).send({data:'unable to add record'});
}
})
.catch(
// Log the rejection reason
(err) => {
console.log(err.toString());
});
}
}
function updateProject(req, res) {
let required = ['name', 'description', 'start_date', 'deadline', 'color', 'priority_id', 'project_id'],
params = req.body;
if (!general.checkIfObjectContains(params, required)) {
res.status(400).send({data:"Missing Parameter"});
} else {
projectsModel.updateProject(params)
.then(data => {
if (data !== null && data.affectedRows > 0) {
res.setHeader('Location', '/projects/' + data.insertId);
res.status(201).send(null);
}
else {
res.status(200).send({data:'unable to add record'});
}
})
.catch(
// Log the rejection reason
(err) => {
console.log(err);
});
}
}
function deleteProject(req, res) {
let required = ['projectId'],
params = req.params;
if (!general.checkIfObjectContains(params, required)) {
res.status(404).send("404 Not Found/Missing Parameter");
} else {
projectsModel.deleteProject(params)
.then(data => {
if (data !== null && data.affectedRows > 0)
res.status(200).send(null);
else
res.status(404).send(null);
})
.catch(
// Log the rejection reason
(err) => {
console.log(err);
});
}
}
module.exports = router;
This follows very much the same format as the controllers index.js. I require express so I can access the router component and then tell it which endpoints to use for which type of request.
router.post('/', createNewProject);
router.get('/', getAllProjects);
router.get('/:projectId', getProjectById);
router.delete('/:projectId', deleteProject);
router.delete('/', deleteProject);
router.put('/:projectId', updateProject);
This tells the router what to do with each endpoint. Notice that a few of the endpoints are just defined as a forward slash, that’s because this code is called within the index.js controller code which already defines the /projects endpoint. So when you see a forward slash as an endpoint here, it actually means /projects/. Each of these router function calls refer to the type of HTTP request it will handle, POST, GET, PUT and DELETE. The endpoint is defined and then a function provided so it knows what to do with that request.
Let’s take a closer look at this line:
function getProjectById(req, res) {
let required = ['projectId'],
params = req.params;
if (!general.checkIfObjectContains(params, required)) {
res.status(400).send("Missing Parameter");
} else {
projectsModel.getProjectById(params)
.then(data => {
if (data.toString() !== '')
res.status(200).send({data: data});
else
res.status(404).send('404 Not Found');
})
.catch(
// Log the rejection reason
(err) => {
console.log(err);
});
}
}
So this is how all of the request functions appear. They define some constants, required and params, the required contains a list of parameter names that are required, if any, and the params constant contains the request parameters, if any.
A custom written function contained within a helper class is used to determine whether or not any of the required parameters are missing from the request and if so, a HTTP 400 Bad Request error code is returned along with an indication as to why.
If no parameters are missing, the code goes on to call a function of the projectsModel class, which handles interaction with the MySQL database. Using promises, the code will either return a status of 200 with the requested data or a 404 Not Found, once the MySQL interaction is completed. Finally, any errors are caught and printed to the console, for now. Errors will be properly logged in the future. And finally, at the end of the code,
Models
Let’s take a look at the models directory now, which contains all the code to interact with MySQL. This directory doesn’t contain an index.js file because it’s not required. It does, however, contain a mysql_model.js file that is used within each of the models, which are, naturally, projects_model.js and tasks_model.js. mysql_model.js is simply a wrapper to make interacting with the mysql library more simple. I picked up the concept of this code from a previous job and have used it in all my nodeJS projects since, so shout out to ‘Ash’ for originally writing it and giving me the inspiration, knowledge and understanding to reproduce it.
let mysql = require('mysql'),
config = require('../config');
module.exports = function() {
this.query = function(sql, params) {
if(!params){params = [];
}
return new Promise(function(resolve, reject) {
con = mysql.createConnection(config.mysql);
con.connect(function(err) { if (err) throw err; });
con.query(sql, params, function(err, result) {
if(err) {
return reject(err);
} else {
return resolve(result);
}
});
});
},
this.Select = function(sql, params) {
return this.query(sql, params);
},
this.Update = function(sql, params) {
return this.query(sql, params);
},
this.Insert = function(sql, params) {
return this.query(sql, params);
},
this.Delete = function(sql, params) {
return this.query(sql, params);
}
};
First, the mysql library is included, or, required, as is a config file that defines the database connection information. I’ll show that code in just a minute so you can see how it looks. Then we attempt to connect to the database, if an error is thrown it is returned. This is wrapped in a Promise to allow easy query chaining. Finally, some specific functions are defined which simply provide a user-friendly means of running queries so instead of calling
The config file looks like this, with sensitive values removed:
module.exports = {
mysql: {
host: 'host_here',
user: 'user_here',
password: 'password_here',
database: 'TaskMan'
}
};
So that’s the mysql wrapper detailed, lets take a look at projects_model.js to see what that model is doing. I’ll focus on the
let mysql = require('./mysql_model'),
db = new mysql();
module.exports = {
getProjectById({projectId}) {
let query = "SELECT * FROM projects WHERE id=?",
params = [projectId];
return db.Select(query, params);
},
getAllProjects() {
let query = "SELECT * FROM projects";
return db.Select(query);
},
newProject({name, description, start_date, deadline, color, priority_id}) {
let query = "INSERT INTO projects (name, description, start_date, deadline, color, priority_id) VALUES (?, ?, ?, ?, ?, ?)",
params = [name, description, start_date, deadline, color, priority_id];
return db.Insert(query, params);
},
deleteProject({projectId}) {
let query = "DELETE FROM projects WHERE id=?";
params = [projectId];
return db.Delete(query, params);
},
updateProject({name, description, start_date, deadline, color, priority_id, project_id}) {
let query = "UPDATE projects SET name=?, desription=?, start_date?, deadline=?, color=?, priority_id=? WHERE id=?";
params = name, description, start_date, deadline, color, priority_id, project_id;
return db.Update(query, params);
}
};
So I include the mysql wrapper that I just talked about, and instantiate a new object of that type, called
getProjectById({projectId}) {
let query = "SELECT * FROM projects WHERE id=?",
params = [projectId];
return db.Select(query, params);
}
So here, the
Summary
Alright, let’s wrap it up. I’ve talked about creating a RESTful API that interacts with a MySQL database, using nodeJS. It follows an MVC architecture, without the V, of course. I’ve discussed creating a database, an express nodeJS server, a mysql wrapper, endpoint controllers and models for those controllers. I’ve shown you how I create a RESTful API using this technology, and I hope it helps somebody out there that’s interested in doing the same thing.
The project structure I used is:
Project Root
--app.js
--controllers
----index.js
----projects_controller.js
----tasks_controller.js
--models
----mysql_model.js
----projects_model.js
----tasks_model.js
--config
----index.js
A final note, if you ever decide to use this in a production environment, adding authentication middleware is a relatively easy step also. You’d just have to use an existing node library like Passport or Auth0 and implement a registration and authentication endpoint too.
Thanks for stopping by!