Node-mssql: Question: Usage of connection

[danpetitt]

Sorry for asking a bit of a dumb question but I havent yet found any examples that best describe best usage in a situation where multiple calls are being made within the same app (like a NestJS web api). All the examples seem to show just a simple one action process; but not something like a web request where multiple calls would be made to the database before the app exits.

Are we supposed to just create a new connection for each query and let the connection pool handle the actual 'create' or 'reuse' for us? Or should we store the return value of the connection.connect() globally and just reuse it for each query? I have stepped through the code and it seems to always create a new connection ad-infinitum.

If we should 'connect' for each query, how do we 'close' them all to cleanup; there isnt any connection pool closedown event we can call on process.exit.

Software versions

• NodeJS: v10.15.0
• node-mssql: v4.3.0
• SQL Server: Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64)

[dimisus]

Also here maybe a lack of knowledge but this is how I use this package - you basically have several options to fire your sql requests.

1 - Establish a connection by instantiating a connection pool

const pool = new sql.ConnectionPool({ /* config */ })

2 - Inquiry connections from pool.

From here on you have the option to inquiry connections from the pool (it can be 1....N) by using either a Transaction, a Request or a PreparedStatement.

const request = new sql.Request(/* [pool or transaction] */)
If you omit pool/transaction argument, global pool is used instead.

const transaction = new sql.Transaction(/* [pool] */)
If you omit connection argument, global connection is used instead.
Once you call begin, a single connection is acquired from the connection pool and all subsequent requests (initialized with the Transaction object) are executed exclusively on this connection. After you call commit or rollback, connection is then released back to the connection pool.

const ps = new sql.PreparedStatement(/* [pool] */)
If you omit the connection argument, the global connection is used instead.
Once you call prepare, a single connection is acquired from the connection pool and all subsequent executions are executed exclusively on this connection.

3 - close the connections.

You can either unprepare the PreparedStatement or finalize the Request or commit the Transaction...

In the end you can just close the pool if you really want to cut the connection.

close()
Close all active connections in the pool.
Example
pool.close()

---

Using one connection only:
You can for example instantiate one sql.Request and use it for all your requests...
You can prepare statements and use them for recurring requests...

PS: configs

pool.max - The maximum number of connections there can be in the pool (default: 10).
pool.min - The minimum of connections there can be in the pool (default: 0).
pool.idleTimeoutMillis - The Number of milliseconds before closing an unused connection (default: 30000).

Also here:
https://github.com/coopernurse/node-pool

[danpetitt]

Thanks, so in the idea of a web api with multiple endpoints on disparate controllers, I would need to store the connection in a global variable and pass it around for all requests/transactions or create a connection on app start and then pass nothing into requests or transactions?

The global connection is still a pool of connections?

I can’t set the options as I am using a connection string and there isn’t an overload for this method to set options as well.

[dhensby]

You would want to store the connection pool globally and pass that around so that the various calls can make use of already created connections.

We do this successfully with the help of a custom DB connection helper which will return us the relevant connection pool depending on the connection config we use. We can then await that connection pool (which will resolve instantly apart from on initial create) and this is shared amongst multiple requests.