# azure-samples/azure-sql-nl2sql architecture

> Architecture diagram of the GitHub repository azure-samples/azure-sql-nl2sql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-02-03). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/azure-samples/azure-sql-nl2sql
- Repository: https://github.com/azure-samples/azure-sql-nl2sql

## Overview

##### Project Type and Purpose
- This is a .NET console application (azure-sql-nl2sql) that lets users “chat” with an AdventureWorks SQL Server database in natural language.
- It orchestrates multiple AI Agents (via Azure OpenAI chat models) to translate NL questions into SQL, execute them, and return results.

##### File Structure
- Top level:
- Program.cs – CLI entrypoint; parses commands (`chat`, `chat --debug`).
- Chat.cs – orchestrates Agent collaboration and the chat loop.
- Agent.cs – base or interface for individual AI agents (e.g. planning, SQL generation).
- Database.cs – handles ADO.NET calls to SQL Server using the connection string from `.env`.
- Logger.cs – centralized logging for debug vs. normal runs.
- prompts.txt – sample NL prompts.
- sql/ – contains SQL scripts (rls.sql, sample.sql) for reference.
- Config files: `.env.sample` for environment variables; `.gitignore`, `.vscode` settings.

##### Readme Insights
- External dependencies:
- Azure OpenAI chat model endpoint & key (or EntraID DefaultAzureCredential).
- Docker-hosted SQL Server with AdventureWorks2022.
- sqlcmd CLI for database provisioning.
- Usage flow:
- “dotnet run chat” invokes the chat scenario.
- “--debug” turns on verbose logging.

##### Main Components & Interactions

A. User / CLI

B. Program.cs (Application Host)
- Loads configuration from `.env`.
- Parses commands.
- Instantiates ChatService (Chat.cs), DatabaseService (Database.cs), Logger.
- → ChatService
- → Logger

C. ChatService (Chat.cs + Agent instances)
- Coordinates multiple Agent objects (from Agent.cs).
- Sequence:
1. Accept user question.
2. Feed to Agent A (e.g. “Question Planner”) → call Azure OpenAI.
3. Feed to Agent B (e.g. “SQL Generator”) → call Azure OpenAI.
4. Validate or refine SQL.
- ← responses

D. DatabaseService (Database.cs)
- Takes SQL statements from ChatService.
- Uses ADO.NET with MSSQL_CONNECTION_STRING to execute against SQL Server.
- ← query results

E. Logger (Logger.cs)
- Subscribes to events in Program, ChatService, DatabaseService.
- Outputs to console; more verbose in debug mode.

F. External Services
- Azure OpenAI Service (chat/completions endpoint)
- Azure SQL Server (Docker container running AdventureWorks)

##### Architectural Patterns & Design Principles
- Orchestration / Chain-of-Responsibility among AI Agents.
- Separation of concerns:
- Program.cs = host & configuration
- ChatService+Agent = business logic (NL2SQL orchestration)
- DatabaseService = data access layer
- Logger = cross-cutting concern
- Config-driven external dependencies via environment variables.

##### Diagramming Conventions
- Green for internal components (ChatService, DatabaseService).
- Blue for external services (Azure OpenAI, SQL Server).
- Orange for configuration (.env).
- Rectangles for code modules.

##### Final Checklist
- [ ] Program.cs (entrypoint)
- [ ] Configuration loader (.env)
- [ ] ChatService & Agent classes
- [ ] Azure OpenAI API boundary
- [ ] DatabaseService & SQL Server
- [ ] Logger (normal vs. debug)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Internal Components
    subgraph "Internal Components"
        direction TB
        User["User (CLI)"]:::internal
        Program["Program.cs"]:::internal
        Config[".env (Configuration)"]:::config
        ChatService["ChatService (Chat.cs)"]:::internal
        Agent["Agent Base/Interface (Agent.cs)"]:::internal
        DatabaseService["DatabaseService (Database.cs)"]:::internal
        Logger["Logger (Logger.cs)"]:::internal
        Prompts["prompts.txt"]:::util
        CSProj["azure-sql-nl2sql.csproj"]:::util
        VSCODE[".vscode"]:::util
        SQLRL["rls.sql"]:::util
        SQLSample["sample.sql"]:::util
    end

    %% External Services
    subgraph "External Services"
        direction TB
        AzureOpenAI(("Azure OpenAI Service")):::azure
        AzureSQL[(Azure SQL Server)]:::azure
    end

    %% Data Flows
    User -->|invokes| Program
    Program -->|loads| Config
    Program -->|parses commands| ChatService
    Program -->|initializes| Logger

    ChatService -->|uses| Agent
    ChatService -->|calls| AzureOpenAI
    AzureOpenAI -->|response| ChatService

    ChatService -->|sends SQL| DatabaseService
    DatabaseService -->|executes| AzureSQL
    AzureSQL -->|results| DatabaseService
    DatabaseService -->|returns results| ChatService

    Program -->|reads samples| Prompts
    Program -->|project info| CSProj
    Program -->|IDE settings| VSCODE
    Program -->|SQL refs| SQLRL
    Program -->|SQL refs| SQLSample

    Program -->|logs events| Logger
    ChatService -->|logs events| Logger
    DatabaseService -->|logs events| Logger

    ChatService -->|outputs| User

    %% Click Events
    click Program "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/Program.cs"
    click Config "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/.env.sample"
    click ChatService "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/Chat.cs"
    click Agent "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/Agent.cs"
    click DatabaseService "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/Database.cs"
    click Logger "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/Logger.cs"
    click Prompts "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/prompts.txt"
    click SQLRL "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/sql/rls.sql"
    click SQLSample "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/sql/sample.sql"
    click CSProj "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/azure-sql-nl2sql.csproj"
    click VSCODE "https://github.com/azure-samples/azure-sql-nl2sql/blob/main/.vscode"

    %% Styles
    classDef internal fill:#e0ffe0,stroke:#333,stroke-width:1px
    classDef azure fill:#e0f0ff,stroke:#333,stroke-width:1px
    classDef config fill:#ffe0b3,stroke:#333,stroke-width:1px
    classDef util fill:#f0f0f0,stroke:#333,stroke-width:1px

    %% Legend
    subgraph Legend
        direction TB
        A["Green: Internal Components"]:::internal
        B["Blue: External Services"]:::azure
        C["Orange: Configuration"]:::config
        D["Grey: Utilities & Files"]:::util
    end
```
