Safe Text-to-SQL

A security-focused natural-language analytics workbench for a local music-store database. It turns a question into SQL, validates the SQL with an AST policy, executes it through a read-only SQLite connection, and produces a grounded answer.

The application runs entirely offline with a deterministic fake provider, or uses Google Gemini for real language-model generation. No Azure service is required.

Live demo

The screenshot is the deployed application answering a live Gemini question: generated SQL, AST validation, read-only execution, and a grounded answer taken from the returned rows.

Why this project exists

Text-to-SQL can make analytics accessible to non-SQL users, but executing model-generated code creates a serious trust problem. This project treats the model as untrusted: generated SQL must cross an application policy and a database-enforced read-only boundary before it can run.

Architecture

flowchart LR
    Q["Natural-language question"] --> V["Input validation"]
    V --> S["Schema introspection"]
    S --> E["Deterministic example selection"]
    E --> L["Gemini or fake provider"]
    L --> N["SQL normalization"]
    N --> G{"SQLGlot guard"}
    G -->|invalid| R["One bounded repair"]
    R --> G
    G -->|valid| D["Read-only SQLite adapter"]
    D --> A["Grounded answer generation"]
    A --> U["Streamlit results and trust trace"]

Loading

The provider, database, validation, and UI layers are independently testable. See the architecture guide for the component boundaries.

Security model

• SQL is parsed as an AST; keyword matching is not the security boundary.
• Only one read-only SELECT or WITH ... SELECT statement is accepted.
• Referenced tables must be present in the introspected application schema.
• System catalogs, write operations, locking clauses, and data-modifying CTEs are rejected.
• A row limit is injected or clamped.
• SQLite is opened in read-only and query-only modes with a fail-closed authorizer.
• Credentials are runtime-only, masked in configuration representations, and excluded from Git and Docker build contexts.
• Logs use request IDs and safe categories without full prompts, rows, local paths, or provider secrets.

These controls reduce risk; they do not turn arbitrary LLM output into trusted code. Read SECURITY.md and docs/security.md before adapting the project to sensitive data.

Providers

fake is the default. It is deterministic, credential-free, and used by tests, CI, screenshots, and the reproducible functional evaluation.

gemini uses the official Google Gen AI Python SDK behind the same asynchronous provider protocol. It supports structured SQL generation, a bounded repair attempt, grounded answer generation, request timeouts, bounded retries, and sanitized failures.

Quick start: offline fake mode

Prerequisites: Python 3.11 or 3.12.

python -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.lock
streamlit run app.py

On Windows PowerShell, activate with .venv\Scripts\Activate.ps1. The app opens at http://localhost:8501.

The synthetic database is generated on first start, so no setup command is required. It is created only when missing, verified against the expected schema, and never overwritten once valid. Run python scripts/initialize_demo_db.py to create it ahead of time, or add --force to rebuild it.

Try:

• How many customers are in the USA?
• Find the top 5 customers by total spending
• Which country generated the highest revenue?
• Count how many tracks exist in each genre

Gemini setup

Copy .env.example to .env, then set:

LLM_PROVIDER=gemini
GEMINI_API_KEY=your_gemini_api_key_here
GEMINI_MODEL=gemini-3.6-flash

Google retires model identifiers for new API projects, and a retired model returns a 404 rather than degrading gracefully. If Gemini mode reports that the model is unavailable, list the models your key can call and set GEMINI_MODEL to a current one:

python scripts/list_gemini_models.py

Never commit .env. If Gemini mode is selected without a key, the UI returns a safe configuration message and does not silently switch providers. Questions, generated SQL, and bounded result context are sent to the selected provider; do not use Gemini mode for data that your provider policy does not permit.

Tests and quality gates

ruff check .
ruff format --check .
mypy src tests scripts app.py
pytest
python scripts/run_evaluation.py
python scripts/verify_release.py

All automated tests and the default evaluation are offline. Gemini tests mock the SDK and never require a key or network access.

Evaluation

evaluation/cases.json covers filters, aggregations, joins, grouping, ordering, nested queries, CTEs, window functions, unsupported questions, prompt injection, and destructive requests. Run:

python scripts/run_evaluation.py

The fake-provider result is a deterministic functional verification—not an LLM accuracy score. Output is written to the ignored evaluation/results/ directory. See docs/evaluation.md.

Docker

docker compose up --build

The non-root container initializes the synthetic database during the image build and starts in fake mode. Gemini can be enabled by injecting environment variables at runtime; the image never contains .env. See docs/docker.md.

Deployment

The application is deployed on Streamlit Community Cloud at the live demo and runs on any container platform that supports a configurable PORT. The demo database is generated at startup, and Gemini credentials are supplied through Streamlit Secrets. See docs/deployment.md.

Data provenance

The demo database is generated locally by scripts/initialize_demo_db.py. Its records are fictional and authored for this portfolio project. It follows a compact music-store analytical structure; it does not redistribute rows from the Chinook sample database. The 25 reviewed question/SQL examples are stored in data/examples.json.

Limitations

• The lexical example selector is deterministic, not semantic vector retrieval.
• SQLite is the only implemented database adapter.
• Schema changes require regenerating or reviewing examples.
• The SQL policy is intentionally conservative and may reject valid advanced SQL.
• LLM output can still be wrong; users must review generated queries and results.
• The evaluation verifies known functional cases and does not establish production accuracy, latency, or safety guarantees.

Roadmap

• Add an isolated PostgreSQL adapter with a dedicated read-only role.
• Add schema-aware evaluation for live provider runs.
• Add optional query-cost controls and audit persistence.
• Evaluate multilingual questions, including Arabic, with a reviewed benchmark.

Project documentation

• Local development
• Architecture
• Security controls
• Evaluation methodology
• Docker
• Deployment
• Contributing

License and author

Code and the authored synthetic demo data are available under the MIT License.

Built by Abdallah Hosni.