Table of Contents
Connecting an Azure MySql Database from a Function
This post will follow on from my previous two posts on Azure this time well use the secrets to connect to an Azure MySql Database. We can just spin up a database we must first create a serer to host the database, this is why I’m using Azure Database for MySql.
Creating an Azure Database for MySql
Within the Azure Portal select Azure Database for MySql.
Within the Azure Database for MySql page select the Create button.
Select the Create button for the Flexible Server Option.
Within the Flexible Server Basics page select the existing Resource Group, and then provide a Server Name. Make sure you set the Workload Type to Development or Hobby Project this is cheaper.
Further down the page, set the Admin User Name and Password, then select Next : Networking.
In my case I’m going to use Heidi MySql Developer app to connect tot he DB from my local machine so i want to be able to connect to the Database from my local machine i.e., Public Access, so I’m going to select the Public Access option.
Then to allow the Azure Function to connect to the the Database we have to enable the option Allow Public Access from any Azure Service Within Azure to this Server.
Finally we want to be able to connect from our local client when testing with Visual Studio, so were going to select the Add Current Client IP Address option to add our own public IP Address. Then select the Next : Security button.
There is nothing to configure on the Security Page or the Tags Page navigate to the Review and Create Page.
In the Review and Create Page select the Create button.
Connecting Heidi MySql Developer
While the deployment is in progress download the Heidi MySQL Developer form here and install, you can also use another MySQL developer IDE if you want. Once the deployment has finished, which can take several minutes, select the Go to Resource link.
Within the MySql Resource select the Connect menu item, within this pane we can now see the connection information required to make a connection with Heidi.
We must also navigate tot he Networking menu item and down load the SSL Certificate.
Let start Heidi and create a new connection. Within the Session Manager window, select the New button at the bottom of the page to create a new connection. Then double click on the new connection to rename it. The copy and paste the Host Name and then provide the User Name and Password for the database login information, then select Save.
Within the Advanced tab, turn on the SSL option and then define the path to the downloaded SSL Certificate within the SSL CA Certificate path. Finally, select Save and then Open.
Heidi should now connect tot he new MySql Database in Azure.
Adding a Schema and Table
Next we need to add a schema and table so that we can store some test data. To do this right mouse click on the connection and select Create New Database from the contextual menu.
Provide a Name and Collation and select Ok
Then within the new Schema, create a New Table. To do this right mouse click on the new Schema and select Create New Tablefrom the contextual menu.
Provide a name for the new table then select the Add button to add additional data Columns as shown below. To make a column a primary key right mouse click on the row and select Create New Index -> Primary from the contextual menu. Each cell contains a dropdown menu allowing you to choose datatypes default values etc.
Once complete select Save to create the new table, the table should now be visible below the schema.
To add data change tab tot he Data tab and then select the Green Plus symbol this will add a row provide some data for the Name, Last Name and Age fields. Create four of five data entry’s.
Well use this data in the next steps.
Get All People
We have one user account setup to access MySql which is the RootAdmin account, but we don’t want to give this information way. So what we want to do in Heidi is to create a service account for the new table that has the minimum amount of access required for it to do its job.
Service User Account
So in Heidi select the Users icon in the top toolbar.
Select the Green Add button to create a new user, then specify the Username, From Host, and Password, finally select Save.
The we must define which Schemas and tables the new user has access to, select the Green Add Object button. Then select the new People Table and pick Ok.
Once the table is added we have to determine which action the user is allowed to perform on this table, select; Select, Create, Delete, Insert, and Update. Then select Save.
Key Vault Secrets
Next, lets go back to the key vault secrets in Azure and update the Secrets with the new data, we need to update all four secrets with the new information, about the Database Connection URI, Service Account User Name and Password and the name of the Database Schema.
Select on one of the secrets to edit, and select New Version.
Key in the New Secret Value and select Create, repeat these steps for all of the secrets.
Testing the New Secrets
If we go back tot he Function and test its URI again we should now see the new secret values, make sure that the list of Secret Names in the Function code, matches those in Azure.
Writing a Get All Data REST API
Let go back to Visual Studio and write our first REST API, the first thing we need to do is create a Model.
Data Transfer Model
This model will directly represent the data model of our table, so create a new class called PeopleModel and add the same attributes that are found in the Database table as shown below.
namespace DemoFunctionApp
{
public class PeopleModel
{
public int ID { get; set; } = 0;
public string Name { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public int Age { get; set; } = 0;
}
}Connection Helper
This Connection helper will take in the logger object, KeyVault URI, and the list of secret names and will generate a connection string for us, which well use to connect tot he database from the Rest API. We do have to add MySql.Data package to our solution.
Important : Make sure that the hardcoded dictionary keys are identical to the Secret Names in Azure.
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
namespace DemoFunctionApp
{
public class AzureMySqlConnectionHelper
{
public static string Connection(ILogger logger,string kvUri, List<string> secrets)
{
logger.LogInformation("AzureMySqlConnectionHelper - Connection");
SecretsManagement secretsManagement = new SecretsManagement(kvUri);
Dictionary<string, string>? secretsModel = secretsManagement.GetDictionaryOfSecrets(secrets).Result;
logger.LogInformation(string.Concat("Number of Secrets Found is : ", secretsModel?.Count.ToString()));
if (secretsModel != null)
{
var builder = new MySqlConnectionStringBuilder
{
Server = secretsModel["DBConnectionURI"],
Database = secretsModel["DBDatabase"],
UserID = secretsModel["DBUserName"],
Password = secretsModel["DBUserPassword"],
SslMode = MySqlSslMode.Required,
};
return builder.ConnectionString;
}
return string.Empty;
}
}
}Updating the Function
Lets start by deleting some of the junk and adding the two static property’s so we have the following code and we need to add the package Dapper.
public static class Function1
{
public static readonly string kvUri = $"https://catiawidget-keyvault.vault.azure.net/";
public static readonly List<string> secrets = new List<String> { "DBConnectionURI", "DBDatabase", "DBUserName", "DBUserPassword" };
[FunctionName("Function1")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
// Code to be Insert Here
//
return new OkObjectResult(responseMessage);
}
}
}Within our code block lets start of by adding the following Using block that will create our connection.
// Code to be Insert Here
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
}
//Now that we have the connection we can create our Sql Query and log it.
// Code to be Insert Here
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
string queryString = "Select * From peopletable";
log.LogInformation(string.Format("Function - GetAllPeopleModels - {0}", queryString));
}
//Next we will create a Try Catch block to contain our Query execution just in case something fails.
// Code to be Insert Here
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
string queryString = "Select * From peopletable";
log.LogInformation(string.Format("Function - GetAllPeopleModels - {0}", queryString));
try
{
}
catch (Exception ex)
{
}
}
//Within the Try block we will use the Query method to retrieve a list of Models from the database using the query string. Log the number of found results and return the data as an Ok Result to the client.
// Code to be Insert Here
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
string queryString = "Select * From peopletable";
log.LogInformation(string.Format("Function - GetAllPeopleModels - {0}", queryString));
try
{
IEnumerable<PeopleModel> output = connection.Query<PeopleModel>(queryString, new DynamicParameters()).ToList();
log.LogInformation(string.Format("Function - NUmber of Return Results is - {0}", output.Count()));
return new OkObjectResult(output);
}
catch (Exception ex)
{
}
}
//Finally, if an error is thrown well catch the error, log the error and return a Bad Result with the error to the client.
// Code to be Insert Here
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
string queryString = "Select * From peopletable";
log.LogInformation(string.Format("Function - GetAllPeopleModels - {0}", queryString));
try
{
IEnumerable<PeopleModel> output = connection.Query<PeopleModel>(queryString, new DynamicParameters()).ToList();
log.LogInformation(string.Format("Function - NUmber of Return Results is - {0}", output.Count()));
return new OkObjectResult(output);
}
catch (Exception ex)
{
log.LogError(ex.Message);
return new BadRequestObjectResult(ex.Message);
}
}
//Publishing and Testing
Now we have completed the code we can publish again to Azure and then test the result. Right Click on the Solution and Select Publish from the contextual menu, then in the Publish window select the Publish button, then wait.
Once its completed, let head back to the Azure Portal, and Navigate back to the Function. As before well get the function URI and paste it into a Chrome tab. You should now see all of the data entry’s from within the database as a Json result.
In the next post we will create the CRUD commands for this database table, and clean up our code a little.
Final Code below, i cleaned up a couple of things; Renamed the Class, Renamed the Static Function, removed the Post argument from the method signature, removed the async task since this method is no longer a async method, and moved the Key Vault URI and that’s about it.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using Dapper;
namespace DemoFunctionApp
{
public static class PeopleFunctions
{
public static readonly string kvUri = $"https://catiawidget-keyvault.vault.azure.net/";
public static readonly List<string> secrets = new List<String> { "DBConnectionURI", "DBDatabase", "DBUserName", "DBUserPassword" };
[FunctionName("GetAllPeople")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Function, "get", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
using (IDbConnection connection = new MySqlConnection(AzureMySqlConnectionHelper.Connection(log,kvUri,secrets)))
{
string queryString = "Select * From peopletable";
log.LogInformation(string.Format("Function - GetAllPeopleModels - {0}", queryString));
try
{
IEnumerable<PeopleModel> output = connection.Query<PeopleModel>(queryString, new DynamicParameters()).ToList();
log.LogInformation(string.Format("Function - NUmber of Return Results is - {0}", output.Count()));
return new OkObjectResult(output);
}
catch (Exception ex)
{
log.LogError(ex.Message);
return new BadRequestObjectResult(ex.Message);
}
}
}
}
}