azure-samples / azure-sql-db-session-recommender

Build a recommender using OpenAI, Azure Functions, Azure Static Web Apps, Azure SQL DB, Data API builder and Text Embeddings

page_type	languages	products	urlFragment	name	description
sample	azdeveloper csharp sql tsql javascript html bicep	azure-functions azure-sql-database static-web-apps sql-server azure-sql-managed-instance azure-sqlserver-vm dotnet azure-openai	azure-sql-db-session-recommender	Session Recommender using Azure SQL DB, Open AI and Vector Search	Build a session recommender using Jamstack and Event-Driven architecture, using Azure SQL DB to store and search vectors embeddings generated using OpenAI

Session Recommender Sample

A session recommender built using

• Azure Static Web Apps
• Azure OpenAI
• Azure Functions
• Azure SQL Database
• Data API builder

For more details on the solution check also the following articles:

• How I built a session recommender in 1 hour using Open AI
• Vector Similarity Search with Azure SQL database and OpenAI

Retrieval Augmented Generation (RAG)

An enhanced version of this sample, that also include Retrieval Augmented Generation (RAG), is available at this repository: https://github.com/Azure-Samples/azure-sql-db-session-recommender-v2. If you are new to similarity search and RAG, it is recommended to start with this repo and then move to the enhanced one.

Deploy the sample using the Azure Developer CLI (azd) template

The Azure Developer CLI (azd) is a developer-centric command-line interface (CLI) tool for creating Azure applications.

Prerequisites

• Install AZD CLI.
• Install .NET SDK.
• Install Node.js.
• Install SWA CLI.

Install AZD CLI

You need to install it before running and deploying with the Azure Developer CLI.

Windows

powershell -ex AllSigned -c "Invoke-RestMethod 'https://aka.ms/install-azd.ps1' | Invoke-Expression"

Linux/MacOS

curl -fsSL https://aka.ms/install-azd.sh | bash

After logging in with the following command, you will be able to use azd cli to quickly provision and deploy the application.

Authenticate with Azure

Make sure AZD CLI can access Azure resources. You can use the following command to log in to Azure:

azd auth login

Initialize the template

Then, execute the azd init command to initialize the environment (You do not need to run this command if you already have the code or have opened this in a Codespace or DevContainer).

azd init -t Azure-Samples/azure-sql-db-session-recommender

Enter an environment name.

Deploy the sample

Run azd up to provision all the resources to Azure and deploy the code to those resources.

azd up 

Select your desired subscription and location. Then choose a resource group or create a new resource group. Wait a moment for the resource deployment to complete, click the Website endpoint and you will see the web app page.

Note: Make sure to pick a region where all services are available like, for example, West Europe or East US 2

GitHub Actions

Using the Azure Developer CLI, you can setup your pipelines, monitor your application, test and debug locally.

azd pipeline config

Test the solution

Add a new row to the Sessions table using the following SQL statement (you can use tools like Azure Data Studio or SQL Server Management Studio to connect to the database. No need to install them if you don't want. In that case you can use the SQL Editor in the Azure Portal):

insert into web.sessions 
    (title, abstract)
values
    ('Building a session recommender using OpenAI and Azure SQL', 'In this fun and demo-driven session you’ll learn how to integrate Azure SQL with OpenAI to generate text embeddings, store them in the database, index them and calculate cosine distance to build a session recommender. And once that is done, you’ll publish it as a REST and GraphQL API to be consumed by a modern JavaScript frontend. Sounds pretty cool, uh? Well, it is!')

immediately the deployed Azure Function will get executed in response to the INSERT statement. The Azure Function will call the OpenAI service to generate the text embedding for the session title and abstract, and then store the embedding in the database, specifically in the web.session_abstract_embeddings table.

select * from web.session_abstract_embeddings

You can now open the URL associated with the created Static Web App to see the session recommender in action. You can get the URL from the Static Web App overview page in the Azure portal.

Run the solution locally

The whole solution can be executed locally, using Static Web App CLI and Azure Function CLI.

Install the required node packages needed by the fronted:

cd client
npm install

once finished, create a ./func/local.settings.json and .env starting from provided samples files, and fill out the settings using the correct values for your environment.

From the sample root folder run:

swa start --app-location ./client --data-api-location ./swa-db-connections/

(Optional) Use a custom authentication provider with Static Web Apps

The folder api contains a sample function to customize the authentication process as described in the Custom authentication in Azure Static Web Apps article. The function will add any user with a @microsoft.com to the microsoft role. Data API builder can be configured to allow acceess to a certain API only to users with a certain role, for example:

"permissions": [
    {
        "role": "microsoft",
        "actions": [{
            "action": "execute"
        }]
    }
]

This step is optional and is provided mainly as an example on how to use custom authentication with SWA and DAB. It is not used in the solution.

azure-sql-db-session-recommender's Issues

Add support for marking a session as favorite

Add support for marking a session as favorite:

• If user is logged add an empty star icon at the right of the session title
• When user click on the empty star, save the related title as a favorite, turn the icon to a full yellow star
• When user click on the full star, remove the related title from favorites, turn the icon back to empty star

Can we get an arm template to create some the resources. It will reduce the steps to get this up and running drastically

Can we get an arm template to create some the resources. It will reduce the steps to get this up and running drastically

Add Local Dependencies for azd to build the project successfully

Description

Add prerequisites section to the main README that includes the prerequisites for the deployment to succeed using azd.

• Function App written in .Net to depends on .NET SDK.
• Static Web App depends on SWA CLI.
• SWA CLI depends on Node.js.

Screenshot from build error

ERROR: failed deploying service 'web': failing invoking action 'deploy', failed deploying static web app: swa deploy: exit code: 1, stdout: , stderr: 'swa' is not recognized as an internal or external command,
operable program or batch file.

Convert embeddings Deployment Name to variable

According to Azure OpenAI API embedding docs:
https://learn.microsoft.com/en-us/azure/ai-services/openai/reference#embeddings

The deployment-id is the Azure OpenAI deployment name, not the word "embeddings" which is hardcoded in the URL whenever you make a request to Azure OpenAI.
POST https://{your-resource-name}.openai.azure.com/openai/deployments/{deployment-id}/embeddings?api-version={api-version}

If someone creates an OpenAI deployment with any name other than the word "embeddings" the sample won't work.

Replace username and password with the same used in the deployment

In the first deployment script, we create a username and password which are used to authenticate requests to the database everywhere.

azure-sql-db-session-recommender/infra/app/sqlserver.bicep

Lines 101 to 108 in b3ffe00

	cat <<SCRIPT_END > ./initDb.sql
	drop user ${APPUSERNAME}
	go
	create user ${APPUSERNAME} with password = '${APPUSERPASSWORD}'
	go
	alter role db_owner add member ${APPUSERNAME}
	go
	SCRIPT_END

Then in the second deployment script for the database, we create a new username that is not used anywhere.

azure-sql-db-session-recommender/database/setup-database.sh

Lines 217 to 225 in b3ffe00

	/*
	Create user
	*/
	if not exists(select * from sys.database_principals where name = 'session_recommender_app')
	begin
	create user session_recommender_app with password = 'unEno!h5!&*KP420xds&@P901afb$^M';
	alter role db_owner add member session_recommender_app;
	end
	go