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
– Zlatko, Commented Feb 12, 2014 at 23:37
@Zlatko that is what i thought, since that connection never returned to the pool when error strike — Shift 'n Tab
– Shift 'n Tab, Commented Oct 26, 2017 at 3:26

Zlatko · Accepted Answer · 2014-02-12 23:43:19Z

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, 2014 at 23:43

Zlatko

19.7k14 gold badges76 silver badges130 bronze badges

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

3 Comments

Philipp Kyeck Over a year ago

that is what I ended up doing - but with promises and generators. thank you for pointing me in the right direction.

Z2VvZ3Vp Over a year ago

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

Zlatko Over a year ago

Oh, you can do that, I thought you can do would release the connection in the makeRequest, but finally would be way better.

Jawa · Accepted Answer · 2015-03-30 12:59:08Z

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, 2015 at 12:59

Jawa

2,3326 gold badges34 silver badges39 bronze badges

answered Mar 30, 2015 at 12:23

Ammy

3792 silver badges8 bronze badges

Comments

Peter Smartt · Accepted Answer · 2016-02-23 00:09:09Z

0

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.

answered Feb 23, 2016 at 0:09

Peter Smartt

3583 silver badges11 bronze badges

Collectives™ on Stack Overflow

node-mysql - when to release connection back into pool

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related