Using AI to Build SQL Queries | Original

Home 2025.08

Database Schema (PostgreSQL):

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(10),
    phone_number VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    zip_code VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE,
    last_login TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT TRUE,
    role VARCHAR(50),
    profile_picture_url VARCHAR(255),
    bio TEXT,
    failed_login_attempts INTEGER DEFAULT 0,
    account_balance NUMERIC(10, 2) DEFAULT 0.00
);

above is postgre database, below is table definition. here is natural language query instructions.

I want to get some users who’s firstname is andy, created_ at around the day last month, and who are 20 years old as of 2025, and whose last_login like recent week, give me the sql to query such users

Generated SQL Query:

SELECT *
FROM users
WHERE first_name ILIKE 'andy'
  AND CAST(created_at AS DATE) BETWEEN 
      (CURRENT_DATE - INTERVAL '1 MONTH' - INTERVAL '1 DAY') 
      AND 
      (CURRENT_DATE - INTERVAL '1 MONTH' + INTERVAL '1 DAY')
  AND EXTRACT(YEAR FROM AGE(date_of_birth)) = 20
  AND last_login >= CURRENT_TIMESTAMP - INTERVAL '7 DAYS';

Below is a PostgreSQL table definition for a conversations table that could be used to store user interactions with an AI system like Grok. The table is designed to track conversations, including the user involved, the conversation content, timestamps, and relevant metadata.

CREATE TABLE conversations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255),
    conversation_type VARCHAR(50) DEFAULT 'chat',
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    ended_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(50) DEFAULT 'active',
    message_count INTEGER DEFAULT 0,
    last_message_at TIMESTAMP WITH TIME ZONE,
    is_archived BOOLEAN DEFAULT FALSE,
    ai_model_version VARCHAR(100),
    total_tokens_used INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Explanation:

This table is designed to be flexible and can be extended based on specific needs, such as storing individual messages in a separate messages table or adding fields for sentiment analysis or conversation tags. Let me know if you need a related table (e.g., for messages) or a query to interact with this table!

To enable a frontend where users can query their own conversations using natural language (e.g., “give me my latest 5 conversations” or “conversations whose title has word about java”), you need to ensure the SQL query restricts results to the authenticated user’s conversations. This involves incorporating the user’s identity (e.g., user_id) into the query and translating the natural language into SQL conditions. Below are prompts and corresponding SQL queries for the conversations table, ensuring users can only access their own data.

Key Considerations

Natural Language Prompts and SQL Queries

Prompt 1: “Give me my latest 5 conversations”

Parsed Requirements:

SQL Query:

SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
ORDER BY started_at DESC
LIMIT 5;

Prompt 2: “Conversations whose title has word about java”

Parsed Requirements:

SQL Query:

SELECT *
FROM conversations
WHERE user_id = :authenticated_user_id
  AND title ILIKE '%java%';

Implementation Notes for the Frontend

  1. User Authentication:
    • The frontend must send the authenticated user’s ID (e.g., from a JWT token or session) to the backend API.
    • The backend should validate the user_id and use it in the WHERE clause to filter results.
  2. Natural Language Processing:
    • Use a simple rule-based parser or an AI model (like Grok) to interpret the natural language query and extract:
      • Intent: List conversations.
      • Filters: Keywords (e.g., “java”), sorting (e.g., “latest”), or limits (e.g., “5”).
    • For example, map “latest 5” to ORDER BY started_at DESC LIMIT 5 and “title has word java” to title ILIKE '%java%'.
  3. API Design:
    • Create an endpoint (e.g., /api/conversations) that accepts:
      • user_id: From the authenticated session.
      • query: The natural language input or structured parameters (e.g., { filter: "title contains java" }).
    • The backend translates the query into SQL, ensuring user_id is always included in the WHERE clause.
  4. Security:
    • Never allow users to directly input SQL or modify the user_id filter.
    • Use parameterized queries (e.g., :authenticated_user_id) to prevent SQL injection.
    • Validate inputs to avoid malicious patterns in ILIKE searches.
  5. Frontend Display:
    • Return fields like id, title, started_at, last_message_at, and status to display a conversation list.
    • Allow sorting or filtering options in the UI (e.g., dropdowns for “latest” or text input for title search).

Example Workflow

  1. User logs in, and the frontend retrieves their user_id (e.g., 123).
  2. User types “give me my latest 5 conversations” in a search bar.
  3. Frontend sends a request to the backend: /api/conversations?user_id=123&query=latest 5.
  4. Backend parses the query, generates the SQL: 
    SELECT *
FROM conversations
WHERE user_id = 123
ORDER BY started_at DESC
LIMIT 5;
  5. Backend returns the results as JSON, and the frontend displays the conversations.

Additional Notes

Let me know if you need help with the frontend code, backend API setup, or additional query examples!

Back Donate