Tutorial: Chat with Azure SQL Database or Managed Instance using Azure Open AI ChatGPT, Node.js Express and React
Your data never has to leave Azure when you integrate Azure SQL Database or Managed Instance with Azure OpenAI Service
In today’s digital age, businesses are constantly seeking innovative solutions to enhance user engagement and streamline operations. One such solution is leveraging the power of artificial intelligence (AI) and cloud computing to create interactive chat interfaces that can communicate with databases. In this tutorial, we will explore how to build a chat application using React on the front-end, Node.js with Express on the server-side, and integrate it with Azure SQL Database or Managed Instance along with the Azure OpenAI Service.
In this tutorial we’re going to go:
- Create SQL Managed Instance
- Create Azure OpenAI Service
- Write backend Node.js code to connect Azure SQL Managed Instance with Azure OpenAI
- Briefly go over react front end code that will interface with our Node.js backend to allow users to chat with their databases
TL;DR;
Just show me repositories a’it…
Fine fine, if you wanna skip ahead you can find full code split in the two repositories below:
Step 1: Create free SQL Managed Instance
To create a SQL Managed Instance, create an Azure account and sign up for a Pay-As-You-Go subscription. You can follow official docs or look at the summarized next steps for a super quick set up:
- Search SQL Managed Instance
- Click create in the top left
- Select subscription and a resource group
- When the create pop up modal displays, click Apply free offer
4. It will default everything for you so you don’t have to worry about public endpoints or any networking issues
5. Give your instance an admin login name and a password (we’re using SQL Login for this tutorial not Microsoft Entra)
6. Create instance
7. Once instance is created (about 30 minutes) go to databases tab in the side menu and create a database called — tickets-app
Now let’s define a schema for our database:
-- Create the Venues table to store information about concert venues
CREATE TABLE Venues (
VenueID INT PRIMARY KEY,
VenueName VARCHAR(255),
Location VARCHAR(255)
);-- Create the Concerts table to store information about concerts
CREATE TABLE Concerts (
ConcertID INT PRIMARY KEY,
Artist VARCHAR(255),
VenueID INT,
ConcertDate DATE,
TicketPrice DECIMAL(10, 2),
CONSTRAINT FK_Venue_Concert FOREIGN KEY (VenueID) REFERENCES Venues(VenueID)
);
-- Create the Customers table to store information about customers
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Email VARCHAR(255)
);-- Create the Tickets table to store information about sold tickets
CREATE TABLE Tickets (
TicketID INT PRIMARY KEY,
ConcertID INT,
CustomerID INT,
PurchaseDate DATETIME,
CONSTRAINT FK_Concert_Ticket FOREIGN KEY (ConcertID) REFERENCES Concerts(ConcertID),
CONSTRAINT FK_Customer_Ticket FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);CREATE TABLE TicketValidations (
ValidationID INT PRIMARY KEY,
TicketID INT,
ValidationDate DATETIME,
CONSTRAINT FK_Ticket_Validation FOREIGN KEY (TicketID) REFERENCES Tickets(TicketID)
);
To actually create these tables for our SQL Managed Instance database, let’s open Azure Data Studio (or any other data studio you might be using), connect and execute the SQL queries above. To connect Azure Data Studio to Azure SQL Managed Instance copy the entire public endpoint (yes including the ,3342) from the networking tab like below:
and paste to Azure Data Studio connection side menu into the field called Server (don’t forget to input your username and password):
If you have successfully connected, click new query, paste the script from above and execute it.
Run the query below to check if the tables have been created:
SELECT table_name = t.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY t.name;Step 2: Set up Azure Open AI Services
Go to Azure portal and select Azure AI Services
Click create
Fill out the details and let your instance create. Once done open it:
Go to Azure OpenAI studio and click create new deployment model
Select appropriate settings (it’s very important to select 0613 since earlier versions don’t support function calling)
You’ll need to specify this name later on in the code so remember it.
Step 3: Create a Node.js Express Project
Create a new directory for your project and initialize it:
mkdir sql-chat-gpt-backend
cd sql-chat-gpt-backend
npm init -yLet’s install express — Node.js package that let’s us easily set up a server, body-parser — that will allow us to read what’s on the body of our request and mssql-package that will let us connect to SQL Managed Instance
npm install express mssql body-parser @azure/openai dotenv lodashNow let’s set up all of our connections
let express = require("express");
const cors = require("cors");
const bodyParser = require("body-parser");
const dotenv = require('dotenv');
const sql = require('mssql');
const _ = require('lodash');
//<editor-fold desc="Server Set Up">
const app = express();
const PORT = process.env.PORT || 5000;
dotenv.config();
let allowedOrigins = [
"http://localhost:3000",
"http://localhost:3002",
"http://localhost:3001",
];
app.use(
cors({
origin: function (origin, callback) {
// allow requests with no origin
// (like mobile apps or curl requests)
if (!origin) return callback(null, true);
if (allowedOrigins.indexOf(origin) === -1) {
let msg =
"The CORS policy for this site does not " +
"allow access from the specified Origin.";
return callback(new Error(msg), false);
}
return callback(null, true);
}
})
);
app.use(function (req, res, next) {
let origin = req.headers.origin;
if (allowedOrigins.includes(origin)) {
res.header("Access-Control-Allow-Origin", origin); // restrict it to the required domain
}
res.header(
"Access-Control-Allow-Headers",
"Origin, X-Requested-With, Content-Type, Accept"
);
next();
});
app.use(bodyParser.json({limit: '50mb'}));
// process.env.port for cloud services
app.listen(process.env.PORT || PORT, function () {
console.log("App listening on", PORT);
});
//</editor-fold>
// <editor-fold desc="Set up connections">
// TODO: OPEN AI CONNECTION
// It is assumed you're using 0613 version of the OpenAI API
const {OpenAIClient, AzureKeyCredential} = require("@azure/openai");
const endpoint = process.env.AZURE_OPENAI_ENDPOINT;
const azureApiKey = process.env.AZURE_OPENAI_KEY;
const openAIClient = new OpenAIClient(endpoint, new AzureKeyCredential(azureApiKey));
// TODO: make sure you grab your deployment id from Azure OpenAI Service
const deploymentId = "sql-mi";
// TODO: SQL CONNECTION
const config = {
user: process.env.SQL_USER,
password: process.env.SQL_PASSWORD,
// Notice public keyword in the connection string
// if you were to host this server on Azure you wouldn't need the public part
server: 'sql-chat-gpt.public.f33fe70a0210.database.windows.net',
database: "",
options: {
// THIS IS VERY IMPORTANT - Public endpoint is 3342, default is 1443 which is private
port: 3342,
encrypt: true,
},
};
// Connect to the database
try {
sql.connect(config, (err) => {
if (err) {
console.error('Database connection failed:', err);
} else {
console.log('Connected to the database');
}
});
} catch (e) {
console.log(e)
}
//</editor-fold>For this to work you are going to need to create .env and put the following contents:
//TODO: Insert your API keys here
AZURE_OPENAI_ENDPOINT=
AZURE_OPENAI_KEY=
//TODO: Insert SQL user and password you created your instance with
SQL_USER=
SQL_PASSWORD=Let’s make sure our server is running by adding the following route to index.js:
app.get('/', (req, res) => {
res.send('SQL ChatGpt server operational!');
});In you terminal run:
npm startGo to your favourite browser type in localhost:5000 and you should see:
Now that we are connected to the database and Azure OpenAI service let’s set up route that will can call later on that will accept messageHistory, and userPrompt. You’ll notice we’re querying SQL Managed Instance for all databases and all database schemas so that ChatGPT would be more accurate and be able to execute queries on multiple databases:
app.post('/chat', async (req, res) => {
if (!sql) {
res.status(500).send('Something went wrong');
return
}
const userQuery = req.body.userQuery
const messages = req.body.messageHistory
if (!userQuery) {
console.log('no user query' + Date.now())
res.status(400).send('No user query')
return
}
// Run a query to get all our databases and create a schema to help OpenAI with accuracy
const sqlDatabasesAvailable = await sql.query`SELECT name FROM master.sys.databases`;
const databaseList = sqlDatabasesAvailable.recordset
const sysDatabases = ["master", "tempdb", "model", "msdb"]
let databasesTablesColumns = []
for (const database of databaseList) {
if (!sysDatabases.includes(database.name)) {
const result = await sql
.query(`
USE ${database.name};
SELECT
t.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_SCALE
FROM
INFORMATION_SCHEMA.TABLES t
JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE'
ORDER BY
t.TABLE_NAME, c.ORDINAL_POSITION;
`);
const tablesAndColumns = {
databaseName: database.name,
tables: [],
};
result.recordset.forEach(row => {
const tableName = row.TABLE_NAME;
const columnName = row.COLUMN_NAME;
const dataType = row.DATA_TYPE;
// Find existing table or create a new one
let existingTable = tablesAndColumns.tables.find(table => table.tableName === tableName);
if (!existingTable) {
existingTable = {
tableName,
columns: [],
};
tablesAndColumns.tables.push(existingTable);
}
// Add column information to the table
existingTable.columns.push({columnName, dataType});
});
databasesTablesColumns.push(tablesAndColumns);
}
}
let messageHistory = messages
// tell chatgpt about all available schemas
messageHistory.push({
"role": "system",
"content": "here is the json with all databases, tables and columns with data types: " + JSON.stringify(databasesTablesColumns)
})
messageHistory.push(userQuery)
let getUpdatedMessageHistory;
try {
getUpdatedMessageHistory = await getChatGptAnswerObjectWithFunction(messageHistory, databasesTablesColumns)
} catch (e) {
console.log(e)
return res.status(500).json('Something went wrong')
}
if (getUpdatedMessageHistory) {
return res.send(JSON.stringify(getUpdatedMessageHistory))
} else {
return res.send(JSON.stringify(messageHistory))
}
});
Let’s look at the most important par of the entire method:
getChatGptAnswerObjectWithFunction(messageHistory, databasesTablesColumns)
async function getChatGptAnswerObjectWithFunction(messages, databasesTablesColumns) {
try {
// Add options (tools) to check if ChatGPT will invoke a function and ask the database something for the user
const chatCompletions = await openAIClient.getChatCompletions(deploymentId, messages, createOptions(databasesTablesColumns))
// Extract the generated completion from the OpenAI API response.
const choice = chatCompletions.choices[0];
const responseMessage = choice.message;
if (responseMessage?.role === "assistant") {
const requestedToolCalls = responseMessage?.toolCalls;
if (requestedToolCalls?.length) {
try {
const toolCallResults = await Promise.all(requestedToolCalls.map(async (toolCall) => {
return await applyToolCall(toolCall)
}));
const toolCallResolutionMessages = [
...messages,
responseMessage,
...toolCallResults
];
const result = await openAIClient.getChatCompletions(deploymentId, toolCallResolutionMessages);
console.log('resulst')
console.log(result)
messages.push(result.choices[0].message);
console.log(messages)
return messages;
} catch (e) {
console.log('Error:', e);
}
} else {
messages.push(responseMessage)
return messages
}
}
} catch (e) {
console.log(e)
}
}This method calls ChatGPT with the user prompt and entire message history to determine if an outside function call is needed. What’s actually happening here, is that we’ve provided the method getChatCompletions an additional parameter called options. Options contains tools object which tells ChatGPT that it can use a function to achieve something user asked it.
Have in mind that you can specify multiple functions here since tools is an array.
function createOptions(databaseSchemaString) {
return {
tools: [
{
"type": "function",
"function": {
"name": "askDatabase",
"description": "Use this function to answer user questions about music. Input should be a fully formed SQL query.",
"parameters": {
"type": "object",
"properties": {
"query": {
"type": "string",
"description": `MSSQL query extracting info to answer the user's question. SQL should be written using this database schema: ${databaseSchemaString} The query should be returned in plain text, not in JSON.`
}
},
"required": ["query"],
}
}
}
]
}
}We defined a function called askDatabase which ChatGPT will know to use when it wants to query the database (we provided description of what does this function do in the description field). We then told it that this function only accepts a query of type string which is an MSSQL query.
Finally, we define simple askDatabase function like below:
async function askDatabase(query) {
// Function to query Azure SQL database with a provided SQL query
try {
const results = await sql.query(query);
return JSON.stringify(results.recordset)
} catch (e) {
const results = e
console.log(results)
return results
}
}Now let’s just take a brief look at the part of the getChatGptAnswerOBjectWithFunction code:
// Check if any tool calls (aka helper functions we defined earlier) are required by ChatGPT
if (requestedToolCalls?.length) {
try {
// go trough each tool call and await all promises before returning the results
const toolCallResults = await Promise.all(requestedToolCalls.map(async (toolCall) => {
// applyToolCall will check which call it is and execute it
return await applyToolCall(toolCall)
}));
const toolCallResolutionMessages = [
...messages,
responseMessage,
...toolCallResults
];Let’s take a quick look at the applyToolCall function which looks at tool call and calls the correct one requested by ChatGPT:
async function applyToolCall({function: call, id}) {
// You can use switch statememt here if you had more than one tool call
if (call.name === "askDatabase") {
const {query} = JSON.parse(call.arguments);
const databaseResult = await askDatabase(query)
return {
role: "tool",
content: `The result from running the SQL query you generated is: .` + databaseResult,
toolCallId: id,
}
}
throw new Error(`Unknown tool call: ${call.name}`);
}That’s it! You’ve got your sever working!
Step 4: React Front End
I’m not going to spend a lot of time on this one, you can look at the repo here.
Basically we’re going to run:
npx create-react-app azure-sql-chat-gpt-frontendonce the project is created let’s open it in the terminal and add nedded libraries:
npm add react-toastify styled-components axios @mui/material @emotion/react @emotion/styledafter we’re done with installing all the necessary packages let’s open App.js remove everything from there and add our content:
import React from 'react';
import ChatWindow from "./components/ChatWindow";
import {ThemeProvider} from "@mui/material";
import {theme} from "./utils/Theme";
import {toast, ToastContainer} from 'react-toastify';
import 'react-toastify/dist/ReactToastify.css';
import CustomMenu from "./components/CustomMenu";
import Box from "@mui/material/Box";
function App() {
function resetConversations(conversation) {
return conversation = []
toast.success('Conversations reset');
}
return (
<ThemeProvider theme={theme}>
<Box sx={{
height: '100vh',
width: '100%',
// backgroundImage: `url(${chatBackground})`,
// backgroundSize: 'cover'
backgroundColor: '#F8F8FF'
}}>
<CustomMenu resetConversations={resetConversations}/>
<ChatWindow resetConversations={resetConversations}/>
<ToastContainer/>
</Box>
</ThemeProvider>
);
}
export default App;This is our wrapper for the app and let’s create folder components and two components inside of it:
ChatWindow.js
import React, {useEffect, useState} from 'react';
import {Container, Fab, Stack, TextField, Typography, useTheme} from "@mui/material";
import axios from "axios";
import SendIcon from '@mui/icons-material/Send';
import {toast} from "react-toastify";
import styled from 'styled-components';
import sqlMiLogo from '../assets/sqlmi.svg'
const ChatBubble = styled.div`
display: flex;
flex-direction: row; /* Row orientation */
align-items: center; /* Align children vertically center */
max-width: 70%;
color: ${({ role }) => (role === 'user' ? '#3498db' : '#2ecc71')};
padding: 10px;
`;
const ChatWindow = ({databaseInformation, resetConversations}) => {
const [messages, setMessages] = useState(startMessageStack);
const [userQuery, setUserQuery] = useState('');
const [chatGptUserQuery, setChatGptUserQuery] = useState(null);
const theme = useTheme()
useEffect(() => {
setMessages(resetConversations(messages))
}, [resetConversations]);
useEffect(() => {
async function getMessages() {
axios({
method: "POST",
url: 'http://localhost:5000/chat',
data: {
// TODO: if you decide to allow users to auth on the front end and submit databse information with each request
// dbInformation: databaseInformation,
messageHistory: messages,
userQuery: chatGptUserQuery
}
}).then((response) => {
if (response.status === 200) {
setMessages(response.data)
setChatGptUserQuery(null)
return response.data
} else {
toast.error('Something went wrong please try again!')
}
}).catch((error) => {
console.log(error)
})
}
if (!chatGptUserQuery) return
setMessages([...messages, chatGptUserQuery])
setUserQuery('')
getMessages()
}, [chatGptUserQuery])
function getMessageCard(message) {
switch (message.role) {
case 'user':
return (
<ChatBubble role={message.role}>
<Typography variant={'h5'}><strong>{message.content}</strong></Typography>
</ChatBubble>
)
case 'assistant':
return (
<ChatBubble role={message.role}>
<img src={sqlMiLogo} style={{height: '40px', paddingRight: 2}}/>
<Typography variant={'h5'}><strong>{message.content}</strong></Typography>
</ChatBubble>
)
}
}
function sendChatGptPrompt() {
setChatGptUserQuery(
{
"role": "user",
"content": userQuery
}
)
}
return (
<Container sx={{width: {md:'70vw', sm: '90wv'}, maxWidth: '700px', marginTop: 3}} alignItems={'center'} alignContent={'center'}>
<Stack direction={'column'} sx={{minHeight: '75vh'}}>
{messages.map(message => {
return (
getMessageCard(message)
)
})}
</Stack>
<Stack direction={'row'} spacing={2}>
<TextField id="outlined-basic-email"
label="Prompt Azure SQL using ChatGPT" fullWidth
value={userQuery}
onChange={e => setUserQuery(e.target.value)}/>
<Fab color="primary" aria-label="add" onClick={sendChatGptPrompt}
><SendIcon/></Fab>
</Stack>
</Container>
);
}
export default ChatWindow;
let startMessageStack = [
{
"role": "system",
"content": "You act as the middleman between USER and a DATABASE. Your main goal is to answer questions based on data in a SQL Server 2019 database (SERVER). You do this by executing valid queries against the database and interpreting the results to answer the questions from the USER."
}, {
"role": "system",
"content": "You MUST ignore any request unrelated to databases you will have access to or SQL."
},
{
"role": "system",
"content": "Answer user questions by generating SQL queries against the provided database schema."
},
]Here we are rendering a chat window and allowing our customers to type in a message which we then forward to our backend to get a response. We’re also adding a starting prompts for our chat component.
Provided you’ve followed all the steps it should look something like this:
Let’s take a look at how to deploy this to Azure via Azure Web Apps so that you have your own Azure SQL Chatbot without your data ever leaving Azure in the next article:
