text2sql

Until recently, LLMs couldn't reliably chain more than a handful of tool calls before losing the thread. Though frontier models now make dozens, hundreds, or even thousands of iterative tool calls from a single prompt, reading each result and deciding what to do next. This unlocks a different shape of text-to-SQL system: instead of pre-computing which schema elements are relevant, you can hand the LLM one tool (execute_sql) and let it explore the schema, write queries, test them against real data, and self-correct before returning a final answer. This SDK requires no RAG, semantic layer, schema descriptions, etc. All that is needed is a connection string and a frontier model, as shown in the example below.

As models keep getting better at recursive tool use, the right move is to keep rearchitecting the harness so it constrains the LLM as little as possible (every guardrail you remove is capability you get back).

Experimental Python-first agent

Python-first mode gives the model one persistent tool, run_python(code), rather than a standalone SQL tool:

t2s = TextSQL("sqlite:///analytics.db", agent_mode="python")
result = t2s.ask("Which five customers generated the most revenue?")

Inside Python the agent receives db (read-only queries and schema inspection), traces, skills, prompt, and examples. Variables persist between Python calls, so the agent can inspect the database, query it, analyze rows, consult old traces, and—when explicitly enabled—save reusable instructions in one workspace. SQL still runs under the hood through db.query(...); it is simply no longer a separate model-facing tool.

Python-first state is local by default:

.text2sql/state.db       # skills and editable prompt addendum
.text2sql/traces.jsonl   # execution traces

Persistence is configurable:

# Explicitly put state tables and traces in the queried database
t2s = TextSQL(url, agent_mode="python", state_store="database")

# Allow the model to persist new skills or edit its prompt addendum
trusted = TextSQL(url, agent_mode="python", allow_self_modification=True)

# Prefer a separate state database
t2s = TextSQL(url, agent_mode="python", state_store="postgresql://.../agent_state")

# Process-memory state and no default trace file
t2s = TextSQL(url, agent_mode="python", state_store=False)

The source database is never written by default. The current persistent runtime is synchronous and in-process: its AST restrictions are useful agent guardrails, not a hardened security boundary. Use read-only database credentials and a separate process/container before running code from untrusted users.

19/20 (95%) on Spider zero-shot across 80 tables and 20 databases. 20/20 after adding one scenario.

from text2sql import TextSQL

engine = TextSQL(
    "postgresql://user:pass@localhost/mydb",
    trace_file="traces.jsonl",
)
result = engine.ask("Which customers have spent more than $10K this year?")

print(result.sql)   # verified SQL
print(result.data)  # [{'name': 'Acme Corp', 'total': 14302.50}, ...]

Getting started

pip install "text2sql-framework[anthropic]"   # or: "text2sql-framework[openai]"

The fastest path from zero to a working query is the bundled demo, which builds its own SQLite database (no external database required) and runs a few questions against it:

git clone https://github.com/Text2SqlAgent/text2sql-framework.git
export ANTHROPIC_API_KEY=sk-ant-...    # or: export OPENAI_API_KEY=sk-... (uses openai:gpt-4o)
python text2sql-framework/examples/demo_with_dummy_db.py

Building the dummy database needs no API key; answering the questions calls your model, so set ANTHROPIC_API_KEY (default anthropic:claude-sonnet-4-6) or OPENAI_API_KEY. See Install and Quick start below for the full package options, supported databases, and the CLI.

How it works

Below is a real agent trace from the Spider benchmark. The agent is connected to a single database containing 80 tables from 20 different schemas — it has to find the right tables for each question on its own.

Question: "List the name of singers in ascending order of net worth."

┌─────────────────────────────────────────────────────────────┐
│ Tool:   execute_sql                                         │
│ Input:  SELECT name FROM sqlite_master                      │
│         WHERE type='table' AND name NOT LIKE 'sqlite_%'     │
│ Output: battle, ship, death, continents, countries,         │
│         car_makers, model_list, car_names, cars_data,       │
│         stadium, singer, concert, singer_in_concert,        │
│         course, teacher, … (80 tables)                      │
├─────────────────────────────────────────────────────────────┤
│ Tool:   execute_sql                                         │
│ Input:  PRAGMA table_info('singer')                         │
│ Output: Singer_ID INT, Name TEXT, Country TEXT,              │
│         Song_Name TEXT, Song_release_year TEXT,              │
│         Age INT, Is_male BOOL                               │
│         ← no Net_Worth column — wrong table                 │
├─────────────────────────────────────────────────────────────┤
│ Tool:   execute_sql                                         │
│ Input:  PRAGMA table_info('singer_solo')                    │
│ Output: Singer_ID INT, Name TEXT, Birth_Year REAL,          │
│         Net_Worth_Millions REAL, Citizenship TEXT            │
│         ← found it                                          │
├─────────────────────────────────────────────────────────────┤
│ Tool:   execute_sql                                         │
│ Input:  SELECT Name FROM singer_solo                        │
│         ORDER BY Net_Worth_Millions ASC                     │
│ Output: Abigail Johnson, Susanne Klatten,                   │
│         Gina Rinehart, Iris Fontbona, …  ✓                  │
└─────────────────────────────────────────────────────────────┘

The agent saw 80 tables, found two singer tables, inspected both, identified which one had the Net_Worth_Millions column, and wrote the correct query. Four tool calls, all autonomous.

Schema retrieval and SQL generation happen in the same loop, not as separate pipeline stages. If the agent picks the wrong table, it goes back and finds the right one. If a query errors, it reads the error message and fixes it. If the output doesn't look right, it rethinks its approach.

Benchmarks

Tested on the Spider benchmark — the most widely used text-to-SQL evaluation, with 10,000+ questions across 200 databases. We merged all 20 dev-set databases into a single 80-table database and ran 20 questions — one per database, randomly selected. The agent had to navigate 80 tables to find the right ones for each question.

19/20 (95%) zero-shot, no examples. The single failure was an ambiguous question — "What is maximum and minimum death toll caused each time?" — where the agent returned per-battle results instead of a global aggregate. After adding a one-line scenario clarifying that "each time" means overall, the agent used lookup_example to retrieve the guidance and got it right: 20/20.

Install

pip install text2sql-framework

# With Anthropic:
pip install "text2sql-framework[anthropic]"

# With OpenAI:
pip install "text2sql-framework[openai]"

# With the Databricks SQLAlchemy dialect:
pip install "text2sql-framework[databricks]"

Quick start

from text2sql import TextSQL

# Connect to any SQLAlchemy-supported database
engine = TextSQL("sqlite:///company.db")

# Ask a question
result = engine.ask("Top 5 products by total revenue")
print(result.sql)
print(result.data)

# Control how many rows come back
result = engine.ask("All customers in New York", max_rows=50)

LLM providers

# Anthropic (recommended)
engine = TextSQL("sqlite:///mydb.db", model="anthropic:claude-sonnet-4-6")

# OpenAI
engine = TextSQL("sqlite:///mydb.db", model="openai:gpt-4o")

Database support

Any database with a SQLAlchemy driver:

TextSQL("postgresql://user:pass@localhost/mydb")
TextSQL("mysql+pymysql://user:pass@localhost/mydb")
TextSQL("sqlite:///mydb.db")
TextSQL("mssql+pyodbc://user:pass@server/db?driver=ODBC+Driver+17+for+SQL+Server")
TextSQL("snowflake://user:pass@account/db/schema")
TextSQL("databricks://token:<token>@<host>?http_path=<path>&catalog=<catalog>&schema=<schema>")

For Databricks, use a SQL warehouse (or compatible compute) and a principal with only CAN USE/USE CATALOG/USE SCHEMA/SELECT privileges. Keep the URL in an environment variable rather than source control. The Databricks SQLAlchemy dialect does not provide this framework a read-only transaction, so least-privilege Databricks grants are the authoritative write boundary. For database-backed traces, use a separate Postgres sink rather than the queried Databricks catalog; see mcp/README.md.

The agent automatically detects the SQL dialect and adjusts its schema exploration strategy — information_schema for PostgreSQL/MySQL/Snowflake, PRAGMA for SQLite, sys.tables for SQL Server.

Scenarios and the feedback loop

The agent works out of the box with just a connection string — but real databases have jargon, business logic, and naming conventions that no LLM can guess. That's where scenarios.md comes in.

A scenarios file is a markdown file where each ## heading contains domain knowledge the agent can't infer from the schema alone — business rules, column name translations, tricky join paths, corrective guidance:

## net revenue
Net revenue = gross revenue minus refunds.
Use INNER JOIN between orders and payments, not LEFT JOIN.
- `orders.amt_ttl` is the gross order total
- Refunds are in the `payments` table where `is_refund = 1`

    -- CORRECT
    SELECT SUM(o.amt_ttl) + SUM(p.amt) FROM orders o
    JOIN payments p ON o.order_id = p.order_id WHERE p.is_refund = 1;

At runtime, the agent doesn't get the entire file dumped into its context. It sees a list of scenario titles and gets a lookup_example tool. When it's about to write a query involving revenue, it calls lookup_example("net revenue") and retrieves the full guidance before writing SQL. The agent decides when it needs help, and only pulls in what's relevant.

engine = TextSQL(
    "postgresql://localhost/mydb",
    examples="scenarios.md",
    trace_file="traces.jsonl",
)

Use as a keyless Claude Code subagent

Claude Code can supply the model reasoning while Text2SQL supplies a persistent, read-only Python database workspace. No Anthropic/OpenAI key is needed by the Text2SQL process:

pip install text2sql-framework
cd your-project
text2sql init --database-type postgres
export TEXT2SQL_DATABASE_URL='postgresql://readonly@localhost/analytics'
claude

The initializer creates .mcp.json, .claude/agents/text2sql.md, tracked Markdown skills, and an editable /improve-text2sql command. The subagent uses this lifecycle:

start_query → run_python(query_id, ...) → finish_query(query_id, ...)

After traces accumulate, run /improve-text2sql to commit evidence-based edits to the coding-subagent prompt and its tracked skills.

Completed and explicitly aborted investigations are traced locally by default:

.text2sql/traces.jsonl

Select database tracing during setup if desired:

text2sql init --trace-mode database

That creates and writes the fixed text2sql_traces and text2sql_tool_calls tables. Use local or a separate trace database when the analytics connection should remain strictly read-only. See mcp/README.md for the tool contract and environment options.

CLI

# Interactive mode
text2sql ask "sqlite:///mydb.db"

# Single question
text2sql query "sqlite:///mydb.db" "How many orders per month?"

# With options
text2sql ask "postgresql://localhost/mydb" --model anthropic:claude-sonnet-4-6

Use with LangChain agents

If you're already building an agent with LangChain's create_agent, you can plug text2sql in as middleware instead of using the standalone TextSQL class. The middleware adds an execute_sql tool, dialect-aware schema-exploration guidance to the system prompt, and (optionally) a lookup_example tool wired to your scenarios file.

pip install "text2sql-framework[langchain]"

from langchain.agents import create_agent
from text2sql import Text2SqlMiddleware

t2s = Text2SqlMiddleware(
    db_url="postgresql://user:pass@localhost/mydb",
    examples="scenarios.md",          # optional
    instructions="Revenue is net of refunds.",  # optional
)

agent = create_agent(
    model="anthropic:claude-sonnet-4-6",
    tools=t2s.tools,
    middleware=[t2s],
)

result = agent.invoke({"messages": [{"role": "user", "content": "Top 5 customers by revenue?"}]})
print(result["messages"][-1].content)

The middleware requires langchain>=1.0. A runnable example lives at examples/with_langchain.py.

Framework-agnostic core

By default, TextSQL runs its own agent loop directly against the raw Anthropic or OpenAI SDK (pip install "text2sql-framework[anthropic]" or [openai]) — no LangChain dependency required. OpenAI-compatible endpoints (OpenRouter, Ollama, vLLM, ...) work via OPENAI_BASE_URL.

If you'd rather run on Deep Agents (langchain-ai/deepagents) — automatic context compaction and Anthropic prompt caching via LangChain middleware — pass agent_backend="langchain" and install the langchain extra. This is also what Text2SqlMiddleware uses under the hood.

Architecture

text2sql/
├── core.py          # TextSQL — public API
├── generate.py      # SQLGenerator — builds the agent, parses results
├── connection.py    # Database — SQLAlchemy wrapper
├── tools.py         # execute_sql + lookup_example (LangChain tools)
├── dialects.py      # Per-dialect schema exploration guides
├── examples.py      # ExampleStore — loads scenario markdown
├── tracing.py       # Tracer — captures full agentic loop
├── analyze.py       # AnalysisEngine — deterministic trace analysis
├── models.py        # Pydantic models for analysis reports
└── cli.py           # Click CLI

License

MIT