Vaishnavi-279/sql-debugger-env
0
1---2title: SQL Debugger Environment3emoji: 🛠️4colorFrom: blue5colorTo: indigo6sdk: docker7pinned: false8tags:9 - openenv10 - rl11 - sql12 - debugging13 - agent14---15 16# 🛠️ SQL Debugger — OpenEnv RL Environment17 18An [OpenEnv](https://github.com/meta-pytorch/OpenEnv) reinforcement learning environment where an AI agent must **debug and fix broken SQL queries**.19 20The agent receives a broken SQL query + database schema each episode and iteratively submits corrected queries until the result exactly matches the expected output — or it runs out of attempts.21 22---23 24## 🎯 Motivation25 26SQL debugging is a **genuine, high-value daily task** for developers and data engineers:27 28- Production queries silently return wrong data29- New engineers inherit broken legacy code30- LLMs frequently generate subtly wrong SQL31 32This environment trains and evaluates agents on exactly this skill, with **deterministic grading** and **5-tier partial reward signals** at every step.33 34---35 36## 📋 Environment Summary37 38| Property | Value |39| --------------------- | ------------------------------------- |40| Max steps per episode | 5 |41| Reward range | 0.0 – 1.0 |42| Tasks | 3 (easy → medium → hard) |43| Database | In-memory SQLite (zero deps) |44| Server port | 8000 |45| Termination | Perfect match OR max attempts reached |46 47---48 49## 🔁 Action Space50 51```json52{53 "sql": "SELECT name, salary FROM employees WHERE dept='Engineering' ORDER BY salary DESC"54}55```56 57| Field | Type | Description |58| ----- | ------ | ------------------------------------------- |59| `sql` | string | A corrected SQL SELECT statement to execute |60 61---62 63## 👁️ Observation Space64 65| Field | Type | Description |66| ------------------ | ------------ | ---------------------------------------------- |67| `task_id` | string | Task identifier |68| `difficulty` | string | `easy` / `medium` / `hard` |69| `task_description` | string | What the query must return |70| `schema_ddl` | string | CREATE TABLE + INSERT statements |71| `broken_query` | string | The original broken query to fix |72| `error_message` | string | SQLite error from last attempt (empty if none) |73| `execution_result` | list\|null | Rows from last submitted query |74| `expected_result` | list | Ground-truth rows to match exactly |75| `attempt` | int | Attempts made this episode |76| `max_attempts` | int | Max allowed (5) |77| `hint` | string\|null | Optional bug-type hint |78| `done` | bool | Episode ended |79| `reward` | float | Reward from last step |80 81---82 83## 🏆 Reward Function84 85| Condition | Reward |86| --------------------------------------- | --------------------- |87| Query errored / no output | `0.0` |88| Wrong row count | `0.3` |89| Right count, wrong values | `0.6` |90| Right values, wrong order | `0.8` |91| **Perfect match** | **`1.0`** |92| Efficiency penalty (per wasted attempt) | `-0.05 × (attempt−1)` |93 94Rewards are clamped to `[0.0, 1.0]`. First-attempt correct = `1.0`; second attempt = `0.95`; etc.95 96---97 98## 📚 Tasks99 100### Easy — `task_easy_syntax`101 102**Bug:** Three keyword typos (`SELEC`, `FORM`, `DESK`) 103**Schema:** `employees(id, name, dept, salary)` 104**Goal:** Return Engineering employees ordered by salary DESC105 106```sql107-- Broken:108SELEC name, salary FORM employees WHERE dept = 'Engineering' ORDER BY salary DESK;109 110-- Fixed:111SELECT name, salary FROM employees WHERE dept = 'Engineering' ORDER BY salary DESC;112```113 114---115 116### Medium — `task_medium_join`117 118**Bug:** Wrong JOIN column (`o.id` → `o.customer_id`) + wrong aggregation (`SUM(quantity)` → `SUM(quantity*unit_price)`) 119**Schema:** `customers(id, name)` + `orders(id, customer_id, quantity, unit_price)`120 121```sql122-- Broken:123SELECT c.name, SUM(o.quantity) AS total_value124FROM customers c JOIN orders o ON c.id = o.id125GROUP BY c.name ORDER BY total_value DESC;126 127-- Fixed:128SELECT c.name, SUM(o.quantity * o.unit_price) AS total_value129FROM customers c JOIN orders o ON c.id = o.customer_id130GROUP BY c.name ORDER BY total_value DESC;131```132 133---134 135### Hard — `task_hard_complex`136 137**Bug:** 3 simultaneous bugs — NULL not excluded in subquery, LEFT JOIN skews averages, ORDER BY ASC should be DESC 138**Schema:** `products(id, name)` + `reviews(id, product_id, score)` — score is nullable139 140```sql141-- Broken (3 bugs):142SELECT p.name, AVG(r.score) AS avg_score143FROM products p LEFT JOIN reviews r ON p.id = r.product_id144GROUP BY p.name145HAVING AVG(r.score) > (SELECT AVG(score) FROM reviews)146ORDER BY avg_score ASC;147 148-- Fixed:149SELECT p.name, AVG(r.score) AS avg_score150FROM products p JOIN reviews r ON p.id = r.product_id151WHERE r.score IS NOT NULL152GROUP BY p.name153HAVING AVG(r.score) > (SELECT AVG(score) FROM reviews WHERE score IS NOT NULL)154ORDER BY avg_score DESC;155```156 157---158 159## 🚀 Setup & Usage160 161### Prerequisites162 163- Python 3.10+164- Docker Desktop (running)165- Git166 167### Clone & install168 169**Linux / Mac:**170 171```bash172git clone https://github.com/YOUR_USERNAME/my-openenv.git173cd my-openenv/sql_debugger_env174pip install openenv-core175uv sync # or: pip install -e .176```177 178**Windows:**179 180```cmd181git clone https://github.com/YOUR_USERNAME/my-openenv.git182cd my-openenv\sql_debugger_env183pip install openenv-core184pip install -e .185```186 187### Build & run with Docker188 189```bash190# Build (run from inside sql_debugger_env/)191docker build -t sql_debugger_env-env:latest -f server/Dockerfile .192 193# Run194docker run -p 8000:8000 sql_debugger_env-env:latest195```196 197### Test the server198 199**Linux / Mac:**200 201```bash202# Health check203curl http://localhost:8000/health204 205# Reset — start episode206curl -X POST http://localhost:8000/reset \207 -H "Content-Type: application/json" -d "{}"208 209# Submit a fix210curl -X POST http://localhost:8000/step \211 -H "Content-Type: application/json" \212 -d '{"action": {"sql": "SELECT name, salary FROM employees WHERE dept='\''Engineering'\'' ORDER BY salary DESC"}}'213```214 215**Windows (PowerShell):**216 217```powershell218# Health check219Invoke-RestMethod http://localhost:8000/health220 221# Reset222Invoke-RestMethod -Uri "http://localhost:8000/reset" -Method POST `223 -ContentType "application/json" -Body "{}"224 225# Submit a fix226Invoke-RestMethod -Uri "http://localhost:8000/step" -Method POST `227 -ContentType "application/json" `228 -Body '{"action": {"sql": "SELECT name, salary FROM employees WHERE dept=''Engineering'' ORDER BY salary DESC"}}'229```230 231### Run inference232 233**Linux / Mac:**234 235```bash236cd my-openenv # repo root — where inference.py lives237 238export HF_TOKEN=your_token_here239export IMAGE_NAME=sql_debugger_env-env:latest240export API_BASE_URL=https://router.huggingface.co/v1241export MODEL_NAME=Qwen/Qwen2.5-72B-Instruct242 243python inference.py244```245 246**Windows (Command Prompt):**247 248```cmd249cd my-openenv250 251set HF_TOKEN=your_token_here252set IMAGE_NAME=sql_debugger_env-env:latest253set API_BASE_URL=https://router.huggingface.co/v1254set MODEL_NAME=Qwen/Qwen2.5-72B-Instruct255 256python inference.py257```258 259**Windows (PowerShell):**260 261```powershell262cd my-openenv263 264$env:HF_TOKEN = "your_token_here"265$env:IMAGE_NAME = "sql_debugger_env-env:latest"266$env:API_BASE_URL = "https://router.huggingface.co/v1"267$env:MODEL_NAME = "Qwen/Qwen2.5-72B-Instruct"268 269python inference.py270```271 272**Expected output:**273 274```275[START] task=task_easy_syntax env=sql_debugger model=Qwen/Qwen2.5-72B-Instruct276[STEP] step=1 action=SELECT name... reward=1.00 done=true error=null277[END] success=true steps=1 score=0.200 rewards=1.00278[INFO] task=task_easy_syntax score=0.200279...280[INFO] overall_avg_score=0.253281```282 283### Validate284 285```bash286cd my-openenv/sql_debugger_env287openenv validate288# Expected: [OK] sql_debugger: Ready for multi-mode deployment289```290 291---292 293## 🤗 Deploy to HuggingFace Spaces294 2951. Go to [huggingface.co/new-space](https://huggingface.co/new-space) → SDK = **Docker**2962. Push your repo:297 298```bash299git remote add hf https://huggingface.co/spaces/YOUR_HF_USERNAME/sql-debugger-env300git push hf main301```302 3033. In Space **Settings → Variables and secrets**, add:304 305| Variable | Value |306| -------------- | ---------------------------------- |307| `HF_TOKEN` | Your HuggingFace token |308| `API_BASE_URL` | `https://router.huggingface.co/v1` |309| `MODEL_NAME` | `Qwen/Qwen2.5-72B-Instruct` |310| `IMAGE_NAME` | `sql_debugger_env-env:latest` |311 3124. Wait for the Space to show **"Running"** before submitting.313 314---315 316## 📊 Baseline Scores317 318`Qwen/Qwen2.5-72B-Instruct` · 5 steps max · temperature 0.2319 320| Task | Difficulty | Expected Score |321| ------------------- | ---------- | -------------- |322| `task_easy_syntax` | 🟢 Easy | ~0.85 |323| `task_medium_join` | 🟡 Medium | ~0.65 |324| `task_hard_complex` | 🔴 Hard | ~0.40 |325| **Overall** | | **~0.63** |326 327---328 329## 🗂️ Project Structure330 331```332my-openenv/333├── inference.py ← inference script (repo root)334└── sql_debugger_env/335 ├── models.py ← Pydantic Action + Observation336 ├── tasks.py ← Task definitions (easy/medium/hard)337 ├── executor.py ← SQLite runner + 5-tier grader338 ├── client.py ← Async EnvClient339 ├── openenv.yaml ← OpenEnv spec metadata340 ├── pyproject.toml ← Package config341 ├── uv.lock ← Locked dependencies342 └── server/343 ├── app.py ← FastAPI server (port 8000)344 ├── sql_debugger_env_environment.py ← Core environment logic345 ├── Dockerfile ← Container definition346 └── requirements.txt ← Server dependencies347```348 349---350 351## 🔌 API Endpoints352 353| Method | Endpoint | Description |354| ------ | ----------- | ----------------------------------- |355| GET | `/health` | `{"status": "healthy"}` |356| GET | `/metadata` | Name, description, version |357| GET | `/schema` | Action + Observation schemas |358| POST | `/reset` | Start new episode |359| POST | `/step` | Submit `{"action": {"sql": "..."}}` |360| GET | `/state` | Episode state (id, step count) |361 362---363 364## ⚙️ Environment Variables365 366| Variable | Required | Default | Description |367| ------------------- | -------- | ---------------------------------- | --------------------------------------- |368| `HF_TOKEN` | ✅ | — | HuggingFace token (or `OPENAI_API_KEY`) |369| `API_BASE_URL` | ❌ | `https://router.huggingface.co/v1` | LLM endpoint |370| `MODEL_NAME` | ❌ | `Qwen/Qwen2.5-72B-Instruct` | Model identifier |371| `IMAGE_NAME` | ❌ | `sql_debugger_env-env:latest` | Docker image |372| `SQL_DEBUGGER_TASK` | ❌ | `task_easy_syntax` | Which task to serve |373| `PORT` | ❌ | `8000` | Server port |374