QueryLytic Case Study: Natural Language Database Queries

Every organization has a data bottleneck, and it is almost never the database. It is the gap between the people who have the questions and the people who know SQL. A marketing manager wants to know which campaign drove the most signups last quarter. A finance lead needs vendor payments broken down by category. Each of those is a short query for someone who knows SQL, but the request queue, context switching and back-and-forth turn it into a wait measured in hours or days.

That bottleneck is what QueryLytic was built to remove.

A data analytics company asked CODERCOPS for a way to let business analysts, marketers and other non-technical people query their databases in plain English, without giving up accuracy or security. This is the case study.

Turning natural language into accurate SQL queries requires more than just calling an LLM

What QueryLytic does

QueryLytic is an AI-powered database interface CODERCOPS built in 2024. Users ask a question in plain English, and QueryLytic translates it into a query for the connected database, checks that it is safe and efficient, runs it, and shows readable results. It supports PostgreSQL, MySQL and MongoDB, and cut time-to-insight from hours to seconds.

The problem space: text-to-SQL

Text-to-SQL sounds simple. Take a question, work out what it means, map it to the schema, generate a correct query, run it and present the results.

In practice it is a lot harder. Take this question:

“Show me our top 10 customers by revenue last year”

To get it right, the system has to:

1. Work out that “customers” maps to a customers table (or accounts, or clients)
2. Know that “revenue” means SUM(order_total) from an orders table joined on customer_id
3. Turn “last year” into a date range: WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
4. Apply GROUP BY, ORDER BY ... DESC and LIMIT 10
5. Decide the edge cases: are cancelled orders excluded? Is revenue gross or net?

Complex joins, subqueries and ambiguous column names are where text-to-SQL systems usually fall over. A production tool has to handle those cases for real business users, or at least fail in a way they can understand.

Architecture overview

QueryLytic has four parts:

Component	What it does
React frontend	Query input and result visualization
NLP engine	Interprets conversational questions
Query translation engine	Converts English into optimized, database-specific syntax
Database connectors	PostgreSQL, MySQL, MongoDB, SQLite, CSV and Excel

Between translation and execution sits a validation step, which checks every generated query before it touches a database. Around all of it is a workspace: organizations, data sources and projects, with querying, dashboards and collaboration in one place.

Question (plain English)
        |
        v
Schema context for the connected data source
        |
        v
LLM translation (OpenAI) -> database-specific query
        |
        v
Validation (safe? efficient? real tables?)
        |
        v
Execution through the connector
        |
        v
Results, presented as tables or charts

Step 1: Schema understanding

QueryLytic learns the database structure automatically. When a data source is connected, it introspects the schema, so translations are built on the real table and column names instead of guesses.

Sending the whole schema to the model on every question is a bad idea once a database has more than a handful of tables. It costs more, it is slower, and a model handed hundreds of tables is more likely to invent a column that does not exist. The usual fix is to retrieve only the tables relevant to the question, plus the tables they reference through foreign keys. The sketch below shows that idea with embeddings. It illustrates the approach and is not QueryLytic’s production code.

class SchemaContextBuilder:
    def __init__(self, schema, embeddings_cache):
        self.schema = schema
        self.embeddings = embeddings_cache

    def build_context(self, user_query: str, max_tables: int = 10) -> str:
        # Stage 1: rank tables by semantic similarity to the question
        query_embedding = get_embedding(user_query)
        scored = []
        for table in self.schema.tables:
            table_text = f"{table.name}: " + ", ".join(
                c.name for c in table.columns
            )
            table_emb = self.embeddings.get(
                table.name, get_embedding(table_text)
            )
            scored.append(
                (table, cosine_similarity(query_embedding, table_emb))
            )

        # Stage 2: keep the top tables plus their foreign-key neighbors
        selected = set()
        for table, _ in sorted(scored, key=lambda x: x[1], reverse=True)[:max_tables]:
            selected.add(table.name)
            for fk in table.foreign_keys:
                selected.add(fk.referenced_table)

        # Build a compact schema string for the prompt
        lines = []
        for table in self.schema.tables:
            if table.name in selected:
                cols = ", ".join(f"{c.name} ({c.type})" for c in table.columns)
                lines.append(f"TABLE {table.name}: {cols}")
                for fk in table.foreign_keys:
                    lines.append(
                        f"  FK: {fk.column} -> "
                        f"{fk.referenced_table}.{fk.referenced_column}"
                    )
        return "\n".join(lines)

Introspected schema data changes rarely, so caching it per data source and refreshing it on demand is a natural fit for the cache-aside pattern. The same retrieval tradeoff shows up in more general terms in RAG vs. fine-tuning vs. prompt engineering.

Step 2: Query generation with an LLM

The schema context and the question go to an OpenAI model with a tightly scoped prompt. A prompt for this job looks something like this:

SQL_GENERATION_PROMPT = """
You are a SQL query generator. Given a database schema and a natural
language question, generate the correct SQL query.

RULES:
1. Generate ONLY the SQL query. No explanations, no markdown.
2. Use ONLY tables and columns that exist in the provided schema.
3. Always use explicit JOIN syntax.
4. Always alias tables in multi-table queries.
5. Use the SQL dialect named below.
6. For ambiguous terms, prefer the most common business meaning:
   - "revenue" = SUM of order/transaction amounts
   - "last month" = previous calendar month
   - "top N" = ORDER BY DESC LIMIT N
7. If the schema cannot answer the question, respond with:
   CANNOT_ANSWER: [reason]
8. Never generate DROP, DELETE, UPDATE, INSERT, ALTER, or TRUNCATE.
9. Always include a LIMIT clause.

DIALECT: {dialect}

DATABASE SCHEMA:
{schema_context}

USER QUESTION:
{user_query}

SQL:
"""

Rule 8 matters most, and it is also the rule you should trust least on its own. A prompt instruction is a request, not a guarantee. That is why validation comes next.

Step 3: Query validation

QueryLytic validates every generated query to make sure it is safe and efficient before it runs. For any text-to-SQL tool, a validator should at least reject write and DDL keywords, confirm every referenced table exists, enforce a row limit, and flag queries likely to be expensive. Here is a sketch of those checks:

class QueryValidator:
    FORBIDDEN_KEYWORDS = {
        'DROP', 'DELETE', 'UPDATE', 'INSERT', 'ALTER',
        'TRUNCATE', 'CREATE', 'GRANT', 'REVOKE', 'EXEC',
        'EXECUTE', 'INTO OUTFILE', 'INTO DUMPFILE', 'LOAD_FILE'
    }

    def validate(self, sql: str, schema) -> ValidationResult:
        errors, warnings = [], []

        # 1. Forbidden keyword check
        sql_upper = sql.upper()
        for keyword in self.FORBIDDEN_KEYWORDS:
            if keyword in sql_upper:
                errors.append(f"Forbidden keyword detected: {keyword}")

        # 2. Every referenced table must exist
        parsed = sqlparse.parse(sql)[0]
        valid_tables = {t.name for t in schema.tables}
        for table in extract_table_names(parsed):
            if table not in valid_tables:
                errors.append(f"Unknown table: {table}")

        # 3. Enforce a row limit
        if 'LIMIT' not in sql_upper:
            warnings.append("No LIMIT clause. Adding LIMIT 1000.")
            sql = sql.rstrip(';') + ' LIMIT 1000;'

        # 4. Flag expensive queries (EXPLAIN-based estimate)
        if self.estimate_cost(sql) > self.COST_THRESHOLD:
            warnings.append("High estimated cost. Query may be slow.")

        return ValidationResult(
            is_valid=not errors,
            errors=errors,
            warnings=warnings,
            sanitized_sql=sql,
        )

When validation fails, the useful move is to send the specific error back to the model and ask for a corrected query, rather than showing the user a failure straight away. A keyword list is crude (it will reject a column called updated_at if you match naively, so match on tokens, not substrings), which is another reason it should never be the only layer.

Step 4: Execution and results

Validated queries run through the connector for the target data source. The last line of defense belongs at the database itself: the connection should use a user with read-only permissions, so even a query that slipped past every other check cannot change data.

QueryLytic presents results in user-friendly formats rather than raw rows. A simple rule set covers most cases when picking a chart automatically:

Result shape	Sensible default
1 text column + 1 numeric column	Bar chart
1 date column + 1 numeric column	Line chart
2 numeric columns	Scatter plot
3+ columns or no clear pattern	Table

Multi-database support

QueryLytic supports PostgreSQL, MySQL and MongoDB, with connectors for SQLite, CSV and Excel as well. Each engine needs the translation adjusted to its dialect.

PostgreSQL and MySQL

Both are SQL, so they can share one generation pipeline with dialect-specific instructions in the prompt. The differences that trip up generated queries most often are small ones:

Feature	PostgreSQL	MySQL
String concatenation	|| operator	CONCAT() function
Date truncation	DATE_TRUNC()	DATE_FORMAT()
Boolean type	Native BOOLEAN	TINYINT(1)
Pagination	LIMIT n OFFSET m	LIMIT m, n (or LIMIT n OFFSET m)
JSON access	->, ->> operators	JSON_EXTRACT()

MongoDB: the harder problem

MongoDB does not use SQL. Queries are aggregation pipelines, which is a different way of thinking about the same question, so it needs its own translation path:

Natural language -> LLM -> MongoDB aggregation pipeline (JSON)

A prompt for that path asks for a JSON array of pipeline stages instead of a SQL string:

MONGO_GENERATION_PROMPT = """
You are a MongoDB aggregation pipeline generator. Given a
collection schema and a natural language question, generate the
correct aggregation pipeline.

Output ONLY a valid JSON array of pipeline stages.

Example:
Question: "How many orders per customer last month?"
Output:
[
  {"$match": {"created_at": {"$gte": "2026-01-01", "$lt": "2026-02-01"}}},
  {"$group": {"_id": "$customer_id", "count": {"$sum": 1}}},
  {"$sort": {"count": -1}},
  {"$limit": 100}
]

COLLECTION SCHEMA:
{schema_context}

QUESTION:
{user_query}

PIPELINE:
"""

The validation step needs a MongoDB version too, one that rejects stages that write data, such as $out and $merge.

Security: the layers a text-to-SQL tool needs

The client needed data access opened up without compromising security. For a tool that holds database credentials and runs AI-generated queries, that comes down to a few layers, and none of them should be skipped because another one exists:

Layer	What it stops
Prompt rules	Most bad queries, most of the time
Validator	Write statements, unknown tables, runaway queries
Read-only database user	Anything that gets past the first two
Row-level filters	Users seeing rows they should not, such as salaries or customer PII
Credential handling	Connection strings leaking. Send the model the schema, never the credentials

For row-level rules, you can push filtering down into Postgres’s native row-level security when the data lives in PostgreSQL. A multi-database tool also needs the same rules to apply to MySQL and MongoDB, which is an argument for keeping a copy of the logic in the application layer.

Prompt engineering lessons

Building text-to-SQL is a good education in prompt design, and much of it overlaps with the shift toward context engineering. The schema context, examples and error history you give the model matter as much as the instructions.

Few-shot examples beat more rules

A list of rules only gets you so far. A handful of varied worked examples (a join, an aggregation, a date filter, a subquery, NULL handling) usually teaches the model your conventions faster than another paragraph of instructions.

Less schema, fewer hallucinations

Trimming the schema to what the question needs reduces invented columns as well as cost. It is the single change I would make first on any text-to-SQL prototype that is hallucinating.

Retry prompts need the specific error

When a query fails validation, the retry prompt has to say exactly what went wrong:

Bad:  "The previous query was incorrect. Try again."

Good: "The previous query referenced table 'customer' which
       does not exist. Available tables containing customer
       data: 'customers', 'customer_orders',
       'customer_profiles'. Generate a corrected query."

The vague version gives the model nothing to work with. The specific version usually gets fixed on the next attempt.

Show users the assumptions

Business language is ambiguous. “Last quarter” depends on the date and on the company’s fiscal year. The honest approach is to pick a documented default and show the user what was assumed:

Your query: "Show me revenue by region last quarter"
Interpreted as: Q4 2025 (Oct 1 - Dec 31, 2025)
Change interpretation? [Q3 2025] [Q1 2026] [Custom range]

Results

Outcome	Result
Access	Non-technical users can get database insights on their own
Time-to-insight	Reduced from hours to seconds
Training	SQL training no longer needed for business users
Reach	Data access opened up across the organization

The time-to-insight change is the one that matters. A question that used to wait in an analyst’s queue now gets answered while the person asking still has the context in their head, and the data team gets its time back for the analysis it was hired to do.

Limitations and honest assessment

Text-to-SQL tools have limits, and QueryLytic is no exception:

1. Complex multi-step questions (for example, “what share of customers who bought Product A in Q1 also bought Product B in Q2?”) are where generation is least reliable, and they may need a rephrase or a hand-written query.
2. MongoDB is a harder target than SQL databases, because aggregation pipelines are a different paradigm and need their own prompt and validation path.
3. Very large result sets are hard to render in a browser, and charts with huge numbers of points get slow.
4. It does not write data. A read-only tool is a deliberate choice. Adding writes to an AI query tool changes its risk profile completely.

Conclusion

QueryLytic shows that text-to-SQL, while not solved in any academic sense, works in real business settings when you combine good schema context, careful prompts, strict validation and a UX that is honest about uncertainty.

The key insight is that the AI does not need to be perfect. It needs to be fast, usually right, and open about its assumptions. An answer in seconds with its assumptions on screen is more useful than a perfect answer that waits days for a busy analyst.

If your organization has valuable data locked behind SQL expertise, talk to us about building your own QueryLytic.