How text2sqlagent/text2sql-framework works
1. What this repo is
- text2sql is a Python SDK that turns a natural-language question into a verified SQL query and result set.
- It is built around an agentic loop: the LLM can inspect schema, try SQL, observe errors/results, and self-correct until it can answer.
2. Primary entrypoint and public API
- text2sql.core.TextSQL is the main façade.
- From the outside, callers give it a database connection string, optional model choice, optional scenario/examples file, and optional trace output path.
- The returned result contains both the final SQL and the query data, so the architecture exposes the verified query as a first-class output, not just the answer text.
3. Database boundary
- text2sql.connection.Database wraps SQLAlchemy and is the boundary to the real database.
- The repo is database-agnostic at the API level, but dialect-specific behavior matters for schema discovery and introspection.
- Supported backends include SQLite, PostgreSQL, MySQL, SQL Server, and Snowflake through their SQLAlchemy drivers.
4. Dialect-aware schema exploration
- text2sql.dialects contains per-dialect guidance for discovering schema information.
- This is important because the agent does not rely on precomputed schema embeddings or RAG; instead it directly queries the database metadata using the right dialect-specific mechanism.
- Example strategies mentioned in the README include PRAGMA for SQLite and information_schema/system catalogs for other engines.
5. Agent and SQL generation loop
- text2sql.generate.SQLGenerator assembles the agent, runs the iterative loop, and parses the final outcome.
- The core behavior is exploratory: the LLM can issue execute_sql repeatedly, inspect table lists and column metadata, then refine the query.
- This is the central control plane of the system and the main place where model behavior and tool availability shape the architecture.
6. Tool boundary
- text2sql.tools defines the tools exposed to the agent, primarily:
- execute_sql for querying the database
- lookup_example for fetching relevant scenario guidance
- These tools are the only capabilities the agent needs; the design intentionally avoids a larger tool surface to keep the loop focused on SQL synthesis and validation.
7. Scenario/example knowledge layer
- text2sql.examples.ExampleStore loads markdown scenarios from a file like scenarios.md.
- Scenarios are not dumped into context up front; they are indexed by heading and fetched on demand via lookup_example.
- This layer injects domain-specific rules, naming translations, and join conventions that are not inferable from schema alone.
8. Tracing and observability
- text2sql.tracing captures the full agentic interaction: tool calls, SQL attempts, errors, and corrections.
- Traces are written to a file when configured, which makes the system auditable and also feeds the feedback loop for improving scenarios.
- In architecture terms, this is an important side channel between runtime execution and later analysis.
9. Analysis and feedback loop
- text2sql.analyze and text2sql.models support deterministic trace analysis and structured reporting.
- The README describes a workflow where traces are analyzed to identify repeated failure patterns and then converted into better scenario guidance.
- This creates a closed improvement loop: query execution produces traces, traces produce scenarios, scenarios improve future query success.
10. CLI and packaging layer
- text2sql.cli provides command-line access for interactive asking and single-shot querying.
- The package is configured in pyproject.toml, with optional extras for model providers such as Anthropic and OpenAI.
- This is a thin shell over the same core engine, not a separate execution path.
11. Testing and examples
- tests/test_core.py likely validates the public API and core behavior at the package boundary.
- The examples/ directory demonstrates real usage patterns, including dummy DB runs and scenario-driven workflows.
- These are support artifacts, but they reflect the intended architecture: one core engine, multiple entry surfaces.
12. Overall data flow
- A user question enters TextSQL.
- The agent uses the database wrapper and dialect guidance to inspect schema.
- It may fetch scenario help from the example store.
- It executes SQL through the tool layer, reads results/errors, iterates, and emits a final verified SQL statement plus data.
- If tracing is enabled, the entire loop is recorded for later analysis and scenario refinement.
Main entry points: core.py TextSQL · generate.py SQL Generator · agent.py Agent · tools.py Tools
Components 16
Core engine
Public API and agentic SQL synthesis loop.
- TextSQL · Main public entrypoint for asking questions and getting verified SQL plus results.
text2sql/core.py - SQL Generator · Builds the agent, runs iterative SQL execution, and returns the final outcome.
text2sql/generate.py - Agent · Coordinates tool use, schema inspection, retries, and self-correction.
text2sql/agent.py - Tools · Defines execute_sql and lookup_example as the agent's limited action surface.
text2sql/tools.py
Data access
Database boundary and dialect-specific introspection.
Scenario knowledge
On-demand examples and domain guidance.
- Example Store · Loads markdown scenarios and retrieves relevant guidance on demand.
text2sql/examples.py - Scenarios · Human-authored scenario and domain rule content used by the example store.
examples/scenarios.md
Observability
Tracing, analysis, and reporting feedback loop.
Interfaces
CLI and package entry surface.
- CLI · Command-line entry surface for interactive and single-shot querying.
text2sql/cli.py - Package Init · Package-level exports that expose the public SDK surface.
text2sql/__init__.py - README · Documents the agentic workflow, supported backends, and operational loop.
README.md - Examples · Runnable demonstrations of the SDK and scenario-driven workflows.
examples/demo_with_dummy_db.py - Tests · Validates the public API and core behavior at the package boundary.
tests/test_core.py
Connections 17
- TextSQL → SQL Generator: invokes · TextSQL delegates query solving to the generator loop.
- SQL Generator → Agent: assembles · The generator constructs and runs the agent orchestration.
- Agent → Tools: uses tools · The agent can only act through the exposed SQL and lookup tools.
- Tools → Database: executes SQL · execute_sql routes queries through the database boundary.
- Agent → Dialects: inspects schema · The agent relies on dialect-specific discovery guidance.
- Dialects → Database: introspects · Dialect strategies target the underlying connection and metadata APIs.
- Tools → Example Store: looks up · lookup_example fetches relevant scenario guidance on demand.
- Example Store → Scenarios: loads · Scenario content is stored in markdown files.
- SQL Generator → Tracing: records · Execution attempts and outcomes can be traced end-to-end.
- Tracing → Analyze: feeds analysis · Recorded traces are later analyzed for failure patterns.
- Analyze → Models: structures · Analysis results are represented with structured models.
- Analyze → Scenarios: improves · Analysis can inform better scenario guidance.
- CLI → TextSQL: calls · The CLI is a thin shell over the core façade.
- Package Init → TextSQL: exports · Package init exposes the public SDK entrypoint.
- README → TextSQL: documents · The README describes the primary runtime flow and API.
- Examples → TextSQL: demonstrates · Examples show how callers use the same core engine.
- Tests → TextSQL: verifies · Tests exercise the public boundary and main behavior.