How to connect to Azure SQL database from multiple Azure Functions TypeScript API endpoints

Question

I'm creating an API in Azure Functions using TypeScript, with multiple endpoints connecting to the same Azure SQL Server. Each endpoint was set up using the Azure Functions extension for VS Code, with the HttpTrigger TypeScript template. Each endpoint will eventually make different calls to the database, collecting from, processing and storing data to different tables.

There don't seem to be any default bindings for Azure SQL (only Storage or Cosmos), and while tedious is used in some Microsoft documentation, it tends not to cover Azure Functions, which appears to be running asynchronously. What's more, other similar StackOverflow questions tend to be for standard JavaScript, and use module.exports = async function (context) syntax, rather than the const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> syntax used by the TypeScript HttpTrigger templates.

Here's what I've got so far in one of these endpoints, with sample code from the tedious documentation in the default Azure Functions HttpTrigger:

var Connection = require('tedious').Connection;
var config = {
    server: process.env.AZURE_DB_SERVER,
    options: {},
    authentication: {
      type: "default",
      options: {  
        userName: process.env.AZURE_DB_USER,
        password: process.env.AZURE_DB_PASSWORD,
      }
    }
  };

import { AzureFunction, Context, HttpRequest } from "@azure/functions"

const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> {
    context.log('HTTP trigger function processed a request.');
    const name = (req.query.name || (req.body && req.body.name));

    if (name) {

        var connection = new Connection(config);
        connection.on('connect', function(err) {
            if(err) {
              console.log('Error: ', err)
            }
            context.log('Connected to database');
        });

        context.res = {
            // status: 200, /* Defaults to 200 */
            body: "Hello " + (req.query.name || req.body.name)
        };
    }
    else {
        context.res = {
            status: 400,
            body: "Please pass a name on the query string or in the request body"
        };
    }
};

export default httpTrigger;

This ends up with the following message:

Warning: Unexpected call to 'log' on the context object after function execution has completed. Please check for asynchronous calls that are not awaited or calls to 'done' made before function execution completes. Function name: HttpTrigger1. Invocation Id: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Learn more: https://go.microsoft.com/fwlink/?linkid=2097909

Again, the async documentation linked to covers just the standard JavaScript module.exports = async function (context) syntax, rather than the syntax used by these TypeScript httpTriggers.

I've also been reading that best practice might be to have a single connection, rather than connecting anew each time these endpoints are called - but again unsure if this should be done in a separate function that all of the endpoints call. Any help would be much appreciated!

@LeonYue Yes! Thank you! The connection now works. I'll add that solution as an answer here, and maybe create a new question to look at best practice connecting to the same database from multiple endpoints. — mg-mo
– mg-mo, Commented Jun 23, 2020 at 16:03
You are welcome. Did you use 'mssql' as instead and it works？ — Leon Yue
– Leon Yue, Commented Jun 24, 2020 at 0:10
Hi @mg-mo, I post it as an answer, hope you can accept(mark) it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. If you want share us with more details, you also post by yourself, have a good day! — Leon Yue
– Leon Yue, Commented Jun 25, 2020 at 1:21

Leon Yue · Accepted Answer · 2020-06-25 01:17:36Z

1

I'm glad that using the 'mssql' node package works for you:

const sql = require('mssql');
require('dotenv').config();

module.exports = async function (context, req) {
  try {
    await sql.connect(process.env.AZURE_SQL_CONNECTIONSTRING);
    const result = await sql.query`select Id, Username from Users`;
    context.res.status(200).send(result);
  } catch (error) {
    context.log('error occurred ', error);
    context.res.status(500).send(error);
  }
};

Ref:https://stackoverflow.com/questions/62233383/understanding-js-callbacks-w-azure-functions-tedious-sql

Hope this helps.

answered Jun 25, 2020 at 1:17

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mg-mo Over a year ago

Thanks again, this also works exactly the same for me with const httpTrigger: AzureFunction = async function (context: Context, req: HttpRequest): Promise<void> as it does with module.exports = async function (context, req) described above.

Collectives™ on Stack Overflow

How to connect to Azure SQL database from multiple Azure Functions TypeScript API endpoints

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related