Skip to content

akshata29/agentnl2sql

BranchesTags

Repository files navigation

NL2SQL AI-Powered Natural Language to SQL

An end-to-end demo that translates plain-English questions into SQL, executes them against a real database, and streams the answer back to a React chat UI all powered by Azure AI Foundry Agents (Responses API v2), the Microsoft Agent Framework (MAF), and a custom MCP server.

Architecture Overview

  Browser
  React + TypeScript (Vite, Tailwind CSS)   http://localhost:3000
   Chat UI   Sidebar   Local/Remote MCP toggle

                             │
                             │  POST /api/chat  (Server-Sent Events)
                             ▼

  FastAPI Backend   http://localhost:8001
  backend/main.py
   Receives chat request (message + mode: local | remote)
   Manages in-memory conversation store (messages + thread IDs)
   Calls run_agent(mode=...)  ──►  streams response as SSE chunks

            │                              │
            │ mode=local                   │ mode=remote
            ▼                              ▼

  ┌──────────────────────────┐    ┌────────────────────────────────┐
  │ LOCAL MODE               │    │ REMOTE MODE                    │
  │ backend/agent.py         │    │ backend/agent.py               │
  │                          │    │                                │
  │ AzureOpenAI              │    │ AzureAIProjectAgentProvider    │
  │ ResponsesClient          │    │ McpTool baked into Foundry     │
  │ +                        │    │ agent definition at creation   │
  │ MCPStreamableHTTPTool    │    │ time (not at runtime)          │
  │                          │    │           │                    │
  │ MCP calls run in         │    │           │  Azure AI Foundry  │
  │ THIS Python process      │    │           ▼  (cloud)           │
  │ via HTTP                 │    │              Responses API v2  │
  └──────────────────────────┘    └────────────────────────────────┘
            │                              │ Foundry cloud calls
            │  HTTP to localhost           ▼ MCP_SERVER_URL
            ▼  (localhost is fine)           (must be publicly reachable)

  NL2SQL MCP Server   http://localhost:8080/mcp  (StreamableHTTP)
  mcp_server/server.py  (FastMCP)

  Tools:
   get_schema    ──  SQLAlchemy inspect → all schemas + tables/columns
   nl2sql_query  ──  Azure OpenAI (gpt-4o)  NL ──► SQL ──► executes
   execute_sql   ──  executes a known SQL statement directly

                             │
                             │  SQLAlchemy (pyodbc / psycopg2)
                             ▼

             SQL Server  /  PostgreSQL
             (e.g. Wide World Importers)

Key Design Decisions

Local vs Remote MCP mode

The UI header has a "Local MCP / Remote MCP" toggle. This controls how the MCP tools are invoked:

Local MCP (default) Remote MCP
Agent class Agent (MAF) + AzureOpenAIResponsesClient AzureAIProjectAgentProvider
MCP class MCPStreamableHTTPTool (agent_framework) McpTool (azure.ai.agents.models)
Where MCP calls run Inside the FastAPI Python process Foundry cloud calls MCP_SERVER_URL
MCP_SERVER_URL requirement localhost is fine Must be publicly reachable (ACA/AKS)
Foundry portal agent Agent visible in portal; MCP tools not shown (tools run in-process, not registered in Foundry) Agent visible in portal; MCP tools shown in the Tools section (baked into agent definition at creation)
Use when Local development Cloud deployment

Why McpTool at creation time, not runtime (remote mode)?
AzureAIProjectAgentProvider wraps AzureAIClient, which logs:

AzureAIClient does not support runtime tools. Use AzureOpenAIResponsesClient instead.
Tools passed at get_agent() / run() are silently dropped. The fix: pass McpTool only to create_agent() so it is baked into the Foundry agent definition. See backend/agent.py _ensure_remote().

Reference: Azure Foundry MCP Tool docs

Agent system prompt & tool workflow

The agent always follows this sequence (enforced in NL2SQL_AGENT_SYSTEM):

  1. get_schema called first on every question; returns all schemas / tables / columns from the live DB
  2. nl2sql_query passes the question + schema to Azure OpenAI (gpt-4o) to generate SQL, then executes it
  3. execute_sql used when the exact SQL is already known

If get_schema returns no tables, both nl2sql_query and the agent abort with a friendly message rather than hallucinating table names.

MCP server schema inspection

SQLAlchemy's get_table_names() without a schema= argument only sees the dbo schema. For multi-schema databases like Wide World Importers (Application, Sales, Purchasing, Warehouse) the MCP server enumerates all schemas:

# mcp_server/server.py  _get_schema()
schemas = [s for s in inspector.get_schema_names() if s not in _SYSTEM_SCHEMAS]
for schema in schemas:
    for table_name in inspector.get_table_names(schema=schema):
        ...
        parts.append(f"TABLE {schema}.{table_name} ({col_defs})")

SQL Server geography / geometry / hierarchyid columns are registered as String stubs so SQLAlchemy doesn't emit SAWarning: Did not recognize type noise.

MAF Python packages

Package Purpose
agent-framework==1.0.0rc2 Core Agent, MCPStreamableHTTPTool, AzureOpenAIResponsesClient
agent-framework-azure-ai AzureAIProjectAgentProvider, AzureAIClient
azure-ai-projects AIProjectClient, PromptAgentDefinition
azure-ai-agents McpTool, McpApprovalMode
mcp>=1.6.0 FastMCP, StreamableHTTPServerTransport

Environment Variables

mcp_server/.env

#  Database 
# DB_TYPE: "mssql" or "postgresql"
DB_TYPE=mssql

# SQL Server with username/password
DB_CONNECTION_STRING=mssql+pyodbc://user:password@server.database.windows.net:1433/database?driver=ODBC+Driver+18+for+SQL+Server

# PostgreSQL example
# DB_CONNECTION_STRING=postgresql+psycopg2://user:password@host:5432/database

#  Azure OpenAI (NLSQL translation inside nl2sql_query tool) 
AZURE_OPENAI_ENDPOINT=https://<resource>.openai.azure.com/
AZURE_OPENAI_API_KEY=<key>
AZURE_OPENAI_API_VERSION=2024-12-01-preview
AZURE_OPENAI_DEPLOYMENT=gpt-4o

#  Server 
MCP_SERVER_PORT=8080
MCP_SERVER_RELOAD=false

backend/.env

#  Azure Service Principal 
# Used by both Local and Remote mode to authenticate to Azure AI Foundry.
# Grant the SP "Azure AI Developer" role on the Foundry project.
AZURE_TENANT_ID=<tenant-id>
AZURE_CLIENT_ID=<client-id>
AZURE_CLIENT_SECRET=<client-secret>

#  Azure AI Foundry Project 
# Format: https://<resource>.services.ai.azure.com/api/projects/<project-name>
FOUNDRY_PROJECT_ENDPOINT=https://<resource>.services.ai.azure.com/api/projects/<project>
FOUNDRY_MODEL_DEPLOYMENT_NAME=gpt-4o

# Agent name used to create/update versions in the Foundry portal (remote mode)
FOUNDRY_AGENT_NAME=NL2SQLAgent

#  MCP Server URL 
# LOCAL mode:  leave as localhost (MCP runs inside the FastAPI process)
# REMOTE mode: set to your deployed container URL
#              e.g. https://nl2sql-mcp.myapp.azurecontainerapps.io/mcp
MCP_SERVER_URL=http://localhost:8080/mcp

#  FastAPI 
CORS_ORIGINS=http://localhost:3000

Local Development

Prerequisites

  • Python 3.11+
  • Node.js 20+
  • ODBC Driver 18 for SQL Server (Windows)
  • An Azure AI Foundry project with a gpt-4o deployment
  • An Azure OpenAI resource (can be the same endpoint) for the MCP server

One-command setup

setup.bat

This creates .venv, installs all Python deps (mcp_server/requirements.txt + backend/requirements.txt), runs npm install, and copies .env.template .env for each service.

Fill in .env files

Edit mcp_server/.env and backend/.env with your real values (see Environment Variables above).

Start services

# Option A  one command, three windows
run_all.bat

# Option B  individual terminals
.\run_mcp_server.bat   # Terminal 1: MCP server   http://localhost:8080
.\run_backend.bat      # Terminal 2: FastAPI       http://localhost:8001
.\run_frontend.bat     # Terminal 3: React UI      http://localhost:3000

Start order matters: MCP server Backend Frontend.
The backend calls ensure_foundry_agent() at startup using the MCPStreamableHTTPTool (local mode, no Foundry round-trip needed).

VS Code debug

.vscode/launch.json has:

  • MCP Server debugpy on mcp_server/server.py
  • Backend (FastAPI) debugpy on backend/main.py
  • Full Stack (MCP + Backend) compound launch for both simultaneously

Run the React dev server via the "Start: Frontend dev server" VS Code Task.

End-to-End Request Flow (Local Mode)

1. User types "Show top 10 customers by revenue"  clicks Send
   frontend/src/components/ChatWindow.tsx  sendMessage()

2. POST /api/chat  { message, conversation_id, mode: "local" }
   frontend/src/api/client.ts  streamChat()

3. FastAPI receives request, looks up or creates conversation entry
   backend/main.py  chat()
   conv["foundry_thread_id_local"]   resumed on subsequent turns

4. run_agent(user_message, mode="local")
   backend/agent.py  run_agent()
    _ensure_local() builds Agent singleton (once at startup)
      AzureOpenAIResponsesClient(project_client=)  [Responses API v2]
      MCPStreamableHTTPTool(name="nl2sql", url="http://localhost:8080/mcp")

5. agent.run(message, session=session)
   MAF routes the turn to AzureOpenAIResponsesClient.get_response()
    POST https://<foundry>/openai/v1/responses  (gpt-4o)

6. Model decides to call get_schema tool
   MCPStreamableHTTPTool connects to http://localhost:8080/mcp
   MCP protocol: initialize  tools/call get_schema
   mcp_server/server.py  get_schema()
    SQLAlchemy inspector enumerates all schemas (Application, Sales, )
    Returns "TABLE Sales.Customers (CustomerID INT, CustomerName NVARCHAR, )\n"

7. Model receives schema, decides to call nl2sql_query
   mcp_server/server.py  nl2sql_query(question)
    Calls Azure OpenAI (chat4o) with schema as system context
    Generates SQL:
       SELECT TOP 10 c.CustomerID, c.CustomerName, SUM(il.ExtendedPrice) AS Revenue
       FROM Sales.Customers c
       JOIN Sales.Invoices i ON c.CustomerID = i.CustomerID
       JOIN Sales.InvoiceLines il ON i.InvoiceID = il.InvoiceID
       GROUP BY c.CustomerID, c.CustomerName
       ORDER BY Revenue DESC;
    Executes via SQLAlchemy  returns {"sql":, "row_count":10, "results":[]}

8. Model formats response as Markdown table + SQL block

9. Agent response text returned to backend/main.py
   Streamed as SSE chunks: data: {"text": ""}\n\n
   Final event:            data: {"event": "done", "conversation_id": ""}\n\n

10. React ChatWindow receives chunks via ReadableStream, appends to message
    ChatMessage.tsx renders Markdown with syntax-highlighted SQL blocks

End-to-End Request Flow (Remote Mode)

Steps 13 identical. Steps 49:

4. run_agent(user_message, mode="remote")
   backend/agent.py  run_agent()
    _ensure_remote() on first call:
      Creates AIProjectClient + AzureAIProjectAgentProvider
      McpTool(server_label="nl2sql", server_url=MCP_SERVER_URL, require_approval="never")
      provider.create_agent(name="NL2SQLAgent", instructions=, tools=[mcp_tool])
       agent version visible in Foundry portal under Agents  NL2SQLAgent

5. agent.run()  Responses API v2
   POST https://<foundry>/api/projects/<proj>/openai/v1/responses

6. Foundry cloud calls MCP_SERVER_URL (must be public)
   e.g. POST https://nl2sql-mcp.myapp.azurecontainerapps.io/mcp

710 identical to local mode.

Why tools must be baked at creation, not runtime:
AzureAIClient (used internally by AzureAIProjectAgentProvider) only supports tools set in the agent definition. Passing McpTool to get_agent() or run() is silently ignored. The create_agent() call pushes a new agent version to Foundry, making the MCP tool visible in the portal's Tools section.
Reference: azure-ai-agents SDK source

Deploying MCP Server to Azure Container Apps (for Remote mode)

# Build and push
az acr build --registry <acr> --image nl2sql-mcp:latest ./mcp_server
az acr build --registry <acr> --image nl2sql-backend:latest ./backend
az acr build --registry <acr> --image nl2sql-frontend:latest ./frontend

# Deploy MCP server as a Container App with external ingress
az containerapp create \
  --name nl2sql-mcp \
  --resource-group <rg> \
  --image <acr>.azurecr.io/nl2sql-mcp:latest \
  --ingress external --target-port 8080 \
  --env-vars DB_CONNECTION_STRING=<...> AZURE_OPENAI_ENDPOINT=<...> ...

# Update backend to use the deployed URL
# backend/.env:  MCP_SERVER_URL=https://nl2sql-mcp.<uid>.azurecontainerapps.io/mcp
# Switch UI to "Remote MCP" in header toggle

For production, replace ClientSecretCredential with Managed Identity:

  • Remove AZURE_CLIENT_ID/SECRET from backend .env
  • Assign Azure AI Developer role to the Container App's system-assigned identity
  • DefaultAzureCredential picks it up automatically

Docker Compose (local all-in-one)

docker-compose up --build -d   # build + start all services
docker-compose logs -f         # tail logs
docker-compose down            # stop

The React frontend is served by nginx on port 3000; nginx proxies /api backend.

References

Topic Link
Azure AI Foundry MCP tool https://learn.microsoft.com/en-us/azure/foundry/agents/how-to/tools/model-context-protocol
Foundry Responses API (v2) https://learn.microsoft.com/en-us/azure/ai-foundry/agents/concepts/responses-api
Microsoft Agent Framework https://github.com/microsoft/agent-framework
MCP specification https://modelcontextprotocol.io/introduction
Wide World Importers schema https://learn.microsoft.com/en-us/sql/samples/wide-world-importers-oltp-database-catalog
azure-ai-agents SDK https://pypi.org/project/azure-ai-agents/
azure-ai-projects SDK https://pypi.org/project/azure-ai-projects/

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages