Node JS Asynchronous Database Calls

Question

I am having issues getting node to make a database call without proceeding despite the database function has not returned a value.

Here is the basic http server code:

var http = require('http');

http.createServer(function (request, response) {

response.writeHead(200, {
    'Content-Type': 'text/plain',
    'Access-Control-Allow-origin': '*' // implementation of CORS
});

response.end("ok");
;
}).listen(8080,'0.0.0.0');

Using the request.on('data') function, I am able to decode JSON from requests and proceed that to make a database call:

request.on('data', function (chunk) {
    var json = JSON.parse(chunk);
    var id = parseInt(json["id"]);
    response.end(callDatabase(id));
});

The database function goes something like this:

function callDatabase(id) {
    var result;
    var connection = mysql.createConnection(
        {
            host        :   '192.168.1.14',
            user        :   'root',
            password    :   '',
            database    :   'test'
        }
    );

    connection.connect();
    var queryString = 'SELECT name FROM test WHERE id = 1';

    connection.query(queryString, function(err, rows, fields) {
        if (err) throw err;

        for (var i in rows) {
            result = rows[i].name;
        }
    });
    connection.end();
    return result;
    }
}

However under testing, this proves that I am doing it wrong. I am aware that I probably want to be using the node asynchronous module, which I have tired. I have also tried using the waterfall method, as well as parallel and many other tutorials online. I feel that the request.on function should be in parallel, then the database call async, so whilst node is waiting for the response from the database server, it is free to get on with any other requests, leaving the queued time to a minimum.

Please inform me if I have miss-understood any of the concepts of node js.

Solution

You are returning result and closing the connection before the query has returned it's value from the db. Place that code inside the callback.

Fixing your code, it should look like this:

function callDatabase(id) {
    var result;
    var connection = mysql.createConnection(
        {
            host        :   '192.168.1.14',
            user        :   'root',
            password    :   '',
            database    :   'test'
        }
    );

    connection.connect();
    var queryString = 'SELECT name FROM test WHERE id = 1';

    connection.query(queryString, function(err, rows, fields) {
        if (err) throw err;

        for (var i in rows) {
            result = rows[i].name;
        }

        connection.end();
        return result;
    });
}

Although, this will only solve part of the problem, since now you're still calling response.end(callDatabase(id)); before waiting for a response from the query.

In order to fix this, you need to return some kind of callback.

function callDatabase(id, callback) {
    // the method code here...
    connection.query(queryString, function(err, rows, fields) {
        // code...

        // instead of returning the result, invoke the callback!
        callback(rows);
    });
}

Now you can call it like this :

request.on('data', function (chunk) {
    var json = JSON.parse(chunk);
    var id = parseInt(json["id"]);
    callDatabase(id, function(res) {
        response.end(res);
    });
});

Comments

[Nicholas Mordecai]

That works great thanks! Just out of curosity, for performance.. should the server stay connected to the database server? Is it a waste of performance closing the connection after every http request?