SQL Agent with Human-in-the-Loop Review

An SQL agent that turns natural language questions into SQL. Every proposed query is shown to you for approval, edit, or reject before execution. Supports OpenAI (default), Anthropic Claude, or Ollama (free, local).

Features

• Natural language → SQL: Ask questions in plain English; the agent proposes SQL.
• Human-in-the-loop: Review and approve, edit, or reject each query before it runs.
• Schema-aware: Automatically uses your database schema so generated SQL matches your tables.
• Schema relations: Optionally define how tables link (e.g. foreign keys); the LLM receives this as context for better JOINs.
• Multi-DB / cross-DB relations: Select multiple databases on the main page to relate tables across DBs. SQLite: multiple .db files are attached in one connection (main., db1., …). MySQL: multiple databases on the same server (same host:port) use one connection; use database.table in SQL. Define cross-DB relations on the Schema relations page.
• SQLite and MySQL: Use a local SQLite file, in-memory DB, or a MySQL connection URL.

Setup

1. Install dependencies (using uv)

   uv sync

   Or with pip: pip install -r requirements.txt

2. Environment variables (API keys, etc.)

   Copy the example file and edit with your values. The CLI and web app load .env from the project directory (where main.py and app.py live), so it is found even if you run from another folder:

   cp .env.example .env
   # Edit .env and set at least OPENAI_API_KEY=sk-proj-... (no quotes needed)

   You can still set variables in the shell; they override values from .env. In .env, write OPENAI_API_KEY=sk-proj-... without quotes around the value (or quotes are stripped automatically).

3. Choose an LLM backend

   Option A – OpenAI (default)

   • Set your API key in .env: OPENAI_API_KEY=sk-proj-...
   • Or: export OPENAI_API_KEY="your-api-key"
   • Get a key at platform.openai.com.
   • If you don't set LLM_BACKEND, OpenAI is used. Optional: export OPENAI_MODEL=gpt-4o-mini (default) or gpt-4o, etc.

   Option B – Free (Ollama, runs locally)

   • Install Ollama and pull a model:

     ollama pull llama3.2

   • Use the free backend:

     export LLM_BACKEND=ollama

   • Optional: export OLLAMA_MODEL=llama3.2 (default) or another model (e.g. mistral, codellama).

   Option C – Anthropic Claude

   • Set your API key:

     export ANTHROPIC_API_KEY="your-api-key"
     export LLM_BACKEND=anthropic

   • Get a key at console.anthropic.com.

4. Optional: create a sample database

   uv run python create_sample_db.py

   This creates sample.db with employees and departments tables.

Usage

With the sample database:

uv run python main.py --db sample.db

With your own SQLite file:

uv run python main.py --db /path/to/your.db

With a MySQL database (connection URL):

uv run python main.py --db "mysql://user:password@host:3306/database_name"

Use the same --db argument with a URL starting with mysql:// or mysql+pymysql://. The agent will connect to MySQL and run read-only queries. Special characters in the password should be URL-encoded (e.g. @ as %40).

In-memory (for testing with no file):

uv run python main.py

(You can use python main.py directly if you already ran uv sync and are in the same environment.)

OpenAI 401 / invalid API key: The app now strips whitespace from OPENAI_API_KEY. If you still get 401: (1) Run in the same terminal where you ran export OPENAI_API_KEY='...', or start the app with OPENAI_API_KEY=sk-proj-... uv run python main.py so the key is set for that process. (2) If using the web UI, ensure the shell where you run uv run python app.py has OPENAI_API_KEY set. (3) Create a new key at https://platform.openai.com/api-keys and replace the old one. (4) The CLI prints the key prefix (e.g. OpenAI key: sk-proj-xxxx...) so you can confirm the right key is loaded.

Database timeout: Connection and query timeouts default to 30 seconds. Set DB_TIMEOUT=10 (seconds) to change. Timeout and connection errors are reported clearly (e.g. Database timeout: ...).

Then type your question in natural language. The agent will propose SQL; you can:

Web interface

A minimal browser UI is available:

uv run python app.py

Put WEB_USER, WEB_PASSWORD, and FLASK_SECRET_KEY in .env (see .env.example) to enable login; the app will show a login page and only allow access after signing in. If you don’t set them, the web UI is open (no login). Open http://localhost:5050 (or set PORT=8080 to use another port). Enter the database path (or MySQL URL) and your question. You’ll see the proposed SQL and can Approve & Run, Reject, or edit the SQL and run it. Results and a short summary are shown on the same page.

Schema relations: Use the Schema relations link to define how tables link (e.g. employees.dept_id → departments.id). Enter the same database path, click Load schema, then add relations with the dropdowns. A Mermaid diagram shows the links. Relations are stored in schema_relations.json and sent to the LLM as context when proposing SQL so it can write better JOINs. Use the Log out link to sign out.

• y – Approve and run the query
• n – Reject (optionally ask a follow-up)
• e – Edit: paste your own SQL, then it will be run after you confirm

Project layout

• main.py – CLI and human-in-the-loop review flow
• app.py – Minimal web UI (Flask)
• agent.py – LLM agent (OpenAI, Anthropic, or Ollama backends)
• db.py – Schema introspection and read-only SQL execution
• relations.py – Store and format table relations for LLM context
• create_sample_db.py – Builds a sample SQLite DB for trying the agent