Spaces:
Sleeping
Sleeping
File size: 9,022 Bytes
7fb9b74 d075fca 7fb9b74 d075fca 7fb9b74 d075fca 7fb9b74 d075fca 7fb9b74 d075fca | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 | ---
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
|