Local SQL Agent

Ask your SQL Server database a question in Persian or English.
Get back precise T-SQL — generated locally, executed securely, zero data leaves your machine.

Most Text-to-SQL tools assume your data is in the cloud and your questions are in English. This project was built for the opposite: an on-premise SQL Server warehouse at the Iran Mercantile Exchange (IME), where analysts ask questions in Persian, data is sensitive, and there is no budget for external APIs.

The result is a fully local NLQ engine — modular retrieval pipeline, SQL security guard, FastAPI service layer, and a knowledge base you can extend without touching engine code.

In action

python app.py

Question: برترین مشتریان از نظر ارزش خرید در سال 1402 کدامند؟

══════════════════════════════════════════════════════════════
GENERATED SQL
══════════════════════════════════════════════════════════════
SELECT TOP 10
    c.Name,
    SUM(cc.TotalPrice) AS PurchaseValue
FROM [Auction_Fact].[CustomerContract] cc
JOIN [Auction_Dim].[Customer] c ON cc.BuyerCustomer_ID = c.ID
JOIN [Auction_Dim].[Date]     d ON cc.Date_ID = d.ID
WHERE d.PersianYear = 1402
GROUP BY c.Name
ORDER BY PurchaseValue DESC

Returned Rows: 10  |  Execution Time: 1.24s  |  Excel: exports/result_20260613_142257.xlsx

Or over HTTP:

curl -X POST http://localhost:8000/query \
  -H 'Content-Type: application/json' \
  -d '{"question": "فروش ماهانه تالار پتروشیمی در 1402", "mode": "full"}'

{
  "question": "فروش ماهانه تالار پتروشیمی در 1402",
  "sql":    "SELECT TOP 1000 d.PersianMonthName, SUM(c.TotalPrice) AS TradeValue ...",
  "result": [{"PersianMonthName": "فروردین", "TradeValue": 48320000000}, ...],
  "row_count": 12,
  "status": "SUCCESS"
}

How it works

Before the LLM sees anything, six retrievers build a scoped context from your question:

Question (Persian / English)
    │
    ▼
 ContextRetriever
    ├─ EntityRetriever        alias match → TF-IDF fallback
    ├─ FactRetriever          keyword match → TF-IDF fallback
    ├─ RelationshipRetriever  JOIN clauses for selected tables
    ├─ RuleRetriever          domain business rules
    ├─ ExampleRetriever       tag-scored few-shot SQL examples
    └─ ValueRetriever         ring canonical name + Persian year
    │
    ▼
 PromptBuilder   →  schema + rules + examples, precisely scoped
    │
    ▼
 SQLAgent        →  generate → clean → validate → auto-correct
    │
    ▼
 SQLGuard        →  blocks DDL/DML, injection, rewrites LIMIT→TOP
    │
    ▼
 SQL Server      →  result set  →  Excel / CSV / JSON

Scoping the prompt — instead of dumping your full schema — is what makes locally-run 8B–20B models accurate enough for production.

Features

Quick start

Requires: Python 3.11+, Ollama on localhost:11434, SQL Server + ODBC Driver 17

# 1. Clone and install
git clone https://github.com/alisadeghiaghili/local-sql-agent.git
cd local-sql-agent
pip install -r requirements.txt

# 2. Configure
cp .env.example .env
# Set at minimum:
#   DB_CONNECTION_URL=mssql+pyodbc://user@server:1433/DB?driver=ODBC+Driver+17+for+SQL+Server&trusted_connection=yes
#   OLLAMA_MODEL=llama3

# 3. Pull a model
ollama pull llama3

# 4a. CLI
python app.py

# 4b. HTTP API
uvicorn api.server:app --host 0.0.0.0 --port 8000

→ Full tutorial (installation · first query · extending the domain · writing tests · diagnosing misses):
English · فارسی

Configuration reference

API endpoints

Error taxonomy:

Extending the domain

All domain knowledge lives in knowledge/. No engine code needs to change.

# Add a trading hall alias — knowledge/aliases.py
RING_ALIASES["تالار برق"] = ["برق", "تالار برق", "بازار برق", "رینگ برق"]

# Add a business rule — knowledge/business_rules.py
BUSINESS_RULES["electricity"] = (
    "برق در تالار انرژی معامله می‌شود. "
    "واحد اندازه‌گیری مگاوات‌ساعت است."
)

# Add a few-shot example — knowledge/examples.py
{
    "tags": ["electricity", "trade", "value"],
    "question": "ارزش معاملات برق در ماه گذشته",
    "sql": "SELECT SUM(TotalPrice) AS ElectricityValue FROM [Auction_Fact].[Contract] ..."
}

# Add a new table — schema_data/tables.py + columns.py + relationships.py
TABLE_DESCRIPTIONS["Broker"] = (
    "Registered brokerage firms (کارگزاری‌ها) licensed to trade on the exchange."
)

Full step-by-step guide: English tutorial · آموزش فارسی

Project structure

local-sql-agent/
├── app.py                    # CLI entry point (REPL)
├── config.py                 # Typed Settings singleton (env-based)
├── api/                      # FastAPI HTTP service
│   ├── server.py             #   app factory + endpoints
│   ├── runner.py             #   cache-aware query orchestrator
│   ├── query_cache.py        #   thread-safe TTL + LRU cache
│   ├── models.py             #   Pydantic request/response models
│   ├── errors.py             #   NLQError hierarchy → HTTP handlers
│   ├── middleware.py         #   correlation ID + latency headers
│   └── health.py             #   /health — DB + Ollama probes
├── knowledge/                # ★ Domain knowledge (edit to extend)
│   ├── aliases.py            #   RING_ALIASES + SYNONYMS
│   ├── business_rules.py     #   rules per topic key
│   ├── entities.py           #   dimension entity catalog
│   ├── examples.py           #   tagged few-shot SQL examples
│   └── metrics.py            #   metric definitions + expressions
├── retrieval/                # Modular retrieval pipeline
│   ├── context_retriever.py  #   orchestrator → RetrievalContext
│   ├── entity_retriever.py   #   dimension table detection
│   ├── fact_retriever.py     #   fact table detection
│   ├── relationship_retriever.py  # JOIN clause selection
│   ├── rule_retriever.py     #   business rule injection
│   ├── value_retriever.py    #   filter extraction
│   └── example_retriever.py  #   tag-scored few-shot selection
├── schema_data/              # Schema definitions (single source of truth)
│   ├── registry.py           #   SchemaRegistry + LRU cache
│   ├── columns.py            #   column allowlist
│   ├── tables.py             #   bilingual table descriptions
│   ├── relationships.py      #   FK → JOIN SQL map
│   └── retriever.py          #   TF-IDF bigram fallback engine
├── prompt_engine/
│   ├── builder.py            #   PromptBuilder.build()
│   └── templates.py          #   PROMPT_TEMPLATE
├── llm/
│   ├── sql_agent.py          #   generate → clean → auto-correct loop
│   ├── ollama_backend.py     #   HTTP client + exponential back-off
│   └── base.py               #   LLMBackend ABC
├── security/
│   └── sql_guard.py          #   clean_sql / validate_sql / ensure_top
├── database/
│   ├── connection.py         #   SQLAlchemy engine singleton
│   └── executor.py           #   timeout + row cap enforcement
├── exporters/                # Excel / CSV / JSON exporters
├── scripts/
│   └── analyze_misses.py     #   offline retrieval miss diagnostics
├── docs/
│   ├── en/tutorial.md        #   full English tutorial
│   └── fa/tutorial.md        #   full Persian tutorial — آموزش کامل فارسی
└── tests/                    # 427+ unit + integration tests

Tests

pytest tests/ -v                        # all tests
pytest tests/test_sql_guard.py -v       # one module
pytest --cov=. --cov-report=html        # with coverage report

CI runs on every push via GitHub Actions (Python 3.13).

Security model

Every generated SQL query passes through security/sql_guard.py before execution:

• Allowlist-only: only SELECT and WITH are permitted as statement openers
• Blocklist: DROP, ALTER, CREATE, TRUNCATE, DELETE, UPDATE, INSERT, MERGE, EXECUTE, XP_, SP_ are rejected
• Injection guard: stacked queries and INFORMATION_SCHEMA / SYS. access blocked
• LIMIT→TOP: SQL Server-incompatible LIMIT n is rewritten to TOP n before execution
• Row cap: MAX_ROWS_RETURNED is enforced as a hard ceiling on every result set
• No hardcoded credentials: all secrets via environment variables only

License

Business Source License 1.1 (BUSL-1.1) — see LICENSE.

• ✅ Free for non-production, research, and personal use
• ❌ Commercial/production use requires a written agreement with the author
• 🔄 Converts to Apache 2.0 on 2029-01-01
• 📌 Derivative works must retain LICENSE and include:

  Based on Local SQL Agent by Ali Sadeghi Aghili — https://github.com/alisadeghiaghili/local-sql-agent

Contributors

Ali Sadeghi Aghili — System Architecture & Engineering

Role: Creator & Lead Engineer

Melika Bahmanabadi — Domain Knowledge & Business Intelligence

Role: Domain Expert & Knowledge Engineer

Contributions welcome — open an issue before submitting a PR.

Built with Ollama · FastAPI · SQLAlchemy · scikit-learn