🧠 Natural Language to SQL Execution using IBM Watsonx

This FastAPI application allows users to convert natural language queries into optimized SQL using IBM Watsonx Orchestrate AgenticAI, execute the queries via REST APIs, and get the results in structured and downloadable formats.

✅ Supports MSSQL,MySQL & PostgreSQL ✉️ Email result support 🌐 Web UI with basic and float layout 🔒 IBM Cloud IAM authentication

---

📁 Project Structure

your_project/
.
├── Aldextra_agent_v2
│   ├── agents
│   │   └── native
│   │       ├── Aldextra_agent_v2.yaml
│   │       ├── authentication_agent.yaml
│   │       ├── emailResultAgent.yaml
│   │       └── NLQWXO_SQL.yaml
│   └── tools
│       └── user_auth_tool
│           ├── auth_tool.py
│           └── requirements.txt
├── arch.xml
├── db
│   ├── db_schema_llm_summary.txt
│   └── db_schema.txt
├── Dockerfile
├── examples
│   └── few_shot_examples.py
├── main.py
├── prompts
│   └── system_prompt_template.py
├── readme.md
├── requirements.txt
├── services
│   └── sql_rest_executor.py
├── static
│   ├── AI-Agents.jpg.webp
│   └── chat_logo.png
├── templates
│   ├── index.html
│   └── indexv2.html
└── v4_ald_openapi.json

---

🚀 Features

• 🔁 Natural Language → SQL → Execution → Results
• 🧠 Integrated with IBM Watsonx LLM for generation
• 📩 Emailing of query result XLS
• 🗃 Uploadable DB schema (.txt)
• 🎨 Clean Web UI (Jinja2 templates)
• ✅ FastAPI backend with Pydantic validation
• 🤖 Configurable Watsonx Orchestrate Agents (via YAML)

---

🤖 Watsonx Orchestrate Integration

This REST API is integrated as a Tool within Watsonx Orchestrate, enabling orchestration agents to use this app for natural language-to-SQL generation and execution.

🔧 Tool Integration

• Endpoints /generate-sql, /execute-sql, /generate-sql-execute-result, and /send-email are registered as tools.
• The OpenAPI spec v4_ald_openapi.json is imported in Watsonx Orchestrate to define tool capabilities.

🧠 Agent Configuration

• Agent YAMLs like NLQWXO_SQL.yaml and emailResultAgent.yaml in Aldextra_agent_v2/agents/native/ define:

  • Task grounding and tool bindings
  • Dialogue state flow and decisions
  • Access to environment variables (via context)

• Agents are grounded on uploaded schema using /upload-schema.

🗂️ Typical Agent Flow

1. User Input: "List all failed transactions for June."
2. Agent Trigger: NLQWXO_SQL.yaml fires, generates SQL.
3. Tool Call: Executes /generate-sql-execute-result.
4. Post-Process: Returns results, optionally triggers /send-email via emailResultAgent.yaml.

💡 Orchestration Benefits

• Seamless Watsonx integration with LLM & tools
• Reusable agent YAMLs for other workflows
• Task chaining (e.g. generate → execute → email)
• Context-driven invocation and control

---

⚙️ Setup Instructions

1. Clone the Repository

git clone https://github.com/your-org/your-repo-name.git
cd your-repo-name

2. Set Up Environment Variables

Create a .env file in the root directory:

wx_api_key=YOUR_IBM_API_KEY
wx_service_url=YOUR_MODEL_INFERENCE_URL
wx_project_id=YOUR_WX_PROJECT_ID
wx_model_id=google/flan-ul2 # Or any other model
wx_model_param_max_tokens=300
DB_ENGINE=mysql
INCLUDE_SCHEMA_SUMMARY=true
##
REST_API_KEY = "api-key-for-rest"
REST_API_HOST = "http://x.x.x.x:5000"
DB_ENGINE=mssql
DB_TYPE=mysql
DB_NAME=db_name
DB_INSTANCE=db_instance
DB_USER=db_user
DB_PASSWORD=db_pass
DB_HOST=db_host_ip
DB_PORT=db_port

3. Install Python Dependencies

pip install -r requirements.txt

✅ Recommended Python version: >=3.9

4. Run the App

uvicorn main:app --reload

Access: http://localhost:8000

---

🧪 API Endpoints

🔼 Upload Schema

POST /upload-schema

Upload .txt schema file used for query generation.

---

🧠 Generate SQL Only

POST /generate-sql

{
  "user_query": "List top 5 customers by purchase"
}

Returns:

{
  "sql": "SELECT * FROM ..."
}

---

⚡ Generate SQL and Execute

POST /generate-sql-execute-result

Returns both the SQL and result (with XLS & HTML).

---

📨 Send Result via Email

POST /send-email

{
  "email": "[email protected]"
}

---

❌ Discard Result File

POST /no-send-email

---

📄 Execute Given SQL

POST /execute-sql

{
  "sql": "SELECT * FROM customers LIMIT 5"
}

---

🖥 UI Access

• Basic: GET /
• Float layout: GET /v2

---

💪 Docker (Optional)

docker build -t nlsql-watsonx .
docker run -p 8000:8000 --env-file .env nlsql-watsonx

---

📚 References

• IBM Watsonx
• FastAPI Documentation
• Pydantic Models
• Uvicorn ASGI Server

---

🧑‍💻 Author

Developed by Shan S,Muralidhar

---

📝 License

This project is licensed under the MIT License. See LICENSE for details.