Prompt2SQL

Ask your PostgreSQL database questions in plain English. Get back the SQL, the rows, and an explanation.

Prompt2SQL connects to any PostgreSQL database at runtime — no hardcoded schema, no config file describing your tables. It indexes that schema into a per-session vector store, then runs an agentic loop that looks up the relevant tables, writes SQL, validates it, executes it, and retries against the real database error when it gets something wrong.

You:  Show all employees in Engineering earning above 100000

SQL:  SELECT name, salary FROM employees
      WHERE department = 'Engineering' AND salary > 100000
      LIMIT 100;

Answer: 7 engineers earn above $100,000, averaging $128,400 —
        the highest is Priya Sharma at $164,000.

Follow up with "only the ones hired this year" and it remembers what "the ones" means.

Why this exists

Most Text-to-SQL demos paste the entire database schema into one giant prompt, take whatever SQL the model emits, run it once, and fail if it's wrong. That breaks down in three predictable ways:

Problem	What Prompt2SQL does instead
The schema doesn't fit. A 60-table database blows the context window and costs a fortune per question.	Schema is embedded once per connection into ChromaDB. Each question retrieves only the tables that are relevant to it.
The first query is often wrong. A hallucinated column name means the request just fails.	The real Postgres error is fed back to the model as a message, and it fixes the query and tries again — bounded, not infinite.
The model can write anything. Nothing stops a `DROP TABLE` from reaching the database.	Every single attempt passes through `SQLValidatorService` — SELECT-only, dangerous-keyword blocking — before it touches the connection.

Highlights

Feature	Detail
Bring your own database	Paste a PostgreSQL connection URL at runtime. Schema is introspected from `information_schema` — nothing is hardcoded.
RAG-based schema retrieval	One embedded document per table in a per-session Chroma collection. The agent's `search_schema` tool returns only relevant tables, not all of them.
Agentic retry-on-error	A LangChain `create_agent` tool loop. Bad column? Syntax error? The database's own error message becomes the next prompt.
Structured SQL, not text	SQL arrives as a tool-call argument (`run_query(sql=...)`) — no markdown fences to strip, no guessing where one statement ends.
Execution safety	SELECT-only enforcement and keyword blocking gate every attempt the agent makes. Schema introspection queries are explicitly allowed.
Conversation memory	Redis-backed history, replayed to the agent as real chat messages, so follow-up questions resolve pronouns and implicit filters.
Session isolation	Every connection gets its own `session_id` → its own Redis entry, its own Chroma collection, its own SQLAlchemy engine. One instance serves many databases concurrently.
Real auth	Email OTP or one-click Google OAuth with automatic redirect back into the UI — no manual token copy/paste.
Fully async	FastAPI, async SQLAlchemy, asyncpg, async Redis, and `ChatGroq.ainvoke()` end to end.

Architecture

Two entry points: you connect a database once, then ask it as many questions as you like.

flowchart TD
    subgraph connect["① POST /database/connect — once per database"]
        A["Connection URL"] --> B["Validate with SELECT 1"]
        B --> C["Generate session_id"]
        C --> D["SchemaService<br/>introspect information_schema"]
        D --> E["SchemaIndexService<br/>embed one doc per table"]
        E --> F[("ChromaDB<br/>schema_session_id")]
        C --> G[("Redis<br/>session_id → database_url")]
    end

    subgraph prompt["② POST /prompt — once per question"]
        H["Question + session_id"] --> I["ConversationMemoryService<br/>load history as chat messages"]
        I --> J["Build tools bound to this session"]
        J --> K{{"SQL Agent — create_agent"}}
        K -->|"search_schema"| F
        K -->|"run_query"| L["SQLValidatorService"]
        L -->|"rejected"| K
        L -->|"passed"| M["SQLExecutionService"]
        M -->|"DB error"| K
        M -->|"rows"| N["ResultExplanationService"]
        N --> O["Answer + SQL + rows"]
        O --> P["Save to Redis memory"]
    end

    G -.->|"lookup"| J

Layers

Layer	Components	Responsibility
API	`prompt_routes`, `database_routes`, `auth_routes`	HTTP surface, JWT guards, DI wiring
Connection	`DatabaseConnectionService`	Validate URLs, mint sessions, build dynamic SQLAlchemy engines
Schema	`SchemaService`, `SchemaRepository`	Introspect `information_schema` once per connection
Retrieval	`SchemaIndexService`	Embed and semantically search table definitions in ChromaDB
Orchestration	`TextToSQLService`	Run the agent, capture the winning SQL/result, explain, remember
Agent tools	`sql_tools.py`	`search_schema` and `run_query`, both bound to the current session
Validation	`SQLValidatorService`	SELECT-only, keyword blocking — called inside `run_query`
Execution	`SQLExecutionService`, `SQLExecutionRepository`	Run the query, catch errors as retryable feedback
Explanation	`ResultExplanationService`	Turn rows into a sentence — a separate, non-agentic LLM call
Memory	`ConversationMemoryService`	Store and replay conversation history from Redis

Endpoints

All routes are prefixed with /api/v1. Interactive docs live at http://localhost:8000/docs.

Method	Path	Auth	Purpose
`POST`	`/auth/send-otp`	—	Email a one-time login code
`POST`	`/auth/signin`	—	Exchange the OTP for a JWT
`GET`	`/auth/google/login`	—	Redirect to Google's consent screen
`GET`	`/auth/google/callback`	—	Handle Google's code, redirect back to the UI with a token
`POST`	`/database/connect`	JWT	Validate a database URL, index its schema, return a `session_id`
`POST`	`/prompt`	JWT	Ask a question against a connected session

How the SQL Agent Works

The retry behavior comes from create_agent's tool-calling loop: the model calls a tool, the tool's result — or its error — is appended to the conversation, and the model gets another turn to react. It repeats until the model stops calling tools or hits MAX_AGENT_STEPS (12).

Three things about this loop are not obvious from reading the code, and matter if you touch it:

1. create_agent's tool node does not catch exceptions. A real Postgres error raised inside run_query would crash the entire request rather than becoming something the model can recover from. sql_tools.py catches it explicitly and returns it as a string, which is the only reason retry-on-error works at all.

2. Hitting the step cap raises, it does not return. MAX_AGENT_STEPS is enforced by LangGraph as a GraphRecursionError. TextToSQLService catches it and falls back to the same error response used when the agent never produced valid SQL.

3. Tool retries do not cover model failures. create_agent retries failures inside a tool call. If the model emits a malformed tool-call generation, Groq rejects it with tool_use_failed before the tool node is ever reached. That's why TextToSQLService retries the whole agent invocation (MAX_AGENT_INVOCATION_ATTEMPTS) with a bumped RETRY_TEMPERATURE of 0.3 — at temperature 0 a retry can only ever reproduce the identical broken output.

Where the token savings actually are. search_schema returning a relevant subset instead of the entire schema is a genuine per-question cost reduction. It isn't free, though: the local embedding model isn't perfect on short schema-only text. During testing, "total revenue from paid invoices" correctly ranked invoices first but ranked an unrelated employees table above the actually relevant orders table. Semantic retrieval can miss a table; dumping the whole schema never could.

Schema-as-a-tool is a separate trade-off. The agent calls search_schema on nearly every question, and each call costs an extra LLM round trip that a directly injected schema wouldn't need. Groq can also fire search_schema and run_query in the same turn, so the model sometimes guesses at SQL before seeing the search results and self-corrects once they arrive. Resilience is being bought with latency — which is also why the Streamlit client's timeout is 120s rather than 60s.

Design Decisions

This project started as a hand-rolled pipeline: a raw Groq client, prompts built with f-strings, a regex SQL cleanup step, and one fixed pass with no retry. It was migrated to LangChain one piece at a time.

Was	Now	Why
Raw `groq.Groq` client	`ChatGroq` (langchain-groq)	Same model, but a genuinely async client instead of a sync one wrapped in an `async def`.
f-string prompts	`ChatPromptTemplate`	Reusable templates instead of raw string formatting.
Regex SQL cleanup (`_clean_llm_sql`)	Structured tool calls	SQL arrives as a tool argument. No markdown fences, no multi-statement guessing.
Single pass, no retry	LangChain agent (`create_agent`)	A failed query becomes feedback the model can act on instead of a failed request.
Schema + history injected into one prompt (`PromptService`)	`search_schema` tool + real chat messages	The agent fetches what it needs. `PromptService` was deleted once nothing referenced it.
`get_schema` (returned every table)	`search_schema` (ChromaDB semantic search)	The first change that actually reduces tokens per question rather than trading cost for resilience.
`llama-3.3-70b-versatile`	`openai/gpt-oss-120b`	Llama 3.3 70B deterministically emitted malformed tool-call syntax for certain phrasings — the identical broken output on every retry, at every temperature. Prompt rewording and shorter tool descriptions didn't help. GPT-OSS 120B, trained with function calling as a first-class capability, resolved it in every run afterward.

Deliberately kept: SQL validation, dynamic per-session connections, and Redis conversation memory. LangChain has no better answer for these — and for validation specifically, LangChain's own documentation says its SQL tooling isn't safe for production as-is, so this validator gates every query regardless of what generated it.

Quick Start

Prerequisites

Python 3.10+
uv for dependency management
PostgreSQL — one instance for the app's own data, plus whatever database you want to query
Redis — sessions and conversation memory
Groq API key — free tier is enough
Google OAuth credentials (optional, only for Google login) — create them here

Setup

1. Clone and install

git clone https://github.com/jaymin-dave-python-ak/Prompt2SQL.git
cd Prompt2SQL
uv sync

Windows + Python 3.10 note: chromadb pulls in onnxruntime, whose latest releases ship no Python 3.10 wheels for Windows. pyproject.toml pins onnxruntime<1.20 to keep the install working — worth knowing if uv sync ever fails to resolve after a dependency bump.

2. Configure the backend

cp .env.example .env

Fill in DATABASE_URL, REDIS_URL, GROQ_API_KEY, the JWT secrets, and your mail settings. See Configuration below for the full list.

3. Configure the frontend

cp .streamlit/secrets.toml.template .streamlit/secrets.toml

This points the UI at http://localhost:8000 by default — change it if your backend runs elsewhere.

4. Run migrations

uv run alembic upgrade head

5. Start the backend

uv run uvicorn app.main:app --reload

API at http://localhost:8000, Swagger docs at http://localhost:8000/docs.

6. Start the frontend (second terminal)

uv run streamlit run streamlit_app.py

UI at http://localhost:8501.

Using the Application

Step 1 — Sign in

Open http://localhost:8501. You have two options:

Email OTP — enter your email, hit Send OTP, then type the 6-digit code from your inbox and click Verify & Login. First-time users can optionally add a first and last name.
Google — click Open Google Login ↗. After Google's consent screen you're redirected straight back into the app, already logged in.

Step 2 — Connect a database

In the sidebar, paste the connection URL of the PostgreSQL database you want to query:

postgresql+asyncpg://user:password@localhost:5432/your_database

Click Connect. The backend runs a SELECT 1 to confirm the URL works, mints a session_id, reads the schema, and builds the vector index for it.

⏳ The very first connection after a fresh install is slow. ChromaDB downloads its local embedding model (all-MiniLM-L6-v2, ~79 MB) to ~/.cache/chroma/onnx_models/ on first use. It's cached afterward — every later connection is fast.

Step 3 — Ask a question

Type a question in plain English and click 🚀 Generate SQL & Run:

Show all employees in Engineering
Which product category made the most revenue last quarter?
How many orders are still unpaid?

You get three things back:

Generated SQL — the exact statement that ran
Explanation — a plain-English summary of the answer
Results — the returned rows, in a table

Step 4 — Ask follow-ups

Conversation memory is per-session, so you can build on the previous question:

Q1: Show all employees in Engineering
Q2: Only those earning above 100000
Q3: Sort them by hire date

The agent knows "those" and "them" refer to the earlier result set.

Step 5 — Ask about the schema itself

Schema introspection is explicitly allowed by the validator, so these work too:

What tables are in this database?
What columns does the orders table have?
How are customers and orders related?

These skip semantic search and query information_schema directly — they need a full listing, not a relevant subset.

Step 6 — Review history, or switch databases

The sidebar keeps a History panel with every question, its SQL, and its answer. Click ❌ Disconnect to drop the session and connect a different database — each one gets its own isolated session and its own schema index.

Configuration

All settings load from .env via pydantic-settings (app/core/config.py).

Required

Variable	Purpose
`DATABASE_URL`	Async SQLAlchemy URL for the app's own database
`GROQ_API_KEY`	Groq API key
`JWT_SECRET_ACCESS_KEY` / `JWT_SECRET_REFRESH_KEY`	JWT signing secrets
`GOOGLE_CLIENT_ID` / `GOOGLE_CLIENT_SECRET` / `GOOGLE_REDIRECT_URI`	Google OAuth
`MAIL_USERNAME` / `MAIL_PASSWORD` / `MAIL_FROM` / `MAIL_SERVER`	OTP email delivery

Optional (with defaults)

Variable	Default	Purpose
`REDIS_URL`	`redis://localhost:6379/0`	Sessions + conversation memory
`GROQ_MODEL`	`openai/gpt-oss-120b`	LLM powering the agent
`CHROMA_PERSIST_DIR`	`.chroma`	Where per-session schema embeddings live on disk
`STREAMLIT_URL`	`http://localhost:8501`	Where OAuth redirects back to
`JWT_ALGORITHM`	`HS256`	JWT algorithm
`JWT_ACCESS_TOKEN_EXPIRE_MINUTES`	`30`	Access token lifetime
`JWT_REFRESH_TOKEN_EXPIRE_DAYS`	`7`	Refresh token lifetime
`APP_NAME` / `DEBUG`	`Prompt2SQL` / `true`	App metadata

Project Structure

app/
├── main.py              # FastAPI entry point (lifespan, Redis init)
├── core/                # Settings (config.py) + async Redis pool
├── api/v1/              # Routers (prompt, database, auth) + dependencies/ DI wiring
├── services/            # Business logic — see the Layers table above
├── repositories/        # Raw data access (schema, execution, users)
├── schemas/             # Pydantic request/response models
├── models/              # SQLAlchemy ORM models
├── db/                  # Session management
└── utils/               # Auth + OTP helpers

streamlit_app.py         # Single-file Streamlit UI
alembic/                 # Migrations

The file worth reading first is app/services/text_to_sql_service.py — it's where the agent is assembled, run, and recovered from. app/services/sql_tools.py is the other half: the two tools the agent actually calls.

Development

uv sync                                    # install dependencies
uv run uvicorn app.main:app --reload       # backend, hot reload
uv run streamlit run streamlit_app.py      # frontend
uv run pytest                              # tests
uv run alembic revision -m "description"   # new migration
uv run alembic upgrade head                # apply migrations
uv run alembic downgrade -1                # roll back one

Testing status: pytest and pytest-asyncio are wired up as dev dependencies, but there is no test suite yet — uv run pytest reports "no tests ran." Verification so far has been manual, live, against a real Postgres + Redis + Groq stack. Automated coverage is the most obvious next contribution.

Limitations

Being upfront about what this does not do yet:

PostgreSQL only. No MySQL, SQLite, or SQL Server dialect support.
Single LLM provider. Groq only — there's no provider abstraction.
Keyword-based validation. SQLValidatorService matches keywords rather than parsing an AST, and it intentionally allows information_schema queries so schema questions work.
No human-in-the-loop approval. Validation is automated; nothing prompts you before a query runs.
No query cost guard. An unbounded scan on a huge table will simply run. There's no statement_timeout or EXPLAIN-based check yet.
Retrieval can miss. search_schema returns a relevant subset, which means it can occasionally omit a table the question actually needed.
No test suite. See Development.

Roadmap

Layer	Planned
Schema	Foreign-key extraction for better JOIN generation; schema caching in Redis
Validation	AST-based validation via `sqlglot` instead of keyword matching; query cost analysis
Execution	Execution metadata (row count, duration); `statement_timeout` and `EXPLAIN`-based cost guards
LLM	Multi-provider support (OpenAI, Gemini, Ollama); benchmarking on accuracy, latency, and cost
Retrieval	Stronger embedding model, column-level chunking, or hybrid keyword + semantic search
Memory	Persistent query history; context compression for long conversations
Databases	Multi-dialect support (MySQL, SQLite, SQL Server)
Observability	Metrics on request volume, latency, validation failures, and LLM usage
Testing	An actual test suite, plus an eval harness for generation accuracy

Status

MVP — functional end to end. Dynamic database connections, RAG-based schema retrieval, agentic SQL generation with retry-on-error, validation, execution, natural-language explanation, conversation memory, session isolation, and email + Google authentication are all implemented and working.

Built by Jaymin Dave.

Name		Name	Last commit message	Last commit date
.streamlit		.streamlit
alembic		alembic
app		app
.env.example		.env.example
.gitignore		.gitignore
.python-version		.python-version
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
alembic.ini		alembic.ini
pyproject.toml		pyproject.toml
streamlit_app.py		streamlit_app.py
uv.lock		uv.lock

Navigation Menu

Repository files navigation

Prompt2SQL

Table of Contents

Why this exists

Highlights

Architecture

Layers

Endpoints

How the SQL Agent Works

Design Decisions

Quick Start

Prerequisites

Setup

Using the Application

Step 1 — Sign in

Step 2 — Connect a database

Step 3 — Ask a question

Step 4 — Ask follow-ups

Step 5 — Ask about the schema itself

Step 6 — Review history, or switch databases

Configuration

Project Structure

Development

Limitations

Roadmap

Status

About

Releases

Packages

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

Prompt2SQL

Table of Contents

Why this exists

Highlights

Architecture

Layers

Endpoints

How the SQL Agent Works

Design Decisions

Quick Start

Prerequisites

Setup

Using the Application

Step 1 — Sign in

Step 2 — Connect a database

Step 3 — Ask a question

Step 4 — Ask follow-ups

Step 5 — Ask about the schema itself

Step 6 — Review history, or switch databases

Configuration

Project Structure

Development

Limitations

Roadmap

Status

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages