Introduction
Writing SQL queries is a fundamental skill for developers, data analysts, and database administrators. However, not everyone knows SQL syntax, and even experienced developers spend time writing repetitive queries.
Recent advances in Generative AI and Large Language Models (LLMs) make it possible to convert plain English into SQL automatically. Instead of writing:
SELECT name, salary
FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;
A user can simply ask:
"Show me all IT employees ordered by salary from highest to lowest."
The AI translates the request into SQL.
In this article, we'll build a Text-to-SQL application using:
Python
Streamlit
Hugging Face Transformers
SQLite
SQLAlchemy
We'll also discuss real-world applications, limitations, and best practices.
Why Text-to-SQL Matters
Organizations generate massive amounts of structured data stored in relational databases.
Business users often need answers without knowing SQL.
Examples include:
Sales managers checking monthly revenue
HR departments analyzing employee records
Finance teams generating reports
Customer support searching order history
AI enables these users to retrieve information using natural language.
Project Architecture
User Question
│
▼
Hugging Face Model
│
▼
Generated SQL Query
│
▼
SQLite Database
│
▼
Results
│
▼
Streamlit Dashboard
The workflow is simple:
User enters a question.
AI generates SQL.
SQL executes against SQLite.
Results appear instantly.
Technologies Used
Technology Purpose
Python Backend
Streamlit Web Interface
Hugging Face LLM for Text-to-SQL
SQLAlchemy Database connection
SQLite Sample database
Installing Dependencies
pip install streamlit
pip install transformers
pip install torch
pip install sqlalchemy
pip install pandas
Creating a Sample Database
from sqlalchemy import create_engine
engine = create_engine("sqlite:///company.db")
engine.execute("""
CREATE TABLE employees(
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT,
salary INTEGER
)
""")
Insert sample data:
engine.execute("""
INSERT INTO employees(name, department, salary)
VALUES
('Alice','IT',7500),
('Bob','Sales',5200),
('Carol','IT',8900)
""")
a Hugging Face Model
One popular Text-to-SQL model is based on T5.
from transformers import AutoTokenizer, AutoModelForSeq2SeqLM
model_name = "tscholak/1wnr382e"
tokenizer = AutoTokenizer.from_pretrained(model_name)
model = AutoModelForSeq2SeqLM.from_pretrained(model_name)
Converting Natural Language into SQL
question = "Show employees working in IT"
inputs = tokenizer(question, return_tensors="pt")
outputs = model.generate(**inputs)
sql = tokenizer.decode(outputs[0], skip_special_tokens=True)
print(sql)
Possible output:
SELECT *
FROM employees
WHERE department='IT';
Executing the SQL
import pandas as pd
result = pd.read_sql(sql, engine)
print(result)
Output:
id name department salary
1 Alice IT 7500
3 Carol IT 8900
Building the Streamlit Interface
import streamlit as st
question = st.text_input("Ask your database")
if st.button("Generate SQL"):
sql = generate_sql(question)
st.code(sql, language="sql")
result = pd.read_sql(sql, engine)
st.dataframe(result)
Now users only need to type questions such as:
Show all employees
List employees in Sales
Average salary by department
Highest paid employee
Real-World Applications
Business Intelligence
Employees can generate reports without learning SQL.
Healthcare
Doctors can retrieve patient records using natural language.
Banking
Analysts can summarize transactions through conversational queries.
E-commerce
Managers can ask:
"Which products sold the most last month?"
instead of writing complex SQL.
Challenges
Although Text-to-SQL is impressive, it has limitations.
Database Schema Understanding
The AI performs much better when it understands the database schema.
SQL Validation
Generated SQL should always be validated before execution.
Never execute AI-generated SQL directly in production.
Security
Restrict permissions to read-only whenever possible.
Avoid allowing AI to execute:
DELETE
UPDATE
DROP
ALTER
without human approval.
Best Practices
✅ Provide the database schema as context.
✅ Validate SQL syntax.
✅ Limit user permissions.
✅ Log generated queries.
✅ Review queries before execution.
Public GitHub Example
A complete open-source implementation can be found in projects like:
https://github.com/vanna-ai/vanna
https://github.com/defog-ai/sqlcoder
https://github.com/langchain-ai/langchain (SQL agents)
These repositories demonstrate production-ready approaches for natural language querying over SQL databases.
Future Improvements
Some ideas to extend this project include:
PostgreSQL support
MySQL support
SQL Server support
Query explanation
Chart generation
Conversational memory
Retrieval-Augmented Generation (RAG)
Integration with local LLMs using Ollama
Conclusion
Text-to-SQL is transforming how users interact with relational databases. By combining Hugging Face, Streamlit, and Python, developers can build applications that allow anyone to query databases using natural language.
While these systems require careful validation and security controls, they significantly lower the barrier to accessing structured data and improve productivity for both technical and non-technical users.
As open-source AI models continue to improve, conversational database interfaces will become an increasingly common feature in modern data applications.
References
Hugging Face Transformers: https://huggingface.co/docs/transformers
Streamlit Documentation: https://streamlit.io
SQLAlchemy Documentation: https://docs.sqlalchemy.org
Vanna AI: https://github.com/vanna-ai/vanna
SQLCoder: https://github.com/defog-ai/sqlcoder