Skip to content

Files

Latest commit

2e408b1 · Jun 3, 2022

History

History
190 lines (122 loc) · 9 KB

functions-bindings-azure-sql.md

File metadata and controls

190 lines (122 loc) · 9 KB
title description author ms.topic ms.custom ms.date ms.author ms.reviewer zone_pivot_groups
Azure SQL bindings for Functions
Understand how to use Azure SQL bindings in Azure Functions.
dzsquared
reference
event-tier1-build-2022
6/3/2022
drskwier
glenga
programming-languages-set-functions-lang-workers

Azure SQL bindings for Azure Functions overview (preview)

This set of articles explains how to work with Azure SQL bindings in Azure Functions. Azure Functions supports input and output bindings for the Azure SQL and SQL Server products.

Action Type
Read data from a database Input binding
Save data to a database Output binding

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

Install extension

The extension NuGet package you install depends on the C# mode you're using in your function app:

Functions execute in the same process as the Functions host. To learn more, see Develop C# class library functions using Azure Functions.

Add the extension to your project by installing this NuGet package.

Functions execute in an isolated C# worker process. To learn more, see Guide for running C# Azure Functions in an isolated process.

Note

In the current preview, Azure SQL bindings aren't supported when your function app runs in an isolated process.


::: zone-end

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

Install bundle

The SQL bindings extension is part of a preview extension bundle, which is specified in your host.json project file.

You can add the preview extension bundle by adding or replacing the following code in your host.json file:

{
  "version": "2.0",
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle.Preview",
    "version": "[3.*, 4.0.0)"
  }
}

You can add the preview extension bundle by adding or replacing the following code in your host.json file:

{
  "version": "2.0",
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle.Preview",
    "version": "[4.*, 5.0.0)"
  }
}

::: zone-end

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

Functions runtime

Note

Python language support for the SQL bindings extension is only available for v4 of the functions runtime and requires runtime v4.5.0 or greater for deployment in Azure. Learn more about determining the runtime in the functions runtime documentation. Please see the tracking GitHub issue for the latest update on availability.

The functions runtime required for local development and testing of Python functions isn't included in the current release of functions core tools and must be installed independently. The latest instructions on installing a preview version of functions core tools are available in the tracking GitHub issue.

Alternatively, a VS Code development container definition can be used to expedite your environment setup. The definition components are available in the SQL bindings GitHub repository.

Install bundle

The SQL bindings extension is part of a preview extension bundle, which is specified in your host.json project file.

You can add the preview extension bundle by adding or replacing the following code in your host.json file:

{
  "version": "2.0",
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle.Preview",
    "version": "[4.*, 5.0.0)"
  }
}

Python support isn't available with the SQL bindings extension in the v3 version of the functions runtime.


Update packages

Support for the SQL bindings extension is available in the 1.11.3b1 version of the Azure Functions Python library. Add this version of the library to your functions project with an update to the line for azure-functions== in the requirements.txt file in your Python Azure Functions project as seen in the following snippet:

azure-functions==1.11.3b1

Following setting the library version, update your application settings to isolate the dependencies by adding PYTHON_ISOLATE_WORKER_DEPENDENCIES with the value 1 to your application settings. Locally, this is set in the local.settings.json file as seen below:

"PYTHON_ISOLATE_WORKER_DEPENDENCIES": "1"

Support for Python durable functions with SQL bindings isn't yet available.

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

SQL connection string

Azure SQL bindings for Azure Functions have a required property for connection string on both input and output bindings. SQL bindings passes the connection string to the Microsoft.Data.SqlClient library and supports the connection string as defined in the SqlClient ConnectionString documentation. Notable keywords include:

  • Authentication allows a function to connect to Azure SQL with Azure Active Directory, including Active Directory Managed Identity
  • Command Timeout allows a function to wait for specified amount of time in seconds before terminating a query (default 30 seconds)
  • ConnectRetryCount allows a function to automatically make additional reconnection attempts, especially applicable to Azure SQL Database serverless tier (default 1)

Considerations

  • Because the Azure SQL bindings doesn't have a trigger, you need to use another supported trigger to start a function that reads from or writes to an Azure SQL database.
  • Azure SQL binding supports version 2.x and later of the Functions runtime.
  • Source code for the Azure SQL bindings can be found in this GitHub repository.
  • This binding requires connectivity to an Azure SQL or SQL Server database.
  • Output bindings against tables with columns of data types NTEXT, TEXT, or IMAGE aren't supported and data upserts will fail. These types will be removed in a future version of SQL Server and aren't compatible with the OPENJSON function used by this Azure Functions binding.

Next steps