Codexzzz/sql-env

1---2title: SQL Query Grader Environment3emoji: 🗃️4colorFrom: blue5colorTo: green6sdk: docker7pinned: false8app_port: 80009base_path: /web10tags:11  - openenv12  - reinforcement-learning13  - sql14  - agent-evaluation15  - nlp16  - text-generation17---18 19# 🗃️ SQL Query Grader — OpenEnv Environment20 21> **An RL training environment where AI agents learn to write correct SQL queries from natural language task descriptions.**22> Built for the **Meta × Hugging Face OpenEnv Hackathon 2026**.23 24[![OpenEnv](https://img.shields.io/badge/OpenEnv-Compatible-blue)](https://github.com/meta-pytorch/OpenEnv)25[![Python](https://img.shields.io/badge/Python-3.11-green)](https://python.org)26[![License](https://img.shields.io/badge/License-MIT-yellow)](LICENSE)27 28---29 30## 🌍 Why This Environment Matters31 32SQL is the universal language of data. Every analyst, data scientist, backend engineer, and BI team writes SQL daily. An AI agent that generates correct SQL from natural language has **immediate, real-world deployment value** in:33 34- **BI tools** — natural language to SQL for non-technical stakeholders35- **IDE copilots** — auto-completing database queries for developers36- **Data pipelines** — automated query generation for ETL workflows37- **Database exploration** — letting agents query and analyze data autonomously38- **Data quality** — automated anomaly detection queries for data engineering39- **Analytics** — window functions for ranking, percentiles, and moving averages40 41Unlike most code generation benchmarks, SQL has **deterministic, programmatic correctness** — the result set either matches expected output or it doesn't. This makes grading perfectly reliable and reproducible, exactly what RL training demands.42 43> **No existing OpenEnv environment covers SQL query generation.** This fills a genuine gap in the ecosystem.44 45---46 47## 🏗️ Architecture48 49```50Agent (LLM)51    │52    │  Natural language task + schema  ← observation53    │  SQL query string                → action54    │55    ▼56FastAPI Server  (server/app.py)57    │58    │  WebSocket /ws   (persistent session)59    │  HTTP   /reset   /step   /health   /docs60    │61    ▼62SqlEnvironment  (server/sql_environment.py)63    ├── SQLite DB           (fresh per episode, zero cross-contamination)64    ├── Module-level session store  (survives new-instance-per-request pattern)65    ├── Multi-component Grader  (execute → columns → rows → values → efficiency)66    ├── Float normalization  (handles IEEE 754 rounding in SUM/AVG/ROUND)67    └── Reward computation      (F1-based partial scoring, never binary)68```69 70---71 72## ⚡ Quick Start73 74### Run inference against the live HF Space75 76```bash77git clone https://github.com/Prabhav-020108/SQL-OpenEnv.git78cd SQL-OpenEnv79 80export API_BASE_URL="https://router.huggingface.co/v1"81export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"82export HF_TOKEN="hf_your_token_here"83export LOCAL_IMAGE_NAME="Codexzzz-sql-env.hf.space"84 85pip install openai "openenv-core[core]>=0.2.2"86python inference.py87```88 89### Use the Python client directly90 91```python92import asyncio93from sql_env import SqlEnv, SqlAction94 95async def main():96    env = await SqlEnv.from_docker_image("Codexzzz-sql-env.hf.space")97 98    # Reset to start an episode on a specific task99    result = await env.reset(task="select_basics")100    obs = result.observation101    print(obs.task_description)102    print(obs.schema_info)103 104    # Send a SQL query as the action105    result = await env.step(SqlAction(106        sql_query="SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name"107    ))108    print(f"Reward:     {result.reward}")109    print(f"Feedback:   {result.observation.feedback}")110    print(f"Breakdown:  {result.observation.score_breakdown}")111 112    await env.close()113 114asyncio.run(main())115```116 117### Choose a specific task118 119```python120result = await env.reset(task="select_basics")    # Easy    — max 5 steps121result = await env.reset(task="aggregate_filter") # Medium  — max 5 steps122result = await env.reset(task="multi_join")       # Hard    — max 7 steps123result = await env.reset(task="data_anomalies")   # Expert  — max 7 steps124result = await env.reset(task="window_functions") # Expert+ — max 8 steps125```126 127---128 129## 🎯 Action Space130 131The agent sends exactly one thing per step: a SQL query string.132 133```python134SqlAction(sql_query: str)135```136 137| Field | Type | Required | Description |138|-------|------|----------|-------------|139| `sql_query` | `str` | ✅ | Any valid SQLite SQL query string |140 141**Examples across difficulty levels:**142 143```sql144-- Easy: filter + sort145SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name;146 147-- Medium: JOIN + GROUP BY + HAVING148SELECT c.name, SUM(o.amount) AS total_spent149FROM customers c JOIN orders o ON c.id = o.customer_id150GROUP BY c.id, c.name HAVING COUNT(o.id) > 2151ORDER BY total_spent DESC;152 153-- Hard: 4-table JOIN with date functions154SELECT strftime('%Y-%m', o.order_date) AS month,155       cat.name, COUNT(DISTINCT o.id) AS order_count,156       SUM(oi.quantity * p.price) AS total_revenue157FROM order_items oi158JOIN products p ON oi.product_id = p.id159JOIN categories cat ON p.category_id = cat.id160JOIN orders o ON oi.order_id = o.id161WHERE strftime('%Y', o.order_date) = '2024'162GROUP BY month, cat.id163ORDER BY month ASC, total_revenue DESC;164 165-- Expert: data quality audit with UNION ALL166SELECT 'duplicate_email' AS issue_type,167       COUNT(*) AS affected_rows168FROM (SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1)169UNION ALL170SELECT 'invalid_age', COUNT(*)171FROM customers WHERE age IS NULL OR age < 0 OR age > 150172UNION ALL173SELECT 'null_name', COUNT(*)174FROM customers WHERE name IS NULL175ORDER BY issue_type;176 177-- Expert+: window functions for analytics178SELECT179    e.name,180    d.name AS department,181    RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank,182    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg183FROM employees e184JOIN departments d ON e.department_id = d.id185ORDER BY d.name ASC, salary_rank ASC;186```187 188---189 190## 👁️ Observation Space191 192Every observation returned by `reset()` and `step()`:193 194```python195SqlObservation(196    task_description:   str,    # Natural language task the agent must solve197    schema_info:        str,    # Full DDL — CREATE TABLE statements198    query_result:       list,   # Rows returned by last query (empty on reset)199    error_message:      str,    # SQL error string if query failed, else ""200    feedback:           str,    # Human-readable grader explanation201    score_breakdown:    dict,   # Per-component partial scores202    attempts_remaining: int,    # Steps remaining in this episode203    done:               bool,   # True when episode ends204    reward:             float,  # Step reward in [-0.10, 1.00]205)206```207 208**After a perfect query:**209 210```python211SqlObservation(212    task_description   = "Find the full name and email of all customers from New York...",213    schema_info        = "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT...)",214    query_result       = [["Alice Brown", "alice@email.com"], ["Bob Smith", "bob@email.com"], ["David Lee", "david@email.com"]],215    error_message      = "",216    feedback           = "Perfect! Exact match.",217    score_breakdown    = {"execute": 0.1, "columns": 0.2, "rows": 0.2, "values": 0.4, "efficiency": 0.1},218    attempts_remaining = 4,219    done               = True,220    reward             = 1.0,221)222```223 224---225 226## 🏋️ Tasks227 228### Task 1: `select_basics` — Easy229 230**Goal:** Retrieve the correct rows using `SELECT`, `WHERE`, and `ORDER BY`.231 232**Task description given to agent:**233```234Find the full name and email address of all customers who live in 'New York'.235Return results sorted alphabetically by name (A to Z).236```237 238**Schema:**239```sql240CREATE TABLE customers (241    id INTEGER PRIMARY KEY, name TEXT NOT NULL,242    email TEXT NOT NULL, city TEXT NOT NULL, age INTEGER243);244```245 246**Expected result:**247```python248[("Alice Brown", "alice@email.com"), ("Bob Smith", "bob@email.com"), ("David Lee", "david@email.com")]249```250 251**Max steps:** 5252 253---254 255### Task 2: `aggregate_filter` — Medium256 257**Goal:** Use `JOIN`, `GROUP BY`, aggregate functions, and `HAVING` to filter groups.258 259**Task description:**260```261Find each customer who has placed MORE THAN 2 orders.262Return their name and total amount spent. Sort by total amount spent, highest first.263```264 265**Expected result:**266```python267[("Alice Brown", 405.50), ("Bob Smith", 300.00)]268```269 270**Max steps:** 5271 272---273 274### Task 3: `multi_join` — Hard275 276**Goal:** Join 4 tables, extract date components, compute derived revenue, filter by year.277 278**Task description:**279```280Generate a monthly revenue report for 2024. Return month (YYYY-MM), category name,281distinct order count, and total revenue. Order by month ASC, revenue DESC.282```283 284**Expected result:**285```python286[("2024-01", "Electronics", 1, 999.0), ("2024-01", "Books", 1, 137.0),287 ("2024-02", "Electronics", 1, 599.0), ("2024-02", "Books", 1, 147.0)]288```289 290**Max steps:** 7291 292---293 294### Task 4: `data_anomalies` — Expert295 296**Goal:** Audit a table for data quality issues using subqueries and `UNION ALL`.297 298**Task description:**299```300Find data quality issues: duplicate_email, invalid_age, null_name.301Return issue type and count. Order alphabetically by issue type.302```303 304**Expected result:**305```python306[("duplicate_email", 2), ("invalid_age", 2), ("null_name", 1)]307```308 309**Max steps:** 7310 311---312 313### Task 5: `window_functions` — Expert+314 315**Goal:** Use SQL window functions (`RANK() OVER`, `AVG() OVER`) for analytics.316 317**Task description:**318```319For each employee, calculate their salary rank within their department320and the difference between their salary and their department's average salary.321Return: employee name, department name, salary rank (1 = highest),322and salary minus department average (rounded to 2 decimal places).323Order by department name ASC, then rank ASC.324```325 326**Schema:**327```sql328CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL);329CREATE TABLE employees (330    id INTEGER PRIMARY KEY, name TEXT NOT NULL,331    department_id INTEGER NOT NULL, salary REAL NOT NULL332);333```334 335**Expected result:**336```python337[("Alice", "Engineering", 1,  5000.0),338 ("Carol", "Engineering", 2,     0.0),339 ("Bob",   "Engineering", 3, -5000.0),340 ("Eve",   "Marketing",   1,  5000.0),341 ("Dave",  "Marketing",   2,     0.0),342 ("Frank", "Marketing",   3, -5000.0)]343```344 345**Max steps:** 8 | **Why expert+:** Requires `RANK() OVER (PARTITION BY ...)` and `AVG() OVER (PARTITION BY ...)` — real-world analytics patterns used in every data team.346 347---348 349## 🏆 Reward Function350 351```352reward = execute_bonus + column_score + row_score + value_score + efficiency_bonus353 354execute_bonus    = +0.10  if query ran without error355                   -0.05  if syntax/runtime error356                   -0.10  if query timed out (>5 seconds)357 358column_score     = +0.20 × (matching_columns / expected_columns)359row_score        = +0.20 × min(1.0, returned_rows / expected_rows)360value_score      = +0.40 × F1(result_set, expected_set)361efficiency_bonus = +0.10  if SELECT * is NOT used362 363Final clamp: reward = max(-0.10, min(1.00, reward))364```365 366**Float normalization:** The grader rounds all float values to 2 decimal places before comparison, preventing false mismatches from IEEE 754 floating-point arithmetic (e.g. `405.4999999999` vs `405.5`).367 368---369 370## 📊 Baseline Scores371 372**Model:** `Qwen/Qwen2.5-72B-Instruct` | **API:** `https://router.huggingface.co/v1`373 374```375[START] task=select_basics env=sql_env model=Qwen/Qwen2.5-72B-Instruct376[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null377[END] success=true steps=1 score=0.999 rewards=0.999378[START] task=aggregate_filter env=sql_env model=Qwen/Qwen2.5-72B-Instruct379[STEP] step=1 action=SELECT c.name, SUM(o.amount) AS total_spent FROM customers c JOIN orders o ON c.id = o.customer_id G reward=0.999 done=true error=null380[END] success=true steps=1 score=0.999 rewards=0.999381[START] task=multi_join env=sql_env model=Qwen/Qwen2.5-72B-Instruct382[STEP] step=1 action=SELECT      strftime('%Y-%m', o.order_date) AS month,      c.name AS category_name,      COUNT(DISTI reward=0.999 done=true error=null383[END] success=true steps=1 score=0.999 rewards=0.999384[START] task=data_anomalies env=sql_env model=Qwen/Qwen2.5-72B-Instruct385[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null386[END] success=true steps=1 score=0.999 rewards=0.999387[START] task=window_functions env=sql_env model=Qwen/Qwen2.5-72B-Instruct388[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null389[END] success=true steps=1 score=0.999 rewards=0.999390```391 392---393 394## 🛠️ Setup & Installation395 396### Option 1: Run inference against the live HF Space (fastest)397 398```bash399git clone https://github.com/Prabhav-020108/SQL-OpenEnv.git400cd SQL-OpenEnv401pip install openai "openenv-core[core]>=0.2.2"402 403export API_BASE_URL="https://router.huggingface.co/v1"404export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"405export HF_TOKEN="hf_your_token_here"406export LOCAL_IMAGE_NAME="Codexzzz-sql-env.hf.space"407 408python inference.py409```410 411### Option 2: Run locally via Docker412 413```bash414docker build -t sql-env:latest .415docker run -d -p 8000:8000 sql-env:latest416 417# Test418curl http://localhost:8000/health419curl -X POST http://localhost:8000/reset \420     -H "Content-Type: application/json" \421     -d '{"task": "select_basics"}'422 423export LOCAL_IMAGE_NAME="sql-env:latest"424python inference.py425```426 427### Option 3: Development server (no Docker)428 429```bash430pip install -e sql_env/431PYTHONPATH=./sql_env uvicorn server.app:app --host 0.0.0.0 --port 8000 --reload432```433 434---435 436## 🤖 Environment Variables437 438| Variable | Required | Default | Description |439|----------|----------|---------|-------------|440| `API_BASE_URL` | No | `https://router.huggingface.co/v1` | LLM API endpoint |441| `MODEL_NAME` | No | `Qwen/Qwen2.5-72B-Instruct` | Model identifier |442| `HF_TOKEN` | **Yes** | — | Hugging Face / API key |443| `LOCAL_IMAGE_NAME` | **Yes** | — | Docker image name or Space URL |444 445---446 447## 📁 Project Structure448 449```450SQL-OpenEnv/                         ← repo root451├── inference.py                     ← Baseline agent script452├── README.md                        ← This file453├── openenv.yaml                     ← OpenEnv manifest454├── pyproject.toml                   ← Root package config455├── Dockerfile                       ← Container definition456├── validate-submission.sh           ← Submission validator457├── LICENSE                          ← MIT License458└── sql_env/459    ├── __init__.py460    ├── models.py                    ← SqlAction, SqlObservation461    ├── client.py                    ← SqlEnv client462    ├── openenv.yaml463    ├── pyproject.toml464    └── server/465        ├── __init__.py466        ├── sql_environment.py       ← 5 tasks, grader, reward function467        ├── app.py                   ← FastAPI app468        ├── requirements.txt469        └── Dockerfile470```471 472---473 474## 🔌 API Endpoints475 476| Endpoint | Method | Description |477|----------|--------|-------------|478| `/health` | GET | Health check |479| `/reset` | POST | Start new episode — pass `{"task": "task_name"}` |480| `/step` | POST | Execute SQL — pass `{"action": {"sql_query": "..."}}` |481| `/state` | GET | Get current episode state |482| `/ws` | WebSocket | Persistent session endpoint |483| `/docs` | GET | Interactive OpenAPI documentation |484| `/web` | GET | Built-in OpenEnv web UI |485 486---487 488## ✅ OpenEnv Spec Compliance489 490- ✅ `openenv.yaml` at root with `spec_version: 1`491- ✅ Typed Pydantic `Action` and `Observation` models492- ✅ `reset()`, `step()`, `state()` endpoints implemented493- ✅ WebSocket `/ws` persistent session support494- ✅ Docker containerized — builds and runs cleanly495- ✅ Deployed on HF Space tagged with `openenv`496- ✅ Baseline inference script: correct `[START]`/`[STEP]`/`[END]` format497- ✅ 5 tasks with programmatic F1-based graders498- ✅ All rewards clamped to `[-0.10, 1.00]` range499- ✅ Score output strictly in `(0, 1)` — never exactly `0.0` or `1.0`500- ✅ Float normalization prevents IEEE 754 false mismatches501 502---503 504## 📜 License505 506MIT License — see [LICENSE](LICENSE) for details.507 508---509 510*Built with [OpenEnv](https://github.com/meta-pytorch/OpenEnv) | Meta × Hugging Face Hackathon 2026*511*Team: Prabhav Tiwari, Shaurya Khanna, Yashraj Pala (Devsters)*