Skip to content

Files

Latest commit

dzsquareddzsquared
fix warnings
Jun 1, 2022
cb6d416 · Jun 1, 2022

History

History
554 lines (426 loc) · 17.5 KB

functions-bindings-azure-sql-output.md

File metadata and controls

554 lines (426 loc) · 17.5 KB
title description author ms.topic ms.custom ms.date ms.author ms.reviewer zone_pivot_groups
Azure SQL output binding for Functions
Learn to use the Azure SQL output binding in Azure Functions.
dzsquared
reference
event-tier1-build-2022
5/24/2022
drskwier
glenga
programming-languages-set-functions-lang-workers

Azure SQL output binding for Azure Functions (preview)

The Azure SQL output binding lets you write to a database.

For information on setup and configuration details, see the overview.

Examples

::: zone pivot="programming-language-csharp"

More samples for the Azure SQL output binding are available in the GitHub repository.

In-process

This section contains the following examples:

The examples refer to a ToDoItem class and a corresponding database table:

:::code language="csharp" source="~/functions-sql-todo-sample/ToDoModel.cs" range="6-14":::

:::code language="sql" source="~/functions-sql-todo-sample/sql/create.sql" range="1-7":::

HTTP trigger, write one record

The following example shows a C# function that adds a record to a database, using data provided in an HTTP POST request as a JSON body.

:::code language="csharp" source="~/functions-sql-todo-sample/PostToDo.cs" range="4-49":::

HTTP trigger, write to two tables

The following example shows a C# function that adds records to a database in two different tables (dbo.ToDo and dbo.RequestLog), using data provided in an HTTP POST request as a JSON body and multiple output bindings.

CREATE TABLE dbo.RequestLog (
    Id int identity(1,1) primary key,
    RequestTimeStamp datetime2 not null,
    ItemCount int not null
)
namespace AzureSQL.ToDo
{
    public static class PostToDo
    {
        // create a new ToDoItem from body object
        // uses output binding to insert new item into ToDo table
        [FunctionName("PostToDo")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "PostFunction")] HttpRequest req,
            ILogger log,
            [Sql("dbo.ToDo", ConnectionStringSetting = "SqlConnectionString")] IAsyncCollector<ToDoItem> toDoItems,
            [Sql("dbo.RequestLog", ConnectionStringSetting = "SqlConnectionString")] IAsyncCollector<RequestLog> requestLogs)
        {
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            ToDoItem toDoItem = JsonConvert.DeserializeObject<ToDoItem>(requestBody);

            // generate a new id for the todo item
            toDoItem.Id = Guid.NewGuid();

            // set Url from env variable ToDoUri
            toDoItem.url = Environment.GetEnvironmentVariable("ToDoUri")+"?id="+toDoItem.Id.ToString();

            // if completed is not provided, default to false
            if (toDoItem.completed == null)
            {
                toDoItem.completed = false;
            }

            await toDoItems.AddAsync(toDoItem);
            await toDoItems.FlushAsync();
            List<ToDoItem> toDoItemList = new List<ToDoItem> { toDoItem };

            requestLog = new RequestLog();
            requestLog.RequestTimeStamp = DateTime.Now;
            requestLog.ItemCount = 1;
            await requestLogs.AddAsync(requestLog);
            await requestLogs.FlushAsync();

            return new OkObjectResult(toDoItemList);
        }
    }

    public class RequestLog {
        public DateTime RequestTimeStamp { get; set; }
        public int ItemCount { get; set; }
    }
}

HTTP trigger, write records using IAsyncCollector

The following example shows a C# function that adds a collection of records to a database, using data provided in an HTTP POST body JSON array.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Newtonsoft.Json;
using System.IO;
using System.Threading.Tasks;

namespace AzureSQLSamples
{
    public static class WriteRecordsAsync
    {
        [FunctionName("WriteRecordsAsync")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "addtodo-asynccollector")]
            HttpRequest req,
            [Sql("dbo.ToDo", ConnectionStringSetting = "SqlConnectionString")] IAsyncCollector<ToDoItem> newItems)
        {
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            var incomingItems = JsonConvert.DeserializeObject<ToDoItem[]>(requestBody);
            foreach (ToDoItem newItem in incomingItems)
            {
                await newItems.AddAsync(newItem);
            }
            // Rows are upserted here
            await newItems.FlushAsync();

            return new CreatedResult($"/api/addtodo-asynccollector", "done");
        }
    }
}

Isolated process

Isolated process isn't currently supported.

::: zone-end

::: zone pivot="programming-language-java,programming-language-powershell"

Note

In the current preview, Azure SQL bindings are only supported by C# class library functions, JavaScript functions, and Python functions.

::: zone-end

::: zone pivot="programming-language-javascript"

More samples for the Azure SQL output binding are available in the GitHub repository.

This section contains the following examples:

The examples refer to a database table:

:::code language="sql" source="~/functions-sql-todo-sample/sql/create.sql" range="1-7":::

HTTP trigger, write records to a table

The following example shows a SQL input binding in a function.json file and a JavaScript function that adds records to a table, using data provided in an HTTP POST request as a JSON body.

The following is binding data in the function.json file:

{
    "authLevel": "anonymous",
    "type": "httpTrigger",
    "direction": "in",
    "name": "req",
    "methods": [
        "post"
    ]
},
{
    "type": "http",
    "direction": "out",
    "name": "res"
},
{
    "name": "todoItems",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.ToDo",
    "connectionStringSetting": "SqlConnectionString"
}

The configuration section explains these properties.

The following is sample JavaScript code:

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger and SQL output binding function processed a request.');
    context.log(req.body);

    if (req.body) {
        context.bindings.todoItems = req.body;
        context.res = {
            body: req.body,
            mimetype: "application/json",
            status: 201
        }
    } else {
        context.res = {
            status: 400,
            body: "Error reading request body"
        }
    }
}

HTTP trigger, write to two tables

The following example shows a SQL input binding in a function.json file and a JavaScript function that adds records to a database in two different tables (dbo.ToDo and dbo.RequestLog), using data provided in an HTTP POST request as a JSON body and multiple output bindings.

The second table, dbo.RequestLog, corresponds to the following definition:

CREATE TABLE dbo.RequestLog (
    Id int identity(1,1) primary key,
    RequestTimeStamp datetime2 not null,
    ItemCount int not null
)

The following is binding data in the function.json file:

{
    "authLevel": "anonymous",
    "type": "httpTrigger",
    "direction": "in",
    "name": "req",
    "methods": [
        "post"
    ]
},
{
    "type": "http",
    "direction": "out",
    "name": "res"
},
{
    "name": "todoItems",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.ToDo",
    "connectionStringSetting": "SqlConnectionString"
},
{
    "name": "requestLog",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.RequestLog",
    "connectionStringSetting": "SqlConnectionString"
}

The configuration section explains these properties.

The following is sample JavaScript code:

module.exports = async function (context, req) {
    context.log('JavaScript HTTP trigger and SQL output binding function processed a request.');
    context.log(req.body);

    const newLog = {
        RequestTimeStamp = Date.now(),
        ItemCount = 1
    }

    if (req.body) {
        context.bindings.todoItems = req.body;
        context.bindings.requestLog = newLog;
        context.res = {
            body: req.body,
            mimetype: "application/json",
            status: 201
        }
    } else {
        context.res = {
            status: 400,
            body: "Error reading request body"
        }
    }
}

::: zone-end
::: zone pivot="programming-language-python"

More samples for the Azure SQL output binding are available in the GitHub repository.

This section contains the following examples:

The examples refer to a database table:

:::code language="sql" source="~/functions-sql-todo-sample/sql/create.sql" range="1-7":::

HTTP trigger, write records to a table

The following example shows a SQL input binding in a function.json file and a Python function that adds records to a table, using data provided in an HTTP POST request as a JSON body.

The following is binding data in the function.json file:

{
    "authLevel": "anonymous",
    "type": "httpTrigger",
    "direction": "in",
    "name": "req",
    "methods": [
        "post"
    ]
},
{
    "type": "http",
    "direction": "out",
    "name": "$return"
},
{
    "name": "todoItems",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.ToDo",
    "connectionStringSetting": "SqlConnectionString"
}

The configuration section explains these properties.

The following is sample Python code:

import logging
import azure.functions as func


def main(req: func.HttpRequest, todoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
    logging.info('Python HTTP trigger and SQL output binding function processed a request.')

    try:
        req_body = req.get_json()
        rows = list(map(lambda r: json.loads(r.to_json()), req_body))
    except ValueError:
        pass

    if req_body:
        todoItems.set(rows)
        return func.HttpResponse(
            todoItems.to_json(),
            status_code=201,
            mimetype="application/json"
        )
    else:
        return func.HttpResponse(
            "Error accessing request body",
            status_code=400
        )

HTTP trigger, write to two tables

The following example shows a SQL input binding in a function.json file and a Python function that adds records to a database in two different tables (dbo.ToDo and dbo.RequestLog), using data provided in an HTTP POST request as a JSON body and multiple output bindings.

The second table, dbo.RequestLog, corresponds to the following definition:

CREATE TABLE dbo.RequestLog (
    Id int identity(1,1) primary key,
    RequestTimeStamp datetime2 not null,
    ItemCount int not null
)

The following is binding data in the function.json file:

{
    "authLevel": "anonymous",
    "type": "httpTrigger",
    "direction": "in",
    "name": "req",
    "methods": [
        "post"
    ]
},
{
    "type": "http",
    "direction": "out",
    "name": "$return"
},
{
    "name": "todoItems",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.ToDo",
    "connectionStringSetting": "SqlConnectionString"
},
{
    "name": "requestLog",
    "type": "sql",
    "direction": "out",
    "commandText": "dbo.RequestLog",
    "connectionStringSetting": "SqlConnectionString"
}

The configuration section explains these properties.

The following is sample Python code:

import logging
from datetime import datetime
import azure.functions as func


def main(req: func.HttpRequest, todoItems: func.Out[func.SqlRow], requestLog: func.Out[func.SqlRow]) -> func.HttpResponse:
    logging.info('Python HTTP trigger and SQL output binding function processed a request.')

    try:
        req_body = req.get_json()
        rows = list(map(lambda r: json.loads(r.to_json()), req_body))
    except ValueError:
        pass

    requestLog.set(func.SqlRow({
        RequestTimeStamp: datetime.now(),
        ItemCount: 1
    }))

    if req_body:
        todoItems.set(rows)
        return func.HttpResponse(
            todoItems.to_json(),
            status_code=201,
            mimetype="application/json"
        )
    else:
        return func.HttpResponse(
            "Error accessing request body",
            status_code=400
        )

::: zone-end

::: zone pivot="programming-language-csharp"

Attributes

In C# class libraries, use the Sql attribute, which has the following properties:

Attribute property Description
CommandText Required. The name of the table being written to by the binding.
ConnectionStringSetting Required. The name of an app setting that contains the connection string for the database to which data is being written. This isn't the actual connection string and must instead resolve to an environment variable.

::: zone-end

::: zone pivot="programming-language-javascript,programming-language-python"

Configuration

The following table explains the binding configuration properties that you set in the function.json file.

function.json property Description
type Required. Must be set to sql.
direction Required. Must be set to out.
name Required. The name of the variable that represents the entity in function code.
commandText Required. The name of the table being written to by the binding.
connectionStringSetting Required. The name of an app setting that contains the connection string for the database to which data is being written. This isn't the actual connection string and must instead resolve to an environment variable. Optional keywords in the connection string value are available to refine SQL bindings connectivity.

::: zone-end

[!INCLUDE app settings to local.settings.json]

Usage

::: zone pivot="programming-language-csharp,programming-language-javascript,programming-language-python" The CommandText property is the name of the table where the data is to be stored. The connection string setting name corresponds to the application setting that contains the connection string to the Azure SQL or SQL Server instance.

::: zone-end

Next steps