Text-to-SQL with guardrails

What you'll build

Step-by-step build

1. 1

   Create a read-only boundary

   Set up a Postgres role that can only SELECT, and connect with it. This is your real safety net: even if every other check failed, the database itself refuses writes. Set a statement_timeout so a runaway query cannot hang the service.

2. 2

   Introspect and serialize the schema

   Query the information schema to build a compact description of tables, columns, and types. Feed this to the model in the prompt so it references real columns instead of guessing. Keeping the schema fresh at request time means the app adapts when the database changes.

3. 3

   Generate the SQL

   Prompt Groq with the schema and the user's question, instructing it to produce a single read-only SELECT and nothing else, no explanation. Ask for the query in a fenced block so you can extract it cleanly. Lower the temperature so output is deterministic.

4. 4

   Enforce read-only by parsing

   Parse the generated SQL with sqlglot and reject anything that is not exactly one SELECT statement. Explicitly block INSERT, UPDATE, DELETE, DROP, ALTER, and multiple statements. Never rely on string matching alone; parse the actual statement tree.

5. 5

   Validate names against the schema

   Walk the parsed query and confirm every referenced table and column actually exists in your introspected schema. This catches hallucinated columns before execution and lets you return a helpful error instead of a database exception.

6. 6

   Execute safely

   Run the validated query on the read-only connection, wrapping it to enforce a LIMIT so a broad query cannot return millions of rows. Respect the statement timeout. Catch and format any database error rather than leaking a raw stack trace.

7. 7

   Return SQL alongside results

   Show the user the generated SQL, the rows, and the row count. Surfacing the query builds trust and lets a technical user verify the intent matched their question. Make it obvious when a query was blocked and why.

8. 8

   Test the attacks

   Deliberately try to break it: ask it to delete data, drop a table, query a table that does not exist, or run two statements. Confirm every one is refused with a clear message and nothing reaches the database. Passing your own red-team is the deliverable.

Done when

• ✓Plain-English questions return correct results with the generated SQL shown.
• ✓Every attempt to write, drop, or alter data is refused before execution, verified by your own tests.
• ✓A query referencing a nonexistent table or column is caught by validation with a clear message, not a raw DB error.
• ✓Results are always capped by a row limit and a statement timeout.
• ✓A different database schema works after only re-running introspection, with no code changes.

Ship it

Dockerize the FastAPI service with a seeded read-only demo database and deploy to AWS free tier or Hugging Face Spaces. Ship a live URL and a README that lists the guardrails and shows both a successful question-to-results flow and a blocked malicious query. Emphasize the read-only role and SQL parsing as the layered defense.

What it proves: You can wrap an LLM in a real safety envelope: schema grounding, read-only enforcement by parsing, name validation, and hard database-level limits, which is exactly the guardrail engineering production teams look for.

Paste this into Cursor, Claude, or ChatGPT and build it step by step.

You are my senior AI engineer pair. Help me build "Text-to-SQL with guardrails" step by step. The model turns a natural-language question into SQL, and guardrails are the safety layers that keep it read-only and valid: schema grounding (giving the model the real tables and columns), SQL parsing to enforce SELECT-only, name validation, and a read-only database role. Stack: FastAPI, Postgres with a read-only role and statement_timeout, Groq (Llama 3.3 70B) for generation, sqlglot for parsing and validation, Docker with a seeded demo DB.

Requirements:
1. Connect with a SELECT-only Postgres role and a statement timeout.
2. Introspect the schema each request and put it in the prompt.
3. Generate a single read-only SELECT, extract it from a fenced block.
4. Parse with sqlglot: reject anything that is not one SELECT, and validate every table/column exists.
5. Execute with a forced LIMIT, and return the SQL plus results, showing clearly when a query is blocked.

Work in this order: the read-only role, then schema introspection, then generation, then read-only enforcement by parsing, then name validation, then safe execution, then a red-team test of malicious inputs. Give me the commands and code for each step and STOP after each so I can test. Do not write the whole app at once.