text2sql-easy

text2sql-easy is a Codex skill for building searchable semantic knowledge bases from SQL databases, capturing business context through user-agent dialogue, and helping agents write, preflight, and run SQL.

It is designed for real databases where schema alone is not enough. The scripts extract deterministic schema facts, while the skill workflow asks users for business semantics such as metric definitions, status meanings, time semantics, and undeclared join relationships.

Features

• Standard Codex SKILL.md
• Single helper CLI: scripts/text2sql_easy.py
• SQLAlchemy-based schema reflection
• Searchable YAML knowledge base per database
• Table search, table summaries, neighbor lookup, and join-path lookup
• SQL dialect preflight with optional live EXPLAIN
• SQL execution with CSV output
• Persistent workspace-local business knowledge files
• Credential-safe configuration through environment variables

Supported Databases

Supported through SQLAlchemy URLs and matching drivers:

• SQLite
• MySQL
• PostgreSQL
• Snowflake
• ClickHouse

Repository Layout

text2sql-easy/
├── SKILL.md
├── agents/
│   └── openai.yaml
└── scripts/
    ├── requirements.txt
    ├── text2sql_easy.py
    └── text2sql_easy/
        ├── config.py
        ├── inspector.py
        ├── writer.py
        ├── scorer.py
        ├── relationships.py
        ├── search.py
        ├── sql_preflight.py
        └── sql_runner.py

Installation

Clone this repository into a location where your agent can read skills, or copy the repository folder into your Codex skills directory.

Install core dependencies:

pip install -r scripts/requirements.txt

Install the driver for your database as needed:

pip install pymysql                  # MySQL
pip install psycopg2-binary          # PostgreSQL
pip install snowflake-sqlalchemy     # Snowflake
pip install clickhouse-connect       # ClickHouse

SQLite only needs Python's built-in SQLite support plus SQLAlchemy.

Configuration

Connection configuration is intentionally stored outside the repository.

Default config path on Windows:

%USERPROFILE%\.text2sql-easy\connections.yml

You can override it with:

$env:TEXT2SQL_EASY_CONFIG="D:\path\to\connections.yml"

Create a sample config:

python scripts/text2sql_easy.py config init

Example config:

version: 1
default_db: sales_prod

databases:
  sales_prod:
    dialect: postgresql
    conn_env: SALES_PROD_SQLALCHEMY_URL
    artifact_root: ./.text2sql-easy
    sample_size: 5

  local_demo:
    dialect: sqlite
    conn: sqlite:///D:/data/demo.sqlite
    artifact_root: ./.text2sql-easy

Prefer conn_env for real credentials:

$env:SALES_PROD_SQLALCHEMY_URL="postgresql+psycopg2://user:password@host:5432/dbname"

Quick Start

List configured databases:

python scripts/text2sql_easy.py db list

Build or reuse a knowledge base:

python scripts/text2sql_easy.py kb ensure --db sales_prod

Search relevant tables:

python scripts/text2sql_easy.py search --db sales_prod --query "customer revenue order"

Inspect one table:

python scripts/text2sql_easy.py table --db sales_prod --name orders

Find a join path:

python scripts/text2sql_easy.py join-path --db sales_prod --from-table orders --to-table customers

Preflight SQL:

python scripts/text2sql_easy.py preflight --db sales_prod --sql-file .text2sql-easy/submissions/query.sql --mode both

Run SQL and write CSV:

python scripts/text2sql_easy.py run --db sales_prod --sql-file .text2sql-easy/submissions/query.sql --out .text2sql-easy/submissions/query.csv

Knowledge Artifacts

Generated and user-authored artifacts live under artifact_root, usually ./.text2sql-easy/:

.text2sql-easy/
├── knowledge/<db_id>/
│   ├── catalog.yml
│   ├── DDL.sql
│   ├── ER.md
│   ├── relationships.yml
│   ├── data_source.md
│   ├── examples.md
│   └── tables/*.yml
├── descriptions/
│   ├── <db_id>.md
│   ├── <db_id>_relationships.yml
│   └── <db_id>_examples.md
├── submissions/
└── runs/

knowledge/ is generated from the database plus user-authored sources. descriptions/ is the editable source of business knowledge.

How It Works

text2sql-easy separates two layers:

1. Programmatic schema knowledge

   • table names
   • columns and types
   • comments
   • primary keys and foreign keys
   • row counts
   • sample rows
   • DDL
   • table importance ranking
   • relationship graph

2. Interactive business knowledge

   • what tables mean
   • metric definitions
   • status semantics
   • timestamp semantics
   • joins not declared as foreign keys
   • representative SQL patterns

The agent should first run kb ensure. If the status report shows missing business description, relationships, or SQL examples, the agent should ask the user targeted questions and persist the answers in .text2sql-easy/descriptions/, then rebuild the KB.

Skill Usage

The skill entry point is SKILL.md. A Codex-compatible agent should read it and follow the workflow there.

For direct CLI usage, run:

python scripts/text2sql_easy.py --help

Security Notes

• Do not commit database credentials.
• Prefer conn_env over plaintext conn.
• Keep generated .text2sql-easy/ workspace artifacts out of this repository unless you intentionally want to publish sample data.

License

MIT