[ en ] Working with node.js and MySQL

This is a small article about how to use node.js as web server to display the results of a query to a MySQL database engine.
The first thing to do is to check is that we have installed the mysql module to run it the following:

lucaMac:BLOG_POSTs pepo$ node
> var Client = require('mysql').Client
Error: Cannot find module 'mysql'
at Function._resolveFilename (module.js:332:11)
at Function._load (module.js:279:25)
at Module.require (module.js:354:17)
at require (module.js:370:17)
at repl:1:14
at REPLServer.eval (repl.js:80:21)
at repl.js:190:20
at REPLServer.eval (repl.js:87:5)
at Interface. (repl.js:182:12)
at Interface.emit (events.js:67:17)
> 

If fails, proceed to install the module via npm:

npm install mysql



The first thing we do is create the object for the query:

var Client = require('mysql').Client,
client = new Client();
client.user = 'user';
client.password = 'password';
client.host='127.0.0.1';
client.port='3306';
client.database='database_name'
client.connect();

then create the web server

var http = require('http');
http.createServer(function (req, res) {
res.writeHead(200, {'Content-Type': 'text/plain'});

And within this we perform the query, and associate to its callback function

client.query(
"select * from table where campo1 > 1 limit 10;",    
function select(err, results, fields) {
if (err) {
console.log("Error: " + err.message);
throw err;
}

print data to the console and the web response

console.log("Number of rows: "+results.length);
console.log(results);


for (var i in results){

var result = results[i];
res.write(result.campo1+"\n");
}

res.end();
});

We finished the code, setting up a port for listening

}).listen(1337, "0.0.0.0");
console.log('Server running ');

The finished script is like this:

var Client = require('mysql').Client,
client = new Client();
client.user = 'user';
client.password = 'password';
client.host='127.0.0.1';
client.port='3306';
client.database='DB'
client.connect();


var http = require('http');
http.createServer(function (req, res) {
res.writeHead(200, {'Content-Type': 'text/plain'});


client.query(
"select * from table where campo1 > 1 limit 10;",    
function select(err, results, fields) {
if (err) {
console.log("Error: " + err.message);
throw err;
}
console.log("Number of rows: "+results.length);
console.log(results);
res.write(results);

for (var i in results){

var result = results[i];
res.write(result.campo1+"\n");
}
res.end();
});

}).listen(1337, "0.0.0.0");
console.log('Server running ');

If you want to download this is the link to the   gist