Photo by the AuthorThe obvious Getting started
Data has become the most important resource for any successful business, as it provides valuable insight for informed decision making. Given the importance of data, many companies are still building systems to store and analyze it. However, there are many times when it is difficult to find and analyze the necessary data, especially with the increasing complexity of the data system.
With the introduction of Adventative AI, data work has become much easier, because now we can use simple natural language to find the most accurate information that follows the input you provide. It also applies to data processing and analysis with SQL, where we can request query optimization.
In this article, we will develop a simple API application that translates natural language into SQL queries that our database understands. We will use three main tools: Open it, Fastapiagain Sqlite.
Here is the plan.
The obvious Text-to-SQL application development
First, we will prepare everything needed for our project. All you need is to provide an Opelai API key, which we will use to access the Gentirative model. To take the application, we will use An artistwhich you can get from a local startup using Docker Desktop.
Some components, such as SQLITE, will be automatically available when you install Python, and will be installed immediately.
For the overall project structure, we will use the following:
text_to_sql_app/
├── app/
│ ├── __init__.py
│ ├── database.py
│ ├── openai_utils.py
│ └── main.py
├── demo.db
├── init_db.sql
├── requirements.txt
├── Dockerfile
├── docker-compose.yml
├── .envCreate a structure like the one above, or you can use the following endpoint to make things easier. We will go through each file to get an understanding of how to develop the application.
Let's start with the weight .env The file with the Opelai API key that we got earlier. You can do that with the following code:
OPENAI_API_KEY=YOUR-API-KEYAfter that, go to requirements.txt to fill in the necessary libraries that we will use
fastapi
uvicorn
sqlalchemy
openai
pydantic
python-dotenvNext, we move on to __init__.py File, and we'll put the following code inside:
from pathlib import Path
from dotenv import load_dotenv
load_dotenv(dotenv_path=Path(__file__).resolve().parent.parent / ".env", override=False)The code above ensures that the environment contains all the necessary buttons we need.
After that, we will develop the Python code in database.py The SQLite Database connection file we'll create later (calleddemo.db) and provide a way to use SQL queries.
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
ENGINE = create_engine("sqlite:///demo.db", future=True, echo=False)
def run_query(sql: str) -> list[dict]:
with Session(ENGINE) as session:
rows = session.execute(text(sql)).mappings().all()
return [dict(r) for r in rows]After that, we will prepare openai_utils.py A file that will accept the data schema and input queries. The output will be a json containing the SQL query (guarded to prevent any write operations).
import os
import json
from openai import OpenAI
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
_SYSTEM_PROMPT = """
You convert natural-language questions into read-only SQLite SQL.
Never output INSERT / UPDATE / DELETE.
Return JSON: { "sql": "..." }.
"""
def text_to_sql(question: str, schema: str) -> str:
response = client.chat.completions.create(
model="gpt-4o-mini",
temperature=0.1,
response_format={"type": "json_object"},
messages=[
{"role": "system", "content": _SYSTEM_PROMPT},
{"role": "user",
"content": f"schema:n{schema}nnquestion: {question}"}
]
)
payload = json.loads(response.choices[0].message.content)
return payload["sql"]With some code and the right connections, we will configure the application using Fastapi. The application will accept natural language queries and data schema, Guver they sql SELECT Queries, run them through a SQLite database, and return the results as Json. The app will be an API that we can access through the CLI.
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import inspect
from .database import ENGINE, run_query
from .openai_utils import text_to_sql
app = FastAPI(title="Text-to-SQL Demo")
class NLRequest(BaseModel):
question: str
@app.on_event("startup")
def capture_schema() -> None:
insp = inspect(ENGINE)
global SCHEMA_STR
SCHEMA_STR = "n".join(
f"CREATE TABLE {t} ({', '.join(c['name'] for c in insp.get_columns
for t in insp.get_table_names()
)
@app.post("/query")
def query(req: NLRequest):
try:
sql = text_to_sql(req.question, SCHEMA_STR)
if not sql.lstrip().lower().startswith("select"):
raise ValueError("Only SELECT statements are allowed")
return {"sql": sql, "result": run_query(sql)}
except Exception as e:
raise HTTPException(status_code=400, detail=str(e))That's all we need for a big request. The next thing we will configure the database. Use the database below to init_db.sql The example is for example purposes, but you can always change it if you want.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT,
signup_date DATE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total REAL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
unit_price REAL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE payments (
id INTEGER PRIMARY KEY,
order_id INTEGER,
payment_date DATE,
amount REAL,
method TEXT,
FOREIGN KEY (order_id) REFERENCES orders(id)
);
INSERT INTO customers (id, name, country, signup_date) VALUES
(1,'Alice','USA','2024-01-05'),
(2,'Bob','UK','2024-03-10'),
(3,'Choi','KR','2024-06-22'),
(4,'Dara','ID','2025-01-15');
INSERT INTO products (id, name, category, price) VALUES
(1,'Laptop Pro','Electronics',1500.00),
(2,'Noise-Canceling Headphones','Electronics',300.00),
(3,'Standing Desk','Furniture',450.00),
(4,'Ergonomic Chair','Furniture',250.00),
(5,'Monitor 27"','Electronics',350.00);
INSERT INTO orders (id, customer_id, order_date, total) VALUES
(1,1,'2025-02-01',1850.00),
(2,2,'2025-02-03',600.00),
(3,3,'2025-02-05',350.00),
(4,1,'2025-02-07',450.00);
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1,1,1,1500.00),
(1,2,1,300.00),
(1,5,1,350.00),
(2,3,1,450.00),
(2,4,1,250.00),
(3,5,1,350.00),
(4,3,1,450.00);
INSERT INTO payments (id, order_id, payment_date, amount, method) VALUES
(1,1,'2025-02-01',1850.00,'Credit Card'),
(2,2,'2025-02-03',600.00,'PayPal'),
(3,3,'2025-02-05',350.00,'Credit Card'),
(4,4,'2025-02-07',450.00,'Bank Transfer');After that, run the following code in your CLI to create a SQLite database for our project.
sqlite3 demo.db < init_db.sql With the database ready, we will create a Dockerfile Downloading our app.
FROM python:3.12-slim
WORKDIR /code
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]We will also build a docker-compose.yml The application file is very efficient.
services:
text2sql:
build: .
env_file: .env
ports:
- "8000:8000"
restart: unless-stopped
volumes:
- ./demo.db:/code/demo.dbWith everything ready, start your docker desktop and run the following code to build the system.
docker compose build --no-cache
docker compose up -d If everything is done correctly, you can test the application by using the following code. We will ask how many customers we have in the database.
curl -X POST " -H "Content-Type: application/json" -d "{"question":"How many customers?"}"The output will look like this.
{"sql":"SELECT COUNT(*) AS customer_count FROM customers;","result":[{"customer_count":4}]}We can try something more complex, like the number of orders for each customer:
curl -X POST " -H "Content-Type: application/json" -d "{"question":"What is the number of orders placed by each customer"}"By coming out as an underdog.
{"sql":"SELECT customer_id, COUNT(*) AS number_of_orders FROM orders GROUP BY customer_id;","result":[{"customer_id":1,"number_of_orders":2},{"customer_id":2,"number_of_orders":1},{"customer_id":3,"number_of_orders":1}]}That's all you need to build a basic Text-to-SQL program. You can upgrade it further with a first-to-end display and a more sophisticated program tailored to your needs.
The obvious Wrapping up
Data is the heart of any data operation, and companies use it to make decisions. Many times, the system we have is very complex, and we need to rely on artificial intelligence to help us figure it out.
In this article, we learned how to develop a simple Text-to-SQL program using the Opelai model, Fastapi, and SQLITE.
I hope this helped!
Cornelius Yudha Wijaya is the scientific manager and data writer. While I work full-time at Allianz Indonesia, he likes to share Python and data advice with social media and media writing. Cornelius writes on various aspects of AI and machine learning.
