Jaime Lucena Pérez

Posted on Nov 18

Building Your First RAG Application: From Natural Language to SQL Queries

#ai #programming #langchain #python

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

If you found this article informative and valuable, I’d greatly appreciate your support:

Give it a few reactions on dev.to (❤️ 🦄) to help others discover this content. Every reaction boosts the post in the algorithm.
Star the repo ⭐ and follow me on GitHub to stay updated with future projects.

🙏 Acknowledgments

This project is designed for students learning generative AI. Feel free to fork, modify, and experiment!

Happy Learning! 🚀

DEV Community