text2sql

A schema-agnostic, config-driven Text-to-SQL library. Give it a natural-language request and metadata for any relational schema, and it returns a SQL string.

It is a library, not an app. No UI, no web server, no CLI. It never connects to or runs against a database. Generating the SQL is the last step. Running it is the caller's job.

How it works — a 2-stage LLM pipeline

user request
      │
      ▼
┌─────────────────────────────┐   full schema metadata
│ Stage 1: Schema Linking LLM │◄──────────────────────
│  reduce schema → subset     │
└─────────────────────────────┘
      │  linked schema (tables, columns, joins, entities, filters, rationale)
      ▼
┌─────────────────────────────┐
│ Stage 2: SQL Generation LLM │
│  produce SQL in dialect     │
└─────────────────────────────┘
      │
      ▼
  Text2SQLResult(sql, linked_schema, metadata, explanation)

• Stage 1 (linking) takes the request and the full schema metadata. It reduces the schema to the relevant part and returns structured JSON. Output is forced via response_format (OpenAI) or tool-calling (Anthropic), with strict-JSON parsing and retry as a fallback.
• Stage 2 (generation) takes the request, that reduced subset, and Stage 1's reasoning (rationale, chosen joins, column reasons). It follows that logic and writes SQL in the target dialect. Two output modes (config output_mode): structured forces JSON with sql + explanation; raw asks for plain SQL text, for specialized text-to-SQL models.
• The two stages can use different providers and models. For example a cheap general model for linking, a stronger SQL model for generation.

Installation

pip install text2sql-engine            # from PyPI (import name: text2sql)
pip install "text2sql-engine[all]"     # + openai and anthropic SDKs

The PyPI name is text2sql-engine. The import name is text2sql (import text2sql). Different names on purpose, since text2sql was taken.

From a checkout of this repo:

pip install -e .              # core library
pip install -e ".[all]"       # + openai and anthropic SDKs
pip install -e ".[dev]"       # + pytest

The provider SDKs (openai, anthropic) are imported lazily. So the library imports, and the tests run, without them installed.

Quick start

from text2sql import Text2SQL

engine = Text2SQL.from_config()          # reads .env + config.toml
result = engine.run("get me the top 5 best-selling products last month")

print(result.sql)              # the generated SQL string
print(result.linked_schema)    # Stage 1 output (reduced schema)
print(result.metadata)         # models, tokens, latency, attempts

1. Copy .env.example to .env. Fill in provider selection and API keys.
2. Adjust config.toml for behavior (dialect, sampling, retries).
3. Point SCHEMA_PATH at your metadata file. See examples/schema.yaml.

Configuration — two separate layers

Config is split by concern. Secrets, paths, and provider/model selection go in .env. Everything tunable about behavior goes in config.toml. One typed layer (text2sql.config.Settings) loads both. It fails fast with a clear error if a required .env variable is missing.

.env — secrets, paths, provider & model selection

Variable	Required	Description
SCHEMA_PATH	yes	Path to the schema file (.json, .yaml, .yml).
PROMPT_DIR	no	Directory with prompt templates (linking.txt, generation.txt). If unset, the templates packaged inside the library are used. So it works after pip install, from any working directory.
CONFIG_PATH	no	Override the config.toml location. Defaults to ./config.toml.
LINKING_PROVIDER	yes	Provider for Stage 1. One of the registered names: openai, anthropic.
LINKING_MODEL	yes	Model id for Stage 1 (schema linking).
LINKING_BASE_URL	no	Endpoint override for Stage 1 (for OpenAI-compatible gateways).
SQL_PROVIDER	yes	Provider for Stage 2.
SQL_MODEL	yes	Model id for Stage 2 (SQL generation).
SQL_BASE_URL	no	Endpoint override for Stage 2.
OPENAI_API_KEY	if used	API key. Needed only if a stage uses provider openai.
ANTHROPIC_API_KEY	if used	API key. Needed only if a stage uses provider anthropic.

Provider, model, and base_url are picked per stage, independently.

config.toml — behavior

No magic numbers in code. Every tunable is here.

[general]

Key	Type	Default	Description
sql_dialect	string	postgres	Target dialect (e.g. postgres, mysql, sqlite). Passed to the prompts.
log_level	string	INFO	DEBUG / INFO / WARNING / ERROR.
strict_json	bool	true	true: parse LLM JSON strictly. false: tolerate fences / extra prose.

[linking] — Stage 1

Key	Type	Default	Description
temperature	float	0.0	Sampling temperature.
top_p	float	1.0	Nucleus-sampling cutoff.
max_tokens	int	1024	Max tokens for the linking response.
prompt_template	string	linking.txt	Template file, looked up in PROMPT_DIR.
max_tables	int	8	Max tables linking may return.
timeout_seconds	float	60	Per-request timeout.

[generation] — Stage 2

Key	Type	Default	Description
temperature	float	0.0	Sampling temperature.
top_p	float	1.0	Nucleus-sampling cutoff.
max_tokens	int	1024	Max tokens for the SQL response.
prompt_template	string	generation.txt	Template file, looked up in PROMPT_DIR. In raw mode, defaults to generation_raw.txt when unset.
timeout_seconds	float	60	Per-request timeout.
output_mode	string	structured	structured: force JSON with sql + explanation, reliable parsing, best for general models. raw: ask for plain SQL text (no explanation), better for specialized text-to-SQL models trained to emit SQL only.

[retry] — shared by both stages

Key	Type	Default	Description
max_attempts	int	3	Attempts per LLM call before failing. Retries on JSON-parse and transient provider errors.
backoff_seconds	float	1.0	First delay between retries.
backoff_factor	float	2.0	Delay is multiplied by this each retry.

Schema metadata format

The schema is never hardcoded. It is loaded from an external file through a SchemaProvider. examples/schema.yaml (and the same examples/schema.json) show the full format: a small e-commerce database with customers, categories, products, orders, and order_items. Each table has a description. Each column has a name, type, description, primary-key flag, and sample values. Foreign keys are listed. Both JSON and YAML work, picked by extension.

The example runs out of the box. The tests exercise the pipeline against it with mocked LLM calls.

Extending

Everything is swappable via config + dependency injection:

from text2sql import Text2SQL, SchemaProvider, PromptTemplates
from text2sql.schema import Schema

# 1. Custom schema source, e.g. live DB introspection instead of a file.
class MyIntrospectionProvider(SchemaProvider):
    def load(self) -> Schema:
        ...   # build and return a Schema

# 2. Inject any part. Anything left None is built from config.
engine = Text2SQL.from_config(
    schema_provider=MyIntrospectionProvider(),
    prompts=PromptTemplates("/path/to/my/templates"),
    # linking_provider=..., generation_provider=...,
)

Adding a new LLM provider

Subclass LLMProvider, implement complete (and optionally complete_json for native structured output), and register it. One class, one decorator:

from text2sql import LLMProvider, register_provider
from text2sql.types import LLMResponse

@register_provider("myprovider")
class MyProvider(LLMProvider):
    def complete(self, *, system, user, temperature, top_p, max_tokens) -> LLMResponse:
        ...

Then set LINKING_PROVIDER=myprovider (or SQL_PROVIDER) in .env.

Prompts

Prompt templates are plain, editable text files in PROMPT_DIR. Each has a SYSTEM: and a USER: section with {placeholder} substitution. Edit them without touching code.

Result object

engine.run(...) returns a Text2SQLResult:

Field	Description
sql	The generated SQL string. The deliverable.
explanation	Short explanation from Stage 2.
linked_schema	Stage 1 output: tables, columns, joins, entities, filters, rationale.
metadata	RunMetadata: per-stage provider/model, token usage, latency, attempts. Plus total_usage and total_latency_seconds.
request	The original request.

Error handling

Every error subclasses Text2SQLError:

• ConfigError — missing/invalid .env var or config.toml.
• SchemaError — schema file missing or invalid.
• ProviderError — provider build or API call failed.
• StructuredOutputError — output not parseable as JSON after all retries.
• EmptyLinkingError — Stage 1 matched no table/column.

Uses stdlib logging throughout, never print. Secrets are never logged.

Try it end to end

With a real provider and key set in .env:

python examples/run_example.py "top 5 best-selling products last month"

Makes real LLM calls for both stages. Prints the linked schema, the SQL, and the metadata. Never connects to a database.

Development

pip install -e ".[dev]"
pytest

Tests mock all LLM calls (no network) and run on examples/schema.yaml.

Project layout

text2sql/
  config.py              .env + config.toml loading, typed settings
  types.py               result dataclasses
  errors.py              exceptions
  logging_utils.py       logging setup
  schema/                SchemaProvider, file loader, models, serializer
  providers/             base + registry + openai + anthropic
  prompts/               editable linking.txt / generation.txt templates
  pipeline/              linking (Stage 1), generation (Stage 2), orchestrator
examples/
  schema.yaml / schema.json    sample metadata "spec"
  run_example.py               runnable end-to-end example (real LLM call)
config.toml              behavioral parameters
.env.example             secrets / paths / model selection template
tests/                   pipeline, schema, config, and prompt tests
.github/workflows/ci.yml GitHub Actions: pytest on 3.11 and 3.12
LICENSE                  MIT

License

MIT. See LICENSE.