How to structure Node.js script with MySQL so that connection doesn't close prematurely

Question

How to structure Node.js script with MySQL so that connection doesn't close prematurely

So I am making my first attempt with Node and I can't really wrap my head around how to work with the MySQL connection. The script is somewhat simplified like this

var mysql       = require('mysql');
var connection  = mysql.createConnection({
  host     : '192.168.40.1',
  user     : 'user',
  password : 'password',
  database : 'database'
});

function DoSomething(connection, item, callback) {
    connection.query(
        'SELECT COUNT(*) AS count FROM another_table WHERE field=?', 
        item.field, 
        function (err, results) {
            if (err) throw err;

            if (results.length > 0 && results[0].count >= 1) {
                callback(err, connection, item, 'Found something')
            }
    });
}

function DoSomethingElse(connection, item, callback) {
    // Similar to DoSomething()
}

function StoreResult(err, connection, item, reason) {
    if (err) throw err;

    connection.query(
        'INSERT INTO result (item_id, reason) VALUES (?, ?)',
        [item.id, reason],
        function (err, results) {
            if (err)  throw err;
    });
}

connection.query('SELECT * FROM table WHERE deleted=?', [0], function (err, results) 
{
    if (err) throw err;

    results.forEach(function (item, index) {
        DoSomething(connection, item, StoreResult);
        DoSomethingElse(connection, item, StoreResult);
    });
});

connection.end();

What I am having trouble with (as far as I can tell) is that since DoSomething() it seems that connection.end() is called before all of the DoSomething()'s have finished causing errors that queries can't be performed when the connection is closed.

I tried playing around with the async library, but I haven't gotten anywhere so far. Anyone with some good advice on how to do this?

mysql
node.js

Answer 1

The problem with your code is that you're closing the connection synchronously while an asynchronous request is still being handled. You should call connection.end() only after all query callbacks have been called.

Since you are doing multiple queries, this means using some way to wait for all their results. The simplest way is to nest every next call into the callback of the previous one, but that way leads to the pyramid of doom. There are a number of popular libraries that solve this, but my own preference is for async.

Using async I would rewrite your code as follows:

async.waterfall([function(next) {
  connection.query('SELECT * FROM table WHERE deleted=?', [0], next); // note the callback
},
function(results, next) {
  // asynchronously handle each results. If they should be in order, use forEachSeries
  async.forEach(results, function(item, next) {
    // handle in parallel
    async.parallel([function(done) {
      DoSomething(connection, item, function(err, connection, item, reason) {
        // This is a hack, StoreResult should have a callback which is called
        // after it's done, because the callback is now being called too soon
        StoreResult(err, connection, item, reason);
        callback(err);
      });
    }, function(done) {
      DoSomethingElse(connection, item, function(err, connection, item, reason) {
        // This is a hack, StoreResult should have a callback which is called
        // after it's done, because the callback is now being called too soon
        StoreResult(err, connection, item, reason);
        callback(err);
    }], function(err) {
      // this callback is called with an error as soon as it occurs
      // or after all callbacks are called without error
      next(err);
    });
  }, function(err) {
    // idem
    next(err);
  });
}], function(err, results) {
  // idem
  // Do whatever you want to do with the final error here
  connection.end();
});

This also allows you to solve a possible issue with the order of your queries in the forEach: They are started in order, but are not guaranteed to finish in order due to their asynchronous nature.

Answer 2

Close your connection after you have done everything you want in the script.

When programming in asynchronous language, keep in mind that the real ending point of your script is the last asynchronous callback, instead of the last line like other scripts (e.g. PHP).

Note that you don't want to simply ignore the connection.end(); as the underlying MySQL driver will keep the connection alive and your script will stuck in the last line forever until you kill it.

This is the modified version of your code.

connection.query('SELECT * FROM table WHERE deleted=?', [0], function (err, results) 
{
    if (err) throw err;

    results.forEach(function (item, index) {
        DoSomething(connection, item, StoreResult);
        DoSomethingElse(connection, item, StoreResult);
    });

    // End your connection here
    connection.end();
});