SQL Database Assistant with Safe Execution

An enterprise Text-to-SQL assistant that parses English questions into optimized SQL queries, validates Abstract Syntax Trees (AST) to prevent mutations, and executes read-only operations.

---

Key Features

• Natural: language to SQL query generation based on schema definitions
• Abstract: Syntax Tree (AST) validation blocking DROP, DELETE, UPDATE, and INSERT mutations
• Read-only: execution transaction isolation
• Tabular: result set formatting with natural language explanation
• Built-in: schema introspection for SQLite and PostgreSQL

---

Architecture

flowchart TD
    Question([English Question]) --> Generator[Text-to-SQL Generator]
    Generator --> ASTValidator{Read-Only AST Validator}
    ASTValidator -->|Destructive Query| Reject[Reject Query - Safety Violation]
    ASTValidator -->|Safe SELECT| Exec[Execute Read-Only SQL]
    Exec --> DB[(Database)]
    Exec --> Explainer[Natural Language Synthesizer]
    Explainer --> Answer[Answer + Data Table]

Loading

---

Tech Stack

Component	Technology	Purpose
Runtime	Python 3.12	Core execution environment
API Framework	FastAPI & Uvicorn	High-performance asynchronous REST endpoints
Data Validation	Pydantic v2	Strict schema validation and serialization
Execution Engine	Dual-Mode (Local + Cloud)	Production-ready logic with offline verification
Testing	Unittest & Pytest	Deterministic automated verification suite

---

Project Structure

sql-database-agent/
├── app/
│   ├── __init__.py
│   ├── api.py           # FastAPI routes and server definitions
│   ├── config.py        # Environment variables and application settings
│   ├── models.py        # Pydantic data schemas
│   └── services/        # Core business automation logic
├── tests/
│   ├── __init__.py
│   └── test_sql_agent.py   # Automated test suite
├── .env.example         # Template for environment configuration
├── .gitignore           # Python and runtime exclusions
├── LICENSE              # MIT License
├── README.md            # Comprehensive project documentation
└── requirements.txt     # Python package dependencies

---

Getting Started

Prerequisites

• Python 3.10+ (Python 3.12 recommended)
• pip package manager

Installation

1. Clone the repository:

   git clone https://github.com/erhatechnologiesai/sql-database-agent.git
   cd sql-database-agent

2. Create and activate a virtual environment:

   python -m venv venv
   # On Windows:
   venv\Scripts\activate
   # On macOS/Linux:
   source venv/bin/activate

3. Install dependencies:

   pip install -r requirements.txt

4. Configure environment variables:

   cp .env.example .env

---

Running the Application

Start the local development server with auto-reload:

python -m uvicorn app.api:app --reload --host 0.0.0.0 --port 8000

Once running, interactive documentation is accessible at:

• Swagger UI: http://127.0.0.1:8000/docs
• ReDoc: http://127.0.0.1:8000/redoc

---

API Endpoints

Method	Endpoint	Description
POST	/query-database	Convert English question to safe SQL, execute, and return formatted results

Example Request

curl -X POST http://127.0.0.1:8000/query-database -H "Content-Type: application/json" -d '{"question": "Show top 5 customers by total order spend"}'

---

Running Tests

Execute the automated test suite:

python -m unittest tests/test_sql_agent.py

Or using pytest:

pytest tests/

All test cases are self-contained and run offline without requiring third-party API credentials.

---

Security & Best Practices

• Zero Credential Leakage: API tokens and secrets are loaded exclusively via environment variables and excluded by .gitignore.
• Strict Validation: All incoming request payloads are strictly validated using Pydantic schemas.
• Fail-Safe Fallbacks: Deterministic offline engines guarantee application continuity even during external provider outages.

---

License

This project is licensed under the terms of the MIT License.