NodeJS + mysql - automatically closing pool connections?

Question

I wish to use connection pooling using NodeJS with MySQL database. According to docs, there are two ways to do that: either I explicitly get connection from the pool, use it and release it:

var pool = require('mysql').createPool(opts);

pool.getConnection(function(err, conn) {
    conn.query('select 1+1', function(err, res) {
        conn.release();
    });
});

Or I can use it like this:

var mysql = require('mysql');
var pool  = mysql.createPool({opts});

pool.query('select 1+1', function(err, rows, fields) {
  if (err) throw err;

  console.log('The solution is: ', rows[0].solution);
});

If I use the second options, does that mean, that connections are automatically pulled from the pool, used and released? And if so, is there reason to use the first approach?

Answer 1

Yes, the second one means that the pool is responsible to get the next free connection do a query on that and then release it again. You use this for "one shot" queries that have no dependencies.

You use the first one if you want to do multiple queries that depend on each other. A connection holds certain states, like locks, transaction, encoding, timezone, variables, ... .

Here an example that changes the used timezone:

pool.getConnection(function(err, conn) {
    function setTimezone() {
       // set the timezone for the this connection
       conn.query("SET time_zone='+02:00'", queryData);
    }

    function queryData() {
       conn.query( /* some query */, queryData);
    }


    function restoreTimezoneToUTC() {
       // restore the timezone to UTC (or what ever you use as default)
       // otherwise this one connection would use +02 for future request
       // if it is reused in a future `getConnection`
       conn.query("SET time_zone='+00:00'", releseQuery);
    }

    function releaseQuery() {
        // return the query back to the pool
        conn.release()
    }

    setTimezone();
});

Answer 2

In case anyone else stumbles upon this:

When you use pool.query you are in fact calling a shortcut which does what the first example does.

From the readme:

This is a shortcut for the pool.getConnection() -> connection.query() -> connection.release() code flow. Using pool.getConnection() is useful to share connection state for subsequent queries. This is because two calls to pool.query() may use two different connections and run in parallel.

So yes, the second one is also calling connection.release() you just don't need to type it.