Node-mssql: Connection Error after a few SQL Queries

[glbrtcaro]

i am receiving in my logs ConnectionError after a few scripts executions. but when i execute the SQL scripts one at a time i mean manually, it returns a result, But when i start to run my web app i encounter connectionError.

below is some reference of my logs:

'Get Revenue Budget By Product Parameters'
'Execute Stored Procedure for Revenue Budget by Product'
'Return Stored Procedure Data for Revenue Budget by Product'
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' }
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' }
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' }
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' }
{ [ConnectionError: Connection is closed.]
name: 'ConnectionError',
message: 'Connection is closed.',
code: 'ECONNCLOSED' }
'Connected to Database'
'Store Procedure getProductList execute'
'Product data retrieve successfully'
'Connected to DB...'
Getting Parameter: AUDIT
'Execute Partner Lookups...'
'Connected to DB...'
'Get FTE Budget Data'
'Execute Stored Procedure for FTE Budget'
'Return Partner Lookup Data for Parsing...'

[benoror]

I have the same issue when I run 2 or more queries one right after the other

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Related to https://github.com/patriksimek/node-mssql/issues/138

[christiaanwesterbeek]

Try to reduce your code as much as possible so that the problem still occurs, then show us your code. That way we can help.

[guillaumedardenne]

Maybe you are trying to reach your database with the same connection you used for the previous query which may not be terminated ?
This thread may help you :
https://github.com/patriksimek/node-mssql/issues/138

[benoror]

Yep, it seems I wasn't properly terminating the connection. Somehow Promises seem to handle it properly now (as mentioned in https://github.com/patriksimek/node-mssql/issues/138#issuecomment-103549335).

I'm still afraid/not sure about using a global connection for multiple queries, would that be some kind of singleton? Why would you rather go that way?

[patriksimek]

It's strange, all of the supported methods (callback, streaming, Promises) uses the same code under the hood. A small snipet to reproduce the issue would be nice.

[benoror]

Sure, here it is: https://gist.github.com/benoror/ab77e6a9872a20d6dcfc

It's worth mentioning that the error happened at a random rate, for example, when calling the method quickly 3 times in a row sometimes only one call would fail, sometimes two would fail and other times the 3 of them.
In rare occasions all 3 would be successful.

[christiaanwesterbeek]

@benoror I've seen exactly the same behaviour with my program when I would rapidly call a method where a new db connection pool is created each time. The code in your gist does this too. With my program, it was fixed when I created the connection pool outside that method and reuse the single connection pool in the method. In my opinion (re)using a single connection pool is the way to go. https://github.com/patriksimek/node-mssql/issues/118

[benoror]

@devotis So do you simply create the connection in your Express app and reuse ir later?:

// ran once in express app:
var sql_conn = new sql.Connection(that.config.db);

//......

// then for each request:
sql_conn.connect().then(function () {
        var sql_req = new sql.Request(sql_conn);

//......

Or do you use a fancier method like https://github.com/patriksimek/node-mssql/issues/118#issuecomment-91329956 ?

[christiaanwesterbeek]

The first. I don't use the one you refer (https://github.com/patriksimek/node-mssql/issues/118#issuecomment-91329956) to, although that will do the job too.

I have a decent solution worked out to reuse a connection in an Express app. It's here: https://github.com/patriksimek/node-mssql/issues/164#issuecomment-103943936 and as a stackoverflow answer too. http://stackoverflow.com/questions/30356148/how-can-i-use-a-single-mssql-connection-pool-across-several-routes-in-an-express/30356582#30356582

[benoror]

Wow thanks a lot @devotis , that will do the trick! @patriksimek you can close the issue now right?

[takotuesday]

@devotis I tried to do something similar as @benoror's above example. First the following snippet:

sql_conn.connect().then(function () {
        var sql_req = new sql.Request(sql_conn);

wont work because connect().then is not a function.
@devotis, I appreciate the example, but it still does not work when my application is deployed to heroku. Any ideas?

[rainabba]

@takotuesday .connect() is a promise and will return one having .then() on it IF things are working properly.

I ended up here because I too was having "random" connection issues. I realized that I was calling connect() for every hit to my route.

I've updated my code so I'm only calling connect() once on my sql object, then using new sql.Request() for each hit to the route and now my connection issues are gone, likely because I'm holding a single connection using pooling, rather than establishing one every single time the route is hit.