Spring AI Text-to-SQL: Turning Questions into SQL with LLMs

Explains Text-to-SQL as a backend pipeline from question to SQL execution. Focuses on responsibilities without writing any code.

Designs the REST API for Text-to-SQL requests and responses. Implements the controller and verifies it using Postman.

Introduces internal services and their responsibilities. Refactors the controller to delegate logic cleanly to the service layer.

Integrates Spring AI to generate SQL from user questions. Builds a minimal prompt and returns generated SQL via the API.

Executes AI-generated SQL using JdbcTemplate. Demonstrates real query results with joins and aggregations.

Demonstrates unsafe and incorrect SQL generation scenarios. Explains why LLMs cannot be trusted without backend validation.

Recaps the complete Text-to-SQL pipeline built so far. Explicitly lists missing safety and validation concerns.

Demonstrates common failures caused by missing schema context. Shows that errors come from ambiguity, not randomness.

Introduces a backend-controlled schema representation. Programmatically injects tables and columns into prompts.

Enhances prompts with semantic descriptions. Reduces ambiguity and improves SQL generation accuracy.

Adds domain rules as soft constraints in prompts. Demonstrates how rules guide but do not enforce behavior.

Teaches structured prompt design techniques. Covers common anti-patterns and best practices.

Explains why static, hardcoded schemas break down in real systems. Clarifies the scope of dynamic schema discovery versus validation and safety.

Implements runtime schema discovery using PostgreSQL metadata. Integrates the dynamic schema into the existing prompt pipeline.

Loads table and column descriptions using PostgreSQL comments. Separates structural schema data from semantic meaning.

Caches schema at application startup for predictable behavior. Discusses schema refresh strategies at a conceptual level.

Verifies dynamic schema behavior with multi-domain queries. Summarizes capabilities gained and prepares for SQL safety and validation.

Explains why prompts and schema awareness cannot guarantee safe SQL.

Shows where validation fits between SQL generation and execution.

Implements an AST-based SQL validator using a real SQL parser.

Enforces SELECT-only rules and blocks all DML and DDL statements.

Validates tables and columns against the discovered schema.

Demonstrates failures caused by hallucinated tables, columns, and aliases.

Detects missing LIMIT clauses and enforces maximum row limits.

Wires the validator before execution and blocks unbounded queries.

Summarizes the final validated execution flow.

Clearly states the safety guarantees enforced by the backend.

Uses a prebuilt HTML file to call the existing API using JavaScript fetch.

Displays generated SQL and query results as a table in the browser.

Shows how backend validation errors appear clearly in the interface.

Demonstrates that all safety logic lives in the backend, not the UI.