Azure Functions Write to Azure SQL Database (Output Binding)

Azure SQL Databases support writing from Azure Functions through Output Bindings. We define the table name and a variable pointing to a Function App Setting that has the value of the connection string as part of the SQL output binding.

In this post, let's explore how we can write to an Azure SQL Database from Azure Functions using C#, featuring the OPTIONAL use of OpenAI's Chat Completions API to safely come up with a random 6 letter word in a random language, a random number and the translation of the word in English, because of course.. AI is powerful!!🦾. Then we save these details in Azure SQL Database.

In this demo, I will assume:

• You already have an Azure SQL Server and an Azure SQL Database in your Azure account with a working connectionString to connect to it from DBMS tools. I created a SQL Server admin user during the Azure SQL Server creation process to login into my SQL Server and I use this to connect in the last section of this post with SQL Server Management Studio.
• You have enabled 'Allow Azure services and Resources to access this server' for your SQL Server under Networking > Public Access > Selected Networks

The Code

Here we specify that the Function will be requiring a Function Key and will be using an HTTP POST request. We specify the name of our target Database Table, reference the specific connectionString through a Function App Setting called DbConnection, and an IAsyncColletor<ourDBEntity> object to collect our intended data to be written to Azure SQL Database:    

public static class WordSpinFunctions
{
//utilty member to set the chosen random language
public static string chosenLanguage { get; set; }

[FunctionName("WordSpin")]
public static async Task<IActionResult> WordSpin(
[HttpTrigger(AuthorizationLevel.Function, "post")] HttpRequest req,
[Sql( commandText: "dbo.spintable",
ConnectionStringSetting = "DbConnection")] IAsyncCollector<SpinTableItem> spinTableItems,
ILogger log)
{
    log.LogDebug($"Now entering function...");

    try
    {
        //BEGINNING OF OPTIONAL CODE BUT FUN CODE!!.
        //Here I rely on KeyVault and the Function's Managed
        //Identity to access my OpenAI dev key, craft a prompt,
        //then call to the OpenAI completions endpoint 
        string openAISecretDevKey = await GetKeyVaultSecret("OpenAIDevKey");
        log.LogDebug($"key ok");

        HttpClient client = new HttpClient();
        client.DefaultRequestHeaders.Authorization = 
        	new AuthenticationHeaderValue("Bearer", openAISecretDevKey);

        string requestBody = CraftPromptRequestBody();
        var content = 
        	new StringContent($"{requestBody}", Encoding.UTF8, "application/json");
        var response = 
        	await client.PostAsync("https://api.openai.com/v1/chat/completions", content);
        string gptJsonResult = await response.Content.ReadAsStringAsync();
        log.LogDebug($"gpt call ok");
        //End of OPTIONAL CODE
        
        //Craft a DB Entity with your code logic or the above logic
        SpinTableItem spinTableItem = CreateDBItem(gptJsonResult, chosenLanguage);
        if (spinTableItem != null)
        {
            //Then WRITE TO DB HERE
            log.LogDebug($"Now writing to Azure SQL DB");
            await spinTableItems.AddAsync(spinTableItem);
            await spinTableItems.FlushAsync();
            log.LogDebug($"Finished writing to Azure SQL DB");
            return new OkObjectResult(spinTableItem);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        log.LogDebug($"Error, {ex.Message}");
        return null;
    }

    return new OkObjectResult("The process produced an empty result");
   }
}

Important: For my particular case, I am using KeyVault to retrieve an application-specific secret (in this case a sensitive OpenAI Dev Key to use as part of my Function). After deploying the Function App and before running for the first time, I then enabled a Managed Identity for the Function App (in Settings > Identity) and then assigned only Get permissions for the Function's Managed Identity in my KeyVault's Access policies. This makes sure the below code works properly when executed in a deployed environment.

The GetKeyVaultSecret method for fetching a secret from Azure KeyVault:

public static async Task<string> GetKeyVaultSecret(string kvSecretName)
{
    //use a Managed Identity and KV Access policy to access secret
    var credential = new DefaultAzureCredential();

    var secretClient = 
    	new SecretClient(new Uri("https://{YOUR_VAULT_NAME}.vault.azure.net/"), credential);

    KeyVaultSecret secret = await secretClient.GetSecretAsync(kvSecretName);
    return secret.Value;
}

My code logic also contains a Function to craft an OpenAI Chat completions prompt inside a json request body:  

public static string CraftPromptRequestBody()
{
    Random rand = new Random();
    int langKey = rand.Next(0, 7);
    List<string> languages = 
    	new List<string>() { 
        "Danish",
        "French",
        "Shona",
        "Spanish",
        "Italian",
        "Finnish",
        "German",
        "Dutch"
        };
    string prompt = $"Write an inoffensive word with only 6 letters" +
    $" in the {languages[langKey]} language," +
    $"and write a number between 7331 and 19000." +
    $"Then write the english translation of the word afterwards." +
    $"Present your answer in the format <number>|<word>|<englishtranslation> ";
    //gpt 3.5 turbo is balances cost efficiency whilst 
    //still minimising hallucinations.
    //Note Using GPT APIs is not free and is charged
    //seperately from a ChatGPT Plus subscription!!
    string requestBody = $@"{{
        ""model"": ""gpt-3.5-turbo"",
        ""messages"": [
            {{
                ""role"": ""user"",
                ""content"": ""{prompt}""
            }}
       	   ]
        }}";
    chosenLanguage = languages[langKey]; //use this value for later
    return requestBody;
}

The CreateDBItem helper function looks like this and prepares a model class of our choosing intended to be written to the DB, and is modelled similarly to our database Table:

public static SpinTableItem CreateDBItem(string gptJsonResult, string wordLanguage)
{
	//maninpulate and extract the info we need from
    //the GPT json response, construct our DB ready object
    if (!string.IsNullOrEmpty(gptJsonResult))
    {
        JObject jsonObject = JObject.Parse(gptJsonResult);

        string value = (string)jsonObject["choices"][0]["message"]["content"];

        List<string> gptItems = value.Split("|").ToList();
        SpinTableItem spinTableItem = new SpinTableItem
        {
            Number = int.Parse(gptItems[0]),
            Word = gptItems[1],
            WordLanguage = wordLanguage,
            EnglishTranslation = gptItems[2]
        };
        return spinTableItem;
    }
    else
    {
    	return null;
    }
}

The SpinTableItem class (a model class of our choosing):

public class SpinTableItem
{
    public int Id { get; set; }
    public int Number { get; set; }
    public string Word { get; set; }
    public string WordLanguage { get; set; }
    public string EnglishTranslation { get; set; }
}

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <AzureFunctionsVersion>v4</AzureFunctionsVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Azure.Security.KeyVault.Secrets" Version="4.5.0" />
    <PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.15.0" />
    <PackageReference Include="Microsoft.Azure.WebJobs.Extensions.Sql" Version="0.1.457-preview" />
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="4.1.1" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
</Project>

Deploy and Prepare Azure Function

Once the Azure Function is deployed (I deployed mine through Visual Studio 2022), you'll want make sure your working connectionString is set to the name of connectionString variable in the Function App's App Settings under Settings section > Configuration. So in my case I set the name 'DbConnection' in my Function code, therefore I will set an App Setting name called DbConnection and the value will be my connection string (as the SQL Server Login connection string):

Click OK and Save your Configuration Settings with your newly saved  Connection String as an App Setting. You may need to refresh/restart the Function App.

Run Azure Function and Observe Data

Here we connect to our Azure SQL Database using a SQL Server Login created during the Azure SQL Server creation process. Using something like Postman, we can call our POST Function using the Function URL, and it works!!:

Conclusions

I noticed that by default, the column Collation in Azure SQL Database is set to SQL_Latin1_General_CP1_CI_AS , therefore words in certain languages, for example Siamese, will not display fully or simply be encoded as ????. There is a set list of languages including English and most European languages that fall under this collation and an even larger set list outside of the SQL_Latin1_General_CP1_CI_AS collation. And finally, GPT 3.5-turbo does mistranslate words sometimes.