node-mysql - when to release connection back into pool

connection-pooling, mysql, node-mysql, node.js

Solution

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?

Problem

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?

Original source