Connect Azure Functions to Azure SQL Database using Visual Studio Code

Article
04/25/2024

Azure Functions lets you connect Azure services and other resources to functions without having to write your own integration code. These bindings, which represent both input and output, are declared within the function definition. Data from bindings is provided to the function as parameters. A trigger is a special type of input binding. Although a function has only one trigger, it can have multiple input and output bindings. To learn more, see Azure Functions triggers and bindings concepts.

This article shows you how to use Visual Studio Code to connect Azure SQL Database to the function you created in the previous quickstart article. The output binding that you add to this function writes data from the HTTP request to a table in Azure SQL Database.

Before you begin, you must complete the quickstart: Create a C# function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

Before you begin, you must complete the quickstart: Create a JavaScript function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

Before you begin, you must complete the quickstart: Create a Python function in Azure using Visual Studio Code. If you already cleaned up resources at the end of that article, go through the steps again to recreate the function app and related resources in Azure.

More details on the settings for Azure SQL bindings and trigger for Azure Functions are available in the Azure Functions documentation.

Create your Azure SQL Database

Follow the Azure SQL Database create quickstart to create a serverless Azure SQL Database. The database can be empty or created from the sample dataset AdventureWorksLT.

Provide the following information at the prompts:

Prompt	Selection
Resource group	Choose the resource group where you created your function app in the previous article.
Database name	Enter `mySampleDatabase`.
Server name	Enter a unique name for your server. We can't provide an exact server name to use because server names must be globally unique for all servers in Azure, not just unique within a subscription.
Authentication method	Select SQL Server authentication.
Server admin login	Enter `azureuser`.
Password	Enter a password that meets the complexity requirements.
Allow Azure services and resources to access this server	Select Yes.

Once the creation has completed, navigate to the database blade in the Azure portal, and, under Settings, select Connection strings. Copy the ADO.NET connection string for SQL authentication. Paste the connection string into a temporary document for later use.
Create a table to store the data from the HTTP request. In the Azure portal, navigate to the database blade and select Query editor. Enter the following query to create a table named dbo.ToDo:
```
CREATE TABLE dbo.ToDo (
    [Id] UNIQUEIDENTIFIER PRIMARY KEY,
    [order] INT NULL,
    [title] NVARCHAR(200) NOT NULL,
    [url] NVARCHAR(200) NOT NULL,
    [completed] BIT NOT NULL
);
```
Verify that your Azure Function will be able to access the Azure SQL Database by checking the server's firewall settings. Navigate to the server blade on the Azure portal, and under Security, select Networking. The exception for Allow Azure services and resources to access this server should be checked.

Update your function app settings

In the previous quickstart article, you created a function app in Azure. In this article, you update your app to write data to the Azure SQL Database you've just created. To connect to your Azure SQL Database, you must add its connection string to your app settings. You then download the new setting to your local.settings.json file so you can connect to your Azure SQL Database when running locally.

Edit the connection string in the temporary document you created earlier. Replace the value of Password with the password you used when creating the Azure SQL Database. Copy the updated connection string.
Press Ctrl/Cmd+shift+P to open the command palette, then search for and run the command Azure Functions: Add New Setting....
Choose the function app you created in the previous article. Provide the following information at the prompts:

Prompt Selection

Enter new app setting name Type SqlConnectionString.

Enter value for "SqlConnectionString" Paste the connection string of your Azure SQL Database you just copied.

This creates an application setting named connection SqlConnectionString in your function app in Azure. Now, you can download this setting to your local.settings.json file.
Press Ctrl/Cmd+shift+P again to open the command palette, then search for and run the command Azure Functions: Download Remote Settings....
Choose the function app you created in the previous article. Select Yes to all to overwrite the existing local settings.

Prompt	Selection
Enter new app setting name	Type `SqlConnectionString`.
Enter value for "SqlConnectionString"	Paste the connection string of your Azure SQL Database you just copied.

This downloads all of the setting from Azure to your local project, including the new connection string setting. Most of the downloaded settings aren't used when running locally.

Register binding extensions

Because you're using an Azure SQL output binding, you must have the corresponding bindings extension installed before you run the project.

With the exception of HTTP and timer triggers, bindings are implemented as extension packages. Run the following dotnet add package command in the Terminal window to add the Azure SQL extension package to your project.

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Sql

Your project has been configured to use extension bundles, which automatically installs a predefined set of extension packages.

Extension bundles usage is enabled in the host.json file at the root of the project, which appears as follows:

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": true,
        "excludedTypes": "Request"
      }
    }
  },
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  },
  "concurrency": {
    "dynamicConcurrencyEnabled": true,
    "snapshotPersistenceEnabled": true
  }
}

:::

Now, you can add the Azure SQL output binding to your project.

Add an output binding

In Functions, each type of binding requires a direction, type, and a unique name to be defined in the function.json file. The way you define these attributes depends on the language of your function app.

Open the HttpExample.cs project file and add the following ToDoItem class, which defines the object that is written to the database:

namespace AzureSQL.ToDo
{
    public class ToDoItem
    {
        public Guid Id { get; set; }
        public int? order { get; set; }
        public string title { get; set; }
        public string url { get; set; }
        public bool? completed { get; set; }
    }
}

In a C# class library project, the bindings are defined as binding attributes on the function method. The function.json file required by Functions is then auto-generated based on these attributes.

Open the HttpExample.cs project file and add the following output type class, which defines the combined objects that will be output from our function for both the HTTP response and the SQL output:

public static class OutputType
{
    [SqlOutput("dbo.ToDo", connectionStringSetting: "SqlConnectionString")]
    public ToDoItem ToDoItem { get; set; }
    public HttpResponseData HttpResponse { get; set; }
}

Add a using statement to the Microsoft.Azure.Functions.Worker.Extensions.Sql library to the top of the file:

using Microsoft.Azure.Functions.Worker.Extensions.Sql;

Binding attributes are defined directly in your code. The Azure SQL output configuration describes the fields required for an Azure SQL output binding.

For this MultiResponse scenario, you need to add an extraOutputs output binding to the function.

app.http('HttpExample', {
  methods: ['GET', 'POST'],
  extraOutputs: [sendToSql],
  handler: async (request, context) => {

Add the following properties to the binding configuration:

const sendToSql = output.sql({
  commandText: 'dbo.ToDo',
  connectionStringSetting: 'SqlConnectionString',
});

Binding attributes are defined directly in the function_app.py file. You use the generic_output_binding decorator to add an Azure SQL output binding:

@app.generic_output_binding(arg_name="toDoItems", type="sql", CommandText="dbo.ToDo", ConnectionStringSetting="SqlConnectionString"
    data_type=DataType.STRING)

In this code, arg_name identifies the binding parameter referenced in your code, type denotes the output binding is a SQL output binding, CommandText is the table that the binding writes to, and ConnectionStringSetting is the name of an application setting that contains the Azure SQL connection string. The connection string is in the SqlConnectionString setting in the local.settings.json file.

Add code that uses the output binding

Replace the existing Run method with the following code:

[Function("HttpExample")]
public static OutputType Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req,
    FunctionContext executionContext)
{
    var logger = executionContext.GetLogger("HttpExample");
    logger.LogInformation("C# HTTP trigger function processed a request.");

    var message = "Welcome to Azure Functions!";

    var response = req.CreateResponse(HttpStatusCode.OK);
    response.Headers.Add("Content-Type", "text/plain; charset=utf-8");
    response.WriteString(message);

    // Return a response to both HTTP trigger and Azure SQL output binding.
    return new OutputType()
    {
         ToDoItem = new ToDoItem
        {
            id = System.Guid.NewGuid().ToString(),
            title = message,
            completed = false,
            url = ""
        },
        HttpResponse = response
    };
}

Add code that uses the extraInputs output binding object on context to send a JSON document to the named output binding function, sendToSql. Add this code before the return statement.

const data = JSON.stringify([
  {
    // create a random ID
    Id: crypto.randomUUID(),
    title: name,
    completed: false,
    url: '',
  },
]);

// Output to Database
context.extraOutputs.set(sendToSql, data);

To utilize the crypto module, add the following line to the top of the file:

const crypto = require("crypto");

At this point, your function should look as follows:

const { app, output } = require('@azure/functions');
const crypto = require('crypto');

const sendToSql = output.sql({
  commandText: 'dbo.ToDo',
  connectionStringSetting: 'SqlConnectionString',
});

app.http('HttpExample', {
  methods: ['GET', 'POST'],
  extraOutputs: [sendToSql],
  handler: async (request, context) => {
    try {
      context.log(`Http function processed request for url "${request.url}"`);

      const name = request.query.get('name') || (await request.text());

      if (!name) {
        return { status: 404, body: 'Missing required data' };
      }

      // Stringified array of objects to be inserted into the database
      const data = JSON.stringify([
        {
          // create a random ID
          Id: crypto.randomUUID(),
          title: name,
          completed: false,
          url: '',
        },
      ]);

      // Output to Database
      context.extraOutputs.set(sendToSql, data);

      const responseMessage = name
        ? 'Hello, ' +
          name +
          '. This HTTP triggered function executed successfully.'
        : 'This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response.';

      // Return to HTTP client
      return { body: responseMessage };
    } catch (error) {
      context.log(`Error: ${error}`);
      return { status: 500, body: 'Internal Server Error' };
    }
  },
});

Update HttpExample\function_app.py to match the following code. Add the toDoItems parameter to the function definition and toDoItems.set() under the if name: statement:

import azure.functions as func
import logging
from azure.functions.decorators.core import DataType

app = func.FunctionApp()

@app.function_name(name="HttpTrigger1")
@app.route(route="hello", auth_level=func.AuthLevel.ANONYMOUS)
@app.generic_output_binding(arg_name="toDoItems", type="sql", CommandText="dbo.ToDo", ConnectionStringSetting="SqlConnectionString"
    data_type=DataType.STRING)
def test_function(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
     logging.info('Python HTTP trigger function processed a request.')
     name = req.params.get('name')
     if not name:
        try:
            req_body = req.get_json()
        except ValueError:
            pass
        else:
            name = req_body.get('name')

     if name:
        toDoItems.set(func.SqlRow({"id": uuid.uuid4(), "title": name, "completed": false, url: ""}))
        return func.HttpResponse(f"Hello {name}!")
     else:
        return func.HttpResponse(
                    "Please pass a name on the query string or in the request body",
                    status_code=400
                )

Run the function locally

Visual Studio Code integrates with Azure Functions Core tools to let you run this project on your local development computer before you publish to Azure. If you don't already have Core Tools installed locally, you are prompted to install it the first time you run your project.

To call your function, press F5 to start the function app project. The Terminal panel displays the output from Core Tools. Your app starts in the Terminal panel. You can see the URL endpoint of your HTTP-triggered function running locally.

If you don't already have Core Tools installed, select Install to install Core Tools when prompted to do so.
If you have trouble running on Windows, make sure that the default terminal for Visual Studio Code isn't set to WSL Bash.
With the Core Tools running, go to the Azure: Functions area. Under Functions, expand Local Project > Functions. Right-click (Windows) or Ctrl - click (macOS) the HttpExample function and choose Execute Function Now....
In the Enter request body, press Enter to send a request message to your function.
When the function executes locally and returns a response, a notification is raised in Visual Studio Code. Information about the function execution is shown in the Terminal panel.
Press Ctrl + C to stop Core Tools and disconnect the debugger.

Run the function locally

As in the previous article, press F5 to start the function app project and Core Tools.
With Core Tools running, go to the Azure: Functions area. Under Functions, expand Local Project > Functions. Right-click (Ctrl-click on Mac) the HttpExample function and choose Execute Function Now....
In Enter request body you see the request message body value of { "name": "Azure" }. Press Enter to send this request message to your function.
After a response is returned, press Ctrl + C to stop Core Tools.

Verify that information has been written to the database

On the Azure portal, go back to your Azure SQL Database and select Query editor.
Connect to your database and expand the Tables node in object explorer on the left. Right-click on the dbo.ToDo table and select Select Top 1000 Rows.
Verify that the new information has been written to the database by the output binding.

Redeploy and verify the updated app

In Visual Studio Code, press F1 to open the command palette. In the command palette, search for and select Azure Functions: Deploy to function app....
Choose the function app that you created in the first article. Because you're redeploying your project to the same app, select Deploy to dismiss the warning about overwriting files.
After deployment completes, you can again use the Execute Function Now... feature to trigger the function in Azure.
Again check the data written to your Azure SQL Database to verify that the output binding again generates a new JSON document.

Clean up resources

In Azure, resources refer to function apps, functions, storage accounts, and so forth. They're grouped into resource groups, and you can delete everything in a group by deleting the group.

You created resources to complete these quickstarts. You may be billed for these resources, depending on your account status and service pricing. If you don't need the resources anymore, here's how to delete them:

In Visual Studio Code, press F1 to open the command palette. In the command palette, search for and select Azure: Open in portal.
Choose your function app and press Enter. The function app page opens in the Azure portal.
In the Overview tab, select the named link next to Resource group.
On the Resource group page, review the list of included resources, and verify that they're the ones you want to delete.
Select Delete resource group, and follow the instructions.

Deletion may take a couple of minutes. When it's done, a notification appears for a few seconds. You can also select the bell icon at the top of the page to view the notification.

Next steps

You've updated your HTTP triggered function to write data to Azure SQL Database. Now you can learn more about developing Functions using Visual Studio Code:

Connect Azure Functions to Azure SQL Database using Visual Studio Code

Create your Azure SQL Database

Update your function app settings

Register binding extensions

Add an output binding

Add code that uses the output binding

Run the function locally

Run the function locally

Verify that information has been written to the database

Redeploy and verify the updated app

Clean up resources

Next steps

Feedback

Additional resources