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

Question

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?

does the script work fine if you comment out connection.end();? — user1703809
– user1703809, Commented Dec 13, 2012 at 9:02
The script seems to do all it is supposed to, but it does not finish. — danneth
– danneth, Commented Dec 13, 2012 at 9:57

mtsr · Accepted Answer · 2012-12-13 11:39:25Z

5

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.

edited Dec 13, 2012 at 11:39

answered Dec 13, 2012 at 11:31

mtsr

3,1521 gold badge16 silver badges21 bronze badges

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

2 Comments

mtsr Over a year ago

And actually the inner parallel with stuff in the callback could be done very nicely as an async.auto call, but that will be left as an exercise to the OP :).

danneth Over a year ago

I experimented with async.waterfall and looked at async.parallell, but I did not get as far as thinking about combining them. I will see where this gets me.

Vicary · Accepted Answer · 2012-12-13 11:37:29Z

0

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();
});

answered Dec 13, 2012 at 11:37

Vicary

1,0741 gold badge15 silver badges36 bronze badges

1 Comment

danneth Over a year ago

Although not mentioned in the question, this is how I had it originally, and I got the same result, some of the DoSomething were still running when connection.end() was called.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related