nodejs mysql - How to implement pooledConnection in a realworld project?

Question

I am working on nodejs with mysql(8.x). I have intalled mysql library from npm.

I wrote some code like below.

File A - connection.js

const pooledConInfo = { 
    host: 'localhost',
    user: 'user',
    password: 'pw',
    database: 'db',
    insecureAuth : true,
    connectionLimit : 10, 
};
const pooledConnection = mysql.createPool(pooledConInfo);
module.exports = pooledConnection;

File B - MemberRouter.js

const con = require('../db/connection');

...

router.get('/api/member', (req, res, nxt) => {
    let rs = Object.assign({}, resForm);
    try {
        con.getConnection((err, connection) => { // #1. Do not want to repeat in every query situation
            if(err) throw err;
            connection.query('SELECT * FROM MEMBER LIMIT ?', 10, (err, result, fields) => {
                connection.release(); // #2. Do not want to repeat in every query situation
                if(err) throw err;
                rs.data = result;
                return res.json(rs);
            })
        });
    } catch (queryException) {
        rs.cause = queryException;
        return res.json(rs);
    }
});

It works, but I do not believe people use like this.

This is the main 2 questions I want to ask

• The most annoying part is I have to release each pool in every query callback. Is it right way
• Is there any good pattern to apply ? I want to wrap getConnection and connection.release part...

Thanks

Answer 1

You need not do connect.release() for a pooled connection everytime and its done automatically

According to the docs

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

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. This is the basic structure:

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

pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});