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

Question

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');

Leitao

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. – Lilylivered

Answer 1

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()

Lilylivered

This is excellent information. Thank you Marco. My concern is that my app code gets called thousands of times a minute. What is happening each time const pool is created? My concern is that maybe the connection pool should be created elsewhere and stay open. Or is this a misplaced concern? It looks to me like createPool is being called constantly whereas I would think a connection pool should be opened once and stay open. – Leitao

@Leitao The connection pool should be created only once. If you import your module in app.js, and there you have all the endpoints that are called, the pool will be created once and the connections will be reused. – Lilylivered

@Leitao if models each live in their own file, and each model needs pool, does the code const pool = require('./lib/dbpool'); re-run the connection code each time a model is instantiated? – Giovannagiovanni

@KearneyTaaffe I believe, based on Marco Altieri's comments, that the connection code is only run once if imported in app.js. Someone with a better understanding, please correct me if I'm wrong. – Leitao

I am using this patern in my nodejs application. I am handling 60 transaction per second. For each request i am executing a database write. But after few minutes of continuous TPS my docker container is crashing. Anyone faced this issue? – Beginning

Wouldn't that create a new pool connection every time I'll import pool in a controller and not on my app.js? – – Matheny

Answer 2

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.

Koetke