Building local SQL RAG layers using private databases

Ordinary RAG retrieves chunks of unstructured text; text-to-SQL RAG retrieves structured schema metadata instead — which tables and columns are semantically relevant to a natural-language question. This distinction matters practically: a private enterprise database can easily have hundreds of tables, far more than fit in any prompt, and research on the topic confirms that including irrelevant schema hurts SQL-generation accuracy rather than just wasting tokens.

Building a schema-aware SQL RAG layer

1. Embed schema metadata, not table contents. For each table and column, embed its name plus a short description (from documentation or comments) — this is what gets matched against the user's question, not the actual row data.

2. Retrieve the top-K relevant tables/columns for the question. An embedding similarity search over schema descriptions narrows a 200-table database down to the 3-5 tables actually relevant to "what were our top customers last quarter."

3. Pass only the retrieved schema to the SQL-generating LLM call. The model writes its query against a small, relevant schema slice — then that generated SQL still goes through parameterized execution and role-based access limits, never raw string execution.

# Schema-aware retrieval: embed table/column descriptions,
# retrieve only the relevant slice for a given question

schema_docs = [
    {"table": "orders", "description": "Customer purchase orders with totals and dates"},
    {"table": "employees", "description": "Staff records including department and hire date"},
    {"table": "customers", "description": "Customer contact info and account status"},
]

# Embed each schema_doc["description"] and store in a vector index.
# For "who are our top customers this quarter?", retrieval should
# surface "orders" and "customers" -- NOT "employees".

def build_schema_prompt(retrieved_tables: list) -> str:
    return "\n".join(f"Table {t['table']}: {t['description']}" for t in retrieved_tables)

This is genuinely a different technique from document RAG, even though both share the word "retrieval" — the index holds schema descriptions instead of text passages, and the retrieval question is "which tables matter here" instead of "which paragraphs answer this." Combine it with the parameterized-execution and access-control practices from earlier in this course, and you have a full pipeline: retrieve relevant schema, generate SQL against that narrowed context, execute it safely.