MySQL Pooling Connections

Nodejs Course

Rather than creating and managing connections one-by-one, "mysql" module also provides built-in connection pooling using mysql.createPool(config) method. Pools accept all the same options as a connection. When creating a new connection, the options are simply passed to the connection constructor.
Example of using pool directly:

const mysql = require('mysql');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

let sql ='SELECT 1 + 1 AS solution';

pool.query(sql, (err, res, fields)=>{
  if(err) throw err;
  console.log('The solution is: ', res[0].solution);
});

- Call the pool.getConnection() method to use a connection from pool.
When you are done with a connection, just call con.release();, and the connection will return to the pool, ready to be used again by someone else.

const mysql = require('mysql');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'nodedb',
  charset: 'utf8'
});

//use pool connection
var sql ='SELECT 1 + 1 AS solution';

pool.query(sql, (err, res, fields)=>{
  if(err) throw err;
  console.log('The solution is: ', res[0].solution);
});

//another connection
pool.getConnection((err, con)=>{
  sql ='SELECT id, name FROM friends LIMIT 1';

  // Use the connection
  con.query(sql, (err, res, fields)=>{
    console.log(res[0]);
    con.release(); //done with the connection, free memory

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

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

Results: - If you would like to close the connection and remove it from the pool, use con.destroy(); instead. The pool will create a new connection the next time getConnection() is called.

- For more details and examples, see documentation: Node.js MySQL Pooling connections.