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

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

1. Streamlit Frontend: Interactive web UI for chatting
2. FastAPI Backend: REST API that handles requests
3. RAG Pipeline: LangChain chain that generates SQL and formats results
4. 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

1. Streaming Responses: Use LangChain’s streaming for real-time answers
2. Query Validation: Add SQL injection protection
3. Caching: Cache frequent queries for better performance
4. Multi-turn Conversations: Maintain context across questions
5. Query Explanation: Show the generated SQL to users
6. Authentication: Add user authentication and rate limiting

Learning Resources

• LangChain Documentation
• FastAPI Tutorial
• Streamlit Documentation
• RAG Paper (Original)

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:

1. RAG bridges the gap between LLMs and your data
2. Prompt engineering is crucial for reliable outputs
3. Modular architecture makes systems maintainable
4. 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