This content originally appeared on DEV Community and was authored by Jaime Lucena Pérez
Learn how to build a complete Retrieval-Augmented Generation (RAG) system that transforms natural language questions into SQL queries and returns human-friendly answers.
🚀 👉 View Full Project on GitHub 👈
⭐ Star the repo if you find this helpful!
🎯 Introduction
Have you ever wondered how AI assistants can answer questions about your data? How does ChatGPT understand "Show me all houses under $300,000" and translate it into a database query? The answer lies in Retrieval-Augmented Generation (RAG).
In this tutorial, we'll build a complete RAG application from scratch that:
- Converts natural language questions into SQL queries
- Executes queries on a real estate database
- Returns human-friendly answers
This is a hands-on project perfect for students learning generative AI, LangChain, and full-stack development.
🤔 What is RAG?
Retrieval-Augmented Generation (RAG) is a technique that combines:
- Retrieval: Fetching relevant information from a knowledge base (like a database)
- Generation: Using an LLM to generate natural language responses
Instead of relying solely on the LLM's training data, RAG allows the model to access up-to-date, specific information from your database.
Why RAG Matters
Traditional LLMs have limitations:
- ❌ They can't access real-time data
- ❌ They don't know about your specific database
- ❌ They might hallucinate information
RAG solves this by:
- ✅ Providing access to your actual data
- ✅ Ensuring answers are based on real information
- ✅ Allowing dynamic, context-aware responses
🏗️ Project Architecture
Our application follows a clean, modular architecture:
User Question → Streamlit Frontend → FastAPI Backend → RAG Pipeline → Database → Response
Components
- Streamlit Frontend: Interactive web UI for chatting
- FastAPI Backend: REST API that handles requests
- RAG Pipeline: LangChain chain that generates SQL and formats results
- SQLite Database: Real estate data (properties, agents, clients)
🚀 Getting Started
Prerequisites
- Python 3.11+
- OpenAI API key (Get one here)
- Basic understanding of Python
Installation
# Clone the repository
git clone https://github.com/JaimeLucena/rag-database-chat
cd rag-database-chat
# Install dependencies using uv (or pip)
uv sync
# Create .env file
echo "OPENAI_API_KEY=sk-your-key-here" > .env
echo "DATABASE_URL=sqlite:///./real_estate.db" >> .env
echo "API_BASE_URL=http://localhost:8000" >> .env
# Seed the database
uv run python -m app.database.seed
🧠 Understanding the RAG Pipeline
Let's dive into the core RAG implementation. The magic happens in app/rag/chain.py:
Step 1: SQL Generation
The LLM converts natural language to SQL:
sql_prompt = ChatPromptTemplate.from_messages([
("system", """You are an expert SQL query writer for a real estate database.
Database schema:
{schema}
When writing SQL queries:
1. Use proper SQL syntax for SQLite
2. Only query the tables and columns that exist
3. For text searches, use LIKE with % wildcards
4. Return only the SQL query, nothing else
5. Do not include markdown code blocks or backticks
Write a SQL query to answer the user's question about the real estate database."""),
("human", "{input}")
])
sql_chain = sql_prompt | llm | StrOutputParser()
Example transformation:
- Input:
"Show me houses with 3 bedrooms" - Generated SQL:
SELECT * FROM properties WHERE property_type = 'house' AND bedrooms = 3
Step 2: Query Execution
Execute the generated SQL on the database:
result = db.run(sql_query)
Step 3: Natural Language Formatting
Format the raw SQL results into a friendly answer:
format_prompt = ChatPromptTemplate.from_messages([
("system", """You are a helpful assistant that explains database query results in natural language.
Provide a clear, concise answer based on the SQL query result.
If the result is empty or shows no data, explain that no matching records were found."""),
("human", "Question: {question}\n\nSQL Query: {sql_query}\n\nQuery Result: {result}\n\nProvide a natural language answer:")
])
format_chain = format_prompt | llm | StrOutputParser()
answer = format_chain.invoke({
"question": question,
"sql_query": sql_query,
"result": result
})
Example transformation:
- SQL Result:
[(1, '123 Oak St', 'house', 3, 250000), ...] - Formatted Answer:
"I found 2 houses with 3 bedrooms: 123 Oak Street ($250,000) and 987 Birch Boulevard ($280,000)..."
💻 Building the Backend API
The FastAPI backend provides a clean REST interface:
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from app.rag.chain import query_database
app = FastAPI(title="Real Estate RAG API")
class QueryRequest(BaseModel):
question: str
class QueryResponse(BaseModel):
answer: str
@app.post("/api/query", response_model=QueryResponse)
async def query(request: QueryRequest):
"""Query the database using natural language."""
if not request.question or not request.question.strip():
raise HTTPException(status_code=400, detail="Question cannot be empty")
try:
answer = query_database(request.question)
return QueryResponse(answer=answer)
except Exception as e:
raise HTTPException(status_code=500, detail=f"Error: {str(e)}")
Key features:
- ✅ Type-safe request/response models with Pydantic
- ✅ Error handling for empty questions
- ✅ CORS middleware for frontend communication
🎨 Creating the Frontend
The Streamlit frontend provides an intuitive chat interface:
import streamlit as st
import httpx
def query_backend(question: str) -> str:
"""Query the backend API with a natural language question."""
try:
with httpx.Client(timeout=60.0) as client:
response = client.post(
f"{st.session_state.api_url}/api/query",
json={"question": question}
)
response.raise_for_status()
return response.json()["answer"]
except httpx.ConnectError:
return f"Error: Could not connect to the backend at {st.session_state.api_url}"
except Exception as e:
return f"Error: {str(e)}"
# Chat interface
if prompt := st.chat_input("Ask a question about the real estate database..."):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("assistant"):
with st.spinner("Thinking..."):
answer = query_backend(prompt)
st.markdown(answer)
st.session_state.messages.append({"role": "assistant", "content": answer})
Features:
- 💬 Real-time chat interface
- 🔌 Backend connection status indicator
- ⚙️ Configurable API URL
- 📝 Chat history persistence
🎓 Key Learning Concepts
1. Prompt Engineering
The quality of your prompts directly affects the output. Notice how we:
- Provide clear system instructions
- Include the database schema in context
- Specify output format requirements
- Handle edge cases (empty results, invalid queries)
2. LangChain Chains
LangChain's chain composition makes complex workflows readable:
chain = (
RunnablePassthrough()
| generate_and_execute # Step 1: Generate SQL
| format_prompt # Step 2: Format for LLM
| llm # Step 3: Generate answer
| StrOutputParser() # Step 4: Parse output
)
3. Error Handling
Robust error handling is crucial:
- Validate user input
- Catch SQL generation errors
- Handle database connection issues
- Provide helpful error messages
4. Full-Stack Architecture
Separating concerns:
- Frontend: User interaction and UI
- Backend: Business logic and API
- RAG Pipeline: Core AI functionality
- Database: Data persistence
🧪 Testing Your Application
Start the Backend
uv run uvicorn app.api.main:app --reload --host 0.0.0.0 --port 8000
Visit http://localhost:8000/docs for interactive API documentation.
Start the Frontend
uv run streamlit run app/streamlit_app.py
Open http://localhost:8501 in your browser.
Try These Questions
"What properties are available?""Show me houses with 3 bedrooms""What's the average price of properties?""Find properties under $300,000""Which agent has the most properties?"
🚀 Next Steps & Improvements
Ideas for Enhancement
- Streaming Responses: Use LangChain's streaming for real-time answers
- Query Validation: Add SQL injection protection
- Caching: Cache frequent queries for better performance
- Multi-turn Conversations: Maintain context across questions
- Query Explanation: Show the generated SQL to users
- Authentication: Add user authentication and rate limiting
Learning Resources
💡 Why This Project Matters
This project teaches you:
✅ RAG Fundamentals: How retrieval and generation work together
✅ LangChain Patterns: Building complex AI pipelines
✅ SQL Generation: Converting natural language to structured queries
✅ Full-Stack Development: Building complete applications
✅ API Design: Creating clean, maintainable APIs
✅ Error Handling: Building robust production systems
🎯 Conclusion
You've built a complete RAG application! This project demonstrates:
- How to combine LLMs with databases
- The power of LangChain for building AI pipelines
- Best practices for full-stack AI applications
- Real-world error handling and user experience
Key Takeaways:
- RAG bridges the gap between LLMs and your data
- Prompt engineering is crucial for reliable outputs
- Modular architecture makes systems maintainable
- Error handling improves user experience
What's Next?
- Experiment with different LLMs (Claude, Gemini, etc.)
- Add more complex queries and aggregations
- Implement query result visualization
- Deploy to production (AWS, GCP, Azure)
📚 Full Project Repository
Check out the complete project with documentation:
GitHub Repository
Star the repo if you found this helpful! ⭐
🙏 Acknowledgments
This project is designed for students learning generative AI. Feel free to fork, modify, and experiment!
Happy Learning! 🚀
This content originally appeared on DEV Community and was authored by Jaime Lucena Pérez
Jaime Lucena Pérez | Sciencx (2025-11-18T15:45:50+00:00) Building Your First RAG Application: From Natural Language to SQL Queries. Retrieved from https://www.scien.cx/2025/11/18/building-your-first-rag-application-from-natural-language-to-sql-queries/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.