functions-bindings-azure-sql-output.md

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.	JetterMcTedder	reference	build-2023, devx-track-extended-java, devx-track-js, devx-track-python, devx-track-ts	6/26/2024	bspendolini	glenga	programming-languages-set-functions

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.

::: zone pivot="programming-language-javascript,programming-language-typescript" [!INCLUDE functions-nodejs-model-tabs-description] ::: zone-end

Examples

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

[!INCLUDE functions-bindings-csharp-intro-with-csx]

[!INCLUDE functions-in-process-model-retirement-note]

Isolated worker model

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

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

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

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

To return multiple output bindings in our samples, we'll create a custom return type:

publicstaticclassOutputType{[SqlOutput("dbo.ToDo",connectionStringSetting:"SqlConnectionString")]publicstaticToDoItemToDoItem{get;set;}publicstaticHttpResponseDataHttpResponse{get;set;}}

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. The return object is the OutputType class we created to handle both an HTTP response and the SQL output binding.

usingSystem;usingSystem.Collections.Generic;usingSystem.IO;usingSystem.Threading.Tasks;usingMicrosoft.Azure.Functions.Worker.Extensions.Sql;usingMicrosoft.Azure.Functions.Worker;usingMicrosoft.Azure.Functions.Worker.Http;usingMicrosoft.Extensions.Logging;usingNewtonsoft.Json;namespaceAzureSQL.ToDo{publicstaticclassPostToDo{// create a new ToDoItem from body object// uses output binding to insert new item into ToDo table[FunctionName("PostToDo")]publicstaticasyncTask<OutputType>Run([HttpTrigger(AuthorizationLevel.Anonymous,"post",Route="PostFunction")]HttpRequestDatareq,FunctionContextexecutionContext){varlogger=executionContext.GetLogger("PostToDo");logger.LogInformation("C# HTTP trigger function processed a request.");stringrequestBody=awaitnewStreamReader(req.Body).ReadToEndAsync();ToDoItemtoDoItem=JsonConvert.DeserializeObject<ToDoItem>(requestBody);// generate a new id for the todo itemtoDoItem.Id=Guid.NewGuid();// set Url from env variable ToDoUritoDoItem.url=Environment.GetEnvironmentVariable("ToDoUri")+"?id="+toDoItem.Id.ToString();// if completed is not provided, default to falseif(toDoItem.completed==null){toDoItem.completed=false;}returnnewOutputType(){ToDoItem=toDoItem,HttpResponse=req.CreateResponse(System.Net.HttpStatusCode.Created)}}}publicstaticclassOutputType{[SqlOutput("dbo.ToDo",connectionStringSetting:"SqlConnectionString")]publicToDoItemToDoItem{get;set;}publicHttpResponseDataHttpResponse{get;set;}}}

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.

CREATETABLEdbo.RequestLog ( Id int identity(1,1) primary key, RequestTimeStamp datetime2 not null, ItemCount intnot null )

To use an additional output binding, we add a class for RequestLog and modify our OutputType class:

usingSystem;usingSystem.Collections.Generic;usingSystem.IO;usingSystem.Threading.Tasks;usingMicrosoft.Azure.Functions.Worker.Extensions.Sql;usingMicrosoft.Azure.Functions.Worker;usingMicrosoft.Azure.Functions.Worker.Http;usingMicrosoft.Extensions.Logging;usingNewtonsoft.Json;namespaceAzureSQL.ToDo{publicstaticclassPostToDo{// create a new ToDoItem from body object// uses output binding to insert new item into ToDo table[FunctionName("PostToDo")]publicstaticasyncTask<OutputType>Run([HttpTrigger(AuthorizationLevel.Anonymous,"post",Route="PostFunction")]HttpRequestDatareq,FunctionContextexecutionContext){stringrequestBody=awaitnewStreamReader(req.Body).ReadToEndAsync();ToDoItemtoDoItem=JsonConvert.DeserializeObject<ToDoItem>(requestBody);// generate a new id for the todo itemtoDoItem.Id=Guid.NewGuid();// set Url from env variable ToDoUritoDoItem.url=Environment.GetEnvironmentVariable("ToDoUri")+"?id="+toDoItem.Id.ToString();// if completed is not provided, default to falseif(toDoItem.completed==null){toDoItem.completed=false;}requestLog=newRequestLog();requestLog.RequestTimeStamp=DateTime.Now;requestLog.ItemCount=1;returnnewOutputType(){ToDoItem=toDoItem,RequestLog=requestLog,HttpResponse=req.CreateResponse(System.Net.HttpStatusCode.Created)}}}publicclassRequestLog{publicDateTimeRequestTimeStamp{get;set;}publicintItemCount{get;set;}}publicstaticclassOutputType{[SqlOutput("dbo.ToDo",connectionStringSetting:"SqlConnectionString")]publicToDoItemToDoItem{get;set;}[SqlOutput("dbo.RequestLog",connectionStringSetting:"SqlConnectionString")]publicRequestLogRequestLog{get;set;}publicHttpResponseDataHttpResponse{get;set;}}}

In-process model

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:

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

:::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.

CREATETABLEdbo.RequestLog ( Id int identity(1,1) primary key, RequestTimeStamp datetime2 not null, ItemCount intnot null )

namespaceAzureSQL.ToDo{publicstaticclassPostToDo{// create a new ToDoItem from body object// uses output binding to insert new item into ToDo table[FunctionName("PostToDo")]publicstaticasyncTask<IActionResult>Run([HttpTrigger(AuthorizationLevel.Anonymous,"post",Route="PostFunction")]HttpRequestreq,ILoggerlog,[Sql(commandText:"dbo.ToDo",connectionStringSetting:"SqlConnectionString")]IAsyncCollector<ToDoItem>toDoItems,[Sql(commandText:"dbo.RequestLog",connectionStringSetting:"SqlConnectionString")]IAsyncCollector<RequestLog>requestLogs){stringrequestBody=awaitnewStreamReader(req.Body).ReadToEndAsync();ToDoItemtoDoItem=JsonConvert.DeserializeObject<ToDoItem>(requestBody);// generate a new id for the todo itemtoDoItem.Id=Guid.NewGuid();// set Url from env variable ToDoUritoDoItem.url=Environment.GetEnvironmentVariable("ToDoUri")+"?id="+toDoItem.Id.ToString();// if completed is not provided, default to falseif(toDoItem.completed==null){toDoItem.completed=false;}awaittoDoItems.AddAsync(toDoItem);awaittoDoItems.FlushAsync();List<ToDoItem>toDoItemList=newList<ToDoItem>{toDoItem};requestLog=newRequestLog();requestLog.RequestTimeStamp=DateTime.Now;requestLog.ItemCount=1;awaitrequestLogs.AddAsync(requestLog);awaitrequestLogs.FlushAsync();returnnewOkObjectResult(toDoItemList);}}publicclassRequestLog{publicDateTimeRequestTimeStamp{get;set;}publicintItemCount{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.

usingMicrosoft.AspNetCore.Http;usingMicrosoft.AspNetCore.Mvc;usingMicrosoft.Azure.WebJobs;usingMicrosoft.Azure.WebJobs.Extensions.Http;usingNewtonsoft.Json;usingSystem.IO;usingSystem.Threading.Tasks;namespaceAzureSQLSamples{publicstaticclassWriteRecordsAsync{[FunctionName("WriteRecordsAsync")]publicstaticasyncTask<IActionResult>Run([HttpTrigger(AuthorizationLevel.Anonymous,"post",Route="addtodo-asynccollector")]HttpRequestreq,[Sql(commandText:"dbo.ToDo",connectionStringSetting:"SqlConnectionString")]IAsyncCollector<ToDoItem>newItems){stringrequestBody=awaitnewStreamReader(req.Body).ReadToEndAsync();varincomingItems=JsonConvert.DeserializeObject<ToDoItem[]>(requestBody);foreach(ToDoItemnewIteminincomingItems){awaitnewItems.AddAsync(newItem);}// Rows are upserted hereawaitnewItems.FlushAsync();returnnewCreatedResult($"/api/addtodo-asynccollector","done");}}}

---

::: zone-end

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

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

This section contains the following examples:

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

The examples refer to a ToDoItem class (in a separate file ToDoItem.java) and a corresponding database table:

packagecom.function; importjava.util.UUID; publicclassToDoItem { publicUUIDId; publicintorder; publicStringtitle; publicStringurl; publicbooleancompleted; publicToDoItem() { } publicToDoItem(UUIDId, intorder, Stringtitle, Stringurl, booleancompleted) { this.Id = Id; this.order = order; this.title = title; this.url = url; this.completed = completed; } }

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

HTTP trigger, write a record to a table

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>

packagecom.function; importjava.util.*; importcom.microsoft.azure.functions.annotation.*; importcom.microsoft.azure.functions.*; importcom.microsoft.azure.functions.sql.annotation.SQLOutput; importcom.google.gson.Gson; importjava.util.Optional; publicclassPostToDo { @FunctionName("PostToDo") publicHttpResponseMessagerun( @HttpTrigger(name = "req", methods = {HttpMethod.POST}, authLevel = AuthorizationLevel.ANONYMOUS) HttpRequestMessage<Optional<String>> request, @SQLOutput( name = "toDoItem", commandText = "dbo.ToDo", connectionStringSetting = "SqlConnectionString") OutputBinding<ToDoItem> output) { Stringjson = request.getBody().get(); Gsongson = newGson(); ToDoItemnewToDo = gson.fromJson(json, ToDoItem.class); newToDo.Id = UUID.randomUUID(); output.setValue(newToDo); returnrequest.createResponseBuilder(HttpStatus.CREATED).header("Content-Type", "application/json").body(output).build(); } }

HTTP trigger, write to two tables

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:

CREATETABLEdbo.RequestLog ( Id INT IDENTITY(1,1) PRIMARY KEY, RequestTimeStamp DATETIME2 NOT NULL DEFAULT(GETDATE()), ItemCount INTNOT NULL )

and Java class in RequestLog.java:

packagecom.function; importjava.util.Date; publicclassRequestLog { publicintId; publicDateRequestTimeStamp; publicintItemCount; publicRequestLog() { } publicRequestLog(intId, DateRequestTimeStamp, intItemCount) { this.Id = Id; this.RequestTimeStamp = RequestTimeStamp; this.ItemCount = ItemCount; } }

packagecom.function; importjava.util.*; importcom.microsoft.azure.functions.annotation.*; importcom.microsoft.azure.functions.*; importcom.microsoft.azure.functions.sql.annotation.SQLOutput; importcom.google.gson.Gson; importjava.util.Optional; publicclassPostToDoWithLog { @FunctionName("PostToDoWithLog") publicHttpResponseMessagerun( @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, finalExecutionContextcontext) { context.getLogger().info("Java HTTP trigger processed a request."); Stringjson = request.getBody().get(); Gsongson = newGson(); ToDoItemnewToDo = gson.fromJson(json, ToDoItem.class); newToDo.Id = UUID.randomUUID(); output.setValue(newToDo); RequestLognewLog = newRequestLog(); newLog.ItemCount = 1; outputLog.setValue(newLog); returnrequest.createResponseBuilder(HttpStatus.CREATED).header("Content-Type", "application/json").body(output).build(); } }

::: zone-end

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

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:

:::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 output binding that adds records to a table, using data provided in an HTTP POST request as a JSON body.

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

Model v4

:::code language="typescript" source="~/azure-functions-nodejs-v4/ts/src/functions/sqlOutput1.ts" :::

Model v3

TypeScript samples aren't documented for model v3.

---

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

Model v4

:::code language="javascript" source="~/azure-functions-nodejs-v4/js/src/functions/sqlOutput1.js" :::

Model v3

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=asyncfunction(context,req){context.log('HTTP trigger and SQL output binding function processed a request.');context.bindings.todoItems=req.body;context.res={status: 201}}

---

::: zone-end ::: zone pivot="programming-language-javascript,programming-language-typescript"

HTTP trigger, write to two tables

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:

CREATETABLEdbo.RequestLog ( Id int identity(1,1) primary key, RequestTimeStamp datetime2 not null, ItemCount intnot null )

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

Model v4

:::code language="typescript" source="~/azure-functions-nodejs-v4/ts/src/functions/sqlOutput2.ts" :::

Model v3

TypeScript samples aren't documented for model v3.

---

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

Model v4

:::code language="javascript" source="~/azure-functions-nodejs-v4/js/src/functions/sqlOutput2.js" :::

Model v3

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=asyncfunction(context,req){context.log('HTTP trigger and SQL output binding function processed a request.');constnewLog={RequestTimeStamp: Date.now(),ItemCount: 1};context.bindings.requestLog=newLog;context.bindings.todoItems=req.body;context.res={status: 201}}

---

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

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:

:::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 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 usingnamespaceSystem.Netparam($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 bindingPush-OutputBinding-Name todoItems -Value $req_bodyPush-OutputBinding-Name res -Value ([HttpResponseContext]@{ StatusCode= [HttpStatusCode]::OK Body=$req_body })

HTTP trigger, write to two tables

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:

CREATETABLEdbo.RequestLog ( Id int identity(1,1) primary key, RequestTimeStamp datetime2 not null, ItemCount intnot 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:

usingnamespaceSystem.Netparam($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_bodyPush-OutputBinding-Name requestLog -Value $new_logPush-OutputBinding-Name res -Value ([HttpResponseContext]@{ StatusCode= [HttpStatusCode]::OK Body=$req_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:

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

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 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.

v2

The following is sample python code for the function_app.py file:

importjsonimportloggingimportazure.functionsasfuncfromazure.functions.decorators.coreimportDataTypeapp=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")defadd_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) returnfunc.HttpResponse( body=req.get_body(), status_code=201, mimetype="application/json" )

v1

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:

importloggingimportazure.functionsasfuncdefmain(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=func.SqlRowList(map(lambdar: func.SqlRow.from_dict(r), req_body)) exceptValueError: passifreq_body: todoItems.set(rows) returnfunc.HttpResponse( todoItems.to_json(), status_code=201, mimetype="application/json" ) else: returnfunc.HttpResponse( "Error accessing request body", status_code=400 )

---

HTTP trigger, write to two tables

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:

CREATETABLEdbo.RequestLog ( Id int identity(1,1) primary key, RequestTimeStamp datetime2 not null, ItemCount intnot null )

v2

The following is sample python code for the function_app.py file:

fromdatetimeimportdatetimeimportjsonimportloggingimportazure.functionsasfuncapp=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")defadd_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(lambdar: func.SqlRow.from_dict(r), req_body)) exceptValueError: passrequestLog.set(func.SqlRow({ "RequestTimeStamp": datetime.now().isoformat(), "ItemCount": 1 })) ifreq_body: todoItems.set(rows) returnfunc.HttpResponse( "OK", status_code=201, mimetype="application/json" ) else: returnfunc.HttpResponse( "Error accessing request body", status_code=400 )

v1

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:

importloggingfromdatetimeimportdatetimeimportazure.functionsasfuncdefmain(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(lambdar: func.SqlRow.from_dict(r), req_body)) exceptValueError: passrequestLog.set(func.SqlRow({ "RequestTimeStamp": datetime.now(), "ItemCount": 1 })) ifreq_body: todoItems.set(rows) returnfunc.HttpResponse( todoItems.to_json(), status_code=201, mimetype="application/json" ) else: returnfunc.HttpResponse( "Error accessing request body", status_code=400 )

---

::: zone-end

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

Attributes

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.

::: zone-end

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

Annotations

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.

::: zone-end
::: zone pivot="programming-language-javascript,programming-language-typescript"

Configuration

Model v4

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.

Model v3

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

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 ::: zone pivot="programming-language-powershell,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

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.

[!INCLUDE functions-sql-database-authentication-note]

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().

Next steps

• Read data from a database (Input binding)
• Run a function when data is changed in a SQL table (Trigger)
• Review ToDo API sample with Azure SQL bindings