Build This Project · AI Portfolio
Natural-Language-to-SQL Analytics Assistant for Your AI Portfolio
A natural-language-to-SQL assistant lets someone type "total revenue by city" and get back a table, without knowing SQL. A large language model (LLM) writes the query; your code checks it, runs it safely and shows the result. It is a good portfolio project because it combines three things employers look for in applied AI roles: working with an LLM, working with real data, and building guardrails around a model that will sometimes be wrong.
This guide builds the whole thing in plain Python and SQLite: a sample database, the prompt, a safety layer, a retry loop, an evaluation script and a small Streamlit app. The scripts below were run and the outputs shown are their real outputs. The LLM call sits behind one function, so you can use whichever provider you have access to.
How the pieces fit
- Read the database schema (table and column definitions).
- Send the schema and the user's question to the LLM, asking for one SELECT query.
- Clean the reply (models often wrap SQL in markdown fences).
- Validate it: one statement, read-only, nothing dangerous.
- Run it on a read-only connection with a row limit.
- If it fails, send the error back to the model once and try again.
- Measure accuracy against a test set of questions with known answers.
Step 1: a small sales database
Three tables (customers, products, orders) are enough to need joins and aggregates. Save this as setup_db.py and run it once.
import sqlite3
con = sqlite3.connect("sales.db")
con.executescript("""
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL,
signup_date TEXT NOT NULL -- ISO date, e.g. 2026-01-15
);
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL -- in rupees
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
product_id INTEGER NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL,
order_date TEXT NOT NULL
);
INSERT INTO customers VALUES
(1,'Aarav','Delhi','2026-01-05'), (2,'Diya','Mumbai','2026-01-20'),
(3,'Kabir','Delhi','2026-02-11'), (4,'Meera','Pune','2026-03-02');
INSERT INTO products VALUES
(1,'Wireless Mouse','Electronics',799), (2,'Notebook Pack','Stationery',250),
(3,'USB-C Charger','Electronics',1499);
INSERT INTO orders VALUES
(1,1,1,2,'2026-03-01'), (2,2,3,1,'2026-03-03'), (3,1,2,5,'2026-03-10'),
(4,3,3,2,'2026-04-02'), (5,4,2,3,'2026-04-05'), (6,2,1,1,'2026-04-18');
""")
con.commit()
con.close()
print("sales.db created")If joins between these tables are unfamiliar, review SQL joins explained with real examples first. You need to be able to check the model's SQL yourself.
Step 2: the core module
Save this as nl2sql.py. It does not depend on any LLM library: answer() takes an ask_llm function that receives a prompt string and returns the model's text.
import re
import sqlite3
DB = "sales.db"
FORBIDDEN = re.compile(r"\b(insert|update|delete|drop|alter|create|replace|attach|pragma)\b", re.I)
def get_schema(db=DB):
con = sqlite3.connect(db)
rows = con.execute("SELECT sql FROM sqlite_master WHERE type = 'table'").fetchall()
con.close()
return "\n\n".join(r[0] for r in rows)
def build_prompt(question, schema, error=None):
prompt = (
"You write SQLite queries for a sales database.\n"
"Use only the tables and columns in this schema:\n\n"
f"{schema}\n\n"
"Rules: return ONE read-only SELECT statement and nothing else. "
"Prices are in rupees. Revenue = price * quantity.\n\n"
f"Question: {question}\nSQL:"
)
if error:
prompt += f"\n\nYour previous query failed with: {error}\nReturn a corrected query."
return prompt
def extract_sql(text):
"""Strip markdown fences and trailing semicolons from a model reply."""
m = re.search(r"```(?:sql)?\s*(.*?)```", text, re.S | re.I)
sql = m.group(1) if m else text
return sql.strip().rstrip(";").strip()
def validate(sql):
if ";" in sql:
raise ValueError("only one statement is allowed")
if not re.match(r"^\s*(select|with)\b", sql, re.I):
raise ValueError("only SELECT queries are allowed")
if FORBIDDEN.search(sql):
raise ValueError("query contains a forbidden keyword")
return sql
def run_query(sql, db=DB, max_rows=200):
con = sqlite3.connect(f"file:{db}?mode=ro", uri=True) # read-only connection
try:
cur = con.execute(f"SELECT * FROM ({sql}) LIMIT {max_rows}")
cols = [d[0] for d in cur.description]
return cols, cur.fetchall()
finally:
con.close()
def answer(question, ask_llm, retries=1):
schema = get_schema()
error = None
for _ in range(retries + 1):
sql = extract_sql(ask_llm(build_prompt(question, schema, error)))
try:
return sql, run_query(validate(sql))
except (ValueError, sqlite3.Error) as e:
error = str(e)
raise RuntimeError(f"could not answer: {error}")What each part does and why:
get_schema()reads theCREATE TABLEstatements fromsqlite_master. Giving the model the exact schema, including comments like "in rupees", is the biggest single accuracy improvement. For large databases, send only the tables relevant to the question; this is where retrieval, as in retrieval-augmented generation, comes in.build_prompt()states the rules and defines business terms ("Revenue = price * quantity"). Without that definition a model may sumpricealone. On a retry it includes the database error so the model can fix its mistake.validate()rejects multiple statements, anything that is notSELECTorWITH, and write keywords. It is deliberately strict: it would also reject a semicolon inside a text literal or a call to SQLite'sreplace()function.run_query()opens the database withmode=ro, so even a query that slipped past validation cannot change data. This is the real safety net; keyword checks alone are never enough. Wrapping the query in an outerSELECT ... LIMITstops a careless query from returning millions of rows.answer()gives the model one chance to correct itself using the error message, then gives up with a clear error instead of showing wrong output.
Step 3: test the pipeline offline
Before spending API credits, test the plumbing with a fake LLM that returns fixed replies. The second question's first reply is deliberately wrong (it forgets the join), and the third question asks to delete data.
from nl2sql import answer
# Stand-in for a real LLM call, so the pipeline can be tested offline.
CANNED = {
"Total revenue by city": [
"```sql\nSELECT c.city, SUM(p.price * o.quantity) AS revenue\n"
"FROM orders o JOIN customers c ON c.customer_id = o.customer_id\n"
"JOIN products p ON p.product_id = o.product_id\n"
"GROUP BY c.city ORDER BY revenue DESC;\n```"],
"Which product sold the most units?": [
"SELECT name, SUM(quantity) AS units FROM orders GROUP BY name", # wrong: no join
"SELECT p.name, SUM(o.quantity) AS units FROM orders o "
"JOIN products p ON p.product_id = o.product_id "
"GROUP BY p.name ORDER BY units DESC LIMIT 1"],
"Delete all orders": ["DELETE FROM orders", "DELETE FROM orders"],
}
def make_fake_llm(question):
replies = iter(CANNED[question])
return lambda prompt: next(replies)
for q in CANNED:
try:
sql, (cols, rows) = answer(q, make_fake_llm(q))
print(q, "->", cols, rows)
except RuntimeError as e:
print(q, "->", e)Output:
Total revenue by city -> ['city', 'revenue'] [('Delhi', 5846.0), ('Mumbai', 2298.0), ('Pune', 750.0)]
Which product sold the most units? -> ['name', 'units'] [('Notebook Pack', 8)]
Delete all orders -> could not answer: only SELECT queries are allowedCheck the first result by hand: Delhi has three orders, 2 × 799 + 5 × 250 + 2 × 1,499 = 5,846 rupees. For the second question, the first query failed with "no such column: name", the error went back into the prompt, and the corrected query joined products. The delete request was blocked by validate() on both attempts.
Step 4: connect a real model
Create a file llm.py with one function, ask_llm(prompt), that sends the prompt to your provider's chat API and returns the text of the reply. Every major provider's Python SDK can do this in a few lines. Copy the current call and model name from the provider's documentation, because both change over time. Keep the API key in an environment variable, never in the code or the GitHub repository, and use a temperature of 0 or close to it so the same question gives the same SQL.
Step 5: measure accuracy
A demo that works on three questions proves little. Interviewers will ask how accurate it is, and the honest answer comes from a test set: questions paired with a hand-written "gold" query. The script compares result values, not SQL text, because two different queries can be equally correct.
from nl2sql import answer, run_query
# Each test pairs a question with a hand-written "gold" query.
TESTS = [
("Total revenue by city",
"SELECT c.city, SUM(p.price * o.quantity) FROM orders o "
"JOIN customers c USING (customer_id) JOIN products p USING (product_id) GROUP BY c.city"),
("How many customers signed up in January 2026?",
"SELECT COUNT(*) FROM customers WHERE signup_date BETWEEN '2026-01-01' AND '2026-01-31'"),
]
def same_result(a, b):
# compare values only, ignoring row order and column names
return sorted(a[1]) == sorted(b[1])
def evaluate(ask_llm):
passed = 0
for question, gold in TESTS:
try:
_, got = answer(question, ask_llm)
ok = same_result(got, run_query(gold))
except RuntimeError:
ok = False
passed += ok
print("PASS" if ok else "FAIL", question)
print(f"{passed}/{len(TESTS)} passed")
if __name__ == "__main__":
fake = {
"Total revenue by city":
"SELECT c.city, SUM(p.price*o.quantity) AS revenue FROM orders o "
"JOIN customers c ON c.customer_id=o.customer_id "
"JOIN products p ON p.product_id=o.product_id GROUP BY c.city",
"How many customers signed up in January 2026?":
"SELECT COUNT(*) FROM customers WHERE signup_date LIKE '2026-02%'", # wrong month
}
evaluate(lambda prompt: fake[prompt.split("Question: ")[1].split("\n")[0]])Output with the fake model, which gets the month wrong on the second question:
PASS Total revenue by city
FAIL How many customers signed up in January 2026?
1/2 passedFor your portfolio, write 30 to 50 test questions covering filters, dates, joins, grouping and "top N" questions, run them against the real model, and report the pass rate together with the failure types you saw (wrong date logic, missing joins, invented columns). That table of failures impresses interviewers more than any single accuracy figure.
Step 6: a simple interface
Streamlit turns the module into a web app in a few lines. Install it with pip install streamlit and start it with streamlit run app.py.
import streamlit as st
from nl2sql import answer
from llm import ask_llm # your wrapper around the LLM provider you use
st.title("Ask the sales database")
question = st.text_input("Question", placeholder="Total revenue by city")
if question:
try:
sql, (cols, rows) = answer(question, ask_llm)
st.code(sql, language="sql")
st.dataframe([dict(zip(cols, r)) for r in rows])
except RuntimeError as e:
st.error(str(e))Always show the generated SQL next to the result, as this app does. Users and reviewers can then see exactly what was run.
Limitations to state honestly
- Ambiguity. "Best customers" could mean revenue, order count or recency. A production tool asks a clarifying question or documents its definitions.
- Large schemas. Real warehouses have hundreds of tables; you need schema selection, column descriptions and example queries in the prompt.
- Silent wrong answers. The worst failure is valid SQL that answers a different question. Only evaluation and showing the SQL protect against it.
- Data access. In a company, the database user must have only the permissions the person asking is allowed, so the assistant cannot reveal data they should not see.
- Privacy. If you add a step that summarises results with the model, the data goes to the provider; check that this is allowed.
Presenting it in a portfolio
Put the code on GitHub with a README covering the architecture, the safety measures (read-only connection, validation, row limit), the evaluation results with failure examples, and what you would add next (query caching, clarifying questions, PostgreSQL support). In an interview, be ready to explain why keyword filtering alone is unsafe and how you measured accuracy. For more ideas at a similar level, see 25 AI projects for students; for a simpler first SQLite project, try a to-do list app with Python and SQLite.
Frequently asked questions
Which LLM is best for natural-language-to-SQL?
Current general-purpose models from the major providers write simple SQL well. The differences show up on complex joins and ambiguous questions, so compare models on your own test set rather than on general claims.
Is it safe to let an LLM run SQL on a database?
Only with layers of protection: a read-only connection or a database user with minimal permissions, validation of the query, a row limit, and showing the SQL to the user. Never connect it to a database with write access.
Can I build this without paying for an API?
Yes. You can develop and test everything with a fake model as shown above, and open-source models running locally let you try the real step without per-call charges.
Should I use SQLite or PostgreSQL?
SQLite is ideal for learning because it needs no server. Moving to PostgreSQL later mainly means changing the connection code and telling the model which SQL dialect to write.