Local text-to-SQL: query your database in natural language naturel

#Why use text-to-SQL with a local LLM

Cloud text-to-SQL solutions (BI assistants, data warehouse copilots) send your schema—table names, column names, and sometimes row samples—to a third-party server. For a customer, HR, or financial database, this is often a deal-breaker: the schema alone already reveals the structure of your business, and the samples contain personal data.

A local LLM solves this problem at the root: the model runs on your machine through Ollama, the schema remains in local memory, and the generated query runs against your database without a single byte traveling over the internet. It is also free to use and independent of any API rate limit.

Privacy

The schema and data never leave your network — facilitating GDPR/business-secret compliance.

Cost

No per-request cost. A data analyst can iterate hundreds of times without receiving a bill.

Accessibility

Business users who do not know SQL query the database in natural language.

Control

You decide on the model, the prompt, and the guardrails—not a remote black box.

#How it works in practice

The principle behind text to SQL with an LLM comes in three steps. First, describe the database schema to the model (the DDL for the relevant tables). Then send it the user's question with a strict instruction: produce only an SQL query for the target dialect. Finally, retrieve the query, validate it, and execute it in read-only mode.

1. 01
   Schema introspection

   We extract the table structure (columns, types, keys) from the database—automatically instead of manually, so it stays synchronized.
2. 02
   Prompt construction

   We assemble a system prompt containing the SQL dialect, the relevant schema, and the rules (SELECT only, LIMIT required, no comments).
3. 03
   Generation

   The local LLM returns a query. We clean it up (removing any ```sql Markdown tags).
4. 04
   Validation + execution

   We verify that it is a SELECT statement, execute it through a read-only database role, and return the rows.

#Prerequisites

Ollama installed

The daemon must listen on http://localhost:11434. Check with « ollama ps ».

A capable model

A recent coding model (Qwen3-Coder 30B-A3B, Devstral 24B) delivers much better SQL results than a small general-purpose model (see the models section).

Python 3.10+

With the appropriate database client: psycopg2-binary (PostgreSQL) or PyMySQL (MySQL).

Basic read-only access

Ideally, a dedicated SQL role that can only perform SELECTs—the most important safeguard.

# Récupérer un modèle adapté au SQL
ollama pull qwen3-coder:30b

# Dépendances Python
pip install ollama psycopg2-binary sqlparse

#Give the model your database schema

This is the step that determines 80% of the result quality. The model can generate a correct query only if it knows the exact names of the tables and columns, their types, and the relationships between them. Two approaches: paste the raw DDL, or introspect the database to build a compact description.

For a small database (fewer than twenty tables), you can inject everything. Beyond that, the schema exceeds the useful context and overwhelms the model: you then need to select the tables relevant to the question (using an initial search pass or a business-domain mapping). Here is a PostgreSQL introspection query that produces a schema the LLM can read.

import psycopg2

def get_schema(conn):
    """Retourne le schéma sous forme de CREATE TABLE simplifiés."""
    query = """
        SELECT table_name, column_name, data_type
        FROM information_schema.columns
        WHERE table_schema = 'public'
        ORDER BY table_name, ordinal_position;
    """
    tables = {}
    with conn.cursor() as cur:
        cur.execute(query)
        for table, col, dtype in cur.fetchall():
            tables.setdefault(table, []).append(f"{col} {dtype}")

    lines = []
    for table, cols in tables.items():
        cols_str = ", ".join(cols)
        lines.append(f"TABLE {table} ({cols_str});")
    return "\n".join(lines)

#Complete Python pipeline with Ollama

Here is a minimal but functional pipeline: schema → prompt → generation → cleanup → validation → execution. It uses the official Python client for Ollama and a read-only database role.

import re
import ollama
import psycopg2
import sqlparse

MODEL = "qwen3-coder:30b"

SYSTEM_PROMPT = """Tu es un expert PostgreSQL. Génère UNE seule requête SQL
qui répond à la question de l'utilisateur, en respectant ces règles :
- Uniquement des requêtes SELECT (jamais INSERT/UPDATE/DELETE/DROP).
- Utilise exactement les noms de tables et colonnes du schéma fourni.
- Ajoute toujours LIMIT 100 si la question ne précise pas de limite.
- Réponds UNIQUEMENT avec le SQL, sans explication ni balise Markdown.

Schéma de la base :
{schema}"""

def generate_sql(question, schema):
    resp = ollama.chat(
        model=MODEL,
        messages=[
            {"role": "system", "content": SYSTEM_PROMPT.format(schema=schema)},
            {"role": "user", "content": question},
        ],
        options={"temperature": 0},  # déterminisme : crucial pour du SQL
    )
    return clean_sql(resp["message"]["content"])

def clean_sql(raw):
    # Retire les fences Markdown ```sql ... ``` si le modèle en ajoute
    raw = re.sub(r"```(?:sql)?", "", raw).strip()
    return raw.rstrip(";") + ";"

The execution layer deliberately separates validation from the actual database call. It rejects anything that is not a single SELECT before even opening the cursor.

def is_read_only(sql):
    statements = sqlparse.parse(sql)
    if len(statements) != 1:
        return False  # une seule requête, pas d'empilement
    stmt = statements[0]
    if stmt.get_type() != "SELECT":
        return False
    forbidden = ("insert", "update", "delete", "drop",
                 "alter", "truncate", "grant", "create")
    lowered = sql.lower()
    return not any(kw in lowered for kw in forbidden)

def run_query(sql):
    if not is_read_only(sql):
        raise ValueError(f"Requête refusée (non lecture seule) : {sql}")
    # Rôle 'readonly' : ne dispose QUE du privilège SELECT côté base
    conn = psycopg2.connect(
        dbname="analytics", user="readonly",
        password="...", host="localhost",
    )
    with conn.cursor() as cur:
        cur.execute("SET statement_timeout = '5s';")  # anti-requête folle
        cur.execute(sql)
        cols = [d[0] for d in cur.description]
        rows = cur.fetchall()
    conn.close()
    return cols, rows

if __name__ == "__main__":
    from schema import get_schema
    ro = psycopg2.connect(dbname="analytics", user="readonly",
                          password="...", host="localhost")
    schema = get_schema(ro)
    question = "Combien de commandes par mois en 2025 ?"
    sql = generate_sql(question, schema)
    print("SQL généré :", sql)
    cols, rows = run_query(sql)
    print(cols)
    for r in rows:
        print(r)

#Make generated SQL reliable and secure

This is the section that distinguishes a demo from a real deployment. An LLM can generate a destructive query if asked—or accidentally through an injection in the question. Defense must never rely on the prompt alone: it has to be enforced in depth, on the database side.

1. 01
   Read-only base role (primary defense)

   Create an SQL role that has ONLY the SELECT privilege. Even if the model generates a DROP TABLE, the database rejects it. This is the only truly reliable safeguard: « GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; » and nothing else.
2. 02
   Application validation

   Upstream, parse the SQL with sqlparse and reject anything that isn't a single SELECT. Use the database role as a second barrier.
3. 03
   Request timeout

   SET statement_timeout prevents a malformed query (a Cartesian product over millions of rows) from overwhelming the database.
4. 04
   Forced LIMIT

   Impose a LIMIT on both the prompt and code sides so you never bring entire tables into memory.
5. 05
   Correction loop

   If execution returns an SQL error, send the error message back to the model and ask for a corrected query (1 or 2 attempts max).

-- À exécuter une fois par un admin
CREATE ROLE readonly WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE analytics TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Les tables créées plus tard héritent aussi du SELECT
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

The correction loop significantly improves the success rate. Many errors are trivial (a slightly incorrect column name, a date function specific to the dialect), and the model fixes them on the second attempt if it sees the engine's error message.

def answer(question, schema, max_retries=2):
    sql = generate_sql(question, schema)
    for attempt in range(max_retries + 1):
        try:
            return sql, run_query(sql)
        except Exception as e:
            if attempt == max_retries:
                raise
            # On renvoie l'erreur au modèle pour correction
            fix_prompt = (
                f"La requête suivante a échoué :\n{sql}\n\n"
                f"Erreur PostgreSQL : {e}\n"
                f"Corrige la requête. SQL uniquement."
            )
            resp = ollama.chat(model=MODEL, messages=[
                {"role": "system", "content": SYSTEM_PROMPT.format(schema=schema)},
                {"role": "user", "content": fix_prompt},
            ], options={"temperature": 0})
            sql = clean_sql(resp["message"]["content"])

#Which local models excel at SQL

SQL is a coding task: specialized "coder" models clearly outperform general-purpose models of the same size. In 2026, a recent coding model such as Qwen3-Coder 30B-A3B changes everything—small 2B to 8B models can cobble together simple queries but fail as soon as multiple joins or a windowed aggregation are required. (Codestral 22B, long cited for SQL, is now under a non-production license: exclude it from enterprise use.)

Qwen3-Coder 30B-A3B

The default choice for 2026. A code MoE with 3B active parameters: fast, 256k context for large schemas, ≈19 GB in Q4 on a RTX 4090 or a recent Mac. Apache 2.0 license.

Devstral 24B

Specialized in code, Mistral AI (Apache 2.0), ≈14 GB in Q4 — it fits on a 16 GB card such as RTX 4080. The best compromise for SQL on a modest workstation.

Qwen 3.8 27B

Recent general-purpose model with strong reasoning on complex joins (≈18 GB, 262k context, vision). Set its reasoning effort to “low”: on a task as structured as SQL, it tends to overthink with the default setting.

Small models 2B–8B (Qwen 3.5 4B, Granite 4.2 8B)

For very simple schemas and direct questions only. Avoid it as soon as the database has nontrivial relationships.

#Troubleshooting

The model invents columns

The schema is incomplete or too large. Reduce it to the relevant tables and add business comments for ambiguous columns.

Responses with text around the SQL

Strengthen the instruction “SQL only, no explanation” and keep Markdown fence cleanup in clean_sql.

Date function errors

Specify the dialect in the system prompt (PostgreSQL and MySQL differ on DATE_TRUNC, YEAR(), etc.). The correction loop handles the rest.

Slow or timing-out requests

statement_timeout is doing its job. Add “always filter on a reasonable date range” to the prompt for large tables.

“Connection refused” Ollama

The daemon isn't running. Check « ollama ps » and make sure the service is listening on http://localhost:11434.

#Go further

Text-to-SQL reuses several building blocks already covered on the site. These guides build directly on this one:

Integrate Ollama into a Python application via the REST API

To expose this pipeline behind a FastAPI API and handle streaming and JSON mode.

Function calling and structured JSON outputs with Ollama

An alternative for reliably structuring the output (query + explanation), instead of cleaning up text afterward.

Choose your quantization (Q4, Q5, Q8, FP16)

To weigh SQL model size against the VRAM available on your card.