Hitting connection limits with Azure functions and Azure SQL (node.js)

Question

I have a function app that serves a node.js API. We are hitting the 900 concurrent connections limit with tedious connected to Azure SQL and realize we should add connection pools (unless there is a better recommendation of course).

Azure Functions + Azure SQL + Node.js and connection pooling between requests? seems to answer our prayers but wanted to validate how you can use a single connection pool with Azure functions

Is the best practice to put "let pool = new ConnectionPool(poolConfig, connectionConfig);" above mode.exports on all functions? Is that not creating a new pool every time an individual function is called?

Microsoft doesn't have clear documentation on this for node.js unfortunately so any help would be greatly appreciated!

Answer 1

To make the whole Function app share one single pool, we need to put the initialization part to a shared module. Christiaan Westerbeek had posted a wonderful solution using mssql, there's not so much difference between a Function app and a web app in this respect.

I recommend using mssql(use tedious and generic-pool internally) instead of tedious-connection-pool which seems not updated for 2 years.

Put the connection code in poolConfig.js under a SharedLib folder.

const sql = require('mssql');
const config = {
    pool:{
        max:50 // default: 10
    },
    user: '',
    password: '',
    server: '', 
    database: '',
    options: {
        encrypt: true // For Azure Sql 
    }
};
const poolPromise = new sql.ConnectionPool(config).connect().then(pool => {
    console.log('Connected to MSSQL');
    return pool;
  })
  .catch(err => console.log('Database Connection Failed! Bad Config: ', err));

module.exports = {
  sql, poolPromise
}

And load the module to connect to sql. We use await to get ConnectionPool the function should be async(default for v2 js function).

const { poolPromise } = require('../SharedLib/poolConfig');

module.exports = async function (context, req) {
    var pool = await poolPromise;
    var result =  await pool.request().query("");
    ...
}

Note that if Function app is scaled out with multiple instances, new pool will be created for each instance as well.