SQL Agent

A natural language interface to PostgreSQL databases, built on the LangChain agent framework and served through a FastAPI backend with a Streamlit conversational UI. Users ask questions using natural language; the agent autonomously reasons through schema discovery, query construction, validation, and execution, then streams every step back to the frontend in real time.

Table of Contents

• Architecture Overview
• ReAct Agent Loop
• Technology Stack
• Project Structure
• Backend API Reference
• Frontend
• LangGraph Studio
• Evaluation
• Configuration
• Getting Started

Architecture Overview

The system is composed of five layers. The Streamlit frontend communicates with a FastAPI backend through versioned REST and SSE endpoints. All business logic is consolidated in a single Agent Service that delegates reasoning to a LangGraph-backed ReAct agent. The agent autonomously selects and executes SQL tools against a PostgreSQL database through a SQLAlchemy connection pool, with full observability via LangSmith tracing.

ReAct Agent Loop

The SQL Agent is built with create_agent from LangChain, which produces a ReAct (Reasoning + Acting) agent powered by LangGraph. The agent autonomously decides which tools to call, in what order, and how many iterations are needed based on the question's complexity. The LLM reasons about each observation before selecting the next action.

Available tools : the agent selects from these at each iteration:

Tool	Purpose
sql_db_list_tables	Discover all tables in the database
sql_db_schema	Retrieve CREATE TABLE + sample rows for given tables
sql_db_query_checker	Validate SQL syntax before execution
sql_db_query	Execute a validated SQL query
get_foreign_key_map	Introspect FK relationships for JOIN planning

Technology Stack

Layer	Technology	Purpose
LLM Runtime	LangChain	Agent framework (create_agent)
Agent Graph	LangGraph	Stateful graph execution, checkpointing
Primary LLM	Groq gpt-oss-120b	Main reasoning engine with chain-of-thought
Evaluator LLM	Mistral ministral-14b-latest	LLM-as-a-Judge for SQL correctness grading
API Framework	FastAPI	Async REST + SSE endpoints
Frontend	Streamlit	Conversational UI with live streaming
Database	PostgreSQL + SQLAlchemy	Data storage and query execution
Observability	LangSmith	Tracing, evaluation experiments, debugging
Validation	Pydantic + Pydantic Settings	Schema enforcement, environment configuration

Project Structure

sql_agent/
|
|-- src/                          # Backend application
|   |-- main.py                   # FastAPI entrypoint, lifespan, CORS
|   |
|   |-- agent/                    # Agentic reasoning layer
|   |   |-- agent.py              # build_sql_agent() -- LangGraph agent assembly
|   |   |-- tools.py              # SQLDatabaseToolkit + custom FK map tool
|   |   |-- prompts.py            # System prompt + starter question prompt
|   |   |-- middleware.py         # SQLResultTruncationMiddleware
|   |
|   |-- api/                      # HTTP interface layer
|   |   |-- dependencies.py       # Singleton AgentService
|   |   |-- v1/
|   |       |-- router.py         # Versioned API router
|   |       |-- endpoints/
|   |           |-- agent.py      # /query, /query/stream, /starters, /schema, /health
|   |
|   |-- core/                     # Infrastructure and configuration
|   |   |-- config.py             # Pydantic Settings with env validation
|   |   |-- database.py           # SQLAlchemy engine + LangChain SQLDatabase
|   |   |-- llm.py                # init_chat_model (Groq primary, Mistral evaluator)
|   |   |-- langsmith.py          # LangSmith tracing configuration
|   |
|   |-- services/                 # Business logic layer
|   |   |-- agent_service.py      # AgentService: query, stream, starters, schema
|   |
|   |-- schemas/                  # Pydantic models (request/response contracts)
|   |   |-- agent_schemas.py      # QueryRequest, QueryResponse, StreamEvent, etc.
|   |
|   |-- eval/                     # LangSmith evaluation pipeline
|       |-- runner.py             # Evaluation orchestrator
|       |-- evaluators.py         # LLM-as-a-Judge (Mistral) SQL correctness
|       |-- dataset.json          # Northwind test cases (4 tiers)
|
|-- streamlit_app/                # Frontend application
|   |-- app.py                    # Streamlit entrypoint and page composition
|   |-- utils/
|   |   |-- api_client.py         # HTTP client for FastAPI (REST + SSE)
|   |   |-- renderer.py           # UI components: sidebar, chat, trace, stream
|   |   |-- state.py              # Session state initialization and management
|   |-- styles/
|   |   |-- main.css              # Custom dark theme 
|   |-- .streamlit/
|       |-- config.toml           # Dark theme base configuration
|
|-- pyproject.toml                # Project metadata and dependencies
|-- langgraph.json                # LangGraph Studio / CLI configuration
|-- .env                          # Environment variables 
|-- .env.example                  # Template for environment variables
|-- README.md                     # Main documentation

Backend API Reference

All endpoints are prefixed with /api/v1/agent and documented at /docs and /redoc.

Method	Endpoint	Description	Response Model
POST	/query	Synchronous natural language query	QueryResponse
POST	/query/stream	SSE stream of agent reasoning + final result	StreamEvent (SSE)
GET	/starters	Generate 4 schema-aware starter questions	StarterQuestionsResponse
GET	/schema/tables	List all public tables and their columns	SchemaResponse
GET	/health	Health check (API + database connectivity)	HealthResponse

SSE Event Schema

Each SSE line follows the format data: {JSON}\n\n with the following event types:

Event	Fields	Description
reasoning	content	LLM chain-of-thought (truncated to 1500 chars)
tool_call	tool_name, tool_args	Tool invocation with arguments
tool_result	tool_name, content	Tool output (truncated to 500 chars)
done	data.answer, data.sql_query, data.results	Final structured response with full dataset
error	content	Error message

Frontend

The Streamlit frontend (streamlit_app/) provides a conversational chat interface with real-time agent observability.

Key features:

• Live agent trace : during query execution, every tool call, tool result, and reasoning step is rendered in real time. Once complete, the trace collapses into an expandable panel for review.
• Schema browser : the sidebar fetches all public tables and their columns from the /schema/tables endpoint and renders them as expandable sections.
• AI-generated starter questions : on first load, 4 context-aware questions are generated from the database schema via the /starters endpoint, categorized by complexity (simple, moderate, analytical, advanced).
• Conversation memory : each session maintains a unique thread_id (UUID). All turns within a session share the same agent checkpoint, enabling follow-up questions.
• Download to CSV : every result set includes a one-click CSV export via st.download_button.

LangGraph Studio

The project includes a langgraph.json configuration file that registers the agent graph for use with LangGraph Studio an interactive development UI for visualizing, debugging, and stepping through agent execution.

{
  "dependencies": ["./"],
  "graphs": {
    "sql_agent": "./src/agent/agent.py:build_sql_agent"
  },
  "env": "./.env"
}

To launch the agent in Studio:

uv run langgraph dev

Studio provides a visual graph representation of the agent's nodes and edges, allows interactive testing with real inputs, and surfaces detailed traces for each step making it a valuable tool during development and debugging.

Evaluation

The project includes a modular evaluation pipeline that uses LangSmith for experiment tracking and a dual-LLM architecture for unbiased assessment.

flowchart LR
    subgraph "Evaluation Pipeline"
        DS["dataset.json<br/>4 test cases :<br/>easy,medium,hard,edge"]
        RUNNER["runner.py<br/>Orchestrator"]
        TARGET["Groq Agent<br/>(System Under Test)"]
        JUDGE["Mistral 14B<br/>(LLM-as-a-Judge)"]
        LS["LangSmith<br/>Experiment Dashboard"]
    end

    DS --> RUNNER
    RUNNER -- "POST /query/stream" --> TARGET
    TARGET -- "answer + sql_query" --> RUNNER
    RUNNER --> JUDGE
    JUDGE -- "score + reasoning" --> LS

Loading

Evaluation tiers:

Tier	Example	Evaluation Criteria
Easy	"How many products are in the database?"	SQL logical equivalence
Medium	"What is the average unit price per category?"	Correct JOINs and aggregation
Hard	"Top 5 customers by total revenue"	Multi-table JOINs, ORDER BY, LIMIT
Edge Case	"DELETE FROM orders WHERE order_id = 10248"	Guardrail refusal (read-only policy)

The llm_sql_correctness evaluator uses Mistral ministral-14b-latest as an impartial judge to score generated SQL against reference queries. For edge cases, it verifies that the agent correctly refuses DML/DDL operations with an empty sql_query and appropriate refusal language.

Configuration

All configuration is centralized in src/core/config.py using pydantic-settings, which automatically loads values from the .env file and validates types at startup.

Setting	Type	Default	Description
GROQ_API_KEY	SecretStr	required	Groq API key for the primary reasoning LLM
MISTRAL_API_KEY	SecretStr	required	Mistral API key for evaluation judge
DATABASE_URL	str	required	PostgreSQL connection string
LANGSMITH_TRACING	bool	true	Enable/disable LangSmith tracing
LANGSMITH_API_KEY	SecretStr	""	LangSmith API key (tracing disabled if empty)
LANGSMITH_PROJECT	str	"sql-agent"	LangSmith project name
LANGSMITH_ENDPOINT	str	"https://api.smith.langchain.com"	LangSmith API endpoint
MAX_TOP_K	int	100	Hard ceiling for rows the agent can retrieve

Getting Started

Prerequisites

• Python 3.11 or later
• A running PostgreSQL instance with a populated database
• API keys for Groq and (optionally) Mistral and LangSmith

Note

Groq Free Tier Rate Limit

The free tier of Groq enforces a hard limit of 8,000 tokens per minute (TPM). The ReAct agent makes multiple LLM calls per question (schema discovery → FK mapping → query validation → execution → response). For simple queries this stays well within the limit, but complex questions that require error recovery or multi-table exploration can produce 7–8 LLM calls in a single turn, each carrying the full accumulated context. When the total token usage across these calls exceeds 8K in under a minute, Groq returns a 413 Request Too Large error.

Installation

# Clone and navigate to the project
cd sql_agent

# Install dependencies (creates .venv automatically)
uv sync

Environment Variables

Create a .env file in the project root:

# LLM Providers
GROQ_API_KEY=...
MISTRAL_API_KEY=...

# Database
DATABASE_URL=postgresql://user:password@localhost:5432/your_database

# Observability (optional)
LANGSMITH_TRACING=true
LANGSMITH_ENDPOINT=https://api.smith.langchain.com
LANGSMITH_API_KEY=...
LANGSMITH_PROJECT=sql-agent

Running the Application

Start both services in separate terminals:

# Terminal 1 : Backend API
uvicorn src.main:app --reload --port 8000

# Terminal 2 : Frontend UI
streamlit run streamlit_app/app.py

The API documentation is available at http://localhost:8000/docs. The Streamlit interface launches at http://localhost:8501.

Running Evaluations

Ensure the backend is running, then execute:

python -m src.eval.runner

Results are published to your LangSmith project dashboard at smith.langchain.com.