How does pool.query() and pool.getGetConnection() differ on connection.release()?

Question

As i can understand every pool.query() will cost a connection and it is automatically release when it ends. based from this comment on github issue. But what about the nested queries performed using pool.getConnection()?

pool.getConnection(function(err, connection) {

  // First query
  connection.query('query_1', function (error, results, fields) {

    // Second query
    connection.query('query_2', function (error, results, fields) {

          // Release the connection
          // DOES THIS ALSO RELEASE query_1?
          connection.release();

          if (error) throw error;

          // you can't use connection any longer here..
    });
  });
});

UPDATE

Here is my code using transaction when performing nested queries.

const pool = require('../config/db');

function create(request, response) {
   try {

       pool.getConnection(function(err, con) {

           if (err) {
               con.release();
               throw err;
           }

           con.beginTransaction(function(t_err) {

               if (t_err) {
                   con.rollback(function() {
                      con.release();
                      throw t_err;
                   });
               }


               con.query(`insert record`, [data], function(i_err, result, fields){

                   if (i_err) {
                       con.rollback(function() {
                           con.release();
                           throw i_err;
                       });
                   }


                   // get inserted record id.
                   const id = result.insertId;

                   con.query(`update query`, [data, id], function(u_err, result, fields)=> {

                       if (u_err) {
                          con.rollback(function() {
                             con.release();
                             throw u_err;
                          });
                       }

                       con.commit(function(c_err){
                          if (c_err) {
                             con.release();
                             throw c_err;
                          }
                       });

                       con.release();

                       if (err) throw err;

                       response.send({ msg: 'Successful' });
                   });
               });

           });
       });

   } catch (err) {
      throw err;
   }
}

I made a lot of defensive error catching and con.release() since at this point i do not know how to properly release every connection that is in active.

And i also assume that every con.query() inside pool.getConnection() will cost a connection.

Ricks

Why ever have nested queries? Seems like it is just asking for trouble. Please provide a small example where nesting is useful. – Indigence

What i mean is a nested connection.query() as shown in my example, this is necessary for instance that the first query may insert data and get the insertId and update another table row/s. – Nealey

Answer 1

EDIT:

A connection is like a wire that connects your application to your database. Each time you connection.query() all you're doing is sending a message along that wire, you're not replacing the wire.

When you ask the pool for a connection, it will either give you a 'wire' it already has in place or create a new wire to the database. When you release() a pooled connection, the pool reclaims it, but keeps it in place for a while in case you need it again.

So a query is a message along the connection wire. You can send as many messages along as you want, it's only one wire.

---

Original Answer

pool.query(statement, callback) is essentially

const query = (statement, callback) => {
    pool.getConnection((err, conn) => {
        if(err) {
            callback(err);    
        } else {
            conn.query(statement, (error, results, fields) => {
                conn.release();
                callback(error, results, fields);
            });
        }
    })
}

Ideally you shouldn't be worrying about connections as much as the number of round trips you're making. You can enable multiple statements in your pool config multipleStatements: true on construction of your pool and then take advantage of transactions.

BEGIN;
INSERT ...;
SELECT LAST_INSERT_ID() INTO @lastId;
UPDATE ...;
COMMIT;

Alessandraalessandria

not including the multipleStatement but, i am questioning the flow of pool.getConnection(); if it has multiple con.query() when calling con.release() all con.queries inside it is all release then? – Nealey

A connection is like a wire that connects your application to your database. Each time you connection.query() all you're doing is sending a message along that wire, you're not replacing the wire. When you ask the pool for a connection, it will either give you a 'wire' it already has in place or create a new wire to the database. When you release() a pooled connection, the pool reclaims it, but keeps it in place for a while in case you need it again. So a query is a message along the connection wire. You can send as many messages along as you want, it's only one wire. – Alessandraalessandria

I hit enter instead of shift-enter while responding... See my edit above. – Alessandraalessandria

i see that would be answer to my question. so when ever i call pool.getConnection() it just one wire and regardless how many con.queries it may have. This is exactly what i need to clear and you can make your comment as an answer – Nealey

Edited in the answer above – Alessandraalessandria

Answer 2

It sounds like you are not closing the first query as quickly as you should.

Please show us the actual code. You do not need to hang onto the query to get insertid.

(After Update to Question:) I do not understand the need for "nesting". The code is linear (except for throwing errors):

BEGIN;
INSERT ...;
get insertid
UPDATE ...;
COMMIT;

If any step fails, throw an error. I see no need for two "connections". You are finished with the INSERT before starting the UPDATE, so I don't see any need for "nesting" SQL commands. And get insertid is a meta operation that does not involve a real SQL command.

Indigence

so that it means i just only used only 1 connection when calling pool.getConnection()? but let me clear the misunderstanding all i asked was how many connection i used in my given query and how should i properly release it when using pool.getConnection() – Nealey

All statements in a transaction must be in the same connection, else transactional info will be lost. – Indigence

Answer 3

I don't know Node.js, but looking at the code and Github documentaion, it is almost certain that pool.getConnection gets a connection from a connection pool and it calls the function with a connection object obtained and any error encountered while getting a connection from the pool. Within the function body we may use the connection object any number of times, but once it is released it won't be usable as it goes back to the pool and I assume the connection object will no longer have the reference to underlying mysql connection (a little lower level connection object may be). Now we have to release the connection object only once, and we must release the connection object if we don't want to run out of free connection from the connection pool; otherwise subsequent call to pool.getConnection won't find any connection in "free" list of connection as they are already moved to "in_use" list of connections and they are never released.

Generally, after getting a connection from the connection pool, it may used for any number of operations/queries and it is released "once" to give it back to "free" list of the pool. That is how the connection pooling generally works.

Tahitian

Thanks for your response, so that it means that regardless of how many connection.query() are called then it only cost you single connection inside pool.getConnection()? and would instantly release after the connection.release() is called. But on the other hand calling pool.query() multiple times will cost you based on how many times it was called but its automatically release when it ends. – Nealey