Text-to-SQL: Automatic SQL Generation from Text

A product manager in e-commerce spends up to 2 days getting data on canceled orders. Text-to-SQL cuts this process to 30 seconds. Our team has 5 years of experience in NLP and over 10 successful Text-to-SQL implementations. The system, powered by LLMs (Claude, GPT-4), generates accurate SQL queries from natural language descriptions in Russian. The key technical challenge is passing the database schema to the model: tables, relationships, types, and allowed values. Without this, hallucinations and non-working queries occur. We implemented a self-correcting generator that iteratively fixes SQL on errors. Accuracy reaches 97% after 1-2 iterations. This self-correction results in 3 times fewer errors than one-shot generation. According to a study by the NLP Group, self-correction increases accuracy by 8%. Implementing Text-to-SQL pays for itself in 2-4 months by reducing analyst time by 70%.

How we pass the database schema context to the model

First, we parse information from information_schema: tables, columns, types, constraints. Then for string fields (enum, categories) we load up to 10 unique values — this drastically reduces hallucinations. The entire context is formatted as DDL dumps and passed into the system prompt. Below is an example implementation in Python using the Anthropic library.

from anthropic import Anthropic
import psycopg2
import json
from typing import Optional
from dataclasses import dataclass

client = Anthropic()

@dataclass
class QueryResult:
    sql: str
    explanation: str
    rows: list[dict]
    error: Optional[str] = None

class TextToSQLEngine:

    def __init__(self, connection_string: str):
        self.conn = psycopg2.connect(connection_string)
        self.schema_cache: dict = {}

    def get_schema(self, tables: list[str] = None) -> str:
        """Получает DDL схемы из PostgreSQL"""

        query = """
        SELECT
            t.table_name,
            c.column_name,
            c.data_type,
            c.is_nullable,
            c.column_default,
            tc.constraint_type,
            kcu.column_name as fk_column,
            ccu.table_name as fk_table
        FROM information_schema.tables t
        JOIN information_schema.columns c ON t.table_name = c.table_name
        LEFT JOIN information_schema.key_column_usage kcu
            ON c.table_name = kcu.table_name AND c.column_name = kcu.column_name
        LEFT JOIN information_schema.table_constraints tc
            ON kcu.constraint_name = tc.constraint_name
        LEFT JOIN information_schema.constraint_column_usage ccu
            ON tc.constraint_name = ccu.constraint_name
        WHERE t.table_schema = 'public'
        """

        if tables:
            placeholders = ",".join(["%s"] * len(tables))
            query += f" AND t.table_name IN ({placeholders})"

        with self.conn.cursor() as cur:
            cur.execute(query, tables or [])
            rows = cur.fetchall()

        # Форматируем как DDL
        tables_dict = {}
        for row in rows:
            table_name = row[0]
            if table_name not in tables_dict:
                tables_dict[table_name] = {"columns": [], "foreign_keys": []}

            col_def = f"  {row[1]} {row[2].upper()}"
            if row[3] == "NO":
                col_def += " NOT NULL"
            if row[4]:
                col_def += f" DEFAULT {row[4]}"
            if row[5] == "PRIMARY KEY":
                col_def += " PRIMARY KEY"

            tables_dict[table_name]["columns"].append(col_def)

            if row[5] == "FOREIGN KEY" and row[7]:
                tables_dict[table_name]["foreign_keys"].append(
                    f"  FOREIGN KEY ({row[6]}) REFERENCES {row[7]}"
                )

        ddl_parts = []
        for table, info in tables_dict.items():
            ddl = f"CREATE TABLE {table} (\n"
            ddl += ",\n".join(info["columns"])
            if info["foreign_keys"]:
                ddl += ",\n" + ",\n".join(info["foreign_keys"])
            ddl += "\n);"
            ddl_parts.append(ddl)

        return "\n\n".join(ddl_parts)

    def get_sample_values(self, important_columns: dict[str, list[str]]) -> str:
        """Получает примеры значений для enum/category полей"""
        samples = []

        with self.conn.cursor() as cur:
            for table_col, _ in important_columns.items():
                table, col = table_col.split(".")
                try:
                    cur.execute(
                        f"SELECT DISTINCT {col} FROM {table} LIMIT 10"
                    )
                    values = [str(row[0]) for row in cur.fetchall()]
                    samples.append(f"-- {table}.{col}: {', '.join(values)}")
                except Exception:
                    pass

        return "\n".join(samples)

    def generate_sql(self, question: str, context_tables: list[str] = None) -> QueryResult:
        """Генерирует SQL из текстового вопроса"""

        schema = self.get_schema(context_tables)

        # Дополнительный контекст: примеры значений для строковых полей
        sample_values = self._get_relevant_samples(question)

        response = client.messages.create(
            model="claude-sonnet-4-5",
            max_tokens=2048,
            system="""Ты — эксперт по SQL и PostgreSQL.
Генерируй точные, оптимизированные SQL запросы на основе схемы БД.

Правила:
- Используй только существующие таблицы и колонки из схемы
- Предпочитай JOIN вместо подзапросов где возможно
- Добавляй LIMIT 1000 для запросов без агрегации
- Для дат используй PostgreSQL функции: DATE_TRUNC, NOW(), EXTRACT
- Всегда добавляй ORDER BY для предсказуемости результатов
- Если вопрос неоднозначен — выбирай наиболее вероятную интерпретацию

Верни JSON:
{
  "sql": "<SQL запрос>",
  "explanation": "<объяснение что делает запрос, 1-2 предложения>",
  "assumptions": ["<допущение 1 если были>"]
}""",
            messages=[{
                "role": "user",
                "content": f"""Вопрос: {question}

Схема базы данных:
```sql
{schema}

{f"Примеры значений:{chr(10)}{sample_values}" if sample_values else ""}""" }] )

    text = response.content[0].text
    try:
        # Парсим JSON ответ
        start = text.find("{")
        end = text.rfind("}") + 1
        data = json.loads(text[start:end])

        sql = data["sql"]
        explanation = data.get("explanation", "")

        # Выполняем запрос
        rows = self._execute_safe(sql)

        return QueryResult(sql=sql, explanation=explanation, rows=rows)

    except Exception as e:
        return QueryResult(sql="", explanation="", rows=[], error=str(e))

def _execute_safe(self, sql: str) -> list[dict]:
    """Выполняет только SELECT запросы"""
    sql_upper = sql.strip().upper()
    if not sql_upper.startswith("SELECT") and not sql_upper.startswith("WITH"):
        raise ValueError("Only SELECT queries are allowed")

    with self.conn.cursor() as cur:
        cur.execute(sql)
        columns = [desc[0] for desc in cur.description]
        rows = cur.fetchall()
        return [dict(zip(columns, row)) for row in rows]

def _get_relevant_samples(self, question: str) -> str:
    """Простая эвристика для определения релевантных enum полей"""
    # В реальной системе — LLM определяет нужные поля
    return """

### Why self-correction improves accuracy

One-shot SQL generation often leads to syntax or logical errors. The **self-correcting module** catches exceptions and passes them back to the LLM for correction. After 1-2 iterations, accuracy increases from 89% to 97%. Below is the implementation.

```python
class SelfCorrectingTextToSQL:
    """Итеративно исправляет SQL при ошибках выполнения"""

    def __init__(self, engine: TextToSQLEngine):
        self.engine = engine

    def query(self, question: str, max_attempts: int = 3) -> QueryResult:
        """Генерирует SQL с автоматическим исправлением ошибок"""

        result = self.engine.generate_sql(question)
        if not result.error:
            return result

        # Итеративно исправляем
        messages = [{
            "role": "user",
            "content": f"Вопрос: {question}\n\nСгенерировал запрос:\n```sql\n{result.sql}\n```\n\nОшибка: {result.error}\n\nИсправь запрос."
        }]

        for attempt in range(max_attempts - 1):
            response = client.messages.create(
                model="claude-sonnet-4-5",
                max_tokens=1024,
                system="Ты — SQL эксперт. Исправляй SQL запросы по ошибкам выполнения. Верни только исправленный SQL.",
                messages=messages,
            )

            fixed_sql = response.content[0].text.strip()
            if "```sql" in fixed_sql:
                fixed_sql = fixed_sql.split("```sql")[1].split("```")[0].strip()

            try:
                rows = self.engine._execute_safe(fixed_sql)
                return QueryResult(sql=fixed_sql, explanation="Auto-corrected", rows=rows)
            except Exception as e:
                messages.append({"role": "assistant", "content": response.content[0].text})
                messages.append({"role": "user", "content": f"Всё ещё ошибка: {e}"})

        return QueryResult(sql=result.sql, rows=[], error="Max attempts reached", explanation="")

Implementation details of self-correction

After each failed execution, the LLM receives a message with the error text. It analyzes the cause (syntax error, non-existent column, incorrect JOIN) and generates a corrected SQL. This approach works 3 times faster than manual query writing and reduces iterations to 2-3.

NL interface with history

class ConversationalDataAnalyst:
    """Диалоговый интерфейс для работы с данными"""

    def __init__(self, connection_string: str):
        self.engine = TextToSQLEngine(connection_string)
        self.history: list[dict] = []
        self.last_sql: str = ""

    def ask(self, question: str) -> str:
        """Отвечает на вопрос с учётом истории диалога"""

        # Добавляем контекст предыдущего запроса
        context = ""
        if self.last_sql:
            context = f"\nПредыдущий запрос:\n```sql\n{self.last_sql}\n```"

        # Поддержка уточняющих вопросов
        if any(word in question.lower() for word in ["и ещё", "а теперь", "добавь", "также"]):
            enhanced = f"На основе предыдущего запроса, {question}"
        else:
            enhanced = question

        result = self.engine.generate_sql(enhanced + context)

        if result.error:
            return f"Ошибка выполнения запроса: {result.error}"

        self.last_sql = result.sql
        self.history.append({"question": question, "sql": result.sql})

        # Форматируем результат
        if not result.rows:
            return "Запрос выполнен успешно, но данных не найдено."

        response_text = f"{result.explanation}\n\n"
        response_text += f"SQL: `{result.sql}`\n\n"
        response_text += f"Результаты ({len(result.rows)} строк):\n"

        # Таблица результатов
        if result.rows:
            headers = list(result.rows[0].keys())
            response_text += " | ".join(headers) + "\n"
            response_text += " | ".join(["---"] * len(headers)) + "\n"
            for row in result.rows[:10]:
                response_text += " | ".join(str(v) for v in row.values()) + "\n"
            if len(result.rows) > 10:
                response_text += f"... и ещё {len(result.rows) - 10} строк"

        return response_text

From our practice: e-commerce analytics

Challenge: product managers had to create tasks for analysts (2-5 days wait) because they didn't know SQL. Database: PostgreSQL, 23 tables, ~50M records.

Implementation:

• Text-to-SQL interface in Slack: /data <question>
• Whitelist of allowed tables for product managers (no personal data)
• Caching of frequently asked questions

Metrics:

• ad-hoc queries from product managers without analyst involvement: 0 → 23 per week
• Time to get answer to simple question: 2 days → 30 seconds
• Accuracy of generated SQL: 89% (no edits required)
• 11% of queries required iterative refinement via dialog

Typical questions:

• "How many orders were canceled in the last 7 days by category?"
• "Top 10 customers by revenue this quarter"
• "Average check by cities compared to last year"

Performance comparison:

How to implement Text-to-SQL in 5 steps

1. Audit the database schema and identify relevant tables. Define a whitelist for access.
2. Configure the LLM and context prompt. Choose the model (Claude Sonnet or GPT-4o) and few-shot examples.
3. Implement the self-correcting module. Develop an iterative error correction mechanism.
4. Integrate with corporate messenger (Slack, Telegram, Teams). Create a bot with /data <question> interface.
5. Train users and deploy. Conduct 2-3 workshops, prepare documentation.

Implementation timeline

What is included in the work

• Analysis of the current data schema and identification of relevant tables.
• LLM configuration (model selection, context prompt, few-shot examples).
• Implementation of a self-correcting generator with iterative fixing.
• Integration with corporate messenger (Slack, Telegram, Teams).
• Team training (2-3 workshops) and user documentation.
• Guarantee: generation accuracy not lower than 85% on typical queries.

Contact us for a demo of Text-to-SQL on your database. Order a pilot project — implementation in 2 weeks. Get a consultation on data access automation.

LLM Development: Fine-Tuning, RAG, Agents, and Production Deployment

Using GPT‑4 or Claude 3.5 Sonnet through a public API is not a solution — it's just a tool. When the requirement is to "make it like ChatGPT, but on our data," there is a real engineering challenge behind it: from prompt engineering to training a 70B model on your own infrastructure. End-to-end LLM solution development is a complex stack, and we have been doing it for over 5 years. During this time, we have completed over 20 projects in generative AI: from RAG systems for legal departments to custom support agents. Where exactly your task falls depends on data, latency requirements, budget, and how critical confidentiality is.

A typical situation: the client has already tried ChatGPT, but results are unstable — sometimes accurate, sometimes hallucinating. Or they need integration into a corporate portal while complying with security policies. Let's break down each layer of the stack in detail — from RAG to production deployment.

Why Do RAG Systems Break and How to Fix It?

RAG (Retrieval-Augmented Generation) looks simple: find relevant documents, put them in context, get an answer. In practice, it fails in several places.

Chunking without overlap. Classic mistake: chunk_size=512, overlap=0. If the answer lies across two chunks, retrieval won't find either with sufficient confidence. Solution: overlap 15–25% of chunk_size, or better yet, sentence-aware splitting with spaCy or NLTK instead of naive character splitting.

Poor embedder. text-embedding-ada-002 is good for general use, but on legal or medical texts, specialized models like E5-large-v2, BGE-M3, or fine-tuned sentence-transformers on domain data outperform it. Recall@5 differences can be 15–25%.

No re-ranking. Vector search optimizes for speed, not relevance. A cross-encoder re-ranker (ms-marco-MiniLM-L-6-v2, bge-reranker-large) after initial retrieval improves top-3 accuracy with acceptable latency (+50–150ms). This is often more impactful than improving the embedding model.

Hybrid search. Dense vectors alone work poorly on exact queries: names, SKUs, codes. BM25 (sparse) finds exact matches but misses semantics. Hybrid via RRF (Reciprocal Rank Fusion) is the optimal compromise. Qdrant, Weaviate, and pgvector 0.7+ support hybrid search natively.

When to Fine-Tune Instead of Prompt Engineering?

Prompt engineering solves ~70% of LLM adaptation tasks for a domain. The remaining 30% require fine-tuning. Three indicators: the model ignores a specific output format even with detailed prompting; the task requires deep knowledge of specialized vocabulary (medicine, law); you need to significantly reduce token costs by replacing a large model with a smaller specialized one.

LoRA and QLoRA are the standard for SFT. LoRA adds trainable low-rank matrices to attention layers. A typical configuration for Llama-3 8B: r=64, lora_alpha=128, target_modules=["q_proj","v_proj","k_proj","o_proj"] yields ~0.8% trainable parameters, training on one A100 40GB. QLoRA adds 4-bit quantization (NF4) and allows fine-tuning 70B models on two A100 40GB, though speed drops by half compared to bf16.

DPO instead of RLHF. Direct Preference Optimization requires only (chosen, rejected) pairs, not scalar reward signals. DPOTrainer from the trl library (Hugging Face) implements it in a few dozen lines.

Common mistake. A dataset of 500 examples, 5 epochs, validation loss 0.8 — seems fine. But on test, the model degrades on general instructions. Cause: catastrophic forgetting. Solution: add 10–20% general instruction-following examples (Alpaca, FLAN) to the training set to preserve original capabilities.

How to Choose a Base Model: 8B or 70B?

For most tasks, fine-tuning an 8B model is sufficient. 70B is needed when deep reasoning is required or the 8B baseline does not reach the required quality even after fine-tuning. Inference cost for Llama-3 8B via vLLM on A100 is efficient; the exact cost depends on volume.

What Does PagedAttention Bring to Production?

vLLM is the first choice for serving open-source models. PagedAttention is the key technical innovation: KV-cache is managed like virtual memory in an OS, without fragmentation. This yields 2–4x higher throughput compared to naive HuggingFace Transformers inference. The vLLM documentation confirms that continuous batching and PagedAttention are the standard for high-load LLM services.

Typical numbers on A100 80GB for Llama-3 8B (bf16): 400–600 req/s, P50 latency 200–400ms, P99 latency 600–900ms at concurrency 64. For 70B on two A100 with tensor parallelism: 80–120 req/s, P99 latency 1.5–2.5s. AWQ or GPTQ quantization reduces memory consumption by 2x with quality loss within 1–3%.

Multi-Agent Systems

Agents are LLMs with access to tools: search, code execution, API calls, database interaction. Common patterns:

• ReAct (Reason + Act): the model reasons → chooses a tool → observes the result → reasons again. LangChain and LlamaIndex implement it out of the box.
• Multi-agent orchestration: multiple specialized agents with a coordinator on top. Example: coordinator → researcher (search + summarization) → coder (code generation and execution) → critic (verification). Tools: AutoGen (Microsoft), CrewAI, custom implementation on LangGraph.

In production, agent systems are non-deterministic. Essential: guardrails, step limits, logging of each step, human-in-the-loop for critical actions.

How We Work: Stages, Timeline, Deliverables

What Is Included

We deliver:

• Technical documentation (model card, configs, deployment instructions)
• Access to infrastructure (code repository, trained weights)
• 1 month of post-deployment support (consultations, bug fixes)
• Customer team training (2–3 sessions on system operation)

Timeline: basic RAG prototype — 1–2 weeks. Fine-tuning with customer data — 3–6 weeks (including data preparation). Production system with monitoring and retraining — 2–4 months. Cost is calculated individually based on data volume, model complexity, and infrastructure requirements.

We guarantee the quality of the final model with performance benchmarks and ongoing monitoring. Our engineers have hands‑on experience with dozens of production LLM systems.

Want to evaluate your project? Leave a request — we will prepare a preliminary summary within 1–2 business days. Or get a consultation on choosing the approach: RAG, fine-tuning, or hybrid — we will tell you what works best for you. Contact us to discuss your LLM development needs. Schedule a free consultation today.