How to execute MySQL queries in node.js views?

Question

I have imported mysql module in my app.js:

var mysql = require('mysql');

Then I set mysql configuration:

// Database configuration

var HOST            = 'localhost';
var PORT            = 3306;
var MYSQL_USER      = 'root';
var MYSQL_PASS      = 'pass';
var DATABASE        = 'ruchevits_website';

Created mysql client object:

// Create database client

var db = mysql.createClient({
    host:       HOST,
    port:       PORT,
    user:       MYSQL_USER,
    password:   MYSQL_PASS
});

And selected my database:

// Use database

db.query('use ' + DATABASE);

So, now I can execute queries like:

db.query('select id, firstname, lastname from mytable', function(err, result, fields){
    if (err)
    {
        throw err;
    }
    else
    {
        console.log('Selected people:');
        for (var i in result)
        {
            var people = result[i];
            console.log(people.firstname + ' ' + people.lastname);
        }
    }
});

But it is inconvenient to execute all queries in app.js.

I want to execute them in specific routes files in the section:

exports.index = function(req, res)
{
    // MySQL query

    res.render('resume', {
        title: 'Resume'
    });
};

What is the correct way to do that?

I need to use db object not in app.js file. Actually, the question is how to pass it?

Chris · Accepted Answer · 2013-07-08 21:18:00Z

2

Create a module and export a function that returns the db object.

in dbConnection.js

var mysql = require('mysql');
var db = null;
module.exports = function () {
    if(!db) {
        db = mysql.createClient({
            host:       HOST,
            port:       PORT,
            user:       MYSQL_USER,
            password:   MYSQL_PASS
        });
    };
    return db;
};

use it with var db = require('./dbConnection')(); where ever you need it. Also check out Node.js module caching feature http://nodejs.org/api/modules.html#modules_caching.

edited Jul 8, 2013 at 21:18

answered Aug 27, 2012 at 20:00

Chris

4,2251 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Klaasvaak Over a year ago

You should require it like this: require('./dbConnection')();

Collectives™ on Stack Overflow

How to execute MySQL queries in node.js views?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related