Text-to-SQL en local : interroger sa base de données en langage naturel

#Pourquoi faire du text-to-SQL avec un LLM local

Les solutions cloud de text to SQL (assistants BI, copilotes de data warehouse) envoient votre schéma — noms de tables, de colonnes, parfois des échantillons de lignes — à un serveur tiers. Pour une base client, RH ou financière, c'est souvent rédhibitoire : le schéma seul révèle déjà la structure de votre métier, et les échantillons contiennent des données personnelles.

Un LLM local règle ce problème à la racine : le modèle tourne sur votre machine via Ollama, le schéma reste en mémoire locale, et la requête générée s'exécute contre votre base sans qu'aucun octet ne transite par internet. C'est aussi gratuit à l'usage et indépendant de toute limite de débit d'API.

Confidentialité

Schéma et données ne quittent jamais votre réseau — conformité RGPD/secret des affaires facilitée.

Coût

Aucun coût par requête. Un data analyst peut itérer des centaines de fois sans facture.

Accessibilité

Des utilisateurs métier qui ne connaissent pas SQL interrogent la base en langage naturel.

Contrôle

Vous décidez du modèle, du prompt, et des garde-fous — pas de boîte noire distante.

#Comment ça marche concrètement

Le principe du text to SQL avec un LLM tient en trois temps. D'abord on décrit le schéma de la base au modèle (le DDL des tables pertinentes). Ensuite on lui transmet la question de l'utilisateur avec une consigne stricte : produire uniquement une requête SQL pour le dialecte cible. Enfin on récupère la requête, on la valide, et on l'exécute en lecture seule.

1. 01
   Introspection du schéma

   On extrait la structure des tables (colonnes, types, clés) depuis la base — automatiquement plutôt qu'à la main, pour rester synchronisé.
2. 02
   Construction du prompt

   On assemble un prompt système contenant le dialecte SQL, le schéma pertinent et les règles (SELECT uniquement, LIMIT obligatoire, pas de commentaires).
3. 03
   Génération

   Le LLM local renvoie une requête. On la nettoie (retrait des balises Markdown ```sql éventuelles).
4. 04
   Validation + exécution

   On vérifie que c'est bien un SELECT, on l'exécute via un rôle base de données en lecture seule, on renvoie les lignes.

#Prérequis

Ollama installé

Le daemon doit écouter sur http://localhost:11434. Vérifiez avec « ollama ps ».

Un modèle capable

Un modèle 14B+ orienté code donne de bien meilleurs résultats en SQL qu'un 3B généraliste (voir la section modèles).

Python 3.10+

Avec le client base de données adapté : psycopg2-binary (PostgreSQL) ou PyMySQL (MySQL).

Un accès base en lecture seule

Idéalement un rôle SQL dédié qui ne peut faire que des SELECT — le garde-fou le plus important.

# Récupérer un modèle adapté au SQL
ollama pull qwen2.5-coder:14b

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

#Donner le schéma de sa base au modèle

C'est l'étape qui détermine 80 % de la qualité du résultat. Le modèle ne peut générer une requête juste que s'il connaît les noms exacts des tables et colonnes, leurs types, et les relations entre elles. Deux approches : coller le DDL brut, ou introspecter la base pour construire une description compacte.

Pour une petite base (moins d'une vingtaine de tables), on peut tout injecter. Au-delà, le schéma dépasse le contexte utile et noie le modèle : il faut alors sélectionner les tables pertinentes pour la question (via une première passe de recherche ou un mapping métier). Voici une introspection PostgreSQL qui produit un schéma lisible par le LLM.

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)

#Pipeline Python complet avec Ollama

Voici un pipeline minimal mais fonctionnel : schéma → prompt → génération → nettoyage → validation → exécution. Il utilise le client Python officiel d'Ollama et un rôle base de données en lecture seule.

import re
import ollama
import psycopg2
import sqlparse

MODEL = "qwen2.5-coder:14b"

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(";") + ";"

La partie exécution sépare volontairement la validation de l'appel base. On refuse tout ce qui n'est pas un unique SELECT avant même d'ouvrir le curseur.

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)

#Fiabiliser et sécuriser le SQL généré

C'est la section qui distingue une démo d'un déploiement réel. Un LLM peut générer une requête destructrice si on le lui demande — ou par accident via une injection dans la question. La défense ne doit jamais reposer sur le seul prompt : elle se joue en profondeur, côté base de données.

1. 01
   Rôle base en lecture seule (défense principale)

   Créez un rôle SQL qui ne possède QUE le privilège SELECT. Même si le modèle génère un DROP TABLE, la base le refuse. C'est le seul garde-fou vraiment fiable : « GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; » et rien d'autre.
2. 02
   Validation applicative

   En amont, parsez le SQL avec sqlparse et rejetez tout ce qui n'est pas un unique SELECT. Double barrière avec le rôle base.
3. 03
   Timeout de requête

   SET statement_timeout empêche une requête mal formée (produit cartésien sur des millions de lignes) de saturer la base.
4. 04
   LIMIT forcé

   Imposez un LIMIT côté prompt ET côté code, pour ne jamais ramener des tables entières en mémoire.
5. 05
   Boucle de correction

   Si l'exécution renvoie une erreur SQL, renvoyez le message d'erreur au modèle et demandez une requête corrigée (1 ou 2 tentatives 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;

La boucle de correction améliore nettement le taux de réussite. Beaucoup d'erreurs sont triviales (nom de colonne légèrement faux, fonction de date propre au dialecte) et le modèle les corrige au second essai s'il voit le message d'erreur du moteur.

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"])

#Quels modèles locaux excellent en SQL

Le SQL est une tâche de code : les modèles spécialisés « coder » surclassent nettement les généralistes de même taille. En pratique, viser au moins 14B change tout — les modèles 3B à 7B bricolent des requêtes simples mais échouent dès qu'il faut plusieurs jointures ou une agrégation fenêtrée.

Qwen2.5-Coder 14B / 32B

L'excellent choix par défaut. Le 14B (≈9 Go en Q4) tourne sur une RTX 4070/4080 ; le 32B (≈19 Go) sur une RTX 4090 ou un Mac M4 Pro et gère les schémas complexes.

Codestral / Mistral 22B+

Très solide en SQL multi-dialecte, bon compromis qualité/VRAM sur cartes 24 Go.

Llama 3.x 70B

Généraliste puissant si vous avez la VRAM (≈40 Go en Q4). Excellent raisonnement sur les jointures, plus lourd à héberger.

Modèles 3B–7B

Pour des schémas très simples et des questions directes uniquement. À éviter dès que la base a des relations non triviales.

#Dépannage

Le modèle invente des colonnes

Le schéma est incomplet ou trop gros. Réduisez aux tables pertinentes et ajoutez des commentaires métier sur les colonnes ambiguës.

Réponses avec du texte autour du SQL

Renforcez la consigne « SQL uniquement, aucune explication » et gardez le nettoyage des fences Markdown dans clean_sql.

Erreurs de fonction de date

Précisez le dialecte dans le prompt système (PostgreSQL vs MySQL diffèrent sur DATE_TRUNC, YEAR(), etc.). La boucle de correction rattrape le reste.

Requêtes lentes ou qui timeout

Le statement_timeout fait son travail. Ajoutez « toujours filtrer sur une plage de dates raisonnable » au prompt pour les grosses tables.

« Connection refused » Ollama

Le daemon n'est pas lancé. Vérifiez « ollama ps » et que le service écoute sur http://localhost:11434.

#Pour aller plus loin

Le text-to-SQL réutilise plusieurs briques déjà couvertes sur le site. Ces guides prolongent celui-ci :

Intégrer Ollama dans une application Python via l'API REST

Pour exposer ce pipeline derrière une API FastAPI, gérer le streaming et le JSON mode.

Function calling et sorties JSON structurées avec Ollama

Une alternative pour structurer la sortie (requête + explication) de façon garantie plutôt que par nettoyage de texte.

Choisir sa quantification (Q4, Q5, Q8, FP16)

Pour arbitrer entre taille de modèle SQL et VRAM disponible sur votre carte.