NodeJS and Promises with MySQL Connector - beware of the unreleased connection

In my NodeJS application I am using the nice MySQL library node-MySQL. I have heavily used Q promises for managing the asynchronous nature of code vs. using callbacks. By using a connection pool, I can reuse connections without having to worry about re-negotiating with the database with every query. My general design is to get a connection from the pool, do whatever I need to do with it and release it. I do not share a connection to another class/service except in the case of "in-transaction" pre/post operations. For example, on update() I have a "postUpdateAdditions()" function that takes the connection as an argument. The rule is it can not release/manage the connection as it is within a transaction.

I was recently getting some strange behavior running my integration tests whereby I was getting out-of-connection pool errors. These appear as timeouts in the system/deadlocks. After spending hours hunting down the problem I discovered two places where I was not properly releasing the connection (and thus I was connection leaking). In general this is not easy to detect (and I even added some logic to my connection manager class to track new and released connections (and to try and flag if they get out of wack)

I came across a strange situation with promises that I didn't think would happen, but it must be a timing related issue.

I had some code like the following:

var defer = q.defer();
connection.query(query_string, function (err, rows) {
    if( err ) {
       defer.reject(err); // rejection will rollback and release in caller
    }
    // do another operation on connection and return to caller
    defer.resolve(somedata);
});

return defer.promise;

Recently I had to reinstall my operating system and chose Windows 10. I had an issue with my NodeJS 3.x environment and switched to NodeJS 5.x. In doing so, I had issues with the node-gyp again.

It is not clear the order in which I installed everything or if the order matters, but I had to install both the "Visual Studio 2015 Community Edition" as well as the "Visual Studio C++ Build Tools". This was able to solve the various compilation issues I was having.

Installing libraries with node-gyp and NodeJS 4.1+ on Windows 8+

I have noticed that some node modules come down and compile on Windows. These may be because they detect a compiler environment or because as part of their install they need to be compiled with native libraries. Either way, inevitably you may run into node-gyp trying to compile. Assuming you have the correct tool chain (see the [node-gyp github site](https://github.com/nodejs/node-gyp)) you may still get an error during compile that looks like this:

C:\Program Files (x86)\MSBuild\Microsoft.Cpp\v4.0\V120\Microsoft.Cpp.Platform.targets(64,5): error MSB8020: The build tools for Visual Studio 2012 (Platform To
olset = 'v110') cannot be found. To build using the v110 build tools, please install Visual Studio 2012 build tools. Alternatively, you may upgrade to the cur
rent Visual Studio tools by selecting the Project menu or right-click the solution, and then selecting "Upgrade Solution...". [D:\aurelia-binding-issue-178\nod
e_modules\browser-sync\node_modules\socket.io\node_modules\engine.io\node_modules\ws\node_modules\utf-8-validate\build\validation.vcxproj]
gyp ERR! build error
gyp ERR! stack Error: `C:\Program Files (x86)\MSBuild\12.0\bin\msbuild.exe` failed with exit code: 1
gyp ERR! stack at ChildProcess.onExit (D:\Java\nodejs\node_modules\npm\node_modules\node-gyp\lib\build.js:270:23)
gyp ERR! stack at emitTwo (events.js:87:13)
gyp ERR! stack at ChildProcess.emit (events.js:172:7)
gyp ERR! stack at Process.ChildProcess._handle.onexit (internal/child_process.js:200:12)
gyp ERR! System Windows_NT 6.3.9600
gyp ERR! command "D:\\java\\nodejs\\node.exe" "D:\\Java\\nodejs\\node_modules\\npm\\node_modules\\node-gyp\\bin\\node-gyp.js" "rebuild"
gyp ERR! cwd D:\aurelia-binding-issue-178\node_modules\browser-sync\node_modules\socket.io\node_modules\engine.io\node_modules\ws\node_modules\utf-8-validate
gyp ERR! node -v v4.1.1
gyp ERR! node-gyp -v v3.0.3
gyp ERR! not ok

This is indicating simply that it can not find the 2012 build tools. However installing these tools will not help. Why? Because Node 4.1.1 is compiled to 2013 spec. Therefore you need to pass the following flag to npm install such that it knows to use the 2013 version.... --msvs_version=2013

This will now result in you having to always run install with the following syntax npm install --msvs_version=2013

Update