Integrate Azure Functions and Azure SQL easily with the Azure SQL bindings for Azure Functions, now generally available. Input and output bindings speed up development time by reducing boilerplate code required to read and write from the database in .NET, Python, Node.js, Java, and PowerShell Azure Functions. Azure SQL bindings for Azure Functions are compatible with Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2016-2022.
Simplified development
Input bindings
With Azure SQL bindings, data can be input from a database to the function with an input binding and data can be output from the function to the database. Configuring these bindings is accomplished with a small number of parameters, including specification of the database query or table and the connection string.
An input binding can execute a query or a stored procedure, and the results are passed to the function as an object. The following example .NET Azure Function executes the query “SELECT TOP 10 ProductId, ProductName FROM Sales.Products ORDER BY CreatedDate DESC” when an HTTP GET request is made to the /newproducts endpoint. Data is read from SQL into the newProducts object at the beginning of the function before being returned as the HTTP response content.
[FunctionName("GetToDoItem")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "newproducts")]
HttpRequest req,
[Sql(commandText: "SELECT TOP 10 ProductId, ProductName FROM Sales.Products ORDER BY CreatedDate DESC",
commandType: System.Data.CommandType.Text,
connectionStringSetting: "SqlConnectionString")]
IEnumerable<Product> newProducts)
{
return new OkObjectResult(newProducts);
}
}
Another example with SQL input bindings is highlighted in a blog post on Python Functions and Azure SQL, which utilizes a timer trigger to send data from a SQL database to an FTP server every night. Check it out at: https://devblogs.microsoft.com/azure-sql/using-python-and-azure-functions-to-send-data-from-azure-sq...
Output bindings
An output binding takes one or a list of objects and updates or inserts the data based on the primary key on the table. The following JavaScript Azure Function upserts items from an array in an HTTP POST request and inserts another row to log the request. Data is written to the two SQL output bindings via context.bindings.todoItems and context.bindings.requestLog.
module.exports = async function (context, req) {
const newLog = {
RequestTimeStamp = Date.now(),
ItemCount = 1
}
if (req.body) {
context.bindings.todoItems = req.body.items;
newLog.ItemCount = req.body.items.length;
context.bindings.requestLog = newLog;
context.res = {
body: req.body,
mimetype: "application/json",
status: 201
}
} else {
context.res = {
status: 400,
body: "Error reading request body"
}
}
}
Passwordless authentication
The use of Azure Active Directory Managed Identity authentication with Azure SQL bindings enables passwordless authentication to Azure SQL. The setup requires enabling system-assigned or user-assigned managed identity on an Azure Functions instance and using a connection string setting with Authentication=”Active Directory Managed Identity”. Managed identities make your app more secure by eliminating secrets from your app and are ready for use with Azure SQL bindings for Azure Functions. Learn how you can enable passwordless authentication on your Azure Functions in minutes in this tutorial: https://learn.microsoft.com/azure/azure-functions/functions-identity-access-azure-sql-with-managed-i...
SQL trigger (preview)
In addition to input and output bindings, an Azure Function can be triggered by data changes in Azure SQL. With the Azure SQL trigger you can invoke Azure Functions from data changes to develop event-driven applications on your Azure SQL databases.
The Azure SQL trigger for Azure Functions is in public preview and is available for use with .NET, Java, Python, JavaScript, and PowerShell Azure Functions. With the SQL trigger for Azure Functions, event-driven applications are enabled on top of Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2016-2022.
Learn more
Several code samples are available that highlight the use of SQL bindings with an HTTP trigger to create APIs:
- C# Azure Functions with input and output SQL bindings: https://github.com/Azure-Samples/azure-sql-binding-func-dotnet-todo
- JavaScript Azure Functions with input and output SQL bindings and C# Azure Functions with SQL trigger: https://github.com/Azure-Samples/sql-bindings-guestbook
- C# Azure Functions with input and output SQL bindings: https://github.com/Azure-Samples/azure-sql-blazor-tag-game-sample
Azure SQL bindings are applicable to functions that are triggered by any event, including timers, Azure Service Bus, and Azure Blob Storage. This Python Functions sample with SQL input bindings shows how to accomplish a common scenario of sending data on a schedule from SQL to an FTP server or other integration service: https://github.com/Azure-Samples/sqlbindings-python-datatransfer
Get started building with the Azure SQL bindings for Azure Functions today with examples across all languages (C#, Python, JavaScript, Java, and PowerShell) in the documentation: https://aka.ms/sqlbindings