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.

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;