Skip to content

Latest commit

 

History

History
297 lines (202 loc) · 11.6 KB

functions-bindings-azure-sql.md

File metadata and controls

297 lines (202 loc) · 11.6 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, build-2023, devx-track-extended-java, devx-track-js, devx-track-python
4/17/2023
drskwier
glenga
programming-languages-set-functions-lang-workers

Azure SQL bindings for Azure Functions overview

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

Action Type
Trigger a function when a change is detected on a SQL table SQL trigger (preview)
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:

In-process

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.

dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql

To use a preview version of the Microsoft.Azure.WebJobs.Extensions.Sql package for SQL trigger functionality, add the --prerelease flag to the command.

dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql --prerelease

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the SQL extension package.

Isolated process

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

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

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Sql

To use a preview version of the Microsoft.Azure.Functions.Worker.Extensions.Sql package for SQL trigger functionality, add the --prerelease flag to the command.

dotnet add package Microsoft.Azure.Functions.Worker.Extensions.Sql --prerelease

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the SQL extension package.

C# script

Functions run as C# script, which is supported primarily for C# portal editing. The SQL bindings extension is part of the v4 extension bundle, which is specified in your host.json project file.

This extension is available from the extension bundle v4, which is specified in your host.json file by:

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

You can add the preview extension bundle to use the SQL trigger 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)"
  }
}

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the extension bundle.

::: zone-end

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

Install bundle

The SQL bindings extension is part of the v4 extension bundle, which is specified in your host.json project file. For SQL trigger functionality, use a preview version of the extension bundle.

Bundle v4.x

The extension bundle is specified by the following code in your host.json file:

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

Preview Bundle v4.x

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

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the extension bundle.

::: zone-end

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

Functions runtime

Install bundle

The SQL bindings extension is part of the v4 extension bundle, which is specified in your host.json project file. For SQL trigger functionality, use a preview version of the extension bundle.

Bundle v4.x

The extension bundle is specified by the following code in your host.json file:

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

Preview Bundle v4.x

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

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the extension bundle.

::: zone-end

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

Install bundle

The SQL bindings extension is part of the v4 extension bundle, which is specified in your host.json project file. For SQL trigger functionality, use a preview version of the extension bundle.

Bundle v4.x

The extension bundle is specified by the following code in your host.json file:

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

Preview Bundle v4.x

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

Note

Breaking changes between preview releases of the Azure SQL trigger for Functions requires that all Functions targeting the same database use the same version of the extension bundle.

Update packages

Add the Java library for SQL bindings to your functions project with an update to the pom.xml file in your Java Azure Functions project as seen in the following snippet:

<dependency>
    <groupId>com.microsoft.azure.functions</groupId>
    <artifactId>azure-functions-java-library-sql</artifactId>
    <version>1.0.0</version>
</dependency>

For SQL trigger functionality, use a preview version of the Java library for SQL bindings instead:

<dependency>
    <groupId>com.microsoft.azure.functions</groupId>
    <artifactId>azure-functions-java-library-sql</artifactId>
    <version>2.0.0-preview</version>
</dependency>

::: zone-end

SQL connection string

Azure SQL bindings for Azure Functions have a required property for the connection string on all bindings and triggers. These pass 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)
  • Pooling allows a function to reuse connections to the database, which can improve performance (default true). Additional settings for connection pooling include Connection Lifetime, Max Pool Size, and Min Pool Size. Learn more about connection pooling in the ADO.NET documentation

Considerations

  • Azure SQL binding supports version 4.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.

Samples

In addition to the samples for C#, Java, JavaScript, PowerShell, and Python available in the Azure SQL bindings GitHub repository, more are available in Azure Samples:

Next steps