NL SQL Agent

NL SQL Agent is a local FastAPI application that turns a natural-language question into a read-only SQLite or MySQL query, executes it through a second safety boundary, and returns a plain-language answer, SQL, table, and optional chart.

The model proposes SQL. The harness supplies schema context, validates the statement, controls the database connection, limits time and returned rows, retries one repair prompt when execution fails, and keeps credentials out of responses.

Request path

Question
  → database profile + schema
  → model or deterministic demo SQL
  → SQL Guard
  → read-only executor
  → optional one-time repair
  → answer + SQL + rows + chart

Safety boundary

• Only one SELECT or WITH statement is accepted.
• Write and administration keywords are rejected, including INSERT, UPDATE, DELETE, DROP, ALTER, ATTACH, and PRAGMA.
• File and server-side export paths such as LOAD_FILE and INTO OUTFILE are rejected.
• The executor validates the SQL again before opening a connection.
• SQLite uses read-only mode.
• MySQL must use a read-only account; the UI deliberately warns against root.
• QUERY_TIMEOUT_MS bounds the database call where the driver supports it.
• MAX_RESULT_ROWS limits rows returned to the browser. It is not presented as a database scan-cost limit.
• MySQL passwords are used to build an in-memory connection profile and are masked in API responses.

These controls reduce risk; they are not a replacement for database permissions, network isolation, query-cost controls, and auditing in a production system.

Quick start

Python 3.11+:

git clone https://github.com/kairosbladex/nl-sql-agent.git
cd nl-sql-agent
python3 -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt
python scripts/create_demo_db.py
uvicorn app.main:app --host 127.0.0.1 --port 8000

Open http://127.0.0.1:8000.

Windows PowerShell:

git clone https://github.com/kairosbladex/nl-sql-agent.git
cd nl-sql-agent
py -3 -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt
python scripts\create_demo_db.py
python -m uvicorn app.main:app --host 127.0.0.1 --port 8000

Model configuration

Copy the checked-in example and set one provider key:

cp .env.example .env

Supported environment variables include:

LLM_PROVIDER=deepseek
DEEPSEEK_API_KEY=
DEEPSEEK_MODEL=deepseek-v4-flash
DEEPSEEK_BASE_URL=https://api.deepseek.com

OPENAI_API_KEY=
OPENAI_MODEL=gpt-4.1-mini
OPENAI_BASE_URL=https://api.openai.com/v1

No real key is required for the bundled SQLite demo. Its deterministic rules cover the sample questions and keep tests independent of an external model.

Database configuration

SQLite:

DATABASE_NAME=default
DATABASE_PATH=data/demo.db
DATABASE_URL=

MySQL through environment configuration:

DATABASE_NAME=example
DATABASE_URL=mysql+pymysql://readonly_user:password@127.0.0.1:3306/example?charset=utf8mb4

The browser also provides a MySQL connection panel. Use a dedicated read-only account and a database that is safe for exploratory queries.

Without a model key, MySQL is limited to deterministic metadata questions such as table count and table names.

API

• GET /api/status - active model mode and sanitized database profile
• POST /api/chat - one complete query response
• POST /api/chat/stream - SSE status, answer deltas, and final result
• POST /api/databases/test - validate a MySQL profile and list visible databases

Example:

curl -X POST http://127.0.0.1:8000/api/chat \
  -H 'Content-Type: application/json' \
  -d '{"message":"2026 年 1 月的销售额是多少？"}'

Verify

python -m pytest -q
python -m compileall -q app scripts

The public-release suite contains 32 tests covering deterministic demo queries, answer formatting, API/SSE behavior, profile sanitization, SQLite execution, MySQL profile construction, schema loading, query limits, and SQL Guard rejection paths.

Tests build the demo database in a temporary directory; they do not rely on a developer's local data/demo.db.

Known limits

• SQL generation quality depends on the selected model and the available schema.
• The repair path retries once; it does not loop until a query succeeds.
• Returned-row limits do not prevent an expensive database execution plan.
• The current UI stores a connection only in the active browser session.
• Charts are derived from returned rows and are not a general BI semantic layer.
• No production accuracy, latency, or cost claim is made.

License

MIT. See LICENSE.