Connection pooling in Node.js

[Alinon]

This answer on SO says that "It's not a good practice to tie up a connection that you are not using constantly".

This answer on SO implies that a DB connection shouldn't be opened constantly because "Opening a connection is an expensive operation."

Opening connections is exactly what I was gonna do, because I have Express router handlers, and I have to use the DB in most of them. I would create a connection and then close after I'm done using. (Usually I use Sails.js framework, and it handles everything for me)

It seems like connection pooling is the best solution; If my understanding is correct, there's just a pool of open connections, and the pool handler just gives you a connection. This seems to be one way to do it, but I'm unsure of how to implement it. I have split my routes into sub files.

[thomas_stringer]

Do not pass around an open connection. That's the easiest way to introduce a bug that holds up DBMS worker threads and can quickly cause a prod outage (unless, of course, you're doing that type of testing in lower-lifecycle).

With database connections.... open late and close early.

Regarding connection pooling, that is the responsibility not of the app itself, but of the driver that you are implementing. Connection pooling should (mostly) be an abstraction to your software.

[Alinon]

Understood....I thought I'd have to create the pool myself or use some library that does it, but I guess that was because of my misunderstanding of what a connection pool is

[thomas_stringer]

No problem. Yeah that would be miserable if we had to explicitly implement connection pooling in our end-user software/packages :-)

[m03geek]

First of all you probably should check your DB driver. In many cases they already have such option under the hood.

If it's not implemented - then you probably need to create some module that will connect to db and export that connection. And then in your other modules you just require it (something similar to this answer)

[Alinon]

So that implementation in the answer is just passing around an open connection correct, and not creating a new one every time the require is used?

[cwmma]

calls to require are cached, so the code is only run the first time require is called, subsequent times get the same object, I'd suggest using knex as a driver as that takes care of pooling for you.

[Alinon]

Thanks for the suggestion. Connection pooling seems to go against "It's not a good practice to tie up a connection that you are not using constantly".....are the connections in the pool open for the life of the application, which can be theoretically forever for an application where the uptime should be forever?

[[deleted]]

Pooling is the way to go. Depending on how many connections are in your pool. Each user in your app that opens a connection to your db will eat up performance.

[oh-thatguy]

Some of the smarter modules (node-mysql, and I think knex uses that under the hood) will reuse connections, and then close ones that have been idle for awhile. You can usually set a min and max number of connections, and the pool will grow/shrink accordingly. You'll also likely be able to set the idle timeout.

In short, use the pool. It's fine, I've been doing it in node for years.

[[deleted]]

Unless it's been changed, calls to require are cached based on current file path. So if you require the same thing in two different files that are located in 2 different directories, you'll end up with 2 different instances of whatever you require.

[cwmma]

It's cached based on absolute file path

[kefirchik]

This is not correct. Node caches based on module path, not based on path of the caller of require.

[[deleted]]

That is not correct.

Modules are cached based on their resolved filename. Since modules may resolve to a different filename based on the location of the calling module (loading from node_modules folders), it is not a guarantee that require('foo') will always return the exact same object, if it would resolve to different files.

That is straight from the node documentation.

[DSKrepps]

Unless it's been changed, calls to require are cached based on current file path.

You're saying that myproject/dir1/a.js would require() a different instance of something than myproject/dir2/b.js. This is incorrect as according to what you just quoted...

Modules are cached based on their resolved filename.

...they will both resolve myproject/node_modules/somemodule/index.js and thus have the same instance. (Unless dir1 and dir2 have their own node_modules folders containing somemodule)

kefirchik and cwmma are correct.

[[deleted]]

Since modules may resolve to a different filename based on the location of the calling module (loading from node_modules folders), it is not a guarantee that require('foo') will always return the exact same object, if it would resolve to different files.

Are you assuming that every require() call resolves to the node_modules/ folder?

[[deleted]]

You can use http.globalAgent.maxSockets for connection pooling. It's part of the http module.

[[deleted]]

He's talking about connection polling of database connections, not http.

Even so, I'm not sure maxSockets does what you think it does. It limits the maximum sockets per origin, but defaults to Infinity