node-mysql - when to release connection back into pool

Question

I'm using the node-mysql driver with connection pooling.

Releasing the connection back into the pool when there's only one query, is easy:

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

  query = "SELECT * FROM user WHERE id = ?";
  connection.query(query, [id], function(err, users) {
    connection.release();

    if (err) {
      throw err;
    }

    // ...
  });
});

What if I need to use the connection a second time? I'd have to move the release() down a few lines. But what happens if the error is thrown? Is the connection never returned to the pool?

Do I have to use some control flow lib to have a "finally" moment in which I could release it?
Any better ideas?

Hmm. Really good question! Got me thinking. Handle the release in each error callback? — Zlatko, Feb 12 '14 at 23:37
@Zlatko that is what i thought, since that connection never returned to the pool when error strike — Shift 'n Tab, Oct 26 '17 at 03:26

score 3 · Accepted Answer · answered Feb 12 '14 at 23:43

3

One way this could be handled is promises. Since you're building a pool, you can construct your requests with something like q (or native promises, coming soon):

// assuming you have your local getConnection above or imported
exports.getConnection = function(queryParams) {
    var d = q.defer();
    local.getConnection(function(err, conn) {
        if(err) d.reject(err);
        d.resolve(conn);
    });
});

So, wrap few of your other calls into promises like that and then just compose your query:

db.getConnection()
.then(function(conn){
    makeRequest()
    .then(...)
    ...
.catch(function(err){
    // at the end you release the conn
});

Does it seem like something you're asking for?

answered Feb 12 '14 at 23:43

Zlatko

18,936
14
70
123

that is what I ended up doing - but with promises and generators. thank you for pointing me in the right direction. – Philipp Kyeck Feb 13 '14 at 11:35
Wait, wouldn't this only release the connection if an error was thrown since you are releasing in the catch block? Wouldn't you want to do `.finally(function(){ conn.release(); }` ?? – Z2VvZ3Vp Jan 30 '15 at 19:16
Oh, you can do that, I thought you can do would release the connection in the `makeRequest`, but finally would be way better. – Zlatko Jan 31 '15 at 20:48

score 1 · Answer 2 · edited Mar 30 '15 at 12:59

1

When you perform a MySQL query, for that time it locks the database until query completes. After the successfull completion of query, it releases that database lock.

Same case here: connection.release(); simply releases the DB connection, nothing else.

edited Mar 30 '15 at 12:59

Jawa

2,336
6
34
39

answered Mar 30 '15 at 12:23

Ammy

369
2
8

score 0 · Answer 3 · answered Feb 23 '16 at 00:09

You should use separate connections for that situation. That's what the connection pool is for. That way one doesn't have to wait for the other one to finish before it can start. I only use the same connection if one query cannot start until the other query has finished.

node-mysql - when to release connection back into pool

3 Answers3