node-mysql can't seem to connect

Question

I've been having issues with node-mysql where I haven't been able to get a connection with it. Here is my code:

var mysql = require('mysql');
var pool = mysql.createPool({
    connectionLimit: 10,
    user: 'node',
    password: 'pass',
    host: 'localhost',
    port: 3306,
    database: 'store'
});

exports.getUser = function(user, pass){
    var sql = 'SELECT * FROM store.users WHERE user = \'' + user + '\' AND password = \'' + pass + '\'';
    pool.query(sql, function(err, rows, fields) {
      if(err){
        return err;
      }else{
          return rows.length;
      }
    });
};

Just sort of can't seem to throw an error or anything. The password in the database is plaintext for testing, so if I call it:

var pool = require('./database');
var message = pool.getUser('test','test');

Answer 1

The problem is your getUser function is returning immediately while the database functions are executing asynchronously. So, by the time the query function completes your getUser function has long since exited.

You could get around this by passing a callback function in along with your username and password, like this:

var mysql = require('mysql');

var pool = mysql.createPool({
    connectionLimit: 10,
    user: 'root',
    password: '',
    host: '127.0.0.1',
    port: 3306,
    database: 'store'
});

exports.getUser = function(user, pass, cb){
    var sql = 'SELECT * FROM store.users WHERE user = \'' + user + '\' AND password = \'' + pass + '\'';

    pool.getConnection(function(err, connection) {
        if(err) throw err;

        // Use the connection
        connection.query( sql, function(err, rows) {
            if(err) throw err;
            cb(rows.length);

            // And done with the connection.
            connection.release();
        });
    });

};

Then, you call your function like this:

var pool = require('./database');
pool.getUser('someuser', '1234', function(result){ console.log(result); } );

To get a handle on the asynchronous nature of nodejs I suggest taking a look at The Node Beginner Book (http://www.nodebeginner.org). There is even a section addressing your very problem, titled "Event-driven asynchronous callbacks."