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

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

In-process

This section contains the following examples:

• HTTP trigger, write one record
• HTTP trigger, write to two tables
• HTTP trigger, write records using IAsyncCollector

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

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; }
    }
}

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
);

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.

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

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)
        {
            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 };

            return new OkObjectResult(toDoItemList);
        }
    }
}

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.

Note

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

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

This section contains the following examples:

• HTTP trigger, write records to a table
• HTTP trigger, write to two tables

The examples refer to a database table:

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
);

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"
        }
    }
}

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

This section contains the following examples:

• HTTP trigger, write records to a table
• HTTP trigger, write to two tables

The examples refer to a database table:

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
);

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
        )

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.

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.

When you're developing locally, add your application settings in the local.settings.json file in the Values collection.

Usage

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.

The output bindings uses the T-SQL MERGE statement which requires SELECT permissions on the target database.

Next steps

• Read data from a database (Input binding)
• Review ToDo API sample with Azure SQL bindings