node-mssql: Idle connections not being closed by the pool

Based on the docs quick example, I am trying to run a simple query as follows:

const sql = require('mssql')

(async () => {
    try {
        const pool = await sql.connect('mssql://username:password@localhost/database')
        const result = await sql.query`select * from mytable where id = ${value}`
        console.dir(result)
    } catch (err) {
        // ... error checks
    }
})();

After logging the results, the process remains stucked until I press CTRL+C.

The problem is happening in the await sql.query part - it is returning a promise that seems to resolve, so I cannot figure out what is happening and why the process do not finish and keeps waiting for something.

Node 7.9.0 node-mssql 4.0.2

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 3
Comments: 36

Commits related to this issue

Explicitely use connection pool and fix close issue https://github.com/tediousjs/node-mssql/issues/457 — committed to devotis/db-migrate-mssql by christiaanwesterbeek 5 years ago

Most upvoted comments

I have the same problem with the 4.10 version. I am using the connection pool when triggering a good amount of async queries. And after I return the results from the db, I explicitly close the connection pool but still the connections used remain active and the MSSQL server shows all the unclosed connection. For a heavy use, this makes the Db server to reach the max available connections and the Node.JS server can not connect anymore .

Is there any fix I can use?

Danielus on Feb 6, 2018

Fixed in 4.0.4. Anyway, the fix doesn’t close the process when all connections are closed. You need to explicitly call pool.close().

patriksimek on Apr 25, 2017

I’m using 4.0.4 and can confirm calling connectionPool.close() closes the connections… eventually. In my case, we’re using AWS lambda functions to pull data from the DB. So waiting for the connection pool to terminate is a no go. I worked around the issue my modifying the pool configuration after instantiating the ConnectionPool. This ensures our lambda function will execute quickly and will not hang around after data was returned.

Note: If you are using the non-async-await syntax, instantiating ConnectionPool will also trigger connect. So you’ll have to provide a config object at instantiation time.

My Implementation:

export class DatabaseService {
  readonly connection: ConnectionPool;

  constructor(dbUri: string) {
    this.connection = new ConnectionPool(dbUri);

    this.connection['config'] = Object.assign(
      this.connection['config'] || {},
      {
        pool: {
          max: 1,
          min: 0,
          evictionRunIntervalMillis: 500,
          idleTimeoutMillis: 500,
          testOnBorrow: true,
        },
      },
    );

  async connect(): Promise<ConnectionPool> {
    // not yet connected, start the connection.
    if (!this.connection.connected && !this.connection.connecting) {
      await this.connection.connect();
    }

    return this.connection;
  }

  async disconnect(): Promise<boolean> {
    try {
      await this.connection.close();
      return true;

    } catch (e) {
      console.error('Error while disconnecting from database:', e);
      return false;
    }
  }

  ...
}
```

vangorra on May 24, 2017

Does not help to lower idleTimeoutMillis

On Thu, May 10, 2018 at 5:02 PM, Will Morgan notifications@github.com wrote:

What about using a lower idleTimeoutMillis?

https://github.com/tediousjs/node-mssql#general-same-for-all-drivers

pool.idleTimeoutMillis - The Number of milliseconds before closing an unused connection (default: 30000).

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/tediousjs/node-mssql/issues/457#issuecomment-388184960, or mute the thread https://github.com/notifications/unsubscribe-auth/ARIl6Hsq2VGCfPL_m31qGXzSq0g3kcHVks5txKrmgaJpZM4NGtLQ .

– Mordechai Kohn

mottykohn on May 10, 2018