Correct way to implement single DB pool throughout express app

Question

Note: This problem is mainly about singleton objects in node and not DB pools

I'm building an expressjs app using mysqljs node module for DB connections. I want to create a single pool object that can be reused throughout the app (for the different models etc). I want to know what is the correct way to use a single object instance throughout an express app

I've seen many examples where people create a db.js file or pool.js file like so and require it where they need it

// db.js
var mysql = require('mysql');
var pool;
module.exports = {
    getPool: function () {
      if (pool) return pool;
      pool = mysql.createPool(dbConfig);
      return pool;
    }
};

// app.js
var pool = require('pool').getPool();
pool.query('SELECT * FROM users');

However I feel this is still not the correct approach as you are relying on the nodejs cache to return the same instance every time you require the object. If the node ended up loading the code twice you would end up with two pools, e.g.

const poolA = require('./pool').getPool();
const poolB = require('./POOL').getPool(); // resolves to same file but node will not get result from cache

console.log(poolA === poolB); // false

From most things I've read, it is generally not recommended to use singleton objects in your node applications because of this behaviour, but this seems like a common use case for express apps. So I was wondering how are people creating their single pool for the lifetime of their application?

Note: What I've currently been doing is instantiating the pool in the app root and then passing the instance as a constructor function parameter but I also am not fond of this

I think I found a mistake in what you are intended, if your intention is to only have one pool, only once-created for your entire application. When I call your db.js or pool.js file in different files, even requiring them with the same case, the getPool function is creating a new pool in every require. I added logs to your getPool function in order to track this. And when I require this pool.js file in an app.js file, and in a command.js file (files in different "dirs"), and after both requires I call the getPool func immediately, the code logs that the pool is getting created again. — Angel Chavez Reyes, Aug 16 '23 at 16:33

score 2 · Accepted Answer · answered May 04 '19 at 12:12

Short answer

I don't see any problem in your code.

Long Answer

This solution is assuming you don't actually have a file named POOL.js with the same exact code.

The below require is resolving to the same file as pool.js because you're using a filesystem with case-insensitivity enabled. For example NTFS or APFS.

const poolB = require('./POOL').getPool();

If you use case sensitive file system like ext4 then you would get error.

Error: Cannot find module './POOL'

Internally node caches files based on the file path name you provide. From source:

const relativeResolveCache = Object.create(null);

relResolveCacheIdentifier = `${parent.path}\x00${request}`;
const filename = relativeResolveCache[relResolveCacheIdentifier];

if (filename !== undefined)
...

Since JavaScript is case-sensitive, an object (the cache, from above) with property (simplyfying) pool and POOL is going to be different and refer to entirely 2 different object reference.

Conclusion

Watch out for gotchas with file names or identifier names.

Hey, sorry for late getting back. Thanks for your answer. Just to clarify I woudn't really ever be requiring the file as I described above, I was just using it as an example for the file being loaded two different times and not from the cache. My main worry is that in my original code I could never be 100% sure that it will run only once. The only way I could see that I could ensure this was to use global variables which I know not to. If this though is standard practice when using node I'm fine with it. — Nick, May 22 '19 at 15:31
`My main worry is that in my original code I could never be 100% sure that it will run only once.` As I mentioned in the answer, it'll always return from cache if you're carefull not to *mess* up *case* as js is case-sensitive. — 1565986223, May 22 '19 at 15:37

Correct way to implement single DB pool throughout express app

1 Answers1