tediousjs/tedious-connection-pool

A connection pool implementation for tedious

tedious-connection-pool

A connection pool for tedious.

Installation

npm install tedious-connection-pool

Description

The only difference from the regular tedious API is how the connection is obtained and released. Rather than creating a connection and then closing it when finished, acquire a connection from the pool and release it when finished. Releasing resets the connection and makes in available for another use.

Once the Tedious Connection object has been acquired, the tedious API can be used with the connection as normal.

Example

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var poolConfig = {
    min: 2,
    max: 4,
    log: true
};

var connectionConfig = {
    userName: 'login',
    password: 'password',
    server: 'localhost'
};

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
    console.error(err);
});

//acquire a connection
pool.acquire(function (err, connection) {
    if (err) {
        console.error(err);
        return;
    }

    //use the connection as normal
    var request = new Request('select 42', function(err, rowCount) {
        if (err) {
            console.error(err);
            return;
        }

        console.log('rowCount: ' + rowCount);

        //release the connection back to the pool when finished
        connection.release();
    });

    request.on('row', function(columns) {
        console.log('value: ' + columns[0].value);
    });

    connection.execSql(request);
});

When you are finished with the pool, you can drain it (close all connections).

pool.drain();

Class: ConnectionPool

new ConnectionPool(poolConfig, connectionConfig)

• poolConfig {Object} the pool configuration object

  • min {Number} The minimum of connections there can be in the pool. Default = 10
  • max {Number} The maximum number of connections there can be in the pool. Default = 50
  • idleTimeout {Number} The number of milliseconds before closing an unused connection. Default = 300000
  • retryDelay {Number} The number of milliseconds to wait after a connection fails, before trying again. Default = 5000
  • acquireTimeout {Number} The number of milliseconds to wait for a connection, before returning an error. Default = 60000
  • log {Boolean|Function} Set to true to have debug log written to the console or pass a function to receive the log messages. Default = undefined

• connectionConfig {Object} The same configuration that would be used to create a tedious Connection.

connectionPool.acquire(callback)

Acquire a Tedious Connection object from the pool.

• callback(err, connection) {Function} Callback function
• err {Object} An Error object is an error occurred trying to acquire a connection, otherwise null.
• connection {Object} A Connection

connectionPool.drain(callback)

Close all pooled connections and stop making new ones. The pool should be discarded after it has been drained.

• callback() {Function} Callback function

connectionPool.error {event}

The 'error' event is emitted when a connection fails to connect to the SQL Server. The pool will simply retry indefinitely. The application may want to handle errors in a more nuanced way.

Class: Connection

The following method is added to the Tedious Connection object.

Connection.release()

Release the connect back to the pool to be used again

tedious-connection-pool's Issues

drain callback not always invoked

On my laptop I have no MSSQL installed. I use min 2 and max 4. Upon the timeout the 'error' event handler is invoked. In there I'm calling pool.drain(function() { ... }). I can reproduce that the callback does not get called. Trying to determine the reason I figured connections.length is 2 (as expected), and waiting.length is 1.
In your ConnectionPool.prototype.drain implementation however, while iterating the waiting list, you do not increment the eventCount, which is why in "ended", the callback is never invoked.

pool version: 0.3.6

Juergen

Keep getting acquire Timeout error

I keep getting acquire Timeout, even after closing all the sessions and calling connection.release(); connection.close();. I would like to know what I can do to avoid this situation?

Does acquire timeout error indicate that the sessions are not getting closed?

What happens on connection timeout?

I have an application which I spend a lot of my time debugging the client side. When I do eventually get around to making a database request I quite frequently, but not always get the following error...

Requests can only be made in the LoggedIn state, not the Final state" (edited because I had added some to the error message)

Which I am assuming means I am trying to make a request on a timed out connection or something.

I can post more code if necessary, but for the moment I am not doing anything particularly strange, I just set up a pool as follows.

const ConnectionPool = require('tedious-connection-pool');

  class DB  {
    constructor(ConnectionPool,logger) {
      this.logger = logger;
      this.poolDrained = false;
      this.pool = new ConnectionPool(poolConfig, {
        server: DBHOST,
        userName: DBUSER,
        password: DBPASS,
        options: {
          port: DBPORT,
          database: DBNAME,
          useColumnNames: true,
          connectTimeout: DBCONTO,
          requestTimeout: DBREQTO
        }
      });
      this.pool.on('error', (err) => {
        this.logger('database',err.message);
        this.pool.drain();
        this.poolDrained = true;
        process.kill(process.pid,'SIGINT'); //tells us to shutdown gracefully
      });
    }
    close() {
      if (!this.poolDrained) {
        this.pool.drain();
      }
    }
}

With a pool config of

  var poolConfig = {
    min: (process.env.PAS_POOL_MIN)? process.env.PAS_POOL_MIN : 2,
    max: (process.env.PAS_POOL_MAX)? process.env.PAS_POOL_MAX : 4,
    log: false
  };

The DB Class above wraps an exec method which acquires and releases the connection in one run through returning a promise which resolves when complete. So I am pretty sure I am not holding on to connections that I shouldn't

Connection Issues

Hi there,

Has anyone got the following erro when trying to connect to a remote sql server database.

This si a very urgent problem and any help to help me resolve this will be highly appreciated:

Thanks in advance.

Code:

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var tediousConfig = {
"userName": "test",
"password": "test",
"server": "1.1.1.10",
"options": {
"database": "test"
}
};

var pool = new ConnectionPool({max: 10, idelTimeOutMills:30000, log:true}, tediousConfig);

pool.requestConnection(function (err, connection) {
console.log('connect', err);
if(!err) {
var request = new Request('select 42', function(err, rowCount) {
console.log(rowCount, 1);

                // Release the connection back to the pool.                                                                                                
                connection.close();
                console.log('closed');
        });

        request.on('row', function(columns) {
                console.log(columns[0].value);                    
            });

        connection.on('connect', function(err) {
                connection.execSql(request);
            });
    }

});

Allow prepared statements to persist across returning a connection to the pool

Expected Behavior

When returning a connection to the pool, the connection is reset. This invalidates any prepared statements. It is desired to prepare statements and continue using them if the same connection is re-acquired from the pool. In the case of certain errors (in tedious or in code using the pool), it is also desired to allow a connection to be reset.

To accomplish this, an additional parameter can be passed to Connection.release(suppressReset). When suppressReset is not passed (old behavior) or passed with a falsy value, reset the connection as was previously done. When suppressReset is passed with a truthy value (new behavior), return the connection to the pool without resetting it.

Current Behavior

When a truthy argument is passed to Connection.release, return the connection to the pool WITHOUT resetting it.

Steps to Reproduce (for bugs)

N/A

Reason For Request (for feature requests)

I've seen a notable performance improvement for certain queries when running them as prepared statements, but resetting the connection invalidates prepared statements.

Possible Solution

see SensusDa/tedious-connection-pool@35b4417ae9b6517c5bbdfbc6b2a9a21ef6a49f85

Background Details

Running code which used prepared statements and a custom version of tedious-connection-pool with changes as shown in the commit above resulted in a notable performance improvement.

Environment

N/A

connections not releasing properly

I took over a project using this module to manage MSSQL connections. Many of the API endpoints have multiple queries. One query gets a list of IDs. Each ID is then used in an additional query. The code uses a simple for loop to manage the queries.

I have found that the maximum number of queries that can be run inside the loop is equal to the MIN connections configuration setting. So if MIN is 10, the first ten queries run fine inside of the loop. All the other queries return the same results as the 10th query. If I adjust the MIN value the number of successful queries changes accordingly. The MAX value appears to have no effect. Setting the connection pool MIN to 0 I get one good query and the rest fail in this context.

Connection.release() gets called but it appears that connections are released after additional query attempts are made and it looks like no connections are available.

Here is a chunk of the code:

// GET All IDs to lookup stuff
router.get('/:Id/list_of_stuff',
    passport.authenticate('basic', {session: false}),
    function(req, res){
        var data = [];
        DB.pool.acquire(function(err, connection){
            if (err)
                console.log(err);
            // Get All Vendors
            var getAllIds = new Request('' +
                'USE db_name ' +
                'SELECT StuffID, Name ' +
                'FROM dbo.StuffStuffNames ' +
                'WHERE ID = @stuffId ',
                function(err, stuffRowCount, stuff){
                    // Failure Conditions
                    if (err){
                        console.error(err);
                        return;
                    }
                    connection.release();
                    var rowCounter = 1;
                    for (var i = 0; i < stuffRowCount - 1; i++){
                        console.log('stuffID -- ', stuff[i][0].value);
                        // Every Iteration Gets It's Own Connection
                        // Grab all Employee's Tied to a stuff record
                        DB.pool.acquire(function(err, connection){
                            if (err) {
                                console.log(err);
                            }
                            var currentIteration = i;
                            var getStuffCount = new Request('' +
                                'USE db_name ' +
                                'SELECT EmployeeID, Field1, Field2, Field3 ' +
                                'FROM dbo.StuffEmployeeAssignment ' +
                                'WHERE StuffID = @stuffId',
                                function(err, rowCount, rows){
                                    if (err){
                                        console.error('ERROR -- ', err);
                                        return;
                                    }

                                    // .release() appears to do nothing or to do it to late to be effective
                                    connection.release();

                                    // create some variables to store some data

                                    // loop over the query results and increment some counters

                                    // Return on Last Iteration
                                    // This counter and all the rest with the -1 are strange
                                    // and I think may be incorrect. seem to be off by one
                                    if (rowCounter === (stuffRowCount - 1)){
                                        res.json([{success: true}, data]);
                                    }else{
                                        rowCounter++;
                                    }
                                }
                            );
                            // stuff comes from first query and is used in the query inside of this request
                            getStuffCount.addParameter('stuffId', TYPES.UniqueIdentifier, stuff[i][0].value);
                            connection.execSql(getStuffCount);
                        });
                    }
                }
            );
            getAllIDs.addParameter('stuffId', TYPES.UniqueIdentifier, req.params.Id);
            connection.execSql(getAllIDs);
        });
    });

The product we are building is for a major contract and the previous developer missed multiple deadlines. I am a bit desperate. Anything you can offer would be amazing.

drain() does not release all (network) resources

Like in the previous issue, I want to test first the behavior with no MSSQL installed. Obviously it is not able to establish a connection, an error gets emitted, and my error handler invokes drain(). My little test does nothing else. Interestingly node.js does not exit for another 5-10 secs after the drain callback has been called. And that is because (network) resources are not yet released. process._activeHandler provide the information.

Juergen

tag a first version

Hi Mike,
could you make a tag to allow npm install from tarball.

Regards

Rogelio CANEDO

Error: Acquire Timeout Exceeded for queued queries.

Hi, I'm trying to submit multiple queries (where query execution time (~2-3 mins) is more than acquire time out(1 min)) more than max connections in pool and I get Acquire timeout exceeded error.

Expected Behavior

Queries which couldn't get connection should be queued and connection acquire time should start when there is a free connection available.

Steps to Reproduce (for bugs)

Here is sample code:

function initPool() {
    let poolConfig = {
        min: 1,
        max: 3,
        log: true
    };

    let connectionConfig = {
        "userName": "username",
        "password": "pword",
        "server": "server",
        "options": {
            "database": "database",
            "requestTimeout": "300000",
        }
    };

    // create the pool
    pool = new ConnectionPool(poolConfig, connectionConfig);

    pool.on("error", function (err) {
        console.error(err);
    });
}
function execute2(query: string) {
    pool.acquire(function (err, connection) {
        if (err) {
            console.error(err);
            return;
        }
        let request = new Request(query, function (err, rowCount) {
            if (err) {
                console.error(err);
                return;
            }
            console.log("rowCount: " + rowCount);
            // release the connection back to the pool when finished
            connection.release();
        });
        request.on("row", function (columns) {
        });
        connection.execSql(request);
    });

}
initPool();

for (let i = 0; i < 6; i++) {
    execute2("select top 100000 * from USERS");
}

Console Log:
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 1
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 2
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 3
Tedious-Connection-Pool: connection connected: 1
Tedious-Connection-Pool: acquired connection 1
Tedious-Connection-Pool: connection connected: 2
Tedious-Connection-Pool: acquired connection 2
Tedious-Connection-Pool: connection connected: 3
Tedious-Connection-Pool: acquired connection 3
[Error: Acquire Timeout Exceeded]
[Error: Acquire Timeout Exceeded]
[Error: Acquire Timeout Exceeded]
rowCount: 100000
Tedious-Connection-Pool: connection reset: 2
rowCount: 100000
Tedious-Connection-Pool: connection reset: 1
rowCount: 100000
Tedious-Connection-Pool: connection reset: 3

Environment

• Node.js Version: v4.4.5
• Windows/Mac/Linux: Windows

Incompatibility with Rollup

When the ConnectionPool class is used with Rollup, the following error message is displayed:

TypeError: ConnectionPool$1 is not a constructor

I had to use to following workaround:

let constr = (ConnectionPool.default && ConnectionPool.__moduleExports) ? ConnectionPool.default : ConnectionPool;
let connectionPool = new constr(poolConfig, connectionConfig);

Support tedious 2.0.1?

This is a feature request. It would be nice if this project supported a modern version of tedious.

TypeError: cb is not a function

I saw tediousjs/tedious#917 this issue. But why didn't you fix in the master branch?

Version:
"tedious": "^6.4.0",
"tedious-connection-pool": "^1.0.5",

Code:

function callProcedure(pool, name, params, ip='') {
return new Promise((resolve, reject) => {
pool.acquire(function (err, connection) {
var data = [];

        var request = new tedious.Request(name,  function (err, count, rows) {
            if (err) {
                //don't need to close the connection, put it back to pool
                connection.release();
                logger.error(`${ip}:${name}: ${err.stack}`)
                reject(`Error occured when executing request: ${err.stack}`)
                
            }
            else {
                //handle completion for current connection and request object
                //don't need to close the connection, put it back to pool
                connection.release();
            }
        });

        if (params != null && params.length > 0) {
            //add parameter to request, parameter should be initiated at each class
            for (var i = 0, len = params.length; i < len; i++) {
                request.addParameter(params[i].name, params[i].type, params[i].value)
            }
        }

        request.on('row', function (columns) {
            var obj = {};
            for (var i = 0, len = columns.length; i < len; i++) {
                obj[columns[i].metadata.colName] = columns[i].value;
            }
            data.push(obj);
        });
        
        request.on('doneProc', function (rowCount, more, returnStatus) {
            logger.info(`${ip}:${name}:Stored procedure ${name} executed, rows ${rowCount}, more ${more}, status ${returnStatus}`);
            resolve(JSON.stringify({data}))
        });

        if (connection != null) {
            connection.callProcedure(request);
        }
        else {
            logger.error(`${ip}:${name}: Custom error => Connection is null!`)
            reject('Custom error => Connection is null!')
        }
    });
});

}

Ianchocks said:
Hi @notestyle, are you having the same 'callback' issue as #917? If so are you also using tedious-connection-pool?

Edit: We actually don't have merge permissions for the tedious-connection-pool since it is a different repository than tedious itself.

Issue with connection closed

I got this if somehow the connection is closed

Tedious-Connection-Pool: connection reset: 1
Tedious-Connection-Pool: acquired connection 1
{ RequestError: SHUTDOWN is in progress.
at RequestError (F:\TestConnection\node_modules\tedious\lib\errors.js:34:12)
at Parser. (F:\TestConnection\node_modules\tedious\lib\connection.js:207:36)
at emitOne (events.js:96:13)
at Parser.emit (events.js:188:7)
at Parser. (F:\TestConnection\node_modules\tedious\lib\token\token-stream-parser.js:42:15)
at emitOne (events.js:96:13)
at Parser.emit (events.js:188:7)
at readableAddChunk (F:\TestConnection\node_modules\readable-stream\lib_stream_readable.js:210:18)
at Parser.Readable.push (F:\TestConnection\node_modules\readable-stream\lib_stream_readable.js:169:10)
at Parser.Transform.push (F:\TestConnection\node_modules\readable-stream\lib_stream_transform.js:123:32)
at doneParsing (F:\TestConnection\node_modules\tedious\lib\token\stream-parser.js:87:17)
message: 'SHUTDOWN is in progress.',
code: 'EREQUEST',
number: 6005,
state: 2,
class: 14,
serverName: 'MGL-ACS-KABA',
procName: '',
lineNumber: 1 }
Tedious-Connection-Pool: connection closing because of error
{ ConnectionError: Connection lost - read ECONNRESET
at ConnectionError (F:\TestConnection\node_modules\tedious\lib\errors.js:12:12)
at Connection.socketError (F:\TestConnection\node_modules\tedious\lib\connection.js:535:28)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at emitErrorNT (net.js:1272:8)
at _combinedTickCallback (internal/process/next_tick.js:74:11)
at process._tickCallback (internal/process/next_tick.js:98:9)
message: 'Connection lost - read ECONNRESET',
code: 'ESOCKET' }
F:\TestConnection\node_modules\tedious-connection-pool\lib\connection-pool.js:243
pooled.con.close();
^

TypeError: Cannot read property 'close' of undefined
at F:\TestConnection\node_modules\tedious-connection-pool\lib\connection-pool.js:243:31
at Request.userCallback (F:\TestConnection\node_modules\tedious\lib\connection.js:938:16)
at Request.callback (F:\TestConnection\node_modules\tedious\lib\request.js:33:27)
at Connection.socketError (F:\TestConnection\node_modules\tedious\lib\connection.js:1168:20)
at Connection.dispatchEvent (F:\TestConnection\node_modules\tedious\lib\connection.js:519:45)
at Connection.socketError (F:\TestConnection\node_modules\tedious\lib\connection.js:537:19)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at emitErrorNT (net.js:1272:8)
at _combinedTickCallback (internal/process/next_tick.js:74:11)

Update memory-usage test to add validation.

Expected Behavior

Test change only.

Current Behavior

Current test has a few issues:

1. Converting from Bytes to KB, it uses 1000 in one place and 100 in the other, vs 1024.
2. It's running global.gc() at the end. Looking at the test I believe the intent is to run it after each poolSize number of connection attempts.
3. There is no validation at the end.

Steps to Reproduce (for bugs)

Reason For Request (for feature requests)

I was reading the code to come up to speed on the project and noticed a few things in test\memory-usage.js that I thought I could improve. I'm opening this issue to attach to the pull request I'll be sending.

Possible Solution

Background Details

Test enhancement.

Environment

• Node.js Version: v6.2.1
• Windows/Mac/Linux: Windows

I can't listen the 'done' event

that is wrong in this code ...

var ConnectionPool = require('tedious-connection-pool'),
Request = require('tedious').Request;

var poolConfig = {
min: 10,
max: 50,
log: false
};

var connectionConfig = {
server: 'some',
userName: 'sa',
password: 'some.',
options: {
database: 'myTest',
instanceName: 'SQL2012',
rowCollectionOnDone:true
//rowCollectionOnRequestCompletion:true
}
// port:3308
/*
,options: {
debug: {
packet: true,
data: true,
payload: true,
token: false,
log: true
},
database: 'DBName',
encrypt: true // for Azure users
}
*/
};

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

//acquire a connection
pool.acquire(function (err, connection) {
if (err)
console.error(err);

//use the connection as normal
var request = new Request('SELECT * FROM mytable', function(err, rowCount) {
    if (err)
        console.error(err);

    console.log('rowCount: ' + rowCount);

    //release the connection back to the pool when finished
    connection.release();
});


var arr=[];

// request.on('row', function(columns) {
// console.log(columns);

 //   arr.push( columns.map(function  (item) {
      //  return {name:item.metadata.colName , value:item.value};      
   // })); 



   // console.log(arr);
   //console.log('value: ' + columns[0].value);
//});



request.on('done', function (rowCount, more, rows) { 

    console.log('I am done'); // this don't execute

     var arry = rows.map(function  (item) {
            return {name:item.metadata.colName , value:item.value} 
    });

     var result=[];

     for (var i=0,j=rows.length;i<j;i++){

        result.push( rows[i].map(function  (item) {
            return {
                name:item.metadata.colName,
                value:item.value
            };      
        })); 

     }

     console.log(result);

});



connection.execSql(request);

});

pool.on('error', function(err) {
console.error(err);
});

1.0.5 release may be incompatible with tedious 5.x release

Current Behavior

While I'm aware maintenance of the connection pool library has fallen behind the activity in the main tedious library, for our purposes, all tedious releases through the 4.2.0 have been compatible with the 1.0.5 release of the connection pool. When I attempted to upgrade to the tedious 5.x release, 5.0.1 to be specific, the connection pool library is no longer able to acquire connections. My debugging shows the root cause to be in the recent changes in how tedious handles authentication, specifically in forming the configuration values. Through the tedious 4.2.0 release the connection pool is at least tolerable of these changes, however since the internal updates to the 5.x tedious branch, tedious itself is no longer tolerable of these inconsistencies leaving the connection pool functionality fatally broken.

Reason For Request (for feature requests)

Background Details

At the moment, this is not a showstopper for us as we can live for now with tedious 4.2.0 so I'm only filing this for others benefit and with hopes someone else has the capacity to take on the much needed upgrades required to bring the connection pool library into full tedious 5.x support.

Possible Solution

Upgrade the connection pool package to reflect the new authentication options as well as other changes available in latest versions of tedious.

Environment

• Node.js: v10.15.0
• Windows 10 Pro and Server 2016
• tedious 5.0.1

new maintainer ?

@arthurschreiber @ben-page @pekim

As discussed in this repo's issues and in tedious, this repo isn't being maintained though the functionality is missing from tedious itself.

My company still uses tedious-connection-pool in several projects and we don't wish to rewrite our code to sequelize or mssql. I would be happy to take over as maintainer for this project and help keep it up to date for its users.

Tests not up to date.

The test suite has not been updated to reflect the state of version 0.2.x and doesn't work at all.

Acquire calls do not timeout if pool is drained.

The ConnectionPool.drain function clears waiting acquire calls. These acquire calls will hang forever. Instead, the waiting requests should timeout immediately (which doesn't seem possible with Timers) or don't clear them and let them timeout.

An easy way to see this is:

`var poolConfig = {
acquireTimeout: 5000 //sorten the timeout to make the example easier to understand
};

var connectionConfig = {
userName: 'test',
password: 'test',
server: 'bad-server-name',
options: {
appName: 'pool-test',
database: 'test'
}
};

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
console.error('Error Event: ' + err.message);
pool.drain();
});

//acquire a connection
pool.acquire(function (err, connection) {
if (err)
// you'll never see this
console.error('Acquire Error: ' + err.message);
});

console.log('done!');`

opening too many connections kill tedious driver

When I try to create about 1000 - 2000 connections with the pool, sometimes (not always..) I got the following error log:
connection closing because of error
error: { name: 'ConnectionError',
message: 'Failed to connect to MACHINENAME:undefined in 15000ms',
code: 'ETIMEOUT' }

error thrown in tedious-connection-pool when calling release()

The following error occurs in connection-pool.js line: 245 resulting in the closing of the connection.

The connection should be reused.

Here is my code:

runStoredProc = function (storedprocedure, parameters) {
    return new Promise((resolve, reject) => {
        const results = [];
        pool.acquire((err, mscon) => {
            if (err) {
                logger.error(err);
            } else {
                const request = new Request(storedprocedure, function (err) {
                    if (err) {
                        logger.error("Request Error" + err);
                        reject(err);
                    }
                });
                _.each(parameters, function (value, key) {
                    request.addParameter(key, value[1], value[0]);
                });
                request.on("doneInProc", function (rowCount, more, rows) {
                    results.push(rows);
                });
                request.on("doneProc", function (rowCount, more, returnStatus, rows) {
                    resolve({
                        returnStatus: returnStatus,
                        rows: results
                    });
                    // process.nextTick(mscon.release.bind(mscon));
                    mscon.release();
                });
                mscon.callProcedure(request);
            }
        });
    });
}

Below is the stack trace.

"{"message":"Requests can only be made in the LoggedIn state, not the SentClientRequest state","stack":"RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state
at RequestError (/home/user/dev/services/sqlReader/node_modules/tedious/lib/errors.js:34:12)
at Connection.makeRequest (/home/user/dev/services/sqlReader/node_modules/tedious/lib/connection.js:905:33)
at Connection.execSqlBatch (/home/user/dev/services/sqlReader/node_modules/tedious/lib/connection.js:710:19)
at Connection.reset (/home/user/dev/services/sqlReader/node_modules/tedious/lib/connection.js:941:19)
at ConnectionPool.release (/home/user/dev/services/sqlReader/node_modules/tedious-connection-pool/lib/connection-pool.js:241:24)
at Connection.release (/home/user/dev/services/sqlReader/node_modules/tedious-connection-pool/lib/connection-pool.js:7:15)
at Request. (/home/user/dev/services/sqlReader/src/MSSql.js:85:32)
at emitMany (events.js:127:13)
at Request.emit (events.js:201:7)
at Parser. (/home/user/dev/services/sqlReader/node_modules/tedious/lib/connection.js:345:26)
at emitOne (events.js:96:13)
at Parser.emit (events.js:188:7)
at Parser. (/home/user/dev/services/sqlReader/node_modules/tedious/lib/token/token-stream-parser.js:42:15)
at emitOne (events.js:96:13)
at Parser.emit (events.js:188:7)
at readableAddChunk (/home/user/dev/services/sqlReader/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:213:18)","code":"EINVALIDSTATE"}"

tedious-connection-pool-stacktrace.txt

Cannot call method 'close' of undefined

Cannot call method 'close' of undefined,

TypeError: Cannot call method 'close' of undefined
    at D:\home\site\wwwroot\node_modules\tedious-connection-pool\lib\connection-pool.js:241:32

Is this project dead?

Hi, has this project been abandoned? latest commits were in 2018 and it is not even compatible with latest version of tedious.

Overriding max doesn't change min

If you pass in optional config overrides and include only max and max is less than the default min value, tedious-connection-pool will still establish the default min connections.

The default min value is 10, so if the max value is changed to something like 5, it will still establish 10 connections.

IMO, if the max pool size is changed from the default but min is not specified (i.e., left at default), then min pool size should be adjusted to Math.min(this.max, poolConfig.min >= 0 ? poolConfig.min : 10).

Regardless, I wouldn't expect that the minimum would ever be more than the maximum.

I can provide a PR for this if this is an acceptable change. Let me know your opinion on this.

How to fill the pool with different connections?

Hi.

I am calling the following function 5 times for different select-statements and write the results in an array as follows:

var select_result = [];
execute_SELECT_Statement('SELECT column1 FROM table');
execute_SELECT_Statement('SELECT column2 FROM table');
execute_SELECT_Statement('SELECT column3 FROM table');
execute_SELECT_Statement('SELECT column4 FROM table');
execute_SELECT_Statement('SELECT column5 FROM table');

function execute_SELECT_Statement(sql_statement) {
    var i = 0;
    pool.acquire(function(err, connection) {
        if (err)
            console.error(err);

        //use the connection as normal
        var request = new Request(sql_statement, function(err, rowCount) {
            if (err)
                console.error(err);

            //release the connection back to the pool when finished
            connection.release();
        });
        connection.execSql(request);
        request.on('row', function(columns) {
            select_result[i] = columns[0].value;
            i++;
        });
    });
}

The display output of the array elements looks like:

This is my problem:

There is only 1 connection which fills the pool, therefore there is always the same result!
So how can I fill the pool with all 5 connections?
I need all different results of all the select statements!

Maybe there is an error in:
https://github.com/pekim/tedious-connection-pool/blob/master/lib/connection-pool.js#L144
and the following LOC?

ConnectionError: Failed to connect to <server name> in 15000ms

I am using tedious-connection-pool. I am getting this error intermittently.
unhandledRejection: ConnectionError: Failed to connect to in 15000ms
at Connection. (C:\BuildAgent\work\dc9a3934146969e0\node_modules\mssql\lib\tedious.js:378:25)
at Object.onceWrapper (events.js:315:30)
at emitOne (events.js:116:13)
at Connection.emit (events.js:211:7)
at Connection.connectTimeout (C:\BuildAgent\work\dc9a3934146969e0\node_modules\mssql\node_modules\tedious\lib\connection.js:467:12)
at ontimeout (timers.js:482:11)
at tryOnTimeout (timers.js:317:5)
at Timer.listOnTimeout (timers.js:277:5)

Expected Behavior

Current Behavior

it works most of the time but not consistent.

Steps to Reproduce (for bugs)

Reason For Request (for feature requests)

Possible Solution

Background Details

I am using tedious-connection-pool to call stored procedures and queries.

Environment

• Node.js Version: 8.11.1
• Windows/Mac/Linux: Windows

connection pool doesn't not respect request callback on socket error

Hello,

I've just reported a problem with tedious not calling the request callback when a socket error occurs.

tediousjs/tedious#324

However, If the above issue was fixed there is still a problem with how the connection pool deals with connection errors. currently in tedious on a socket error the error event gets emitted before it internally routes to the error state:

https://github.com/pekim/tedious/blob/master/src/connection.js#L485-L487

This means that the error handling in the connection pool will set the state to STATE.FINAL before the correct error handler is run. This is because the error handler of the connection pool calls .close() on the connection before the the error state has been routed to properly:

https://github.com/pekim/tedious-connection-pool/blob/master/lib/connection-pool.js#L81-L95

    var handleError = function(err) {
        self.log('connection closing because of error');

        connection.removeListener('end', endHandler);
        connection.close(); // <--- because the error event is emitted before the error state is routed this causes the error state always to route to STATE.FINAL

        pooled.status = RETRY;
        pooled.con = undefined;
        if (pooled.timeout)
            clearTimeout(pooled.timeout);

        pooled.timeout = setTimeout(createConnection.bind(self, pooled), self.retryDelay);

        self.emit('error', err);
    };

This issue may not matter except in the future if it's possible to get request callbacks in tedious when a socket error occurs. I just wanted to make note of this issue.

request.transformIntoExecuteSqlRpc() is not a function

• tedious @ v14.0.0 and tedious-connection-pool @ v1.0.1
• Connecting to an Azure DB, although I get the same issue with a standard local SQL as well.

Code

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var poolConfig = {
    min: 2,
    max: 4,
    log: true
};
const connectionConfig = {
   //connection details
  };

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

//acquire a connection
pool.acquire(function (err, connection) {
    if (err) {
        console.error(err);
        return;
    }
    //use the connection as normal
    var request = new Request('select 42', function(err, rowCount) {
        if (err) {
            console.error(err);
            return;
        }

        console.log('rowCount: ' + rowCount);

        //release the connection back to the pool when finished
        connection.release();
    });

    request.on('row', function(columns) {
        console.log('value: ' + columns[0].value);
    });
    
    connection.execSql(request);
});

Receive this:

connection.js:1359 - TypeError: request.transformIntoExecuteSqlRpc is not a function

If I use the tedious library without the pooling, then everything works fine

Possible Solution

I can remove the request.transformIntoExecuteSqlRpc() call in connection.js and the connection is then sent to the server, but it obviously fails as the statement is not interpreted correctly

Environment

Node.js Windows

Draining the pool after releasing connections may cause write to destroyed stream

Using

pool.release();
pool.drain();

May (not necessarily) result in the error below
Connection lost - Cannot call write after a stream was destroyed

The pool.release methods calls the connection.reset method. This methods executes SQL queries to reset to the initial state of the connection. pool.release does not use promises, so pool.drain in the above example is immediately called afterwards. pool.drain closes the connections. If one of these connections still have a request connection pending (or any other request!) it results in the above mentioned error.

I will provide a PR containing a fix soon.

Exception

Started seeing this exception after the process has been running for a while (v0.3.5)

{"err":{"message":"Cannot call method 'close' of undefined","name":"TypeError","stack":"TypeError: Cannot call method 'close' of undefined\n    at null._onTimeout (/src/node_modules/tedious-connection-pool/lib/connection-pool.js:215:20)\n    at Timer.listOnTimeout [as ontimeout] (timers.js:112:15)"},"msg":"Cannot call method 'close' of undefined"}

Nothing fancy in the setup:

var pool = new ConnectionPool(
    {
      acquireTimeout: 5000
    },
    {
      password: ----,
      server: ---,
      userName: ---,
      options: {
        port: ---,
        database: ---
      }
    }
  );

Connecting to remote Sql Server using node tedious

Hi Pekim,

I am trying to connect to my test database server form my local box. suing tedious connection pool and having great difficulty.,

Can you please help me ASAP.

Thanks heaps.. My code:

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var connectionConfig = {
"userName": "user",
"password": "user1",
"server": "test001",
"options": {
"database": "employees"
}
};

var poolConfig = { max: 10, min: 2, idelTimeOutMills:30000 };

var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.requestConnection(function (err, connection) {
if(!err) {
var request = new Request('select 42', function(err, rowCount) {
// Release the connection back to the pool.
connection.close();
});

request.on('row', function(columns) {
  console.log(columns[0].value, 42);
});

connection.on('connect', function(err) {
  connection.execSql(request);
});

}
});

Pause()/resume() doesn't work as expected with connection from connectionPool

Hello, I am trying to run the below code

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var poolConfig = {
min: 2,
max: 4,
log: true
};

var connectionConfig = {
userName: 'sa',
password: 'reallyStrongPwd123',
server: 'localhost'
};

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

pool.on('error', function(err) {
console.error(err);
});

//acquire a connection
pool.acquire(function (err, connection) {
if (err) {
console.error(err);
return;
}

//use the connection as normal
var request = new Request('SELECT * FROM Persons', function(err, rowCount) {
    if (err) {
        console.error(err);
        return;
    }

    console.log('rowCount: ' + rowCount);

    //release the connection back to the pool when finished
    connection.release();
});

request.on('row', function(columns) {
    request.pause()
    // connection.pauseRequest(request)
    console.log('value: ' + columns[0].value);
});

connection.execSql(request);

});

Expected Behavior

The expectation is that the request will be paused after reading a row.

Current Behavior

The connection continues to read all the rows instead of pausing the request after reading a row

Possible Fix

The tedious module being used is of older version which doesn't support pause()/resume() functionality. Updating the tedious module version to 2.2.3 in package.json would fix the issue.

Can you please help?

having difficulty obtaining a connection from the pool

Hello,
I am attempting to use tedious and the tedious connection pool, but i'm having some difficulties.

when i request a connection, i get the following log information

INFO pool - dispense() clients=1 available=0
VERBOSE pool - createResource() - creating obj - count=1 min=0 max=1

Here are a few snippets from my node server...

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request
var pool = new ConnectionPool({max: 10, log: true}, tediousConfig);

---

as a simple test, i am attempting to obtain a connection and then issue a sql query upon obtaining the connection...

---

app.get('/json/clients', function (req, res) {
console.log('requesting a pool connection')
pool.requestConnection(function (err, connection) {
if(!err) {
var request = new Request('select 42', function(err, rowCount) {
assert.strictEqual(rowCount, 1);

      // Release the connection back to the pool.
      connection.close();
    });

    request.on('row', function(columns) {
      assert.strictEqual(columns[0].value, 42);
    });

    connection.on('connect', function(err) {
      connection.execSql(request);
    });
  }
});

});

I never obtain a connection. I notice that my log says 0 available. Any thoughts on what i might be doing wrong? thanks.

tedious config
{
"userName": "avalarasa",
"password": "asdfasdfasdfasd",
"server": "192.168.1.102",
"options": {
"debug": {
"packet": true,
"data": true,
"payload": true,
"token": true
}
}
}

Requests can only be made in the LoggedIn state

I'm having trouble making multiple requests.
Here is my code:

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

var poolConfig = {
    min: 2,
    max: 4,
    log: true
};

var connectionConfig = {
    userName: 'login',
    password: 'password',
    server: 'localhost'
    options: {
        database: 'database'
    }
};

var commands = [
    "select 1"
    , "select 2"
    , "select 3"
    , "select 4"
];

//create the pool
var pool = new ConnectionPool(poolConfig, connectionConfig);

//acquire a connection
pool.acquire(function (err, connection) {
    if (!err) {

        if (commands.length > 0) {
            commands.forEach(function (cmd) {
                console.log(cmd);

                var request = new Request(cmd, function (err, rowCount) {
                    if (err)
                        console.error(err);

                    console.log('\nrowCount: ' + rowCount + '\n');

                    // Release the connection back to the pool.
                    connection.release();
                });

                request.on('row', function (columns) {
                    console.log(columns[0].value);
                });

                connection.execSql(request);

            });
        }

    } else {
        console.log(err);
    }
});

pool.on('error', function (err) {
    console.error(err);
});

When I run the application I get the following error:

Tedious-Connection-Pool: filling pool with 2
Tedious-Connection-Pool: creating connection: 1
Tedious-Connection-Pool: creating connection: 2
Tedious-Connection-Pool: connection connected: 1
{ [RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state]
  message: 'Requests can only be made in the LoggedIn state, not the SentClientRequest state',
  code: 'EINVALIDSTATE' }

rowCount: undefined

Tedious-Connection-Pool: connection ended: 1
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 3
{ [RequestError: Requests can only be made in the LoggedIn state, not the Final state]
  message: 'Requests can only be made in the LoggedIn state, not the Final state',
  code: 'EINVALIDSTATE' }

rowCount: undefined

{ [RequestError: Requests can only be made in the LoggedIn state, not the Final state]
  message: 'Requests can only be made in the LoggedIn state, not the Final state',
  code: 'EINVALIDSTATE' }

rowCount: undefined

Tedious-Connection-Pool: connection connected: 2
Tedious-Connection-Pool: connection connected: 3

What should I do to execute commands at a time?

Timeouts don't seem to work as expected

Expected Behavior

Request timeouts mean requests time out and connections on which the request timeout happened are added back to the connection pool, ready to be used again in the future

Current Behavior

Request timeout settings are being ignored

These are my 4 files containing the test code. A few strings you'll have to substitute before running:

<YOUR_SLOW_SQL_STATEMENT_HERE>
<YOUR_DB_USER_NAME_HERE>
<YOUR_DB_USER_PASSWORD_HERE>
<YOUR_SERVER_IP_HERE>
<YOUR_DB_PORT_HERE>
'<YOUR_DB_NAME_HERE>'

then run by invoking 'node app.js'

my CPU usage spikes like crazy after connection.execSql(sqlRequest) is called.... :/

I've been playing around with some of the timeout settings and I think they are just being ignored. For example, request timeout is set to 5000 millis, it does not timeout after 5 seconds, it just keep going... connection is not being released back to the pool... am I doing something wrong? Maybe there is some config on the SQL server I need to check?

Steps to Reproduce (for bugs)

package.json

{
    "name": "tedconpooltester",
    "version": "1.0.0",
    "description": "tedious connection pool tester",
    "main": "app.js",
    "private": true,
    "dependencies": {
        "tedious": "1.15.0",
        "tedious-connection-pool": "^1.0.5"
    },
    "author": "me",
    "license": "ISC"
}

app.js

const sqlRunner = require('./sqlrunner')
console.log('Tedious connection pool tester running...')

function intervalFunc() {
  sqlRunner.runSql()
    .then(result => {
      console.log(result)
    })
    .catch(err => {
      console.log(err)
    })
}
// setInterval(intervalFunc, 30000);
intervalFunc()

sqlrunner.js

const connectionPool = require('./sqlserverConnectionPool')
const MssqlRequest = require('tedious').Request

module.exports = {
  runSql: function () {
    return new Promise((resolve, reject) => {
      console.log('pool status  ' + connectionPool.connections.map(x => x.status))
      connectionPool.acquire(function (err, connection) {
        console.log('acquiring sqlserver connection...')
        if (err) {
          console.log(err)
          reject(err)
          return
        }
  
        console.log('connection acquired')
  
        let sqlRequest = new MssqlRequest('<YOUR_SLOW_SQL_STATEMENT_HERE>', function (err, rowCount, rows) {
          console.log('releasing connection ' + connection)
          connection.release()
  
            if (err) {
              console.log('Error retrieving SQL data ' + err)
              reject(err)
            } else if (rowCount === 0) {
              console.log('0 rows returned')
              resolve(0)
            } else {
              console.log(rowCount + ' rows returned')
              resolve(rowCount)
            }
  
          })
  
        console.log('executing sql statement')
        connection.execSql(sqlRequest)
      })
    })
  }
}

sqlserverConnectionPool.js

const ConnectionPool = require('tedious-connection-pool')

const poolConfig = {
  min: 1, // 5
  max: 1, // 15
  idleTimeout: 10000,
  acquireTimeout: 12000,
  log: false
}

const config = {
  userName: '<YOUR_DB_USER_NAME_HERE>',
  password: '<YOUR_DB_USER_PASSWORD_HERE>',
  server: '<YOUR_SERVER_IP_HERE>',
  options: {
    port: <YOUR_DB_PORT_HERE>,
    database: '<YOUR_DB_NAME_HERE>'
    trustedConnection: true,
    rowCollectionOnRequestCompletion: true,
    useUTC: false,
    readOnlyIntent: true,
    connectTimeout: 15000,
    requestTimeout: 5000,
    cancelTimeout: 3000,
    maxRetriesOnTransientErrors: 0
  }
}

const pool = new ConnectionPool(poolConfig, config)

pool.on('error', function (err) {
  console.log('db connection pool error - ' + err + ' pool size ' + pool.connections.length')
})

module.exports = pool

Reason For Request (for feature requests)

Possible Solution

Background Details

Environment

• Node.js Version: 8.13.0
• Windows/Mac/Linux: Mac OS X 10.14.4

Memory leak when DB connection is temporarily lost.

Hi,

We had a brief DB outage which sent our app into a huge memory leak situation. Investigation showed that the connection pool, when unable to connect to the DB starts trying to create new connections to replace the failed ones sequentially, going into a loop and leaking a lot memory. This can be simulated by simply trying to connect to a DB with the wrong credentials. Here's the example code:

var ConnectionPool = require('tedious-connection-pool');
var pool = new ConnectionPool({ max: 150, min: 0 }, {
    "userName": "testLogin",
    "password": "wrongPassword",
    "server": "localhost",
    "options": {
        "port": 1433
    }
});

pool.acquire(function(err, connection_) {
    if (err) { return console.log("ERROR:", err); }
    console.log('no error');
});

Tedious connection.release() messes up newrelic error log

Hi,

I am having a hard time deciding on where to put this issue up - newrelic forum, stackoverflow, or here.

However, I have described the issue on the newrelic forum:

https://discuss.newrelic.com/t/custom-parameters-are-lost/34768

If you have any input I would be super happy about it!

/o

Multiple Request On Multiple Connection

I'm trying to insert 100 records in the database. But unfortunately, I get my request object updated with the last value in the loop. Is there any way by which I can send my every request with the current value given by the loop?

This is my code I'm using to acquire connection pool and request:-

function insertData(values) {
let procValue = { columns: 
   [ { name: 'SyncLogID', type: [Object], nullable: true },
     { name: 'RequestID', type: [Object], nullable: true, length: 255 }],
  rows: 
   [ [values.SyncLogID, values.RequestID  ] ] };

pool.acquire(function(err, connection) {
        if (err) {
          return reject(err);
        }
        let request = new Request("userStoredProcedure", function(err, rowCount, rows) {
          if (err) {
            return reject(err);
          } else {
            connection.release();
            return resolve(rows);
          }
        });
        request.addParameter("userDefinedDataType", TYPES.TVP, procValue);
        connection.callProcedure(request);
      });
}

Now notice that the function is called in a loop and the parameter "values" keeps changing. But the problem arises when "connection.callProcedure(request)" intakes the last updated value from the loop and keeps inserting the same last value till the loop count.

response.write on Tedious connection pool

Hello Pekin,
Can you help me about print results on site,

I could print on console.log but not on website.

var ConnectionPool = require('tedious-connection-pool');
var Request = require('tedious').Request;

// LEVANTA ID 
var id = req.params.id;
res.contentType('application/json');

        var connectionConfig = {
                "userName": "user",
                "password": "pass",
                "server": "localhost",
                "options": {
                    "database": "mydb"
                }
            };
        var poolConfig = { max: 10, min: 2, idelTimeOutMills:30000 };
        var pool = new ConnectionPool(poolConfig, connectionConfig);
        pool.requestConnection(function (err, connection) {
          if(!err) {
            var sSiglaEmpresa = id;

            var SQL=String("SELECT * FROM Empresa WHERE Codigo_Empresa = '"+ sSiglaEmpresa +"' ");
            var request = new Request(SQL, function(err, rowCount) {
             // connection.close();
            });
            request.on('row', function(columns) {
                var columna = columns[1].value;



                console.log(columna);
            });
            connection.on('connect', function(err) {
                connection.execSql(request);
            });
         }else{
            console.log("Error al acceder a la base de datos.");
         }
    });
        res.write(JSON.stringify("Hello" +  id ));

get connection Status, pool.acquire method is the only way?

Hi pekim, I create a connection pool as your steps

But I wanna know the connection is valid or not when I create connection.pool .

now after I invoke the pool.acquire method ,in the callback I get the result that connection is invalid.

what should I do ?

Error: connection.release is not a function

Expected Behavior

I can't get this to work.
I followed the README.md,

Current Behavior

I can't release the connection

        //release the connection back to the pool when finished

        connection.release();

Error Message

TypeError: connection.release is not a function
at Request [as userCallback] (D:\webservice\app.js:158:18)
at Request._this.callback (D:\webservice\node_modules\tedious\lib\request.js:60:27)

Environment

var Connection = require('tedious').Connection;

var ConnectionPool = require('tedious-connection-pool');

var Request = require('tedious').Request;

// follow the rest of README.md
// ...
// my additional code not in README.md which I believe is lacking.


var config = {
  userName: creds.user, // update me
  password: creds.password, // update me
  server: creds.host, // update me
  options: {
    database: creds.options.database,
    encrypt: true,
    rowCollectionOnRequestCompletion : true
  }
}

connection = new Connection(config);
connection.on('connect', function(err){
  if(err){
    console.log("Error connecting to database: ", err)
  }
  else {
    console.log("Connection to Database Success.");
  }
})

Steps to Reproduce (for bugs)

1. I basically followed the README.md
2. but connection is not instantiated nor has history on how it was created.
3. As you can see, I have connection created, but I'm now confused of connectionPool and connection
4. Due to that, I can't use connection.release.
   connection.release is not a function

Possible Solution

Please add to README.md more information regarding connection and how it has been instantiated.

Add Exponential Backoff mechanism

The current retry mechanism is too aggressive for SQL Azure.
Too many consecutive retries get "punished" by SQL Azure.

See:

1. Retry service specific guidance
   
2. Transient Fault Handling (Building Real-World Cloud Apps with Azure)

Pool fills up with 'USED' connections - node app restart required as a result

Expected Behavior

On calling connection.release(), connections should be released back into the pool and these connections should be in state = 1, meaning 'FREE' state so that they can be reused in the future, regardless of errors having occurred on the connection (timeouts, etc...)

Current Behavior

My connection pool slowly fills up to the max number of connections where it ends up with all connections being in the 'USED' state (i.e. state = 2).

my minimum pool size = 5 and maximum pool size = 15.

After some time (this is variable, can be after 24 hours, sometimes 48 hours,... depends on the load on the database and how often timeouts occur) I end up with 15 connections in the 'USED' state and I need to restart my application.

Whenever this happens:

RequestError: Timeout: Request failed to complete in 15000ms

--> the connection on which the timeout occurred is not released properly and remains in state = 2 ('USED')

logs from a test run: the pool starts of with 5 connections in the 'FREE' state and every time a request timeout happens on a connection, that connection's state is set to 2 and it is never free'd up.

[2019-05-27T13:00:07.421] [INFO] default - Zdeapp beta_release_0.118_SNAPSHOT running on port 3000...
[2019-05-27T13:01:00.005] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:02:00.004] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:03:00.004] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:04:00.004] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:05:00.008] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:05:00.111] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:06:00.004] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:07:00.006] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:08:00.003] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:09:00.005] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:10:00.003] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:10:00.103] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:11:00.006] [INFO] default - pool status 1,1,1,1,1
[2019-05-27T13:11:15.022] [ERROR] default - Error retrieving todays order IDs RequestError: Timeout: Request failed to complete in 15000ms
[2019-05-27T13:12:00.003] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:13:00.004] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:14:00.003] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:15:00.006] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:15:00.105] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:16:00.004] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:17:00.003] [INFO] default - pool status 2,1,1,1,1
[2019-05-27T13:17:15.018] [ERROR] default - Error retrieving todays order IDs RequestError: Timeout: Request failed to complete in 15000ms
[2019-05-27T13:18:00.005] [INFO] default - pool status 2,2,1,1,1
[2019-05-27T13:19:00.003] [INFO] default - pool status 2,2,1,1,1
[2019-05-27T13:20:00.005] [INFO] default - pool status 2,2,1,1,0
[2019-05-27T13:20:00.100] [INFO] default - pool status 2,2,2,1,1
[2019-05-27T13:21:00.004] [INFO] default - pool status 2,2,1,1,1
[2019-05-27T13:22:00.004] [INFO] default - pool status 2,2,1,1,1

Steps to Reproduce (for bugs)

You won't be able to reproduce with only the code pasted here, I only copied the core of the code involved. I will write up another test case (a separate test application) and I'll also post that test code here if I the same issue manifests itself.

I started printing the status of all the connections in the pool every time a connection is acquired from the pool. As can be seen from the logs, the number of 'USED' connections slowly creeps up
log excerpt:

[2019-05-25T17:33:00.008] [INFO] default - pool status 1,1,1,1,1
[2019-05-25T17:35:00.499] [INFO] default - pool status 2,1,1,1,1
[2019-05-25T17:36:00.004] [INFO] default - pool status 2,1,1,1,1
[2019-05-25T17:39:00.009] [INFO] default - pool status 2,2,1,1,1
[2019-05-25T17:40:00.499] [INFO] default - pool status 2,2,2,1,1
[2019-05-25T17:42:00.005] [INFO] default - pool status 2,2,2,1,1
[2019-05-25T17:45:00.014] [INFO] default - pool status 2,2,2,2,1
[2019-05-25T17:45:00.545] [INFO] default - pool status 2,2,2,2,2
[2019-05-25T17:45:15.676] [ERROR] default - Error when checking for newly invoiced orders RequestError: Timeout: Request failed to complete in 15000ms
[2019-05-25T17:48:00.003] [INFO] default - pool status 2,2,2,2,2
[2019-05-25T17:50:00.489] [INFO] default - pool status 2,2,2,2,2,2
[2019-05-25T17:50:15.597] [ERROR] default - Error when checking for newly invoiced orders RequestError: Timeout: Request failed to complete in 15000ms
[2019-05-25T17:51:00.026] [INFO] default - pool status 2,2,2,2,2,2
[2019-05-25T17:54:00.005] [INFO] default - pool status 2,2,2,2,2,2
[2019-05-25T17:55:00.507] [INFO] default - pool status 2,2,2,2,2,2
[2019-05-25T17:57:00.007] [INFO] default - pool status 2,2,2,2,2,2
[2019-05-25T18:00:00.099] [INFO] default - pool status 2,2,2,2,2,2,2
[2019-05-25T18:00:00.619] [INFO] default - pool status 2,2,2,2,2,2,2,1
[2019-05-25T18:03:00.008] [INFO] default - pool status 2,2,2,2,2,2,2
[2019-05-25T18:05:00.493] [INFO] default - pool status 2,2,2,2,2,2,2
[2019-05-25T18:06:00.027] [INFO] default - pool status 2,2,2,2,2,2,2
[2019-05-25T18:09:00.006] [INFO] default - pool status 2,2,2,2,2,2,2,2
[2019-05-25T18:10:00.505] [INFO] default - pool status 2,2,2,2,2,2,2,2,2
[2019-05-25T18:12:00.029] [INFO] default - pool status 2,2,2,2,2,2,2,2,2
[2019-05-25T18:15:00.007] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:15:00.577] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:18:00.028] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:20:00.495] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:21:00.025] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:24:00.004] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2,2
[2019-05-25T18:25:00.503] [INFO] default - pool status 2,2,2,2,2,2,2,2,2,2,2,2

My connection pool js file

const ConnectionPool = require('tedious-connection-pool')
const appConfig = require('./config')[process.env.ZAPENV]
const logger = require('./logger').mainLog

const poolConfig = {
  min: 5, // 5
  max: 15, // 15
  idleTimeout: 10000,
  acquireTimeout: 12000,
  log: false
}

const config = {
  userName: appConfig.mssql.MSSQL_USER,
  password: appConfig.mssql.MSSQL_PASS,
  // server: appConfig.mssql.MSSQL_EXT_SRV,
  server: appConfig.mssql.MSSQL_INT_SRV,
  options: {
    // port: appConfig.mssql.MSSQL_EXT_PORT,
    port: appConfig.mssql.MSSQL_INT_PORT,
    database: appConfig.mssql.MSSQL_DB,
    trustedConnection: true,
    rowCollectionOnRequestCompletion: true,
    useUTC: false,
    readOnlyIntent: true,
    // acquireTimeout: 10000,
    connectTimeout: 15000,
    requestTimeout: 15000,
    maxRetriesOnTransientErrors: 0
  }
}

const pool = new ConnectionPool(poolConfig, config)

pool.on('error', function (err) {
  logger.error(`db connection pool error - ${err} pool size ${pool.connections.length}`)
})

module.exports = pool

Here's one example usage of how I use the connection pool. There are many modules inside my application which rely on the connection pool and the pattern is always the same.

1. acquire connection
2. execute the sql
3. process the returned data in the sqlrequest callback function

Note that the first line in the callback is 'connection.release()'. This to make sure the connection is definitely released once the callback is hit regardless of connection errors/success.

function getTodaysOrderIds (callback) {
  logger.info('pool status ${connectionPool.connections.map(x => x.status)}')
  connectionPool.acquire(function (err, connection) {
    logger.info('acquiring sqlserver connection...')
    if (err) {
      logger.error(err)
      return
    }

    let dataSet = []

    let sqlRequest = new MssqlRequest('SELECT something FROM somewhere', function (err, rowCount, rows) {
      connection.release()

      if (err) {
        callback(err, null)
      } else if (rowCount === 0) {
        callback(null, null)
      } else {
        rows.forEach(function (row) {
          if (row[0].value !== '') {
            let element = { 'id': row[0].value, 'create_date': row[1].value }
            dataSet.push(element)
          }
        })

        callback(null, dataSet)
      }
    })

    connection.execSql(sqlRequest)
  })
}

Am I right in thinking that in the example code below (taken from the tedious-connection-pool github repo), the connection will not be released when an error occurs? For example, DB connection is established but the request times out...

    //use the connection as normal
    var request = new Request('select 42', function(err, rowCount) {
        if (err) {
            console.error(err);
            return;
        }

        console.log('rowCount: ' + rowCount);

        //release the connection back to the pool when finished
        connection.release();
    });

Ironically, this seems to be happening in my code - connections not being released back into the pool in the correct state.... should I move my connection.release() call back to the bottom of the callback function?

Reason For Request (for feature requests)

Possible Solution

Background Details

Regular restarts are required...

Environment

• Node.js Version: 8.12.0
• Windows/Mac/Linux: CentOS Linux release 7.5.1804, x86_64 x86_64 x86_64 GNU/Linux