Node JS and MySQL Example With Pug Templating Engine

app.js

var express = require('express');
var mysql = require('mysql');

var app = express();

///
///	Create connection to MySQL database server.
/// 
function getMySQLConnection() {
	return mysql.createConnection({
	  host     : 'MYSQL_HOST',
	  user     : 'MYSQL_USERNAME',
	  password : 'MYSQL_PASSWORD',
	  database : 'MYSQL_DATABASE_NAME'
	});
}

///
/// Use pug as templating engine. Pug is renamed jade.
///
app.set('view engine', 'pug');

///
/// HTTP Method	: GET
/// Endpoint 	: /person
/// 
/// To get collection of person saved in MySQL database.
///
app.get('/person', function(req, res) {
	var personList = [];

	// Connect to MySQL database.
	var connection = getMySQLConnection();
	connection.connect();

	// Do the query to get data.
	connection.query('SELECT * FROM test', function(err, rows, fields) {
	  	if (err) {
	  		res.status(500).json({"status_code": 500,"status_message": "internal server error"});
	  	} else {
	  		// Loop check on each row
	  		for (var i = 0; i < rows.length; i++) {

	  			// Create an object to save current row's data
		  		var person = {
		  			'name':rows[i].name,
		  			'address':rows[i].address,
		  			'phone':rows[i].phone,
		  			'id':rows[i].id
		  		}
		  		// Add object into array
		  		personList.push(person);
	  	}

	  	// Render index.pug page using array 
	  	res.render('index', {"personList": personList});
	  	}
	});

	// Close the MySQL connection
	connection.end();
	
});

///
/// HTTP Method	: GET
/// Endpoint	: /person/:id
/// 
/// To get specific data of person based on their identifier.
///
app.get('/person/:id', function(req, res) {
	// Connect to MySQL database.
	var connection = getMySQLConnection();
	connection.connect();

	// Do the query to get data.
	connection.query('SELECT * FROM test WHERE id = ' + req.params.id, function(err, rows, fields) {
		var person;

	  	if (err) {
	  		res.status(500).json({"status_code": 500,"status_message": "internal server error"});
	  	} else {
	  		// Check if the result is found or not
	  		if(rows.length==1) {
	  			// Create the object to save the data.
	  			var person = {
		  			'name':rows[0].name,
		  			'address':rows[0].address,
		  			'phone':rows[0].phone,
		  			'id':rows[0].id
		  		}
		  		// render the details.plug page.
		  		res.render('details', {"person": person});
	  		} else {
	  			// render not found page
	  			res.status(404).json({"status_code":404, "status_message": "Not found"});
	  		}
	  	}
	});

	// Close MySQL connection
	connection.end();
});

///
/// Start the app on port 300 
/// The endpoint should be: 
/// List/Index 	: http://localhost:3000/person
/// Details 	: http://localhost:3000/person/2
///
app.listen(3000, function () {
    console.log('listening on port', 3000);
});

database.sql

-- phpMyAdmin SQL Dump
-- version 3.5.5
-- http://www.phpmyadmin.net
--
-- Host: sql6.freemysqlhosting.net
-- Generation Time: Jul 21, 2016 at 07:55 AM
-- Server version: 5.5.49-0ubuntu0.14.04.1
-- PHP Version: 5.3.28

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `sql6128413`
--

-- --------------------------------------------------------

--
-- Table structure for table `test`
--

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` int(11) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `name`, `address`, `phone`) VALUES
(1, 'Person 1', 310, 821),
(2, 'Person 2', 311, 852),
(3, 'Person 3', 312, 853),
(4, 'Person 4', 313, 854),
(5, 'Person 5', 314, 855);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

details.pug

html
	head
		title= person.name
	body
		h1= person.name
		article 
			p Name #{person.name} 
			p Address: #{person.address} 
			p Phone: #{person.phone}

index.pug

html
	head
		title= 'Person List'
		style
			include style.css
	body
		h1= 'Person List'
	table
		for person in personList
			tr
				td= person.id
				td
					a(href='/person/'+person.id) #{person.name}
				td= person.address
				td= person.phone

style.css

table, th, td {
   border: 1px solid black;
}

This is just an simple Node.js application that using mysqljs as the MySQL client to connect to MySQL database server.

Requirement: Node Package Manager (npm), MySQL database

Create a MySQL database and import data using database.sql file.

To run the app it, use these steps:

1. Make a directory. For example: example-node-mysql.
2. Go to that directory using terminal.
3. Run these command:

-> npm init
-> npm install express mysql mysqljs/mysql pug --save
-> node app.js

Now, you can access the sample on http://localhost:3000/person

can you plz explain such example for store procedure

Thanks for this excellent example!

Really helpful example. Thanks a lot!!!

Lovely!!!!

Very clear and helpful for my own project.
Best Regards.

quá ổn áp :v , cảm ơn