{"id":1648,"date":"2022-12-13T15:05:23","date_gmt":"2022-12-13T23:05:23","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1648"},"modified":"2022-12-13T15:05:23","modified_gmt":"2022-12-13T23:05:23","slug":"developing-with-azure-sql-bindings-and-azure-sql-trigger-for-azure-functions","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/developing-with-azure-sql-bindings-and-azure-sql-trigger-for-azure-functions\/","title":{"rendered":"Developing with Azure SQL bindings and Azure SQL trigger for Azure Functions"},"content":{"rendered":"

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.\u00a0 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.<\/p>\n

<\/p>\n

\"Image<\/a><\/p>\n

Guestbook sample application<\/h2>\n

A website guestbook is a simple form and list interface where users to input their messages for other website visitors to read.<\/p>\n

\"Image<\/a><\/p>\n

In this sample application (aka.ms\/passguestbook<\/a>), 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.\u00a0 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.<\/p>\n

\"Image<\/a>
Guestbook sample application architecture<\/figcaption><\/figure><\/p>\n

 <\/p>\n

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

module.exports = async function (context, req) {\r\n    context.log(req.body);\r\n\r\n    if (req.body) {\r\n        \/\/ create timestamp\r\n        var date = new Date();\r\n        \/\/ setup new entry\r\n        newRow = {\r\n            TextEntry: req.body.newEntry,\r\n            DisableView: false,\r\n            DateCreated: date.toISOString(),\r\n            DateModerated: null\r\n        };\r\n        context.bindings.newEntry = newRow;\r\n        context.res = {\r\n            body: newRow,\r\n            mimetype: \"application\/json\",\r\n            status: 201\r\n        };\r\n    } else {\r\n        context.res = {\r\n            status: 400,\r\n            body: \"Please pass a valid entry in the request body\"\r\n        };\r\n    }\r\n}<\/code><\/pre>\n
 <\/p>\n
Azure Functions SQL trigger<\/h3>\n
The Azure Functions SQL trigger is leveraged to perform moderation on guestbook entries after they’re inserted to the database.\u00a0 With this application pattern we’re able to perform subsequent actions, including data enrichment, without delaying interaction with the user interface.\u00a0 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.<\/p>\n
[FunctionName(\"Moderator\")]\r\npublic static void Run(\r\n    [SqlTrigger(\"[app].[Entry]\", ConnectionStringSetting = \"SqlConnectionString\")] IReadOnlyList<SqlChange<Entry>> changes,\r\n    [Sql(\"app.Entry\", ConnectionStringSetting = \"SqlConnectionString\")] out Entry[] entryUpdates,\r\n    [Sql(\"app.Moderation\", ConnectionStringSetting = \"SqlConnectionString\")] out Moderation[] moderationUpdates,\r\n    ILogger log)\r\n{\r\n    List<Entry> updates = new List<Entry>();\r\n    List<Moderation> moderation = new List<Moderation>();\r\n    \/\/ content moderation setup\r\n    string SubscriptionKey = Environment.GetEnvironmentVariable(\"ModeratorKey\");\r\n    string ContentEndpoint = Environment.GetEnvironmentVariable(\"ModeratorEndpoint\");\r\n    ContentModeratorClient clientText = new ContentModeratorClient(new ApiKeyServiceClientCredentials(SubscriptionKey));\r\n    clientText.Endpoint = ContentEndpoint;\r\n\r\n    foreach (SqlChange<Entry> change in changes)\r\n    {\r\n        log.LogInformation($\"Change operation: {change.Operation}\");\r\n        if (change.Operation == SqlChangeOperation.Insert)\r\n        {\r\n            \/\/ send the new entry for moderation\r\n            string toModerate = change.Item.TextEntry;\r\n...<\/code><\/pre>\n
This function is triggered<\/a> by all changes to the table app.Entry, but only on change type Insert does it step into the moderation branch of execution.\u00a0 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.\u00a0 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.<\/p>\n
Authentication<\/h3>\n
Connectivity between the Azure Functions and Azure SQL Database is specified with the connection string application setting on the Azure Functions.\u00a0 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:<\/p>\n
Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Database=guestbook<\/code><\/pre>\n
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<\/a><\/p>\n
Configuring the database for use with Azure Functions SQL trigger<\/h3>\n
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.\u00a0 The polling duration and maximum number of updates to be processed at a time can be set to custom values in application settings<\/a> to allow for different application scale based on specific use cases and resource sizing.<\/p>\n
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.<\/p>\n
ALTER DATABASE [guestbook]\r\nSET CHANGE_TRACKING = ON;\r\nGO\r\n\r\nALTER TABLE [app].[Entry] ENABLE CHANGE_TRACKING;\r\nGO<\/code><\/pre>\n
The Azure Function with the SQL trigger completes the final setup step itself at startup, creating an az_func<\/em> schema if needed and tables for tracking the processing of changes.<\/p>\n
Next steps<\/h2>\n