Azure SQL output binding for Azure Functions
The Azure SQL output binding lets you write to a database.
For information on setup and configuration details, see the overview.
Important
This article uses tabs to support multiple versions of the Node.js programming model. The v4 model is generally available and is designed to have a more flexible and intuitive experience for JavaScript and TypeScript developers. For more details about how the v4 model works, refer to the Azure Functions Node.js developer guide. To learn more about the differences between v3 and v4, refer to the migration guide.
A C# function can be created by using one of the following C# modes:
- Isolated worker model: Compiled C# function that runs in a worker process that's isolated from the runtime. Isolated worker process is required to support C# functions running on LTS and non-LTS versions .NET and the .NET Framework.
- In-process model: Compiled C# function that runs in the same process as the Functions runtime.
- C# script: Used primarily when you create C# functions in the Azure portal.
Important
Support will end for the in-process model on November 10, 2026. We highly recommend that you migrate your apps to the isolated worker model for full support.
More samples for the Azure SQL output binding are available in the GitHub repository.
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
);
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(commandText: "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);
}
}
}
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(commandText: "dbo.ToDo", connectionStringSetting: "SqlConnectionString")] IAsyncCollector<ToDoItem> toDoItems,
[Sql(commandText: "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; }
}
}
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(commandText: "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");
}
}
}
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 ToDoItem
class (in a separate file ToDoItem.java
) and a corresponding database table:
package com.function;
import java.util.UUID;
public class ToDoItem {
public UUID Id;
public int order;
public String title;
public String url;
public boolean completed;
public ToDoItem() {
}
public ToDoItem(UUID Id, int order, String title, String url, boolean completed) {
this.Id = Id;
this.order = order;
this.title = title;
this.url = url;
this.completed = completed;
}
}
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
);
The following example shows a SQL output binding in a Java function that adds a record to a table, using data provided in an HTTP POST request as a JSON body. The function takes an additional dependency on the com.google.code.gson library to parse the JSON body.
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.10.1</version>
</dependency>
package com.function;
import java.util.*;
import com.microsoft.azure.functions.annotation.*;
import com.microsoft.azure.functions.*;
import com.microsoft.azure.functions.sql.annotation.SQLOutput;
import com.google.gson.Gson;
import java.util.Optional;
public class PostToDo {
@FunctionName("PostToDo")
public HttpResponseMessage run(
@HttpTrigger(name = "req", methods = {HttpMethod.POST}, authLevel = AuthorizationLevel.ANONYMOUS) HttpRequestMessage<Optional<String>> request,
@SQLOutput(
name = "toDoItem",
commandText = "dbo.ToDo",
connectionStringSetting = "SqlConnectionString")
OutputBinding<ToDoItem> output) {
String json = request.getBody().get();
Gson gson = new Gson();
ToDoItem newToDo = gson.fromJson(json, ToDoItem.class);
newToDo.Id = UUID.randomUUID();
output.setValue(newToDo);
return request.createResponseBuilder(HttpStatus.CREATED).header("Content-Type", "application/json").body(output).build();
}
}
The following example shows a SQL output binding in a JavaS 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 function takes an additional dependency on the com.google.code.gson library to parse the JSON body.
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.10.1</version>
</dependency>
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 DEFAULT(GETDATE()),
ItemCount INT NOT NULL
)
and Java class in RequestLog.java
:
package com.function;
import java.util.Date;
public class RequestLog {
public int Id;
public Date RequestTimeStamp;
public int ItemCount;
public RequestLog() {
}
public RequestLog(int Id, Date RequestTimeStamp, int ItemCount) {
this.Id = Id;
this.RequestTimeStamp = RequestTimeStamp;
this.ItemCount = ItemCount;
}
}
package com.function;
import java.util.*;
import com.microsoft.azure.functions.annotation.*;
import com.microsoft.azure.functions.*;
import com.microsoft.azure.functions.sql.annotation.SQLOutput;
import com.google.gson.Gson;
import java.util.Optional;
public class PostToDoWithLog {
@FunctionName("PostToDoWithLog")
public HttpResponseMessage run(
@HttpTrigger(name = "req", methods = {HttpMethod.POST}, authLevel = AuthorizationLevel.ANONYMOUS) HttpRequestMessage<Optional<String>> request,
@SQLOutput(
name = "toDoItem",
commandText = "dbo.ToDo",
connectionStringSetting = "SqlConnectionString")
OutputBinding<ToDoItem> output,
@SQLOutput(
name = "requestLog",
commandText = "dbo.RequestLog",
connectionStringSetting = "SqlConnectionString")
OutputBinding<RequestLog> outputLog,
final ExecutionContext context) {
context.getLogger().info("Java HTTP trigger processed a request.");
String json = request.getBody().get();
Gson gson = new Gson();
ToDoItem newToDo = gson.fromJson(json, ToDoItem.class);
newToDo.Id = UUID.randomUUID();
output.setValue(newToDo);
RequestLog newLog = new RequestLog();
newLog.ItemCount = 1;
outputLog.setValue(newLog);
return request.createResponseBuilder(HttpStatus.CREATED).header("Content-Type", "application/json").body(output).build();
}
}
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:
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
);
The following example shows a SQL output binding that adds records to a table, using data provided in an HTTP POST request as a JSON body.
import { app, HttpRequest, HttpResponseInit, InvocationContext, output } from '@azure/functions';
const sqlOutput = output.sql({
commandText: 'dbo.ToDo',
connectionStringSetting: 'SqlConnectionString',
});
export async function httpTrigger1(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
context.log('HTTP trigger and SQL output binding function processed a request.');
const body = await request.json();
context.extraOutputs.set(sqlOutput, body);
return { status: 201 };
}
app.http('httpTrigger1', {
methods: ['POST'],
authLevel: 'anonymous',
extraOutputs: [sqlOutput],
handler: httpTrigger1,
});
const { app, output } = require('@azure/functions');
const sqlOutput = output.sql({
commandText: 'dbo.ToDo',
connectionStringSetting: 'SqlConnectionString',
});
app.http('httpTrigger1', {
methods: ['POST'],
authLevel: 'anonymous',
extraOutputs: [sqlOutput],
handler: async (request, context) => {
context.log('HTTP trigger and SQL output binding function processed a request.');
const body = await request.json();
context.extraOutputs.set(sqlOutput, body);
return { status: 201 };
},
});
The following example shows a SQL output binding 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
)
import { app, HttpRequest, HttpResponseInit, InvocationContext, output } from '@azure/functions';
const sqlTodoOutput = output.sql({
commandText: 'dbo.ToDo',
connectionStringSetting: 'SqlConnectionString',
});
const sqlRequestLogOutput = output.sql({
commandText: 'dbo.RequestLog',
connectionStringSetting: 'SqlConnectionString',
});
export async function httpTrigger1(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
context.log('HTTP trigger and SQL output binding function processed a request.');
const newLog = {
RequestTimeStamp: Date.now(),
ItemCount: 1,
};
context.extraOutputs.set(sqlRequestLogOutput, newLog);
const body = await request.json();
context.extraOutputs.set(sqlTodoOutput, body);
return { status: 201 };
}
app.http('httpTrigger1', {
methods: ['POST'],
authLevel: 'anonymous',
extraOutputs: [sqlTodoOutput, sqlRequestLogOutput],
handler: httpTrigger1,
});
const { app, output } = require('@azure/functions');
const sqlTodoOutput = output.sql({
commandText: 'dbo.ToDo',
connectionStringSetting: 'SqlConnectionString',
});
const sqlRequestLogOutput = output.sql({
commandText: 'dbo.RequestLog',
connectionStringSetting: 'SqlConnectionString',
});
app.http('httpTrigger1', {
methods: ['POST'],
authLevel: 'anonymous',
extraOutputs: [sqlTodoOutput, sqlRequestLogOutput],
handler: async (request, context) => {
context.log('HTTP trigger and SQL output binding function processed a request.');
const newLog = {
RequestTimeStamp: Date.now(),
ItemCount: 1,
};
context.extraOutputs.set(sqlRequestLogOutput, newLog);
const body = await request.json();
context.extraOutputs.set(sqlTodoOutput, body);
return { status: 201 };
},
});
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:
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
);
The following example shows a SQL output binding in a function.json file and a PowerShell 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 PowerShell code for the function in the run.ps1
file:
```powershell
using namespace System.Net
param($Request)
Write-Host "PowerShell function with SQL Output Binding processed a request."
# Update req_body with the body of the request
$req_body = $Request.Body
# Assign the value we want to pass to the SQL Output binding.
# The -Name value corresponds to the name property in the function.json for the binding
Push-OutputBinding -Name todoItems -Value $req_body
Push-OutputBinding -Name res -Value ([HttpResponseContext]@{
StatusCode = [HttpStatusCode]::OK
Body = $req_body
})
The following example shows a SQL output binding in a function.json file and a PowerShell 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 PowerShell code for the function in the run.ps1
file:
using namespace System.Net
param($Request)
Write-Host "PowerShell function with SQL Output Binding processed a request."
# Update req_body with the body of the request
$req_body = $Request.Body
$new_log = @{
RequestTimeStamp = [DateTime]::Now
ItemCount = 1
}
Push-OutputBinding -Name todoItems -Value $req_body
Push-OutputBinding -Name requestLog -Value $new_log
Push-OutputBinding -Name res -Value ([HttpResponseContext]@{
StatusCode = [HttpStatusCode]::OK
Body = $req_body
})
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:
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
);
The following example shows a SQL output 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 sample python code for the function_app.py file:
import json
import logging
import azure.functions as func
from azure.functions.decorators.core import DataType
app = func.FunctionApp()
@app.function_name(name="AddToDo")
@app.route(route="addtodo")
@app.sql_output(arg_name="todo",
command_text="[dbo].[ToDo]",
connection_string_setting="SqlConnectionString")
def add_todo(req: func.HttpRequest, todo: func.Out[func.SqlRow]) -> func.HttpResponse:
body = json.loads(req.get_body())
row = func.SqlRow.from_dict(body)
todo.set(row)
return func.HttpResponse(
body=req.get_body(),
status_code=201,
mimetype="application/json"
)
The following example shows a SQL output 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 sample python code for the function_app.py file:
from datetime import datetime
import json
import logging
import azure.functions as func
app = func.FunctionApp()
@app.function_name(name="PostToDo")
@app.route(route="posttodo")
@app.sql_output(arg_name="todoItems",
command_text="[dbo].[ToDo]",
connection_string_setting="SqlConnectionString")
@app.sql_output(arg_name="requestLog",
command_text="[dbo].[RequestLog]",
connection_string_setting="SqlConnectionString")
def add_todo(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 = func.SqlRowList(map(lambda r: func.SqlRow.from_dict(r), req_body))
except ValueError:
pass
requestLog.set(func.SqlRow({
"RequestTimeStamp": datetime.now().isoformat(),
"ItemCount": 1
}))
if req_body:
todoItems.set(rows)
return func.HttpResponse(
"OK",
status_code=201,
mimetype="application/json"
)
else:
return func.HttpResponse(
"Error accessing request body",
status_code=400
)
The C# library uses the SqlAttribute attribute to declare the SQL bindings on the function, 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. |
In the Java functions runtime library, use the @SQLOutput
annotation (com.microsoft.azure.functions.sql.annotation.SQLOutput
) on parameters whose value would come from Azure SQL. This annotation supports the following elements:
Element | 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. |
name | Required. The unique name of the function binding. |
The following table explains the properties that you can set on the options
object passed to the output.sql()
method.
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. Optional keywords in the connection string value are available to refine SQL bindings connectivity. |
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.
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 use the T-SQL MERGE statement which requires SELECT permissions on the target database.
If an exception occurs when a SQL output binding is executed then the function code stop executing. This may result in an error code being returned, such as an HTTP trigger returning a 500 error code. If the IAsyncCollector
is used in a .NET function then the function code can handle exceptions throw by the call to FlushAsync()
.