sql_query_env / README.md
rajdeepchatale's picture
Upload folder using huggingface_hub
d075fca verified
|
Raw History Blame Contribute Delete
9.02 kB
---
title: Sql Query Env
emoji: πŸ“Š
colorFrom: blue
colorTo: indigo
sdk: docker
app_port: 8000
pinned: false
base_path: /web
---
# SQL Query Generation Environment
An OpenEnv RL environment that trains AI agents to write SQL queries from natural language questions.
## Motivation
We picked SQL generation because it's something we deal with almost every day in our coursework and internships. Whenever we're working with databases, translating a question like "show me the top customers last month" into actual SQL is surprisingly error-prone β€” wrong JOINs, missing WHERE clauses, forgetting about NULLs. We figured this would make a solid environment because:
- It's a real task with clear right/wrong answers
- Grading is deterministic (compare query results)
- There's a natural difficulty curve from simple SELECTs to complex JOINs
- Partial credit makes sense (right tables but wrong columns should score > 0)
## How it works
The agent gets a database schema and a natural language question. It writes SQL. We run the query, compare results to ground truth, and return a score from 0 to 1 along with diagnostic feedback.
### Multi-domain testing
We test across three different databases to make sure the agent actually *understands* SQL and isn't just memorizing one schema:
| Domain | Tables | Description |
|--------|--------|-------------|
| Company Analytics | departments, employees, products, customers, orders, reviews | Business intelligence queries |
| Hospital Management | wards, doctors, patients, appointments, medications, prescriptions | Healthcare data |
| E-Commerce Platform | sellers, categories, products, users, orders, order_items, returns | Retail analytics |
### Action space
The agent submits a single action per step:
| Field | Type | Description |
|-------|------|-------------|
| `query` | string | A SQL SELECT query to execute against the database |
Only SELECT statements are permitted. Destructive operations (DROP, DELETE, INSERT, UPDATE, etc.) are blocked and penalized.
### Observation space
After each step, the agent receives a rich observation:
| Field | Type | Description |
|-------|------|-------------|
| `task_id` | string | Current task identifier |
| `difficulty` | string | easy, medium, or hard |
| `database_domain` | string | Which database domain this task uses |
| `question` | string | Natural language question to answer with SQL |
| `schema_description` | string | Full database schema with table definitions |
| `query_result` | string | Formatted result of the last query |
| `query_error` | string (nullable) | SQL error message, if any |
| `feedback` | string | Detailed grading feedback |
| `diagnostics` | array | Structured error diagnostics: type, severity, message, suggestion |
| `efficiency_notes` | array | SQL best-practice tips based on the submitted query |
| `expected_row_count` | integer | Expected number of result rows |
| `expected_columns` | array | Expected column names |
| `steps_remaining` | integer | Attempts left in this episode |
| `current_score` | number | Best score so far (0.0–1.0) |
| `history` | array | Previous queries and scores |
### Scoring
We break scoring into five components so the agent always gets useful signal:
| Component | Weight | What it measures |
|-----------|--------|-----------------|
| Syntax | 0.10 | Does the query run at all? |
| Tables | 0.15 | Right tables referenced? |
| Columns | 0.20 | Correct output columns? |
| Results | 0.45 | Do the rows match ground truth? |
| Efficiency | 0.10 | Is it well-written SQL? (aliases, no SELECT *, etc.) |
There's also a -0.10 penalty for destructive SQL (DROP, DELETE) and -0.05 for repeating the same query.
All scores are clamped to the range **[0.0, 1.0]**.
### Diagnostic feedback
Instead of just returning a score, we give structured diagnostics:
- What tables are missing from the query
- Whether a JOIN is needed
- Column name mismatches
- Row count differences
- Efficiency tips (use aliases, avoid SELECT *, handle NULLs)
This is designed for process supervision β€” the agent should be able to use this feedback to iteratively improve its query within an episode.
## Tasks
14 tasks across three difficulty tiers and three database domains:
### Full task list
| Task ID | Domain | Difficulty | Description | Challenge Type |
|---------|--------|-----------|-------------|---------------|
| `company_easy_1` | Company | Easy | List active Engineering employees sorted by salary | Filter + sort |
| `company_easy_2` | Company | Easy | Filter electronics products by price | Single table filter |
| `hospital_easy_1` | Hospital | Easy | List patients still admitted (NULL discharge date) | NULL handling |
| `ecommerce_easy_1` | E-Commerce | Easy | List premium users with signup details | Simple filter |
| `company_medium_1` | Company | Medium | Total revenue per product category for completed orders | JOIN + GROUP BY |
| `company_medium_2` | Company | Medium | Departments with average salary exceeding $100K | GROUP BY + HAVING |
| `hospital_medium_1` | Hospital | Medium | Total medication cost for admitted patients | Multi-table JOIN + aggregation |
| `ecommerce_medium_1` | E-Commerce | Medium | Revenue per seller for delivered orders | JOIN + GROUP BY |
| `company_hard_1` | Company | Hard | Department salary-to-budget utilization percentage | Computed columns |
| `company_hard_2` | Company | Hard | Employees earning more than their direct manager | Self-join |
| `hospital_hard_1` | Hospital | Hard | Doctors prescribing to patients outside their ward | Double-join on wards |
| `hospital_hard_2` | Hospital | Hard | Patients with multiple appointments with the same doctor | GROUP BY + HAVING + COUNT |
| `ecommerce_hard_1` | E-Commerce | Hard | Return rate per product category with edge cases | Subquery + COALESCE |
| `ecommerce_hard_2` | E-Commerce | Hard | In-stock products that have never been ordered | LEFT JOIN anti-join |
### Progressive hints
When the agent gets stuck, we progressively reveal more hints on each failed attempt. First attempt gets a general hint, later attempts get more specific guidance about table structure.
## Baseline scores
Baseline agent: **Qwen/Qwen2.5-72B-Instruct** via Hugging Face Inference API.
| Task ID | Difficulty | Baseline Score |
|---------|-----------|---------------|
| `company_easy_1` | Easy | 0.90 |
| `company_easy_2` | Easy | 0.90 |
| `hospital_easy_1` | Easy | 0.90 |
| `ecommerce_easy_1` | Easy | 0.85 |
| `company_medium_1` | Medium | 0.75 |
| `company_medium_2` | Medium | 0.70 |
| `hospital_medium_1` | Medium | 0.65 |
| `ecommerce_medium_1` | Medium | 0.70 |
| `company_hard_1` | Hard | 0.55 |
| `company_hard_2` | Hard | 0.45 |
| `hospital_hard_1` | Hard | 0.40 |
| `hospital_hard_2` | Hard | 0.50 |
| `ecommerce_hard_1` | Hard | 0.35 |
| `ecommerce_hard_2` | Hard | 0.45 |
**Average score: ~0.65** | Easy: ~0.89 | Medium: ~0.70 | Hard: ~0.45
*Scores are approximate and may vary slightly between runs due to LLM temperature.*
## Getting started
### Environment variables
Before running the inference script, set these required variables:
```bash
export HF_TOKEN="your-huggingface-token"
export API_BASE_URL="https://router.huggingface.co/v1" # default
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct" # default
```
### Running locally
```bash
# install dependencies
uv sync
# start the environment server
uv run server
# run the baseline agent
export HF_TOKEN="your-token"
python inference.py
```
## Client example
```python
import asyncio
from sql_query_env import SqlQueryAction, SqlQueryEnv
async def main():
async with SqlQueryEnv(base_url="http://localhost:8000") as client:
result = await client.reset()
print(result.observation.question)
print(result.observation.schema_description)
result = await client.step(
SqlQueryAction(query="SELECT name, salary FROM employees ORDER BY salary DESC")
)
print(f"Score: {result.reward}")
print(f"Feedback: {result.observation.feedback}")
for diag in result.observation.diagnostics:
print(f" [{diag['type']}] {diag['message']}")
asyncio.run(main())
```
## Docker
```bash
docker build -t sql_query_env:latest .
docker run -p 8000:8000 sql_query_env:latest
```
## Project structure
```
sql_query_env/
β”œβ”€β”€ Dockerfile # Container build
β”œβ”€β”€ models.py # Action/Observation Pydantic models
β”œβ”€β”€ client.py # EnvClient for WebSocket connection
β”œβ”€β”€ inference.py # Baseline inference script
β”œβ”€β”€ openenv.yaml # Environment manifest (tasks, schemas)
└── server/
β”œβ”€β”€ app.py # FastAPI entry point
β”œβ”€β”€ sql_query_env_environment.py # Core environment logic
β”œβ”€β”€ tasks.py # Database schemas + all task definitions
└── graders.py # 5-component grading + diagnostics
```
## License
BSD-3-Clause