NodeJS REST API with MySQL and Express

http://www.nodewiz.biz/nodejs-rest-api-with-mysql-and-express/

NPM Modules

• Express
• felixge/node-mysql - Source

Most articles about building a REST API for NodeJS will be based on MongoDB, I'm going to show you how to do it with MySQL.

Implementing the REST API

To implement all the routes required by the API, the full REST API for the node application will be a single file server.js which consists of the following methods:

Verb	URI	Action
GET	/tableName	Retrieve all wines
GET	/tableName/id	Retrieve the wine with the specified _id
POST	/tableName	Add a new wine
PUT	/tableName/id	Update wine with the specified _id
DELETE	/tableName/id	Delete the wine with the specified _id

Structure

Require your modules and create a http server based on express framework.

1. var express = require('express'),
2. app = express(),
3. mysql = require('mysql');
4. app.listen(3000);
5. console.log('Rest Demo Listening on port 3000');

DB Connection

Setup your database and create a pool of connections to MySQL server;

1. var express = require('express'),
2. app = express(),
3. mysql = require('mysql'),
4. connectionpool = mysql.createPool({
5. host : 'localhost',
6. user : 'root',
7. password : 'secret',
8. database : 'rest_demo'
9. });
10. app.listen(3000);
11. console.log('Rest Demo Listening on port 3000');

Where the configuration uses your host, username, password, and database name of course.

Routes

Your application will only need five REST routes to cover the methods table above.

1. var express = require('express'),
2. app = express(),
3. mysql = require('mysql'),
4. connectionpool = mysql.createPool({
5. host : 'localhost',
6. user : 'root',
7. password : 'secret',
8. database : 'rest_demo'
9. }),
10. res.setHeader({ 'Content-Type': 'application/json' });
11. app.get('/:table', function(req,res){});
12. app.get('/:table/:id', function(req,res){});
13. app.post('/:table', function(req,res){});
14. app.put('/:table/:id', function(req,res){});
15. app.delete('/:table/:id', function(req,res){});
16.  
17. app.listen(3000);
18. console.log('Rest Demo Listening on port 3000');

Each route takes a callback function with request and response objects.

You may also notice we are going to be sending json Content-Type as a response always. I will set it up so that even errors will be responding in json, this is personal preference and you might want to do something else but i see it with AWS, Google, Facebook, ect so figured its generally a good idea.

Connection and Error Handling

We will be getting a connection from our pool, which may have reached its allocated limit and throw an error which needs to be handled.

1. var express = require('express'),
2. app = express(),
3. mysql = require('mysql'),
4. connectionpool = mysql.createPool({
5. host : 'localhost',
6. user : 'root',
7. password : 'secret',
8. database : 'rest_demo'
9. });
10. res.setHeader({ 'Content-Type': 'application/json' });
11. app.get('/:table', function(req,res){
12. connectionpool.getConnection(function(err, connection) {
13. if (err) {
14. console.error('CONNECTION error: ',err);
15. res.statusCode = 503;
16. res.send({
17. result: 'error',
18. err: err.code
19. });
20. } else {
21. // query the database using connection
22. }
23. });
24. });

When we encounter connection errors node.js will log them to the console and our app will respond with http status code 503 Service Unavailable with a mysql server error code.

Querying MySQL

Our routes will define a table name and if required an id, which we will use to build our query and return some json data.

Take a look at fetching the latest 20 rows;

1. var express = require('express'),
2. app = express(),
3. mysql = require('mysql'),
4. connectionpool = mysql.createPool({
5. host : 'localhost',
6. user : 'root',
7. password : 'secret',
8. database : 'rest_demo'
9. });
10. app.get('/:table', function(req,res){
11. connectionpool.getConnection(function(err, connection) {
12. if (err) {
13. console.error('CONNECTION error: ',err);
14. res.statusCode = 503;
15. res.send({
16. result: 'error',
17. err: err.code
18. });
19. } else {
20. connection.query('SELECT * FROM '+req.params.table+' ORDER BY id DESC LIMIT 20', req.params.id, function(err, rows, fields) {
21. if (err) {
22. console.error(err);
23. res.statusCode = 500;
24. res.send({
25. result: 'error',
26. err: err.code
27. });
28. }
29. res.send({
30. result: 'success',
31. err: '',
32. fields: fields,
33. json: rows,
34. length: rows.length
35. });
36. connection.release();
37. });
38. }
39. });
40. });
41. app.get('/:table/:id', function(req,res){});
42. app.post('/:table', function(req,res){});
43. app.put('/:table/:id', function(req,res){});
44. app.delete('/:table/:id', function(req,res){});
45. app.listen(3000);
46. console.log('Rest Demo Listening on port 3000');

Other than the error handling (which returns a http code 500) we have responded to the requester with up to 20 rows of data. 
They also get the field names and how many rows were returned.

Putting It All Together

Using the above technique we put together our server.js like so, which comes in at 175 lines in total.

Source GitHub

Note:

Do not use this in production, it is simply a demo.

The main issue is with the varible table name part in the routing, this is a BADidea.

The first thing I would change is encapsulion of each database table in its own js file under a routes directory, then require the needed js file for each request.

I hope you have enjoyed this demo - please leave your feedback below.

@NodeJS REST API with MySQL and Express http://t.co/NEvSjddwPz#nodewiz @codewiz_biz

— Codewiz.biz (@codewiz_biz) November 20, 2013