node.js - connection pooling

Hello everyone,

just wondering , is this the proper way to :

  • get connection from connection pool ,
  • send a request to mysql server,
  • wait for result
  • return the connection back to connection pool?

This below code is called everytime an user logs in, it checks his username and token, if it's a match it will open main menu page, if not it will return back to login page.

The truth is, that it's all working in Chrome, but sometimes it does not work in Firefox , it just doesn't call the connection.query() part at all..

So I'm just checking with you guys , if everything is okay with the code below..Or if there is anything I could improve or change..

var db_pool = mysql.createPool({
    host: 'localhost',
    user: 'dbuser',
    password: 'pass',
    database: 'db_name'
});

function CheckUser(username, token)
{
    db_pool.getConnection(function(err, connection) 
    {
        console.log(" [i] Getting connection from pool. ");
        var entry = 0;
        var query = connection.query("SELECT token FROM users where token = '"+token+"' and user_id = '"+username+"'");

        query.on('result', function(data){
            entry++;
        });
        query.on('error', 
            function(err){
                throw(err);
            }
        );
        query.on('end', 
            function()
            {
                if(entry == 1)
                {
                    console.log(" [info] User ["+username+"] authorized.");
                    /* DO STUFF */
                }else
                {
                    console.log(" [Error] User ["+username+"] does not have token: ["+token+"].");
                    return false;
                }
            }
        );
        console.log(" [i] Returning back connection to pool. ");
        connection.release();
    });
}

Any help is greatly appreciated,

Alex

I believe that the intermittent behavior is because your release() statement is in line with the connection rather than in the 'end' handler. Here is what works for me:

var mysql = require('mysql'),
    config = require('./config.json');

var pool = mysql.createPool( config );

var checkUser = function(username, token) {
    pool.getConnection(function(err, connection) {
        if (err) throw err;

        console.log(" [i] Getting connection from pool. ");
        var entry = 0,
            statement = 'SELECT token FROM users where token = ? and user_id = ?',
            query = connection.query( statement, [ token, username ] );

        query.on('result', function(data) {
            console.log( data );
            entry++;
        });

        query.on('error',  function(err) {
            console.log( err );
            throw err;
        });

        query.on('end', function() {
            if (entry === 1) {
                console.log(" [info] User ["+username+"] authorized.");
                /* DO STUFF */
            } else {
                console.log(" [Error] User [", username, "] does not have token [', token, ']');
            }

            console.log(" [i] Returning back connection to pool. ");
            connection.release();
        }); 
    });
};

console.log('check the user...');
checkUser( 'test-user', 'mytoken');

I was able to test this (with an alternate statement) with no problems. I also put the config stuff in a separate file and replaced the string statement with parameters.

Hope this helps...