--- 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