CODEWITHSUNDEEP
node.jssql-servertypescriptazureazure-functions
mg-mo
mg-mo

Reputation: 13

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

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!

Upvotes: 1

Views: 1906

Answers (1)

Leon Yue
Leon Yue

Reputation: 16431

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.

Upvotes: 1

Related Questions