SQLens — AI-Powered SQL Optimizer & Natural-Language Analytics

The problem: most developers can write SQL that works, but not SQL that's fast — and finding out why a query is slow normally requires a senior DBA reading EXPLAIN plans. Meanwhile, non-technical teammates can't query the database at all without filing a ticket.

What this does: paste your database schema (no data, no credentials — just structure), then:

1. Optimize — paste a slow SQL query and get back a rewritten, faster version with a plain-English explanation of what changed and why, index recommendations with ready-to-run CREATE INDEX statements, risk flags (e.g. "this rewrite may change NULL handling"), and before/after cost comparison.
2. Ask — type a question in English ("top 10 customers by revenue last quarter") and get the SQL, an explanation of how it was derived, and a step-by-step traceability log (intent → tables → joins → safety check).

Powered by Llama 3.3 70B (via Groq) with a safety layer that guarantees read-only SQL: every query — user-supplied and LLM-generated — passes through a validator that blocks DML/DDL, multi-statement injection, and stored-procedure calls before anything is accepted.

Privacy by design: only schema metadata (table/column names, types, indexes, row-count estimates) is ever sent to the LLM. Your data is never read — the app works from structure alone, and every database feature below is opt-in.

Bring your own database — three ways, pick your comfort level

	How	Credentials
1. Offline script (max privacy)	Run python tools/introspect.py "<db-url>" on your machine → upload the generated schema.json via Schema → Upload Schema	Never leave your computer
2. Connect Database (easiest)	Schema → Connect Database → paste a read-only connection string → Import	Used once server-side to read structure, then discarded — never stored
3. Live plans (most real)	Optimize → Live plans (optional) → paste the same connection string → optimize	Sent per request for EXPLAIN only — your query is planned, never executed

Step-by-step for each flow:

Flow 1 — offline script (works for PostgreSQL, MySQL, SQLite):

1. pip install sqlalchemy psycopg2-binary (or pymysql for MySQL)
2. python tools/introspect.py "postgresql://user:pass@host:5432/mydb" → writes schema.json
3. In SQLens: Schema → Upload Schema → Browse file → pick schema.json → Upload. (Postgres users can even skip Python: psql "<url>" -t -A -f tools/introspect_postgres.sql > schema.json)

Flow 2 — live connect (PostgreSQL, SQLite):

1. (Recommended) create a read-only user: CREATE USER sqlens_ro WITH PASSWORD '…'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO sqlens_ro;
2. Schema → Connect Database → paste postgresql://sqlens_ro:…@host:5432/mydb → Import Structure.
3. Your real tables appear in the schema browser; the connection is already discarded.

Flow 3 — real execution plans (PostgreSQL):

1. On the Optimize page click Live plans (optional) and paste the connection string.
2. Optimize as usual. The results banner switches to "Live plans — real EXPLAIN output from your database", and cost numbers come from the actual Postgres planner (plain EXPLAIN, never EXPLAIN ANALYZE — the query is planned, not run).
3. If the connection fails you still get results, clearly labeled as estimates.

---

Architecture

┌─────────────────────┐         ┌──────────────────────────────────────────┐
│  Next.js 14 (App    │  JSON   │  FastAPI (async)                         │
│  Router, React      │ ──────► │  ┌────────────┐  ┌──────────────────┐    │
│  Query, Tailwind,   │         │  │ API layer  │─►│ Service layer    │    │
│  custom UI kit)     │         │  │ (routers)  │  │ (use cases)      │    │
└─────────────────────┘         │  └────────────┘  └───┬──────────┬───┘    │
                                │                      │          │        │
                                │       ┌──────────────▼───┐  ┌───▼─────┐  │
                                │       │ Repositories     │  │ LLM port│  │
                                │       │ (SQLAlchemy 2.0  │  │ (Groq   │  │
                                │       │  async)          │  │ adapter)│  │
                                │       └──────┬───────────┘  └───┬─────┘  │
                                └──────────────┼──────────────────┼────────┘
                                        ┌──────▼──────┐    ┌──────▼───────┐
                                        │ PostgreSQL  │    │ Groq / Llama │
                                        │ (app state) │    │ 3.3 70B      │
                                        └─────────────┘    └──────────────┘

Clean architecture on the backend: routers are thin, business logic lives in services, all persistence is isolated in repositories, and the LLM is behind a protocol (port) so the provider can be swapped without touching business logic. Dependency wiring happens in one composition root.

Engineering highlights

• Event-loop safety — LLM calls (30–90 s) run off the async event loop via asyncio.to_thread, so one slow request never blocks others.
• Defense-in-depth SQL safety — regex + statement-prefix validation applied to both user input and LLM output; 25 dedicated tests cover bypass attempts (comments, multi-statement, case tricks).
• Structured LLM output — responses are contract-validated with Pydantic; malformed JSON is repaired (fence-stripping, brace extraction) or rejected with typed errors, with retry + exponential backoff on rate limits.
• Typed API contract — Pydantic models serialize camelCase to match the TypeScript types; consistent error envelope (code, message, detail) across every endpoint.
• One active schema everywhere — the connected or uploaded schema drives Optimize, Analytics, and the Schema browser alike, shown in a persistent header with its source (sample / uploaded / live import); survives tab switches and page reloads.
• Custom accessible UI kit — Button/Field/Modal/DataTable primitives with focus trapping, ARIA wiring generated automatically, and type-enforced accessibility (icon-only buttons won't compile without a label).
• Observability — structured logging (structlog) with request-ID propagation, optional OpenTelemetry tracing, health/readiness probes.
• 64 backend tests run against in-memory SQLite with the LLM mocked — no external services needed in CI.

Run it

# One command — Postgres + API + frontend (get a free key at console.groq.com)
GROQ_API_KEY=gsk_your_key docker compose up --build

• Frontend: http://localhost:4028 — click "Load sample schema" to try it instantly
• API docs (Swagger): http://localhost:8005/docs

Local development

# Backend
cd sql-optimizer-backend/sql-optimizer-backend
pip install -r requirements.txt
uvicorn app.main:app --port 8005 --reload    # needs DATABASE_URL + GROQ_API_KEY in .env

# Frontend
cd sql_optimizer_frontend/sql_optimizer_frontend
npm install && npm run dev                    # http://localhost:4028

# Tests
python -m pytest                              # 64 tests, no external services required

Stack

FastAPI · SQLAlchemy 2.0 (async) · PostgreSQL · Pydantic v2 · Groq (Llama 3.3 70B) · structlog · OpenTelemetry · Next.js 14 · TypeScript · TanStack Query · Tailwind CSS · Docker Compose

Honest limitations / roadmap

Without a live connection, execution plans are representative estimates — and the UI labels them as such. With Live plans connected, costs come from the real PostgreSQL planner via EXPLAIN. Roadmap: MySQL live plans, sqlglot AST-based validation replacing regex, Alembic migrations, rate limiting, and auth for multi-user deployments.

For a complete walkthrough of the architecture, every feature, and the reasoning behind each design decision, see PROJECT_GUIDE.md.