Talk2PG — Natural Language to PostgreSQL (NL2SQL)

A web application that lets you query a PostgreSQL database using natural language. Ask questions in plain English, and the app generates the corresponding SQL, runs it safely (read-only), and returns both a human-friendly answer and the generated PostgreSQL query.

---

Features

• Natural language to SQL — Type a question (e.g. "List all products and their current stock quantities, sorted from highest to lowest") and get a PostgreSQL SELECT query.
• AI Answer — Results are summarized in clear, conversational language (no SQL jargon in the answer).
• Generated SQL — View the exact PostgreSQL query in a collapsible section for transparency and learning.
• Read-only execution — Only SELECT / WITH queries are allowed; writes and schema changes are blocked.
• SQLite → PostgreSQL normalization — Generated SQL is normalized for PostgreSQL (date functions, division, casts, etc.).
• Schema-aware generation — The model uses your database schema (and optional example rows) to produce accurate queries.
• Web UI (Streamlit) — Chat-style interface; each new question replaces the previous result.
• CLI mode — Optional main.py for running the same pipeline from the command line.

---

Prerequisites

• Python — 3.10+ (tested with 3.13).
• PostgreSQL — A running PostgreSQL instance with a database and schema you want to query (connection via .env).
• CUDA-capable GPU — Recommended for running the Text2SQL and summarization models locally:
  • Text2SQL: Snowflake/Arctic-Text2SQL-R1-7B
  • Summarization: meta-llama/Llama-3.2-3B-Instruct
• Hugging Face — Models are loaded from Hugging Face; ensure you have enough disk space and, if required, HF token/auth for gated models.
• Environment variables — Database connection details (see Project setup).

---

Project Setup

1. Clone and enter the project

cd /path/to/nl2sql-api

2. (Optional) Restore the included demo database

This repo includes a PostgreSQL dump so you can restore the exact same schema + data used in the project:

• Dump file: assets/postgres_db_data.dump (custom format)

Create an empty database first (example: talk2pg), then restore:

createdb -U postgres talk2pg
pg_restore -U postgres -d talk2pg --no-owner --no-privileges assets/postgres_db_data.dump

If you want to keep privileges for the read-only user used by this project (analyst_user), create the role before restoring:

psql -U postgres -d talk2pg -c "CREATE ROLE analyst_user LOGIN;"
pg_restore -U postgres -d talk2pg assets/postgres_db_data.dump

If your restore tool shows errors like role \"analyst_user\" does not exist, the data can still load successfully — those messages are about missing roles/GRANTs. Using --no-privileges --no-owner avoids that entirely.

If you see unrecognized configuration parameter \"transaction_timeout\", your pg_restore/PostgreSQL version is older than the dump expects. Fix by restoring with a matching (newer) pg_restore, or by restoring while ignoring that setting in your tooling (many GUI tools expose an option to disable “set session parameters”).

3. Create and activate a virtual environment

python -m venv venv
source venv/bin/activate   # Linux/macOS
# or: venv\Scripts\activate   # Windows

4. Install dependencies

pip install -r requirements.txt

Key dependencies include: streamlit, transformers, torch, sqlalchemy, psycopg, python-dotenv, sqlglot, and others (see requirements.txt).

5. Configure environment

Create a .env file in the project root with your PostgreSQL connection details:

DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_HOST=your_host
DB_PORT=your_port
DB_NAME=your_database_name

Alternatively, you can set a single DATABASE_URL (e.g. postgresql+psycopg://user:pass@host:port/dbname). The app uses database_schema_detail.get_database_url_from_env() to read these values.

6. Run the application

Web app (Streamlit):

streamlit run app.py

Then open the URL shown in the terminal (usually http://localhost:8501).

CLI (optional):

python main.py

You will be prompted to type questions; type q to quit. The CLI uses the same pipeline: prompt → SQL generation → normalization → execution → summarization.

---

Usage

1. Web: In the Streamlit app, type your question in the input at the bottom (e.g. "What would you like to know?").
2. Wait while the app loads the schema, generates SQL, runs it, and summarizes the result.
3. View the AI Answer and expand Show Generated SQL (PostgreSQL) to see the query.
4. Each new question replaces the previous result.

---

Result Overview

The app returns:

• Question — Your natural language input.
• AI Answer — A short, natural-language summary of the data (e.g. a numbered list of products and stock quantities).
• Generated SQL — The PostgreSQL query used to produce that answer (in a collapsible block).

The screenshot at the top of this README shows an example: a question about products and stock quantities, the AI answer as a sorted list, and the underlying SELECT ... JOIN ... ORDER BY SQL.

---

Project Structure

File / folder	Purpose
app.py	Streamlit web UI: chat input, model loading, SQL generation, execution, summarization, result display.
main.py	CLI entry point; same pipeline as the app in an interactive loop.
prompts.py	Prompt templates for SQL generation and for result summarization (Llama 3.2 style).
database_schema_detail.py	DB URL from env, schema introspection, optional example rows, caching.
query_executor.py	Read-only SQL validation and execution against PostgreSQL.
sqlite_to_postgresql.py	Normalization and safety fixes (SQLite-style → PostgreSQL).
.env	Database credentials (not committed; use .env.example as a template if you add one).
requirements.txt	Python dependencies.

---

Technology Stack

• UI: Streamlit
• Models: Hugging Face Transformers (Arctic-Text2SQL-R1-7B, Llama-3.2-3B-Instruct)
• Database: PostgreSQL via SQLAlchemy + psycopg
• SQL: sqlglot, custom normalization in sqlite_to_postgresql.py
• Config: python-dotenv, environment variables

---

Notes

• First run will download the Hugging Face models; ensure sufficient disk space and network access.
• For large schemas, schema loading may be cached (e.g. get_schema() in app.py).
• The executor only allows read-only queries; any attempt to run non-SELECT/WITH SQL is rejected with an error message.