Over the weekend I was experimenting with a setup of MySQL connections in the Node/Express web app that I’m working on. It led me to create this super simple middleware module express-myconnection that provides a consistent API for MySQL connections during the request/response life cycle. It supports three different strategies of managing db connections: single for a singleton connection on an app instance level, pool based connections, and a new connection per each request. It’s also capable of auto releasing/closing connections if configured with either the pool or request strategy.

The conclusion of my experiments is that for now I will stick with a single. I believe this should work just fine when you think about the single-threaded nature of node. If I experience any issues switching to pool based or connections per request will be matter of setting single flag in my app configuration.

Below you can find some more info about the different strategies and how you can use them in your own projects.

Strategies

• single – creates a single database connection for the whole application instance. The connection is never closed. In case of a disconnection, it will try to reconnect again as described in the node-mysql docs.
• pool – creates pool of connections on an app instance level, and serves a single connection from the pool per request. The connection is auto released to the pool at the response end.
• request – creates new connection per each request, and automatically closes it at the response end.

Usage

Configuration is straightforward and you use it as any other middleware. The first param it accepts is a node-mysql module, second is a db options hash passed to the node-mysql module when the connection or pool is created. The third is a string defining the strategy type.

express-myconnection extends request object with the getConection(callback) function this way the connection instance can be accessed anywhere in routers during the request/response life cycle: