Codexzzz/sql-env

Agent (LLM)
    │
    │  Natural language task + schema  ← observation
    │  SQL query string                → action
    │
    ▼
FastAPI Server  (server/app.py)
    │
    │  WebSocket /ws   (persistent session)
    │  HTTP   /reset   /step   /health   /docs
    │
    ▼
SqlEnvironment  (server/sql_environment.py)
    ├── SQLite DB           (fresh per episode, zero cross-contamination)
    ├── Module-level session store  (survives new-instance-per-request pattern)
    ├── Multi-component Grader  (execute → columns → rows → values → efficiency)
    ├── Float normalization  (handles IEEE 754 rounding in SUM/AVG/ROUND)
    └── Reward computation      (F1-based partial scoring, never binary)

git clone https://github.com/Prabhav-020108/SQL-OpenEnv.git
cd SQL-OpenEnv

export API_BASE_URL="https://router.huggingface.co/v1"
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"
export HF_TOKEN="hf_your_token_here"
export LOCAL_IMAGE_NAME="Codexzzz-sql-env.hf.space"

pip install openai "openenv-core[core]>=0.2.2"
python inference.py

import asyncio
from sql_env import SqlEnv, SqlAction

async def main():
    env = await SqlEnv.from_docker_image("Codexzzz-sql-env.hf.space")

    # Reset to start an episode on a specific task
    result = await env.reset(task="select_basics")
    obs = result.observation
    print(obs.task_description)
    print(obs.schema_info)

    # Send a SQL query as the action
    result = await env.step(SqlAction(
        sql_query="SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name"
    ))
    print(f"Reward:     {result.reward}")
    print(f"Feedback:   {result.observation.feedback}")
    print(f"Breakdown:  {result.observation.score_breakdown}")

    await env.close()

asyncio.run(main())

result = await env.reset(task="select_basics")    # Easy    — max 5 steps
result = await env.reset(task="aggregate_filter") # Medium  — max 5 steps
result = await env.reset(task="multi_join")       # Hard    — max 7 steps
result = await env.reset(task="data_anomalies")   # Expert  — max 7 steps
result = await env.reset(task="window_functions") # Expert+ — max 8 steps

SqlAction(sql_query: str)

-- Easy: filter + sort
SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name;

-- Medium: JOIN + GROUP BY + HAVING
SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name HAVING COUNT(o.id) > 2
ORDER BY total_spent DESC;

-- Hard: 4-table JOIN with date functions
SELECT strftime('%Y-%m', o.order_date) AS month,
       cat.name, COUNT(DISTINCT o.id) AS order_count,
       SUM(oi.quantity * p.price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
JOIN orders o ON oi.order_id = o.id
WHERE strftime('%Y', o.order_date) = '2024'
GROUP BY month, cat.id
ORDER BY month ASC, total_revenue DESC;

-- Expert: data quality audit with UNION ALL
SELECT 'duplicate_email' AS issue_type,
       COUNT(*) AS affected_rows
FROM (SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1)
UNION ALL
SELECT 'invalid_age', COUNT(*)
FROM customers WHERE age IS NULL OR age < 0 OR age > 150
UNION ALL
SELECT 'null_name', COUNT(*)
FROM customers WHERE name IS NULL
ORDER BY issue_type;

-- Expert+: window functions for analytics
SELECT
    e.name,
    d.name AS department,
    RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
ORDER BY d.name ASC, salary_rank ASC;

SqlObservation(
    task_description:   str,    # Natural language task the agent must solve
    schema_info:        str,    # Full DDL — CREATE TABLE statements
    query_result:       list,   # Rows returned by last query (empty on reset)
    error_message:      str,    # SQL error string if query failed, else ""
    feedback:           str,    # Human-readable grader explanation
    score_breakdown:    dict,   # Per-component partial scores
    attempts_remaining: int,    # Steps remaining in this episode
    done:               bool,   # True when episode ends
    reward:             float,  # Step reward in [-0.10, 1.00]
)

SqlObservation(
    task_description   = "Find the full name and email of all customers from New York...",
    schema_info        = "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT...)",
    query_result       = [["Alice Brown", "alice@email.com"], ["Bob Smith", "bob@email.com"], ["David Lee", "david@email.com"]],
    error_message      = "",
    feedback           = "Perfect! Exact match.",
    score_breakdown    = {"execute": 0.1, "columns": 0.2, "rows": 0.2, "values": 0.4, "efficiency": 0.1},
    attempts_remaining = 4,
    done               = True,
    reward             = 1.0,
)

Find the full name and email address of all customers who live in 'New York'.
Return results sorted alphabetically by name (A to Z).

CREATE TABLE customers (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL,
    email TEXT NOT NULL, city TEXT NOT NULL, age INTEGER
);

[("Alice Brown", "alice@email.com"), ("Bob Smith", "bob@email.com"), ("David Lee", "david@email.com")]

Find each customer who has placed MORE THAN 2 orders.
Return their name and total amount spent. Sort by total amount spent, highest first.

[("Alice Brown", 405.50), ("Bob Smith", 300.00)]

Generate a monthly revenue report for 2024. Return month (YYYY-MM), category name,
distinct order count, and total revenue. Order by month ASC, revenue DESC.

[("2024-01", "Electronics", 1, 999.0), ("2024-01", "Books", 1, 137.0),
 ("2024-02", "Electronics", 1, 599.0), ("2024-02", "Books", 1, 147.0)]

Find data quality issues: duplicate_email, invalid_age, null_name.
Return issue type and count. Order alphabetically by issue type.

[("duplicate_email", 2), ("invalid_age", 2), ("null_name", 1)]

For each employee, calculate their salary rank within their department
and the difference between their salary and their department's average salary.
Return: employee name, department name, salary rank (1 = highest),
and salary minus department average (rounded to 2 decimal places).
Order by department name ASC, then rank ASC.

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL,
    department_id INTEGER NOT NULL, salary REAL NOT NULL
);

[("Alice", "Engineering", 1,  5000.0),
 ("Carol", "Engineering", 2,     0.0),
 ("Bob",   "Engineering", 3, -5000.0),
 ("Eve",   "Marketing",   1,  5000.0),
 ("Dave",  "Marketing",   2,     0.0),
 ("Frank", "Marketing",   3, -5000.0)]

reward = execute_bonus + column_score + row_score + value_score + efficiency_bonus

execute_bonus    = +0.10  if query ran without error
                   -0.05  if syntax/runtime error
                   -0.10  if query timed out (>5 seconds)

column_score     = +0.20 × (matching_columns / expected_columns)
row_score        = +0.20 × min(1.0, returned_rows / expected_rows)
value_score      = +0.40 × F1(result_set, expected_set)
efficiency_bonus = +0.10  if SELECT * is NOT used

Final clamp: reward = max(-0.10, min(1.00, reward))

[START] task=select_basics env=sql_env model=Qwen/Qwen2.5-72B-Instruct
[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null
[END] success=true steps=1 score=0.999 rewards=0.999
[START] task=aggregate_filter env=sql_env model=Qwen/Qwen2.5-72B-Instruct
[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=null
[END] success=true steps=1 score=0.999 rewards=0.999
[START] task=multi_join env=sql_env model=Qwen/Qwen2.5-72B-Instruct
[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=null
[END] success=true steps=1 score=0.999 rewards=0.999
[START] task=data_anomalies env=sql_env model=Qwen/Qwen2.5-72B-Instruct
[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null
[END] success=true steps=1 score=0.999 rewards=0.999
[START] task=window_functions env=sql_env model=Qwen/Qwen2.5-72B-Instruct
[STEP] step=1 action=SELECT name, email FROM customers WHERE city = 'New York' ORDER BY name ASC reward=0.999 done=true error=null
[END] success=true steps=1 score=0.999 rewards=0.999

git clone https://github.com/Prabhav-020108/SQL-OpenEnv.git
cd SQL-OpenEnv
pip install openai "openenv-core[core]>=0.2.2"

export API_BASE_URL="https://router.huggingface.co/v1"
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"
export HF_TOKEN="hf_your_token_here"
export LOCAL_IMAGE_NAME="Codexzzz-sql-env.hf.space"

python inference.py

docker build -t sql-env:latest .
docker run -d -p 8000:8000 sql-env:latest

# Test
curl http://localhost:8000/health
curl -X POST http://localhost:8000/reset \
     -H "Content-Type: application/json" \
     -d '{"task": "select_basics"}'

export LOCAL_IMAGE_NAME="sql-env:latest"
python inference.py

pip install -e sql_env/
PYTHONPATH=./sql_env uvicorn server.app:app --host 0.0.0.0 --port 8000 --reload

SQL-OpenEnv/                         ← repo root
├── inference.py                     ← Baseline agent script
├── README.md                        ← This file
├── openenv.yaml                     ← OpenEnv manifest
├── pyproject.toml                   ← Root package config
├── Dockerfile                       ← Container definition
├── validate-submission.sh           ← Submission validator
├── LICENSE                          ← MIT License
└── sql_env/
    ├── __init__.py
    ├── models.py                    ← SqlAction, SqlObservation
    ├── client.py                    ← SqlEnv client
    ├── openenv.yaml
    ├── pyproject.toml
    └── server/
        ├── __init__.py
        ├── sql_environment.py       ← 5 tasks, grader, reward function
        ├── app.py                   ← FastAPI app
        ├── requirements.txt
        └── Dockerfile