Azure functions reading SQL database
As some of you may already know, Azure functions is a serverless service which can be used to expose APIs to do certain tasks. Today we are going to cover an example of fetching data from an Azure SQL using an Azure function API. But you can use the API to pretty much do any CRUD operations on the DB with some minor tweaks. We will use Postman to trigger our API. A high level diagram of our setup is below.
For starters, let’s create our SQL DB.
Sign in to the Azure portal with your Azure account credentials.
In the Azure portal search box, type in “SQL databases” and select SQL databases.
On the SQL Databases page, select “Create SQL database”.
Select your subscription and resource group (or create a new one). I’ve named mine “anuRG1”.
Enter a name for your database. I’ve used “anudb”.
Under server, click the “Create new” button to open the Create server blade.
Enter a unique server name. I’ve named mine “anuserver1”.
Enter an admin userid and password.
Select a location for your server from the drop down and click on OK.
Select the tier of the SQL DB. For our case, we are going to use basic 5DTU with 100MB storage.
Click on Review and Create and finally click on Create.
Once your deployment is complete. Click on “Go to resource”.
Then click on your server name to take you to your SQL server.
On the left hand side pane, under security, click on “Firewalls and virtual networks.
Click on + Add client IP”. This will allow you to log on from your computer. Then click Save.
On the left side pane, under the Settings label, click on “Connection strings”.
Copy the connection string and store it for later use. Do not forget to replace {your_password} with the admin password you had created earlier.
- In this example we are using the admin credentials but normally you would create a separate user within the database itself.
Now let’s go back to our database
Click on Query editor on the left side pane.
Enter the admin user id and password
Let’s create a table for ourselves using the below query.
CREATE TABLE [dbo].[Mytable](
[customerid] [NVARCHAR](15) NULL,
[firstname] [NVARCHAR](MAX) NULL,
[lastname] [NVARCHAR](MAX) NULL,
[email] [NVARCHAR](MAX) NULL
)
Let’s add 1 record into our new table using the below query.
INSERT INTO [dbo].[Mytable] ([customerid], [firstname], [lastname], [email]) VALUES (‘IND0001’, ‘Anupam’, ‘Chand’, ‘myemail@gmail.com’);
So our Table is now ready. Let’s shift our attention to our function app.
Go to the Azure Home page and click on “Create Resource”.
Enter “function” in the search box and click on “Function app” in the drop down list.
Click on Create.
Select your resource group. Enter a function name (I’ve selected anufunchttp).
Set the runtime stack to .NET. Select your region (I’ve selected South India).
Set the other options as shown below.
Click on “Review + Create” Once validation is complete, click “Create” and wait for the deployment to be completed.
*The function app will automatically be created on consumption plan which is the cheapest plan available.
On your new function app, on the left side pane, click on Configuration under Settings.
Add a new application setting called “SqlConnection” and add the value as the connection string of your SQL DB and click save.
This is so your function can connect to your SQL database.
On the left side pane click on “Functions” on the left side pane.
Click on “+Add”. As a template click on “HTTP trigger” and set the Authorization level to “Anonymous”. Click Add.
Now click on Code+Test on the left hand pane.
Azure will automatically create default function code for you to use.
You can upload new files as needed. You can use the drop down to view the different files.
We don’t need to change the function.json but replace the entire run.csx with the code below and click save.
#r “Newtonsoft.Json”
#r “System.Configuration”
using System;
using System.Data;
using System.Net;
using Newtonsoft.Json;
using Dapper;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System.Configuration;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
public static async Task<object> Run(HttpRequestMessage req, ILogger log)
{
string responseMessage;
//We retrieve the id field, which comes as a parameter to the function, by deserializing req.Content.
string jsonContent = await req.Content.ReadAsStringAsync();
dynamic data = JsonConvert.DeserializeObject(jsonContent);
//If there is no username, we return the error message.
if (data.id == null) {
responseMessage = “ERROR!!!Please pass an id.”;
return new OkObjectResult(responseMessage);
}
var connectionString = Environment.GetEnvironmentVariable(“SqlConnection”, EnvironmentVariableTarget.Process);
//Azure SQLDB Log
var logAdded = true;
try
{
//We get the Connection String in the Function App Settings section we defined.
using(SqlConnection connection = new SqlConnection(connectionString))
{
//Opens Azure SQL DB connection.
connection.Open();
string qs = $”SELECT * FROM [dbo].[MYtable] where [customerid] = ‘{data.id}’”;
SqlCommand command = new SqlCommand(qs, connection);
string queryop = “”;
using (SqlDataReader reader = command.ExecuteReader())
{
queryop = sqlDatoToJson(reader);
}
responseMessage = (queryop);
connection.Close();
}
}
catch(Exception e)
{
logAdded = false;
log.LogError(e.ToString());
responseMessage = e.ToString();
// connection.Close();
}
return new OkObjectResult(responseMessage);
}
static String sqlDatoToJson(SqlDataReader dataReader)
// transform the returned data to JSON
{
var dataTable = new DataTable();
dataTable.Load(dataReader);
string JSONString = string.Empty;
JSONString = JsonConvert.SerializeObject(dataTable);
return JSONString;
}
On your desktop, create an empty file called function.proj and upload this into your function using the upload option. In the portal click the function.proj on the drop down and cut paste the below code and click save.
<Project Sdk=”Microsoft.NET.Sdk”>
<PropertyGroup>
<TargetFramework>netstandard2.0</TargetFramework>
</PropertyGroup>
<ItemGroup>
<PackageReference Include=”Dapper” Version=”2.0.78" />
<PackageReference Include=”Microsoft.Data.SqlClient” Version=”2.1.2" />
<PackageReference Include=”Microsoft.WindowsAzure.ConfigurationManager” Version=”3.2.3" />
</ItemGroup>
</Project>
Each time you click save, your function will compile and assuming you don’t have any compilation errors, you should be ready to proceed.
Click on “Get Function Url”.
This will give you the url of your function. It will be something like https://anufunchttp.azurewebsites.net/api/HttpTrigger1?.
Copy this and store it in your note pad. We will now attempt to call this function from postman.
Use the url https://anufunchttp.azurewebsites.net/api/HttpTrigger1?. With the following body.
{
“id”: ‘IND0001’
}
When trying to call the API from Postman, we get the following response.
Any guesses what was the problem here?
As the error suggests, the function IPs are not whitelisted on the SQL DB and hence it is unable to connect. The solution for our test is to whitelist the function IP. However the the IP of the function is actually determined by the IP of the App service and since we are using a consumption plan, this IP can change for future executions. One option is to white list all the IPs for App service in that particular region. But the easier and better option is to allow Azure resources to connect to your SQL Server as shown below.
Don’t forget to click save
Now let’s call the API one more time from Postman.
And Voila! There you have it.
*The function code is such that it will return all the responses if the SQL query hits multiple results.
Hope this test was useful for you to understand how to connect functions to your SQL table. In production you will be taking care of other aspects like security where the connection string will be secured in something like a key vault.
Please feel free to send me queries or comments if you have any.
Until next time, Cheers.