{"id":761,"date":"2020-12-14T17:30:03","date_gmt":"2020-12-15T01:30:03","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=761"},"modified":"2020-12-14T17:43:25","modified_gmt":"2020-12-15T01:43:25","slug":"seasons-of-serverless-challenge-3-azure-typescript-functions-and-azure-sql-database-serverless","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/seasons-of-serverless-challenge-3-azure-typescript-functions-and-azure-sql-database-serverless\/","title":{"rendered":"Seasons of Serverless Challenge 3: Azure TypeScript Functions and Azure SQL Database serverless"},"content":{"rendered":"

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

Have you heard about the Seasons of Serverless challenge? Join the festive virtual potluck<\/a> by creating solutions to challenges leveraging Azure serverless computing. Throughout the next seven weeks we’ll be sharing a solution to the week’s challenge that integrates Azure SQL Database serverless<\/a> with Azure serverless compute.<\/p>\n

Challenge 3: The Longest Kebab \ud83d\udccf<\/h2>\n

The third week features the traditional “kebab” from Turkey.\u00a0 The challenge calls for quickly calculating a recipe for the kebab based on an input weight of meat. In addition to returning the recipe, we’re also interested in how many people the kebab will feed as well as how long it will be. Below is a high level view of the solution we created, utilizing an Azure Function and Azure SQL Database serverless. While the components are similar to Challenge 1<\/a> where Azure Functions and Azure SQL Database serverless was leveraged to solve the challenge, we decided to write the Azure Functions for Challenge 3 in TypeScript.\u00a0 The completed example can be found on GitHub<\/a>.<\/p>\n

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

\n

Azure SQL Database and Indexes for Performance<\/h3>\n

In challenge 1, we created an Azure SQL database with the serverless compute tier.\u00a0 In this challenge we are going to create another database in the same manner<\/a>, providing us with a serverless SQL database hosted in Azure that automatically starts, pauses and scales with our workload.\u00a0 Through the SQL Database projects extension<\/a> in Azure Data Studio we are able to create a new SQL database project and the handful of database structures we need to store recipes and return the maximum length recorded.<\/p>\n

The table\u00a0dbo.Recipes<\/strong> will store each recipe generated and our Azure Functions will call two stored procedures:\u00a0dbo.saveRecipe\u00a0<\/strong>and\u00a0dbo.getRecord.\u00a0\u00a0<\/strong>In\u00a0dbo.getRecord<\/strong>, we will retrieve only a single record from the table\u00a0dbo.Recipes\u00a0<\/strong>after ordering the recipes by kebab lengths.<\/p>\n

CREATE PROCEDURE [dbo].[getRecord]\r\nAS\r\nBEGIN\r\n  SELECT TOP 1 LambKilos, Onions, GarlicCloves, CuminTeaspoons, SumacTeaspoons, SaltTeaspoons,\r\n      BlackPepperTeaspoons, RedPepperTeaspoons, LengthCm, FeedsPeople\r\n  FROM Recipes\r\n  ORDER BY LengthCm DESC, Id ASC\r\nEND<\/pre>\n
One way to improve performance of sorting the kebab recipes and returning the longest one is to store a presorted copy of all the recipe Id’s and their lengths in our database. This is an\u00a0INDEX<\/em>, and we add it to the definition of our table and specify which column(s) should be used to look up a record in the index.\u00a0 There is a great depth of information on indexes in T-SQL available, one overview of the functionality is the documentation<\/a>.<\/p>\n
CREATE TABLE [dbo].[Recipes]\r\n(\r\n  [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,\r\n  LambKilos DECIMAL(14,2) NOT NULL,\r\n  Onions DECIMAL(14,2) NOT NULL,\r\n  GarlicCloves DECIMAL(14,2) NOT NULL,\r\n  CuminTeaspoons DECIMAL(14,2) NOT NULL,\r\n  SumacTeaspoons DECIMAL(14,2) NOT NULL,\r\n  SaltTeaspoons DECIMAL(14,2) NOT NULL,\r\n  BlackPepperTeaspoons DECIMAL(14,2) NOT NULL,\r\n  RedPepperTeaspoons DECIMAL(14,2) NOT NULL,\r\n  LengthCm INT NOT NULL,\r\n  FeedsPeople INT NOT NULL,\r\n  INDEX IX_Length (LengthCm DESC)\r\n)<\/pre>\n
\"Image<\/a><\/p>\n
After deploying our database project in Azure Data Studio, we can check that our index is being used in the dbo<\/strong>.getRecord<\/strong> procedure by using the command “Run Query with Actual Plan” instead of “Execute Query” on a query editor with the statement EXEC dbo.getRecord<\/em>.\u00a0 The query plan shows our index, IX_Length<\/strong>, reading a single row with an “Index Scan” and doing an inner join between the Id of that row and a key lookup (Id) of our primary key. We could have two, 200, or 2,000,000 rows in our Recipes table and the query would be reading a single row from the index and a single row from the table.\u00a0 Without the index our query would have to read every row in the Recipes table to determine which has the longest length.<\/p>\n
We manually created an index in our database project for this week’s challenge, but with Azure SQL database, the database engine can make and apply index recommendations for you. The serverless tier of Azure SQL database includes intelligent performance features, including Query Performance Insight and Automatic tuning.\u00a0 With Query Performance Insight<\/a> you can quickly identify and drill into the details of long-running or resource-intensive queries on your database.\u00a0 With Automatic tuning<\/a> your database can have performance changes automatically applied, including index creation.<\/p>\n
\n
Creating TypeScript Azure Functions<\/h3>\n
Using the VS Code Azure Functions extension and the “Azure Functions: Create New Project” command, we’re creating a new Azure Function locally in VS Code in TypeScript with the HTTP trigger template.\u00a0 This function will receive HTTP GET requests with an amount of lamb in kilograms (kg) through a query string parameter. The body of the function is defined in the httpTrigger(context: Context, req: HttpRequest)<\/strong> function in the file index.ts<\/strong>.\u00a0 Dependencies for our function that need to be installed to run the function are detailed in the file\u00a0package.json<\/strong>, where we want to begin by adding a line for mssql<\/em> under dependencies by running npm install mssql<\/em> from the terminal. mssql<\/a> is the package name for the open source Nodejs driver for MSSQL that enables connecting our TypeScript functions to Azure SQL Database serverless.<\/p>\n
\"Image<\/a>We want to create 2 functions in our folder,\u00a01<\/strong> for receiving a querystring for an amount of lamb meat and returning the recipe and another (2<\/strong>) for checking the current longest recipe recorded.\u00a0 In both functions, we are going to leverage TypeScript’s namesake – types –\u00a0 in defining classes to assist in working with the kebab recipes and length record.<\/p>\n
For function #1<\/strong>, the recipe calculator, we want to return a response to the user\u00a0as fast as possible<\/em>. We’ll do this in 3 steps: by creating a new instance of a\u00a0KebabRecipe<\/strong>, returning this to the user in the HTTP Response<\/strong>, and finally saving the KebabRecipe to the database with the RecordRecipe<\/strong> function. By declaring a custom constructor function for the KebabRecipe class, we are able to create an instance of the recipe with a single line of code in\u00a0index.ts\u00a0<\/em>and leave all the calculation of the other attributes in the class declaration in recipe.ts<\/em>. The following is the custom constructor for our KebabRecipe class:<\/p>\n
constructor(lambKilos: number) {\r\n        this.lambKilos = lambKilos;\r\n        this.onions = lambKilos \/ 2;\r\n        this.garlicCloves = lambKilos * 2;\r\n        this.cuminTeaspons = lambKilos * (3 \/ 4);\r\n        this.sumacTeaspoons = lambKilos * (3 \/ 4);\r\n        this.saltTeaspoons = lambKilos \/ 4;\r\n        this.blackPepperTeaspoons = lambKilos \/ 8;\r\n        this.redPepperTeaspoons = lambKilos \/ 8;\r\n        this.lengthCm = Math.round(lambKilos * (2.2\/4) * 12);\r\n        this.feedsPeople = Math.round(lambKilos * (2.2\/4));\r\n    }<\/pre>\n
We are able to set the HTTP response and call the function that sends the recipe to our serverless Azure SQL database. The key to ensuring that our user experience is a fast response on the recipe every time, regardless of how long the database access takes, is\u00a0not<\/em> waiting for the\u00a0RecordRecipe()\u00a0<\/strong>function to complete before reaching the end of the\u00a0httpTrigger()<\/strong> function. Although Azure SQL is fast – there may be times when our serverless database is unpausing and we want to return the recipe to the user while the function retries the connection after the database is ready. In the screenshot of our application logs below, we see the log call for “End of function” on the 2nd line and the function’s service reports that the execution took 53ms. On the last line, timestamped 5ms later, we see the log call from the RecordRecipe()<\/strong> function.\u00a0 \u26a1<\/p>\n
\"execution<\/a><\/p>\n
Note: Should the storage of a generated recipe be critical information, we would consider connecting our Azure Function to a messaging service layer<\/a> as a producer, allowing data storage requests to be safely queued as subscribers.<\/em><\/p>\n
The full code for the Azure Function, including index.ts and recipe.ts, are available here<\/a>.<\/p>\n
\n
await: Retrieving the recipe for the longest kebab<\/h3>\n
\"Image<\/a><\/p>\n
While the Recipe function needed to return a recipe as fast as possible, we also want a user to be able to find out what is the longest Kebab Recipe generated via our application.\u00a0 Using the “Azure Functions: Create Function” command we can add another function to our project, allowing the functions to share the same Function host and application settings (like database connection details).\u00a0 The 2nd function is also a TypeScript function with an HTTP request trigger but the program flow in index.ts is more straight forward. The function cannot return results to the user until it hears back from the database what the longest kebab recipe is.\u00a0 Enter\u00a0await<\/em>, which suspends subsequent execution until the called function fulfills the Promise and returns. (async\/await<\/a> is functionality from JavaScript and is available in TypeScript<\/a> as well)<\/p>\n
The root level function\u00a0httpTrigger<\/strong> is an\u00a0async\u00a0<\/em>function, permitting utilization of the await <\/em>keyword within it. We can apply the await<\/em> keyword to\u00a0the KebabRecord.getRecord()<\/strong> function, where we will connect to the serverless Azure SQL database and update the KebabRecord object. In doing so, we force the function to wait for the response from the database before returning an HTTP response to the user.\u00a0 The\u00a0getRecord()\u00a0<\/strong>function is a public function in the KebabRecord class, exposing it to other modules (such as index.ts) via an import statement. There is a\u00a0private<\/em> function within the KebabRecord class, a function named\u00a0connectToSQL()<\/strong> that assists our class in connecting to the Azure SQL database.<\/p>\n
private async connectToSQL(retryCount) {\r\n    if (retryCount < 10) {\r\n        try {\r\n            let pool = await mssql.connect(sqlConfig);\r\n            return pool;\r\n        } catch (err) {\r\n            console.log('retrying sql connection');\r\n            console.log(retryCount);\r\n            retryCount = retryCount + 1;\r\n            console.error(err);\r\n            await sleep(10000);\r\n            return await this.connectToSQL(retryCount);\r\n        }\r\n    }\r\n}<\/pre>\n
After connecting to the Azure SQL database, we can access the first (and only) row returned with the first item in the array recordset.<\/em>\u00a0this<\/em> refers to the calling KebabRecord object, allowing us to update its attributes (such as number of onions).\u00a0 When the function completes, it resolves the Promise and returns execution to the primary function in index.ts.<\/p>\n
public async getRecord() {\r\n    try {\r\n        let pool = await this.connectToSQL(0);\r\n        let resultSet = await pool.request().execute('getRecord');\r\n        let outputRecord = resultSet.recordset[0];\r\n\r\n        this.lambKilos = outputRecord.LambKilos;\r\n        this.onions = outputRecord.Onions;\r\n        this.garlicCloves = outputRecord.GarlicCloves;\r\n        this.cuminTeaspons = outputRecord.CuminTeaspoons;\r\n        this.sumacTeaspoons = outputRecord.SumacTeaspoons;\r\n        this.saltTeaspoons = outputRecord.SaltTeaspoons;\r\n        this.blackPepperTeaspoons = outputRecord.BlackPepperTeaspoons;\r\n        this.redPepperTeaspoons = outputRecord.RedPepperTeaspoons;\r\n        this.lengthCm = outputRecord.LengthCm;\r\n        this.feedsPeople = outputRecord.FeedsPeople;\r\n    } catch (err) {\r\n        console.error('Failed to retrieve record from SQL database.');\r\n        console.error(err);\r\n    }\r\n}\r\n\r\n<\/pre>\n
\n
Wrapping Up<\/h3>\n
\"Image<\/a>We will deploy our Azure Functions into a single serverless Function App.\u00a0 This can be accomplished by the same GitHub action<\/a> for Azure Functions utilized in Challenge 1.\u00a0 One last thing – application settings.\u00a0 The connection details for our Azure SQL database were stored in the local.settings.json<\/em> file in our Azure Function project and need to be input in the application settings under\u00a0Configuration<\/em> on our function app.\u00a0 We have 4 individual settings, the server address, the database name, the SQL username, and the SQL password.<\/p>\n
 <\/p>\n
With the secrets in place and the functions deployed to Azure, we are ready to test our functions from public URLs.\u00a0 The full source code from this example is available here: https:\/\/github.com\/dzsquared\/seasons-of-serverless-week3<\/a><\/p>\n
For a limited time, the longest kebab API test URL is: https:\/\/kebab.azurewebsites.net\/api\/RecordAPI?code=3LwapFdF8mCNGOg0yXAIhvpiyqnNCdIYPN8Au28fkHTzMMnLcJ8F1Q==<\/a><\/p>\n
For a limited time, the URL to generate your own kebab recipe is: https:\/\/kebab.azurewebsites.net\/api\/RecipeAPI?code=3LwapFdF8mCNGOg0yXAIhvpiyqnNCdIYPN8Au28fkHTzMMnLcJ8F1Q==&lamb=2<\/a> (don’t forget to update the query string to the amount of lamb you have available in kilograms)<\/p>\n
Just a reminder… Since both our compute and database are serverless, our costs are minimized when there is no workload.\u00a0 For the database, we are only charged for storage when the database is paused.\u00a0 Next time you need a database for your serverless architecture, consider Azure SQL database serverless.\u00a0 <\/strong><\/p>\n
See you next week for our solution to Challenge 4: A Big Barbecue<\/a>!<\/p>\n","protected":false},"excerpt":{"rendered":"
Throughout the next seven weeks we’ll be sharing a solution to the week’s Seasons of Serverless challenge that integrates Azure SQL Database serverless with Azure serverless compute. Learn how to develop an Azure Function that leverages Azure SQL database serverless and TypeScript with Challenge 3 of the Seasons of Serverless challenge.<\/p>\n","protected":false},"author":46830,"featured_media":762,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,430],"tags":[],"class_list":["post-761","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-javascript"],"acf":[],"blog_post_summary":"
Throughout the next seven weeks we’ll be sharing a solution to the week’s Seasons of Serverless challenge that integrates Azure SQL Database serverless with Azure serverless compute. Learn how to develop an Azure Function that leverages Azure SQL database serverless and TypeScript with Challenge 3 of the Seasons of Serverless challenge.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/46830"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=761"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/761\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/762"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}