Creating a NodeJS REST API with Express and MySQL
NodeJS offers the ability to build Web apps using JavaScript. Today, I will show you how to build a NodeJS REST API for a task list using Express and MySQL.
First, create a new schema in your local MySQL or MariaDB instance. (I am using MariaDB, but either should suffice for this exercise).
Create a table called tasks like so:
CREATE TABLE `tasks` ( `taskID` int(11) NOT NULL AUTO_INCREMENT, `taskName` varchar(50) NOT NULL, `dateCreated` datetime NOT NULL, `dateDue` datetime DEFAULT NULL, `dateCompleted` datetime DEFAULT NULL, PRIMARY KEY (`taskID`), UNIQUE KEY `taskID_UNIQUE` (`taskID`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
Next, create a working folder and subfolders for the application:
$ mkdir tasks $ cd tasks $ mkdir controllers $ mkdir routes $ mkdir model $ npm init
Follow the prompts for npm init to setup your package.json file.
Next, install the necessary packages from npm:
$ npm install express body-parser mysql
Now create an index file as an entry point into the application.
index.js
----------
const express = require( 'express' );
const app = express();
// routes for the API
require( './routes/tasks.js' )(app);
// set our listener
var server = app.listen( 4000, function(){
});
In the index.js file, we require the express package that was installed and initialize the app object. Then we include a list of routes for our API and create a server to listen to requests. Note that we have to pass the app variable into /routes/tasks.js. We'll see why in a moment.
Next, create a file called routes.js in the routes folder to hold the routes for the API:
routes.js
-----------
module.exports = function(app) {
const taskcontroller = require( "../controllers/tasks.js" );
const bodyParser = require('body-parser');
var jsonParser = bodyParser.json();
// get all tasks
app.get( "/tasks", taskcontroller.getAll );
// create a new task
app.post( "/task", jsonParser, taskcontroller.create );
// get a task by ID
app.get( "/task/:ID", taskcontroller.read );
//update a task
app.put( "/task/:ID", jsonParser, taskcontroller.update );
//delete a task
app.delete( "/task/:ID", taskcontroller.delete );
};
Our API defines five separate routes- four for the CRUD functions (create, read, update, and delete), and one - getAll - to get the full task list.
Note that the module.exports statement defines a function that accepts app as an arugment. Hence in the call to routes.js from index.js, we pass app as an argument when requiring the file.
Next, note that we are requiring controllers/tasks.js, our controller for the app. We use the controller to handle the route events for the API.
Lastly, we are requiring body-parser in order to parse the JSON data from the body of the request. body-parser used to be a part of the Express package, but now it must be installed and required separately.
Next, we'll get into the heart of the API event handling with controllers/tasks.js
controllers/tasks.js
-----------------------
const taskservice = require( '../model/taskservice' );
module.exports = {
getAll: function( request, response ){
taskservice.getAll()
.then( function( results ){
response.send( JSON.stringify( results ) );
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
})
},
create: function( request, response){
taskservice.create( request.body.taskName, request.body.dateDue )
.then( function( results ){
//we are reading back the inserted row
taskservice.read( results )
.then( function( task ){
response.send( JSON.stringify( task ) );
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
},
read: function( request, response){
taskservice.read( request.params.ID )
.then( function( results ){
response.send( JSON.stringify( results ) );
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
},
update: function( request, response){
//response.send( JSON.stringify( request.body ) );
taskservice.update( request.params.ID, request.body.taskName, request.body.dateDue, request.body.complete )
.then( function( results ){
//we are reading back the updated row
taskservice.read( request.body.taskID )
.then( function( task ){
response.send( JSON.stringify( task ) );
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
},
delete: function( request, response){
taskservice.delete( request.params.ID )
.then( function( success ){
response.send( JSON.stringify( success ) );
})
.catch( function( error ){
console.log( error );
response.send( JSON.stringify( error ) );
});
}
}
First, note that we are requiring model/taskservice.js, which manages interactions with the DAO and gateway objects. Something you will notice immediately is that we are using Promises to grab to results of the database calls. Note that in some cases (create and update) we are processing the create and update methods, then calling the read() method to return an updated copy of the record in question. We are also inserting our new tasks using an auto-incrementing ID in the database. Strictly speaking, a REST API should not alter the record being inserted, but on a practical level, many APIs labelled as REST work that way.
At this point, you might be wondering why there are so many layers to what is, in effect, a small application. In general, packages should have a single purpose, and should know as little as possible about the rest of the application. In the case of our task controller, it knows how to process requests, move data to and from the task service, and send responses to the client.
Next, let's look at the task service.
model/taskservice.js
-------------------------
const taskdao = require( './taskdao' );
const taskgateway = require( './taskgateway' );
const dao = taskdao();
const gateway = taskgateway();
module.exports = {
getAll: function(){
return( gateway.getAll() );
},
create: function( taskName, dateDue, complete ){
return( dao.create( taskName, dateDue ) );
},
read: function( taskID ){
return( dao.read( taskID ) );
},
update: function( taskID, taskName, dateDue, complete ){
return( dao.update( taskID, taskName, dateDue, complete ) );
},
delete: function( taskID ){
return( dao.delete( taskID ) );
}
};
As you would expect, we are requiring the dao and gateway objects. Next, we define our exports and call our DAO or gateway methods as needed for each API call. The returns, in this case, return Promise objects from the dao and gateway, respectively.
Now let's look at our dao and gateway.
model/taskdao.js
---------------------
var mysql = require( 'mysql' );
module.exports = init;
const pool = mysql.createPool({
connectionLimit : 50,
host : 'localhost',
user : '<user>',
password : '<password>',
database : '<db>'
});
function init(){
return dao;
}
var dao = {
create: function ( taskName, dateDue ){
return new Promise( function( resolve, reject ){
pool.getConnection(function(err, connection) {
if (err) throw err;
connection.query('INSERT INTO tasks ( taskName, dateCreated, dateDue ) VALUES ( ?, curdate(), ? )',
[taskName, dateDue],
function (error, results, fields) {
connection.release();
if (error) reject( error );
resolve( results.insertId );
});
});
});
},
read: function( taskID ){
return new Promise( function( resolve, reject ){
pool.getConnection(function(err, connection){
if (err) throw err;
var query = connection.query('SELECT taskID, taskName, dateCreated, dateDue, dateCompleted FROM tasks WHERE taskID = ?',
[taskID],
function (error, results, fields){
connection.release();
if (error) reject( error );
resolve( results[0] );
});
});
});
},
update: function( taskID, taskName, dateDue, complete ){
return new Promise( function( resolve, reject ){
pool.getConnection(function(err, connection) {
if (err) throw err;
var dateCompleted = ( complete ? new Date() : null );
var due = new Date( dateDue );
connection.query('UPDATE tasks SET taskName = ?, dateDue = ?, dateCompleted = ? WHERE taskID = ?',
[ taskName, due, dateCompleted, taskID ],
function (error, results, fields){
connection.release();
if (error) reject( error );
resolve(true);
});
});
});
},
delete: function( taskID ){
return new Promise( function( resolve, reject ){
pool.getConnection(function(err, connection) {
if (err) throw err;
connection.query('DELETE FROM tasks WHERE taskID = ?',
[taskID],
function (error, results, fields){
connection.release();
if (error) reject( error );
resolve( true ); // successful delete
});
});
});
}
};
and our gateway:
model/taskgateway.js
---------------------------
var mysql = require( 'mysql' );
module.exports = init;
var pool = mysql.createPool({
connectionLimit : 50,
host : 'localhost',
user : '<user>',
password : '<password>',
database : '<db>'
});
function init(){
return gateway;
}
gateway = {
getAll : function(){
return new Promise( function( resolve, reject ){
pool.getConnection(function(err, connection) {
if (err) throw err;
connection.query('SELECT taskID, taskName, dateDue, dateCreated, dateCompleted FROM tasks ORDER BY dateDue',
function (error, results, fields) {
connection.release();
if (error) reject( error );
resolve( results );
});
});
});
}
}
I will not go into too much detail regarding the files. You can study how the parts fit together in relation to how the mysql package works. Make sure you update the connection pool definitions with database name, username, and password for your database.
Lastly, I would note that while this code is not what I would call production-ready, it represents a relatively standard way of organizing an application of this sort. With this kind of structure, a Web programmer who is unfamiliar with NodeJS could open the application and have a general idea of what is happening by how it is structured.
Next, we will build a Single Page App ( SPA ) to test the REST API we've just created.