Sample Node.JS REST API Implementation with MySQL

Here’s a simple example of a RESTful API implementation that I cobbled together for one of the students in our Node.JS Fundamentals course. It’s illustrates how to use the Express framework, along with a mysql datasource to implement create/read/update/destroy functionality on records.

Enjoy!

var express = require('express');
var router = express.Router();

var mysql = require('mysql');
var pool = mysql.createPool({
	host: 'localhost',
	user: 'root',
	password: '',
	database: 'NodeJSExamples'
});

/* GET users listing. */
router.get('/', function(req, res) {

	console.log(req);

	pool.getConnection(function(err, connection) {

		if (err) {
			console.error("An error occurred: " + err);
		}

		connection.query('select * from Person', function(err, rows) {
			if (err) {
				throw err;
			} else {
				res.writeHead(200, {
					"Content-Type": "application/json"
				});
				var result = {
					success: true,
					rows: rows.length,

				}
				res.write(JSON.stringify(rows));
				res.end();
			}

			connection.release();
		});

	});

});


router.delete('/:id', function(req, res) {

	console.log(req);

	pool.getConnection(function(err, connection) {

		if (err) {
			console.error("An error occurred: " + err);
		}

		connection.query('delete from Person where id=?', [req.params.id], function(err, rows) {

			if (err) {
				throw err;
			} else {

				res.writeHead(200, {
					"Content-Type": "application/json"
				});
				var result = {
					success: true,
					rows: rows.length,
					detail: rows

				}
				res.write(JSON.stringify(result));
				res.end();

			}

			connection.release();
		});

	});

});

router.put('/:id', function(req, res) {

	console.log(req.body);

	req.assert('lastName', 'Last Name is required').notEmpty();
	var errors = req.validationErrors();

	pool.getConnection(function(err, connection) {

		if (err) {
			console.error("An error occurred: " + err);
		}

		connection.query('update Person set ? where id = ?', [req.body, req.params.id],
			function(err, rows) {
				if (err) {
					throw err;
				} else {
					res.writeHead(200, {
						"Content-Type": "application/json"
					});
					var result = {
						success: true,
						detail: rows

					}
					res.write(JSON.stringify(result));
					res.end();
				}

				connection.release();
			});

	});

});


router.post('/', function(req, res) {

	console.log(req.body);

	// req.assert('lastName', 'Last Name is required').notEmpty();
	// var errors = req.validationErrors();

	pool.getConnection(function(err, connection) {

		if (err) {
			console.error("An error occurred: " + err);
		}

		connection.query('insert into Person set ?', req.body,
			function(err, rows) {
				if (err) {
					throw err;
				} else {
					res.writeHead(200, {
						"Content-Type": "application/json"
					});
					var result = {
						success: true,
						detail: rows,
						id: rows.insertId

					}
					res.write(JSON.stringify(result));
					res.end();
				}

				connection.release();
			});

	});

});

module.exports = router;

One thought on “Sample Node.JS REST API Implementation with MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s