I'm currently working on a company project and although I'm a little bit familiar with using APIM, Azure Functions, and Azure AD B2C, I am not as familiar with using SQL databases with them. My specific question involves presenting SQL data. How would I go about presenting data in an SQL database in browser using an Azure Function hosted in APIM, in JSON format? I was considering using Sequelize but I wasn't 100% sure if this was the way to go. Currently I have an APIM instance with Azure Functions using a SPA - I used this link as a guide, and it's all I have so far with the API itself. Thank you in advance!

Hunter B Here are the steps you can follow for your scenario: Create a HTTP-triggered function in your function app to receive the data via query parameters (or request body). Use Input binding to get the data from SQL database (Azure SQL input binding) and return data as JSON object to the client app. Here is the sample reference HTTP trigger, get row by ID from query string and more samples/full code snippet are available in InputBindingSamples . Alternatively, you can also use Microsoft.Data.SqlClient library instead of extension. I hope this helps and let me know if any questions.

It's usually best to start with how your front end wants the data. Usually yes, JSON is pretty standard. Your Azure function will act as the translation layer for building up the DTO . For inspiration, have a look at the Developer CLI templates which combine application & infrastructure code for full app deployment pattern examples; https://azure.github.io/awesome-azd/?operator=AND&tags=functions&tags=azuresql

How to present SQL Database data in browser using Azure Function in APIM

Accepted answer

MuthuKumaranMurugaachari-MSFT 22,311 Reputation points

2023-08-29T19:57:59.8033333+00:00
Hunter B Here are the steps you can follow for your scenario:

Create a HTTP-triggered function in your function app to receive the data via query parameters (or request body).

Use Input binding to get the data from SQL database (Azure SQL input binding) and return data as JSON object to the client app. Here is the sample reference HTTP trigger, get row by ID from query string and more samples/full code snippet are available in InputBindingSamples.

Alternatively, you can also use Microsoft.Data.SqlClient library instead of extension. I hope this helps and let me know if any questions.
Please sign in to rate this answer.

1 person found this answer helpful.
hampton123 1,165 Reputation points

2023-08-29T20:15:50.49+00:00

Hi again MuthuKumaranMurugaachari-MSFT, thank you for the quick response!

So the function would take in the query parameters and then after that input binding would be used to get the data from the database, and not Sequelize. Thank you! I just have 2 questions about this approach.

I want to use managed identity for security with this Azure Function - would following the steps on this MS tutorial be good?

How do query parameters work with this system? Can multiple query parameters be inserted?

Thanks again Muthu!

MuthuKumaranMurugaachari-MSFT 22,311 Reputation points

2023-08-29T20:47:37.1333333+00:00

Hunter B

#1 Yes, you can use Managed Identity and https://learn.microsoft.com/en-us/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity tutorial is the right way to approach.

#2 You should be able to pass multiple parameters like "@code1={value1},@code2={value2}" with comma separator as delimiter. Please test it and let me know if any issues. Also, you can post it directly to SQL binding team via https://github.com/Azure/azure-functions-sql-extension/issues that will help in directly engaging with them.

hampton123 1,165 Reputation points

2023-08-29T20:57:14.2033333+00:00

Thanks again MuthuKumaranMurugaachari-MSFT, this is exactly what I needed!

hampton123 1,165 Reputation points

2023-08-31T20:05:42.57+00:00

Hi MuthuKumaranMurugaachari-MSFT, sorry but as I was working on this I came up with another question regarding query parameters:

Is it possible to have query parameters with greater than, less than, etc. with input binding? For example, in the case of the ToDo table, users can search for orders that are less than 100 but greater than or equal to 10, something along those lines.

MuthuKumaranMurugaachari-MSFT 22,311 Reputation points

2023-09-01T19:59:11.2966667+00:00

Hunter B I think you can try with multiple parameters and add the condition in SQL text accordingly. Let me know if you face any issues.

hampton123 1,165 Reputation points

2023-09-06T14:35:44.3133333+00:00

Hi MuthuKumaranMurugaachari-MSFT I've been trying to input a single query parameter however I'm not sure why it isn't working. Also, the query parameter I pass through my SPA is "schoolNo=1". What should I do? This code works when there is no query parameter.

My code is here:

context.res = { status: 200, headers: { "Content-Type": "application/json" }, body: JSON.stringify(toDoSchools, null, 2) }; }

// function.json { "type": "sql", "direction": "in", "name": "toDoSchools", "commandText": "select * from dbo.TestTable_HB where schoolNo = @schoolNo", "commandType": "Text", "parameters": "@schoolNo = {Query.schoolNo}", "connectionStringSetting": "SqlConnectionString" }
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to present SQL Database data in browser using Azure Function in APIM

1 additional answer

Your answer