dhensby
commented
2 years ago As a caveat, I'm not an expert with AWS Lamda and my experience is with Azure Function Apps as as serverless environment and no doubt there are differences. The main unknown for me is if any resources persist between lamda invocations (they do in Azure Functions), ie: a pool created the way you are doing is available to many function executions in azure, I'm not sure if that's the case in lamdas.
You're calling sql.close() despite not using the global connection, so the pool you're creating won't actually be closed. However, that's a good thing because you have no way to reconnect the pool you're caching and returning in getConnection(). You also don't want to be opening and closing the pool for each request, the pools are designed to be long running but you will have problems if your conn_cfg changes over time.
As for what is going on, it's hard to know exactly as you're the one with access to the infrastructure and knowledge about how many connections are likely to be created or lambda instances spawned at any given time.
You're running a pool with max 10 connections, so each spawned instance of the lamda could have up to 10 connections. Depending on what config your on-prem SQL server has, you could be hitting connection limits, etc. You'll need to consult with your database administrator to look into / debug what is going on.
in terms of the code, there are a few issues:
- You're caching the pool connection promise (fine in theory) but you're caching it even if it rejects, which means that if you fail to connect once because of some intermittent error, every call to
getConnection()will reject. - When the pool is closed (though, I note you're not closing the pool you make, but the non-existent global pool), you're not un-setting the
poolRequestvariable, so this will begin to return a disconnected pool, leading to rejections. - You're passing a
configparameter togetConnection()and then to theConnectionPoolbut you're caching the pool regardless of the config, so any changed configs will return the original pool (this may be fine, but it's just worth noting).
const sql = require('mssql')
let poolRequest;
function getConnection(config) {
if (!poolRequest) {
const pool = new sql.ConnectionPool(config);
+ // if the pool connection errors, you're actually caching that error so you'll see that
poolRequest = pool.connect();
}
return poolRequest;
}
exports.execSQL = async function (conn_cfg, cmd) {
let data
try {
console.log('BEFORE CONNECT')
- let conn = await getConnection(conn_cfg)
+ const conn = await getConnection(conn_cfg)
console.log('AFTER CONNECT')
console.log('BEFORE QUERY EXEC')
- data = await conn.request().query(cmd)
- .then(result=> {console.log('QUERY EXEC COMPLETE')})
+ await conn.request().query(cmd)
+ .then(()=> {console.log('QUERY EXEC COMPLETE')})
.catch(err => {console.log("REQUEST ERROR: " + err)})
- sql.close()
}
catch(err) {
- sql?.close()
console.log(err)
throw err
+ } finally {
+ // personally I would not do this, you want to keep the pool open
+ sql?.close()
+ poolRequest = null
}
}
ANTGOMEZ
Running from a NodeJS v14 AWS Lambda, with mssql module v8.0.2,...
The Lambda is being triggered by a SQS QUEUE, we feed in rapid fire events to the Queue which trigger the lambda. In turn, the lambda calls the code below to post the events to an on prem DB.
The result is a mixed bag. Sometimes all data gets posted to DB, no errors, other times we get connection errors, below. No correlation to type of event.
Is there a problem with this code? Doesn't seem like it.
Expected behaviour:
Consistent connections.
Actual behaviour:
Query sometimes throws a connection error; couldn't connect to IP Address.
Configuration:
I've set all relevant timeouts to15 minutes (the lambda timeout value)
Software versions