An open-source RAG-based Text-to-SQL framework that improves accuracy by retrieving similar past queries and schema documentation as context before generating new SQL.
Vanna uses RAG to improve Text-to-SQL accuracy: it stores DDL (CREATE TABLE statements), documentation, and example (question, SQL) pairs in a vector store. When a new question arrives, it retrieves the most relevant DDL and examples and includes them in the prompt. The LLM generates SQL with this rich context. The more examples you train it on, the better it gets.
Train Vanna by adding DDL, documentation, and example pairs. " "This is a one-time setup + ongoing improvement process.
import vanna
from vanna.openai import OpenAI_Chat
from vanna.chromadb import ChromaDB_VectorStore
class MyVanna(ChromaDB_VectorStore, OpenAI_Chat):
def __init__(self, config=None):
ChromaDB_VectorStore.__init__(self, config=config)
OpenAI_Chat.__init__(self, config=config)
vn = MyVanna(config={"api_key": "your-key", "model": "gpt-4o"})
# Add schema knowledge
vn.train(ddl="CREATE TABLE orders (id INT, customer_id INT, amount FLOAT, date DATE);")
vn.train(documentation="The orders table contains all purchase records.")
# Add example pairs (most impactful for accuracy)
vn.train(question="How many orders were placed last month?",
sql="SELECT COUNT(*) FROM orders WHERE date >= DATE_TRUNC('month', NOW() - INTERVAL '1 month')")Once trained, a single call generates, executes, and explains the result.
# Connect to database
vn.connect_to_postgres(host="localhost", dbname="mydb", user="user", password="pass")
# Generate and run SQL
sql = vn.generate_sql("What are the top 10 customers by total spend?")
print(sql) # SELECT customer_id, SUM(amount) as total... ORDER BY total DESC LIMIT 10
df = vn.run_sql(sql)
# Or do it in one call
df, fig, _ = vn.ask("What are the top 10 customers by total spend?")
fig.show() # auto-generated chart!LLMs: OpenAI, Anthropic, Google Gemini, Mistral, Ollama (any local model). Vector stores: ChromaDB, Qdrant, Pinecone, Weaviate, OpenAI Embeddings. Databases: PostgreSQL, MySQL, SQLite, BigQuery, Snowflake, DuckDB, and more. Mix and match: use Ollama + ChromaDB for a fully local, private setup.
Self-hosted (open-source): full control, data stays on-premise, unlimited training examples. Vanna.ai cloud: hosted with a UI for training and querying, team collaboration features. For enterprise/regulated environments, self-hosted is the right choice.
Direct Text-to-SQL: simple, no training required, works out of the box for simple schemas. Vanna: requires upfront training, significantly more accurate for complex schemas and queries, improves over time as more examples are added. The break-even point is roughly 10+ tables or when direct approaches hit 70% accuracy — at that point, Vanna's RAG approach pulls noticeably ahead.
This section covers advanced patterns and implementation considerations for production environments. Understanding these concepts ensures robust and scalable deployments.
// Additional implementation details
result = example_function()
validate(result)
persist(result)| Criteria | Description | Consideration |
|---|---|---|
| Performance | Latency and throughput metrics | Measure against baselines |
| Scalability | Horizontal and vertical scaling | Plan for growth |
| Integration | Compatibility with ecosystem | Reduce friction |
| Cost | Operational and infrastructure costs | Total cost of ownership |
Understanding the fundamentals enables practitioners to make informed decisions about tool selection and implementation strategy. These foundational concepts shape how systems are architected and operated in production environments.
Production deployments require careful consideration of operational characteristics including resource consumption, latency profiles, and failure modes. Comprehensive testing against real-world scenarios helps validate assumptions and identify edge cases.
Community adoption and ecosystem maturity directly impact long-term viability. Active maintenance, thorough documentation, and responsive support channels significantly reduce implementation friction and maintenance burden.
Cost considerations extend beyond initial implementation to include ongoing operational expenses, training requirements, and opportunity costs of technology choices. A holistic cost analysis accounts for both direct and indirect expenses over the system lifetime.
Integration patterns and interoperability with existing infrastructure determine deployment success. Compatibility layers, standardized interfaces, and clear migration paths smooth the adoption process for teams with legacy systems.
Monitoring and observability are critical aspects of production systems. Establishing comprehensive metrics, logging, and alerting mechanisms enables rapid detection and resolution of issues before they impact end users.
Understanding the fundamentals enables practitioners to make informed decisions about tool selection and implementation strategy. These foundational concepts shape how systems are architected and operated in production environments. Key considerations include performance characteristics, resource utilization patterns, and integration requirements that vary significantly based on specific use cases and organizational constraints.
Production deployments require careful consideration of operational characteristics including resource consumption, latency profiles, failure modes, and recovery mechanisms. Comprehensive testing against real-world scenarios helps validate assumptions, identify edge cases, and stress-test systems under realistic conditions. Automation of testing pipelines ensures consistent quality and reduces manual effort during deployment cycles.
Community adoption and ecosystem maturity directly impact long-term viability and maintenance burden. Active development communities, thorough documentation, responsive support channels, and regular updates significantly reduce implementation friction. The availability of third-party integrations, plugins, and extensions extends functionality and accelerates time-to-value for organizations adopting these technologies.
Cost considerations extend beyond initial implementation to include ongoing operational expenses, training requirements, infrastructure costs, and opportunity costs of technology choices. A holistic cost analysis accounts for both direct expenses and indirect costs spanning acquisition, deployment, operational overhead, and eventual maintenance or replacement. Return on investment calculations must consider these multifaceted dimensions.
Integration patterns and interoperability with existing infrastructure determine deployment success and organizational impact. Compatibility layers, standardized interfaces, clear migration paths, and backward compatibility mechanisms smooth adoption for teams managing legacy systems. Understanding integration points and potential bottlenecks helps avoid common pitfalls and ensures smooth operational transitions.
Monitoring and observability are critical aspects of modern production systems and operational excellence. Establishing comprehensive metrics, structured logging, distributed tracing, and alerting mechanisms enables rapid detection and resolution of issues before they impact end users. Instrumentation at multiple layers provides visibility into system behavior and helps drive continuous improvements.
Security considerations span multiple dimensions including authentication, authorization, encryption, data protection, and compliance with regulatory frameworks. Implementing defense-in-depth strategies with multiple layers of security controls reduces risk exposure. Regular security audits, penetration testing, and vulnerability assessments help identify and remediate weaknesses proactively before they become exploitable.
Scalability architecture decisions influence system behavior under load and determine capacity for future growth. Horizontal and vertical scaling approaches present different tradeoffs in terms of complexity, cost, and operational overhead. Designing systems with scalability in mind from inception prevents costly refactoring and ensures smooth expansion as demand increases.
Understanding the fundamentals enables practitioners to make informed decisions about tool selection and implementation strategy. These foundational concepts shape how systems are architected and operated in production environments. Key considerations include performance characteristics, resource utilization patterns, and integration requirements that vary significantly based on specific use cases and organizational constraints.
Production deployments require careful consideration of operational characteristics including resource consumption, latency profiles, failure modes, and recovery mechanisms. Comprehensive testing against real-world scenarios helps validate assumptions, identify edge cases, and stress-test systems under realistic conditions. Automation of testing pipelines ensures consistent quality and reduces manual effort during deployment cycles.
Community adoption and ecosystem maturity directly impact long-term viability and maintenance burden. Active development communities, thorough documentation, responsive support channels, and regular updates significantly reduce implementation friction. The availability of third-party integrations, plugins, and extensions extends functionality and accelerates time-to-value for organizations adopting these technologies.
Cost considerations extend beyond initial implementation to include ongoing operational expenses, training requirements, infrastructure costs, and opportunity costs of technology choices. A holistic cost analysis accounts for both direct expenses and indirect costs spanning acquisition, deployment, operational overhead, and eventual maintenance or replacement. Return on investment calculations must consider these multifaceted dimensions.
Integration patterns and interoperability with existing infrastructure determine deployment success and organizational impact. Compatibility layers, standardized interfaces, clear migration paths, and backward compatibility mechanisms smooth adoption for teams managing legacy systems. Understanding integration points and potential bottlenecks helps avoid common pitfalls and ensures smooth operational transitions.
Monitoring and observability are critical aspects of modern production systems and operational excellence. Establishing comprehensive metrics, structured logging, distributed tracing, and alerting mechanisms enables rapid detection and resolution of issues before they impact end users. Instrumentation at multiple layers provides visibility into system behavior and helps drive continuous improvements.
Security considerations span multiple dimensions including authentication, authorization, encryption, data protection, and compliance with regulatory frameworks. Implementing defense-in-depth strategies with multiple layers of security controls reduces risk exposure. Regular security audits, penetration testing, and vulnerability assessments help identify and remediate weaknesses proactively before they become exploitable.
Scalability architecture decisions influence system behavior under load and determine capacity for future growth. Horizontal and vertical scaling approaches present different tradeoffs in terms of complexity, cost, and operational overhead. Designing systems with scalability in mind from inception prevents costly refactoring and ensures smooth expansion as demand increases.