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!

score 2 · Accepted Answer · answered Dec 06 '18 at 06:56

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.

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

1 Answers1