Developing with Azure SQL bindings and Azure SQL trigger for Azure Functions

Drew Skwiers-Koballa

The Azure Functions SQL trigger will invoke an Azure Function when data is changed in a SQL database table, enabling new event-driven scenarios with SQL database. Accessing data in a SQL database with Azure Functions can be accomplished more quickly with the Azure SQL bindings for Azure Functions integration.  Azure SQL bindings for Azure Functions are available in public preview for all languages supported by Azure Functions and the Azure SQL trigger for Azure Functions is available in public preview for C# Azure Functions.

Guestbook sample application

A website guestbook is a simple form and list interface where users to input their messages for other website visitors to read.

In this sample application (aka.ms/passguestbook), we have created the web interface with an Azure Static Web App and connected it with an Azure SQL Database through Azure SQL bindings for Azure Functions.  By leveraging SQL bindings we’ve reduced the boilerplate code and time to develop for our backend APIs for both SQL read and write operations.

For example, writing a POST request body to the database (#1 in the figure above) is accomplished with the following JavaScript Azure Function.  No driver installation or insert statement creation required as a part of the Azure Function to add an entry to the database.

module.exports = async function (context, req) {
    context.log(req.body);

    if (req.body) {
        // create timestamp
        var date = new Date();
        // setup new entry
        newRow = {
            TextEntry: req.body.newEntry,
            DisableView: false,
            DateCreated: date.toISOString(),
            DateModerated: null
        };
        context.bindings.newEntry = newRow;
        context.res = {
            body: newRow,
            mimetype: "application/json",
            status: 201
        };
    } else {
        context.res = {
            status: 400,
            body: "Please pass a valid entry in the request body"
        };
    }
}

Azure Functions SQL trigger

The Azure Functions SQL trigger is leveraged to perform moderation on guestbook entries after they’re inserted to the database.  With this application pattern we’re able to perform subsequent actions, including data enrichment, without delaying interaction with the user interface.  Additionally, we can ensure that any data inserted into the guestbook entry table is moderated if there was an additional application writing to the table.

[FunctionName("Moderator")]
public static void Run(
    [SqlTrigger("[app].[Entry]", ConnectionStringSetting = "SqlConnectionString")] IReadOnlyList<SqlChange<Entry>> changes,
    [Sql("app.Entry", ConnectionStringSetting = "SqlConnectionString")] out Entry[] entryUpdates,
    [Sql("app.Moderation", ConnectionStringSetting = "SqlConnectionString")] out Moderation[] moderationUpdates,
    ILogger log)
{
    List<Entry> updates = new List<Entry>();
    List<Moderation> moderation = new List<Moderation>();
    // content moderation setup
    string SubscriptionKey = Environment.GetEnvironmentVariable("ModeratorKey");
    string ContentEndpoint = Environment.GetEnvironmentVariable("ModeratorEndpoint");
    ContentModeratorClient clientText = new ContentModeratorClient(new ApiKeyServiceClientCredentials(SubscriptionKey));
    clientText.Endpoint = ContentEndpoint;

    foreach (SqlChange<Entry> change in changes)
    {
        log.LogInformation($"Change operation: {change.Operation}");
        if (change.Operation == SqlChangeOperation.Insert)
        {
            // send the new entry for moderation
            string toModerate = change.Item.TextEntry;
...

This function is triggered by all changes to the table app.Entry, but only on change type Insert does it step into the moderation branch of execution.  Later in the function it sets values for entryUpdates and moderationUpdates, which are used by SQL output bindings in the same Azure Function to update the entry in the SQL table.  Execution takes less than half a second, including the time for an Azure content moderation service to evaluate the text in the post for inappropriate language.

Authentication

Connectivity between the Azure Functions and Azure SQL Database is specified with the connection string application setting on the Azure Functions.  With system-assigned managed identity enabled on the Azure Functions, passwordless connection strings can be used to authenticate the Azure Function to Azure SQL Database:

Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Database=guestbook

A walk-through document is available with information on creating up the managed identity users in Azure SQL Database: https://learn.microsoft.com/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity

Configuring the database for use with Azure Functions SQL trigger

Utilizing change tracking, the Azure Function SQL trigger will poll the database for updates and invoke the function with net changes from the latest period.  The polling duration and maximum number of updates to be processed at a time can be set to custom values in application settings to allow for different application scale based on specific use cases and resource sizing.

Enabling change tracking on the database occurs at both the database level and on specific table, both of which are required for the SQL trigger.

ALTER DATABASE [guestbook]
SET CHANGE_TRACKING = ON;
GO

ALTER TABLE [app].[Entry] ENABLE CHANGE_TRACKING;
GO

The Azure Function with the SQL trigger completes the final setup step itself at startup, creating an az_func schema if needed and tables for tracking the processing of changes.

Next steps

• Dive in with the application sample code at aka.ms/passguestbook and catch a summary of the application on Data Exposed.
• Learn more about the Azure SQL trigger for Azure Functions in the official documentation (aka.ms/sqltrigger) and see additional samples in the Azure SQL bindings for Azure Functions repository.
• For an introduction to utilizing Azure SQL bindings for Azure Functions with Azure Static Web Apps, check out this video from //Build 2022.

Drew Skwiers-Koballa Senior Program Manager

Follow