Unlock Your Data’s Potential: Build Your Own Text-to-SQL App with AI, FastAPI, and SQLite

From English to SQL: Your Gateway to Smarter Data Access

In today’s data-driven world, information is currency. Businesses thrive on insights gleaned from vast datasets, making the ability to access and analyze this data a critical skill. But let’s be honest: navigating complex databases and crafting intricate SQL queries can be a significant hurdle, even for seasoned professionals. What if you could simply ask your database questions in plain English and get the answers you need, instantly?

Welcome to the exciting intersection of Artificial Intelligence and data management. Generative AI, particularly powerful models like those from OpenAI, is revolutionizing how we interact with data. It’s not just about generating creative text or images anymore; it’s about making complex tasks, like data querying, accessible to everyone.

In this comprehensive guide, we’ll embark on a journey to build a simple yet powerful Text-to-SQL application. This tool will act as your personal data translator, converting your natural language questions into precise SQL queries that your database can understand and execute. We’ll leverage the cutting-edge capabilities of OpenAI, the lightning-fast performance of the FastAPI framework, and the simplicity of an SQLite database to bring this vision to life.

Why this matters: Imagine a world where anyone on your team, regardless of their technical SQL expertise, can pull critical reports, analyze trends, or uncover hidden patterns. This is the power we’re unlocking.

Setting the Stage: Your Text-to-SQL Toolkit

Before we dive into the code, let’s get our development environment ready. The core components you’ll need are:

• OpenAI API Key: This is your passport to OpenAI’s intelligent language models. You’ll need to sign up on the OpenAI platform and obtain your unique API key.
• Docker: For seamless containerization and deployment, Docker is your best friend. If you don’t have it installed, grab Docker Desktop for your operating system.
• Python: The backbone of our application. Ensure you have Python installed (version 3.12 or higher is recommended).
• FastAPI: A modern, fast (high-performance) web framework for building APIs with Python.
• SQLite: A lightweight, file-based relational database. It’s perfect for this project because it doesn’t require a separate server installation.

Project Structure: A Blueprint for Success

To keep our project organized and manageable, we’ll adopt a clear directory structure. You can manually create these files and folders, or for a shortcut, you can find a pre-built repository [Link to hypothetical GitHub repo if available]. Let’s break down the essential files:

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
└── .env

• app/: This directory houses our core Python application logic.
  • __init__.py: Initializes the Python package.
  • database.py: Handles all database interactions, including connection and query execution.
  • openai_utils.py: Contains the magic to connect with OpenAI and process language.
  • main.py: The heart of our FastAPI application.
• demo.db: This will be our SQLite database file, populated with sample data.
• init_db.sql: A script to create and populate our SQLite database.
• requirements.txt: Lists all Python dependencies needed for the project.
• Dockerfile: Defines how to build our Docker image.
• docker-compose.yml: Orchestrates the running of our application and its dependencies.
• .env: A crucial file to store sensitive information like your OpenAI API key.

Securing Your Secrets: The .env File

First things first, let’s store your OpenAI API key securely. Create a file named .env in the root of your project directory and add the following line:

OPENAI_API_KEY=YOUR-API-KEY-HERE

Important: Replace YOUR-API-KEY-HERE with your actual OpenAI API key. Never commit this file to a public repository!

Listing Dependencies: requirements.txt

Next, let’s tell Python what libraries we need. Open requirements.txt and add these lines:

fastapi
uvicorn
sentencepiece
sentencepiece
sentencepiece
sentencepiece
sqlalchemy
openai
pydantic
python-dotenv

These libraries will enable us to build the web API, interact with OpenAI, manage database connections, and load environment variables.

Initializing the Environment: app/__init__.py

This small file is key to ensuring your API key is loaded correctly. It runs when the app package is imported.

from pathlib import Path
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv(dotenv_path=Path(__file__).resolve().parent.parent / ".env", override=False)

This code snippet looks for the .env file in the parent directory of app and loads the variables. This makes your API key available throughout your application.

Interacting with Your Data: The Database Layer (app/database.py)

Our application needs a way to talk to the SQLite database. The database.py file handles this. It uses SQLAlchemy, a powerful Object-Relational Mapper (ORM), to simplify database operations.

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

# Database connection string for SQLite
DATABASE_URL = "sqlite:///./demo.db"

# Create the SQLAlchemy engine
# future=True enables modern SQLAlchemy features
# echo=False prevents logging all SQL statements to the console
ENGINE = create_engine(DATABASE_URL, future=True, echo=False)

def run_query(sql: str) -> list[dict]:
    """Executes a given SQL query and returns results as a list of dictionaries."""
    with Session(ENGINE) as session:
        # Use text() to wrap raw SQL for execution
        # .mappings().all() returns rows as dictionary-like objects
        rows = session.execute(text(sql)).mappings().all()
        # Convert each row to a standard Python dictionary
        return [dict(r) for r in rows]

Here, create_engine establishes the connection to our demo.db file. The run_query function takes a SQL string, executes it using the database session, and returns the results in a user-friendly dictionary format.

The AI Brain: Connecting with OpenAI (app/openai_utils.py)

This is where the magic happens! The openai_utils.py file contains the logic to send your natural language question and the database schema to OpenAI and receive a SQL query in return.

import os
import json
from openai import OpenAI

# Initialize the OpenAI client with your API key from environment variables
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))

# A crucial system prompt to guide the AI
# It specifies the model's role, constraints (read-only), and desired output format (JSON)
_SYSTEM_PROMPT = """
You convert natural-language questions into read-only SQLite SQL.
Never output INSERT / UPDATE / DELETE statements.
Return JSON in the format: { "sql": "..." }.
"""

def text_to_sql(question: str, schema: str) -> str:
    """Translates a natural language question into a SQL query using OpenAI's GPT model."""
    try:
        response = client.chat.completions.create(
            model="gpt-4o-mini",  # You can choose other models like gpt-4o for better results
            temperature=0.1,      # Lower temperature for more deterministic, factual outputs
            response_format={"type": "json_object"}, # Ensure the model returns JSON
            messages=[
                {"role": "system", "content": _SYSTEM_PROMPT},
                {"role": "user", "content": f"schema:
{schema}

question: {question}"}
            ]
        )
        # Parse the JSON response from OpenAI
        payload = json.loads(response.choices[0].message.content)
        return payload["sql"]
    except Exception as e:
        # Log or re-raise the exception for debugging
        print(f"Error during text_to_sql conversion: {e}")
        raise

• OpenAI(api_key=os.getenv("OPENAI_API_KEY")): Initializes the OpenAI client, picking up your key from the environment.
• _SYSTEM_PROMPT: This is a vital instruction for the AI. It tells the model exactly what we expect: to generate read-only SQLite SQL and to strictly avoid any data modification commands (like INSERT, UPDATE, DELETE). It also dictates the JSON output format.
• client.chat.completions.create(...): This is the core API call to OpenAI.
  • model="gpt-4o-mini": We’re using the efficient gpt-4o-mini model. For potentially higher accuracy, you could experiment with gpt-4o or other models.
  • temperature=0.1: A low temperature makes the AI’s output more focused and predictable, which is ideal for generating precise SQL.
  • response_format={"type": "json_object"}: This is crucial for ensuring the AI returns its response strictly as a JSON object, making it easy for us to parse.
  • messages: This array contains the conversation history. We provide the system prompt and then the user’s actual request, including the database schema and their question.
• json.loads(...): Once we get a response from OpenAI, this line parses the JSON string into a Python dictionary.
• payload["sql"]: Finally, we extract the generated SQL query from the parsed JSON.

Building the API: FastAPI Takes Center Stage (app/main.py)

Now, let’s assemble everything into a functional API using FastAPI. This file defines our API endpoints, handles incoming requests, and orchestrates the flow from question to answer.

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from sqlalchemy import inspect

# Import our custom database and OpenAI utility functions
from .database import ENGINE, run_query
from .openai_utils import text_to_sql

# Initialize the FastAPI application
app = FastAPI(title="Text-to-SQL Demo Application")

# Define the request body model for incoming queries
class NLRequest(BaseModel):
    question: str

# Global variable to store the database schema
SCHEMA_STR: str = ""

@app.on_event("startup")
def capture_schema() -> None:
    """Captures the database schema upon application startup."""
    global SCHEMA_STR
    insp = inspect(ENGINE) # Create an inspector object for the database
    
    # Construct the schema string by iterating through tables and columns
    SCHEMA_STR = "\n".join(
        f"CREATE TABLE {table_name} ({', '.join(column['name'] for column in insp.get_columns(table_name))});"
        for table_name in insp.get_table_names()
    )
    print("Database schema captured successfully.")

@app.post("/query")
def query(req: NLRequest) -> dict:
    """API endpoint to receive a natural language question, convert it to SQL, execute it, and return results."""
    try:
        # 1. Convert natural language question to SQL using OpenAI
        generated_sql = text_to_sql(req.question, SCHEMA_STR)
        
        # 2. Security Check: Ensure only SELECT statements are executed
        # .lstrip() removes leading whitespace, .lower() makes it case-insensitive
        if not generated_sql.lstrip().lower().startswith("select"):
            raise ValueError("Only SELECT statements are permitted for security reasons.")
        
        # 3. Execute the generated SQL query against the database
        results = run_query(generated_sql)
        
        # 4. Return the generated SQL and the query results
        return {
            "sql": generated_sql,
            "result": results
        }
        
    except ValueError as ve:
        # Handle specific security/validation errors
        raise HTTPException(status_code=400, detail=str(ve))
    except Exception as e:
        # Catch any other unexpected errors during the process
        print(f"An unexpected error occurred: {e}")
        raise HTTPException(status_code=500, detail=f"An internal server error occurred: {e}")

# Optional: A simple root endpoint to confirm the API is running
@app.get("/")
def read_root():
    return {"message": "Welcome to the Text-to-SQL API!"}

Key Components of main.py:

• app = FastAPI(...): Initializes our FastAPI application.
• NLRequest(BaseModel): Defines the structure of the JSON payload our API expects. It must contain a question field.
• @app.on_event("startup"): This decorator ensures that the capture_schema function runs automatically when the FastAPI application starts. This is where we inspect the SQLite database and build a string representation of its schema. This schema is crucial for OpenAI to understand the database structure.
• @app.post("/query"): This defines our primary API endpoint that accepts POST requests.
  • It receives the NLRequest object.
  • It calls text_to_sql to get the SQL query.
  • Crucially, it includes a security check: if not generated_sql.lstrip().lower().startswith("select"): This prevents any malicious attempts to run INSERT, UPDATE, or DELETE commands. Only SELECT statements are allowed.
  • It then calls run_query to execute the validated SQL.
  • Finally, it returns a JSON object containing both the generated SQL and the query results.
• Error Handling: Robust try...except blocks are in place to catch potential errors, from invalid SQL generation to database issues, and return appropriate HTTP error responses.

Populating Your Data: The init_db.sql Script

To test our application, we need some data. The init_db.sql file contains SQL statements to create and populate a sample database. You can customize this to reflect your own data structure.

-- Drop existing tables to ensure a clean slate
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;

-- Table: customers
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    signup_date DATE
);

-- Table: products
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL
);

-- Table: orders
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Table: order_items
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)
);

-- Table: payments
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 sample data into customers table
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 sample data into products table
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 sample data into orders table
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 sample data into order_items table
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 sample data into payments table
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');

Creating the Database:

Once you have the init_db.sql file, you can create your demo.db file by running this command in your terminal within the project’s root directory:

sqlite3 demo.db < init_db.sql

This command uses the sqlite3 command-line tool to execute the SQL script, creating and populating the database.

Containerizing Your Application: Docker to the Rescue

Docker simplifies deployment and ensures your application runs consistently across different environments. We’ll use two files for this:

Dockerfile:

This file defines how to build our Docker image.

# Use an official Python runtime as a parent image
FROM python:3.12-slim

# Set the working directory in the container
WORKDIR /code

# Copy the requirements file into the container
COPY requirements.txt .

# Install any needed packages specified in requirements.txt
# --no-cache-dir reduces image size by not storing the pip cache
RUN pip install --no-cache-dir -r requirements.txt

# Copy the rest of the application code into the container
COPY . .

# Command to run the application when the container launches
# uvicorn is a lightning-fast ASGI server
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

docker-compose.yml:

This file orchestrates our container, making it easy to start and manage.

version: "3.8"

services:
  text2sql:
    build:
      context: .
      dockerfile: Dockerfile
    env_file: .env       # Load environment variables from .env file
    ports:
      - "8000:8000"    # Map port 8000 on the host to port 8000 in the container
    restart: unless-stopped # Automatically restart the container unless explicitly stopped
    volumes:
      - ./demo.db:/code/demo.db # Mount the SQLite database file into the container

Bringing It All Together: Running Your Application

With Docker installed and your files set up, it’s time to build and run the application.

1. Build the Docker image:

   docker compose build --no-cache
   

   The --no-cache flag ensures you’re building with the latest dependencies.

2. Start the application in the background:

   docker compose up -d
   

   The -d flag runs the container in detached mode (in the background).

Testing Your Text-to-SQL App!

Once the application is running, you can start sending it natural language questions using tools like curl or Postman.

Example 1: Counting Customers

Ask: "How many customers do we have?"

curl -X POST "http://localhost:8000/query" \
-H "Content-Type: application/json" \
-d '{"question":"How many customers?"}'

Expected Output:

{
  "sql": "SELECT COUNT(*) AS customer_count FROM customers;",
  "result": [
    {
      "customer_count": 4
    }
  ]
}

Example 2: Orders Per Customer

Ask: "What is the number of orders placed by each customer?"

curl -X POST "http://localhost:8000/query" \
-H "Content-Type: application/json" \
-d '{"question":"What is the number of orders placed by each customer"}'

Expected Output:

{
  "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
    }
  ]
}

Example 3: Total Revenue by Product Category

Ask: "Show me the total revenue generated by each product category."

curl -X POST "http://localhost:8000/query" \
-H "Content-Type: application/json" \
-d '{"question":"Show me the total revenue generated by each product category."}'

Expected Output (may vary slightly based on precise schema representation and model interpretation):

{
  "sql": "SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_revenue FROM products p JOIN order_items oi ON p.id = oi.product_id GROUP BY p.category;",
  "result": [
    {
      "category": "Electronics",
      "total_revenue": 4950.0
    },
    {
      "category": "Furniture",
      "total_revenue": 700.0
    }
  ]
}

Expanding Your Horizons: Next Steps

Congratulations! You’ve successfully built a functional Text-to-SQL application. This is a solid foundation, and the possibilities for enhancement are vast:

• User Interface: Develop a user-friendly web interface (using frameworks like React, Vue, or just plain HTML/JavaScript) to make it even more accessible.
• Advanced Security: Implement more sophisticated security measures, perhaps by analyzing the SQL query structure for potential vulnerabilities beyond just checking for SELECT.
• Error Handling: Improve error reporting to provide more specific feedback to users when queries fail.
• Model Choice: Experiment with different OpenAI models to find the best balance of cost, speed, and accuracy for your specific needs.
• Database Support: Adapt the database.py module to support other database systems like PostgreSQL or MySQL.
• Context Management: For more complex, multi-turn conversations, you might need to implement context management to help the AI remember previous interactions.

Wrapping Up: Empowering Data Access

Data is the lifeblood of modern decision-making, and tools that democratize access to this data are invaluable. By combining the power of OpenAI’s generative AI with the agility of FastAPI and the simplicity of SQLite, we’ve created a powerful application that bridges the gap between human language and database queries.

This project is a testament to how readily available AI tools can dramatically simplify complex technical tasks, making data more accessible and actionable for a wider audience. We hope this guide has equipped you with the knowledge and confidence to build your own intelligent data tools.

Happy querying!