Every time my showData.js script is run it calls

var pool  = mysql.createPool({

Is there a way to create the connection pool on node.js server startup?

I've tried adding createPool to server.js but I can't seem to access the object from my showData.js script when I call

pool.getConnection(function(err,connection){

Is it even necessary to start the connection pool on node.js server startup?

Does the mysql connection pool persist even after calling connection.release and the script closes?

Edit @Marco, I'm getting ReferenceError: pool is not defined. I know the problem is that I'm not pulling pool into showData.js. According to node.js, it's OK to load a module multiple times.

From .html

Caching

Modules are cached after the first time they are loaded. This means (among other things) that every call to require('foo') will get exactly the same object returned, if it would resolve to the same file.

Multiple calls to require('foo') may not cause the module code to be executed multiple times. This is an important feature. With it, "partially done" objects can be returned, thus allowing transitive dependencies to be loaded even when they would cause cycles.

If you want to have a module execute code multiple times, then export a function, and call that function.

Here is my latest setup:

lib/dbpool.js

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'someco',
  user            : 'pinco',
  password        : 'pallino'
});

module.exports = pool;

server.js

const pool = require('./lib/dbpool');

showData.js

'use strict';
module.exports = router => {
    router.route('/show').post((req, res) => {
        pool.query('SELECT * FROM db.users', function(err, rows, fields) {

Do I need the following line in both server.js and showData.js?

const pool = require('./lib/dbpool');

Every time my showData.js script is run it calls

var pool  = mysql.createPool({

Is there a way to create the connection pool on node.js server startup?

I've tried adding createPool to server.js but I can't seem to access the object from my showData.js script when I call

pool.getConnection(function(err,connection){

Is it even necessary to start the connection pool on node.js server startup?

Does the mysql connection pool persist even after calling connection.release and the script closes?

Edit @Marco, I'm getting ReferenceError: pool is not defined. I know the problem is that I'm not pulling pool into showData.js. According to node.js, it's OK to load a module multiple times.

From https://nodejs.org/api/modules.html

Caching

Modules are cached after the first time they are loaded. This means (among other things) that every call to require('foo') will get exactly the same object returned, if it would resolve to the same file.

Multiple calls to require('foo') may not cause the module code to be executed multiple times. This is an important feature. With it, "partially done" objects can be returned, thus allowing transitive dependencies to be loaded even when they would cause cycles.

If you want to have a module execute code multiple times, then export a function, and call that function.

Here is my latest setup:

lib/dbpool.js

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'someco.com',
  user            : 'pinco',
  password        : 'pallino'
});

module.exports = pool;

server.js

const pool = require('./lib/dbpool');

showData.js

'use strict';
module.exports = router => {
    router.route('/show').post((req, res) => {
        pool.query('SELECT * FROM db.users', function(err, rows, fields) {

Do I need the following line in both server.js and showData.js?

const pool = require('./lib/dbpool');

• javascript
• mysql
• node.js
• connection-pooling

• If you do not need pool in server.js, you can import the module only in showData.js. If you need it in both, you can add the "require" in both files. – Marco Altieri Commented May 28, 2017 at 22:27

2 Answers 2

Define a module called lib/dbpool.js with the following content:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'someco.com',
  user            : 'pinco',
  password        : 'pallino'
});

module.exports = pool;

In your app code then use:

const pool = require('./lib/dbpool');

app.post('/your/app/url', (req, res) => {
  pool.query('your query', function(err, rows, fields) {
    if (err) throw err;
    /* Manage your results here */

  });
}

pool.query actually executes: pool.getConnection() and then connection.query() and then connection.release()

This solution uses a variable to hold the pool and return that after the first initializtaion,

const mysql = require("mysql2/promise");
var pool;
module.exports = function getPool() {
    if (!pool) {
      const config = {
        connectionLimit: 100,
        host: process.env.SQL_HOST,
        user: process.env.SQL_USER,
        password: process.env.SQL_PASSWORD,
        database: process.env.SQL_DATABASE,
        debug:false,
        waitForConnections: true,
        multipleStatements: true
      };
      pool = mysql.createPool(config);
    }
    return pool;        
};

const getPool = require('./db.js');

async function handleREquest(req, res) {
   const result = getPool().query('...');
   //...
}

Code based on this Github code: HERE.