NodeJS Using Mysql Connection

Share on TwitterDigg This

Mainly server side stands for connecting and querying a database.
With Node.js,You have to connect the mysql database by installing a library. here, picked the stable and easy to use node-mysql module.
The full name of this module is mysql@2.0.0-alpha2 (everything after the @ is the version number)
Open your console, and execute the following command:

To install nodejs mysql module

npm install mysql@2.0.0-alpha2

Above creates the node_modules folder in the current directory.

NodeJS mysql connecting and querying example

var http = require('http')
mysql = require('mysql');
 
var client = mysql.createClient({
user: 'root',
password: ''
});
 
client.useDatabase('cookbook');
 
http.createServer(function (req, res) {
if (req.url == '/') {
client.query("SELECT r.name AS 'Recipe', ri.amount AS 'Amount',mu.name AS 'Unit',i.name"
+ " AS 'Ingredient' FROM Recipe r JOIN RecipeIngredient ri on r.id = ri.recipe_id"
+ " JOIN Ingredient i on i.id = ri.ingredient_id LEFT OUTER JOIN Measure mu on mu.id = measure_id",
function(err, results, fields) {
if (err) throw err;
var output = '<html><head></head><body><h1>Latest Posts</h1><ul><table border=1><tr>';
for (var index in fields) {
output += '<td>' + fields[index].name + '</td>';
}
output += '</tr>';
for (var index in results) {
output += '<tr><td>' + results[index].Recipe + '</td>';
output += '<td>' + results[index].Amount + '</td>';
output += '<td>' + results[index].Unit + '</td>';
output += '<td>' + results[index].Ingredient + '</td></tr>';
}
output += '</ul></body></html>';
res.writeHead(200, {'Content-Type': 'text/html'});
res.end(output);
}
);
}
}).listen(8080);

Save this file as mysqlconn.js, and execute it

node mysqlconn.js

Navigate to http://localhost:8080 in your browser, and you should be prompted to view the table data.

Related

About prabin t p

Connect with me on Google+
This entry was posted in Javascript, NodeJS and tagged , . Bookmark the permalink.

2 Responses to NodeJS Using Mysql Connection

  1. Jitendra Kachhawa says:

    Error: ‘createClient’ has no method.
    PLS give me the solution.

  2. prabin t p says:

    Try to connect using

    var sqlInfo = {
    host: ‘localhost’,
    user: ‘root’,
    password: ”,
    database: ‘dbname’
    }
    client = mysql.createConnection(sqlInfo);

    client.connect();

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">

*