How to make node.js mysql connection pooling available on startup

user_3179824 0
Viewed 8942

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');
node.js connection-pooling mysql javascript
2 Answers

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.

user_57116550
Related