How to Query Your Database with a Local LLM (Text-to-SQL)

A product manager at Acme Shop asks, “How many people added something to the cart last week but did not buy?” Today that question waits in your queue for two days. With text-to-SQL, a language model turns the sentence into a query, and the answer comes back in seconds.

The demo is easy. A model writes plausible SQL for a clean schema most of the time. The failures are the problem: wrong columns, double-counted users, a query that scans the whole table, or, if you were careless, a statement that deletes data. Text-to-SQL is an engineering problem about containing a fallible author, not a prompting trick.

In this article you will build a small text-to-SQL service in Python. It sends a question and your schema description to a local model through Ollama, checks the SQL that comes back, and runs it through a database role that physically cannot write. It runs on your machine, so your event data never leaves it. You will also build a tiny evaluation set, because you cannot trust what you do not measure.

It assumes the tables from PostgreSQL schema design for analytics. It does not cover retrieval over documents (see the RAG pipeline article) or multi-step agents.

What You Need for Text-to-SQL with a Local LLM

The stack follows the series rules: Python 3.12 or newer, a local model served by Ollama, and PostgreSQL holding the Acme Shop tables. For the model, use an instruction-tuned model of 7B to 14B parameters, pulled through Ollama. Check the current Ollama model library for options, because names and sizes change quickly.

Install the libraries once:

pip install "psycopg[binary]" requests sqlglot

The pipeline has five steps. Each one exists because a specific thing goes wrong without it.

question
   |
   v
[1] prompt = glossary + schema + rules + today's date
   |
   v
[2] local model via Ollama (JSON output, temperature 0)
   |
   v
[3] validate SQL: one SELECT, allowed tables, no dangerous functions
   |
   v
[4] execute as llm_reader: read-only, timeout, row cap
   |
   v
[5] return SQL + rows (user can inspect), log everything

Step 1: Lock Down the Database First

Start with the database, not the model. Assume the model will eventually produce a bad statement, then arrange things so a bad statement cannot do harm. The PostgreSQL GRANT documentation covers the privilege model used here. The views below read the events table and the daily_pageviews rollup, both defined in the schema design article, so create them first.

-- Run as an admin. Views expose only what the model should see.
CREATE SCHEMA llm;

CREATE VIEW llm.events AS
SELECT event_id, event_name, occurred_at, anonymous_id, user_id,
       session_id, page_url, referrer, properties
FROM public.events;   -- note: no user_agent, no raw IPs

CREATE VIEW llm.daily_pageviews AS
SELECT day, page_path, views, visitors
FROM public.daily_pageviews;

CREATE ROLE llm_reader LOGIN PASSWORD 'change-me' CONNECTION LIMIT 5;
GRANT USAGE ON SCHEMA llm TO llm_reader;
GRANT SELECT ON llm.events, llm.daily_pageviews TO llm_reader;

ALTER ROLE llm_reader SET search_path = llm;
ALTER ROLE llm_reader SET statement_timeout = '5s';
ALTER ROLE llm_reader SET default_transaction_read_only = on;
ALTER ROLE llm_reader SET idle_in_transaction_session_timeout = '10s';
ALTER ROLE llm_reader SET timezone = 'UTC';

Several decisions hide in this block. Views run with the privileges of their owner, so llm_reader needs no access to public.events at all. The views leave out user_agent, which keeps raw browser strings out of any prompt and any log.

The role can only select from two relations. Even if validation has a bug, the worst case is a slow read that times out after five seconds.

The trade-off is maintenance. When you add a column to events, the view does not change until you update it, which is intentional. Treat the view definition as the contract between your data and the model. If your tables are partitioned as in the tuning article, the view still works, and the timeout protects you from queries that skip the time filter and scan every partition.

Note that default_transaction_read_only is a default, not a lock. A session could run SET to change it, which is why the table-level grants matter more: without INSERT or UPDATE privileges, a write fails whatever the setting says.

Step 2: Describe the Schema Like a Teammate Would

A raw CREATE TABLE dump is a weak prompt for text-to-SQL. The model sees column names, but it cannot see that user_id is null until signup, that visitors means distinct anonymous_id, or that purchases live in properties. Write those facts down once, in plain language.

# prompt.py
from datetime import datetime, timezone

SCHEMA_DOC = """
You write PostgreSQL SELECT queries for Acme Shop analytics. Timestamps are UTC.

Tables (schema llm):
  events(event_id uuid, event_name text, occurred_at timestamptz,
         anonymous_id text, user_id text NULL, session_id text,
         page_url text, referrer text, properties jsonb)
  daily_pageviews(day date, page_path text, views bigint, visitors bigint)

Valid event_name values: page_view, button_click, form_submit,
  signup_completed, add_to_cart, purchase_completed.

Definitions:
  - A visitor is a distinct anonymous_id. A known user is a non-null user_id.
  - A session is a distinct session_id.
  - Revenue is (properties->>'amount_cents')::bigint / 100.0 on
    purchase_completed events only.
  - user_id is NULL before signup or login.

Rules:
  - Use only the tables above. Output exactly one SELECT statement.
  - Always filter events by occurred_at. Use half-open ranges
    (occurred_at >= start AND occurred_at < end).
  - If the question names no time period, use the last 30 days and
    say so in reason.
  - Prefer daily_pageviews for page view totals by day.
  - If the question cannot be answered with these tables, set
    cannot_answer to true and explain in reason.
"""

EXAMPLES = """
Q: How many page views did we get yesterday?
A: SELECT count(*) AS page_views FROM events
   WHERE event_name = 'page_view'
     AND occurred_at >= date_trunc('day', now()) - interval '1 day'
     AND occurred_at <  date_trunc('day', now());

Q: Which sessions added to cart this week but did not purchase?
A: SELECT DISTINCT c.session_id FROM events c
   WHERE c.event_name = 'add_to_cart'
     AND c.occurred_at >= date_trunc('week', now())
     AND NOT EXISTS (SELECT 1 FROM events p
                     WHERE p.session_id = c.session_id
                       AND p.event_name = 'purchase_completed');
"""

def system_prompt():
    today = datetime.now(timezone.utc).date().isoformat()
    return f"{SCHEMA_DOC}\nToday is {today} (UTC).\n\nExamples:\n{EXAMPLES}"

Three details do most of the work. The definitions section prevents the classic metric mix-up where the model counts rows when you meant visitors. The date line fixes “last week” and “yesterday”, which the model otherwise guesses from its training data. The examples teach your house style, including the half-open time range that avoids double-counting the boundary instant.

Keep the prompt short. Small models lose track in long prompts, and two to four good examples beat twenty mediocre ones. If you have dozens of tables, do not paste them all. Retrieve only the relevant table descriptions for each question, which is a retrieval problem covered in the RAG article.

Step 3: Call the Local Model for Structured Output

Ollama serves a local HTTP API. The Ollama API documentation describes POST /api/chat, a format field that accepts a JSON schema, and a stream flag. Asking for JSON removes a whole class of failures, such as markdown fences, chatty preambles and trailing explanations.

# llm.py
import json, os
import requests
from prompt import system_prompt

OLLAMA_URL = os.environ.get("OLLAMA_URL", "http://localhost:11434")
MODEL = os.environ["OLLAMA_MODEL"]   # an instruction-tuned 7B to 14B model

RESPONSE_SCHEMA = {
    "type": "object",
    "properties": {
        "sql": {"type": "string"},
        "cannot_answer": {"type": "boolean"},
        "reason": {"type": "string"},
    },
    "required": ["sql", "cannot_answer"],
}

def generate_sql(question, history=None):
    messages = [{"role": "system", "content": system_prompt()}]
    messages += history or []
    messages.append({"role": "user", "content": question})
    resp = requests.post(
        f"{OLLAMA_URL}/api/chat",
        json={
            "model": MODEL,
            "messages": messages,
            "stream": False,
            "format": RESPONSE_SCHEMA,
            "options": {"temperature": 0},
        },
        timeout=120,
    )
    resp.raise_for_status()
    return json.loads(resp.json()["message"]["content"])

Set temperature to 0. You want the most likely query, not a creative one, and it makes failures reproducible. Reproducibility matters when you debug why one question went wrong. Also note that “reproducible” has limits: different hardware or model builds can still produce different text, so log the model name with every result.

Structured output guarantees shape, not correctness. A valid JSON object can contain a wrong query. Every later step exists for that reason.

Step 4: Validate the SQL Before It Runs

Parse the SQL with a real parser. Regexes that look for “DROP” miss SELECT pg_sleep(100) and let through creative variants. The sqlglot library parses many dialects, including PostgreSQL, and gives you a syntax tree to inspect. See the sqlglot repository for its API.

# validate.py
import sqlglot
from sqlglot import exp

ALLOWED_TABLES = {"events", "daily_pageviews"}
ALLOWED_SCHEMAS = {"", "llm"}
BLOCKED_FUNCTIONS = {"set_config", "current_setting", "dblink"}
BLOCKED_PREFIXES = ("pg_", "lo_", "query_to_", "table_to_", "cursor_to_",
                    "schema_to_", "database_to_")
WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge)

class Rejected(ValueError):
    pass

def func_name(node):
    if isinstance(node, exp.Anonymous):
        return str(node.name).lower()
    return node.sql_name().lower()

def validate(sql):
    try:
        statements = sqlglot.parse(sql, dialect="postgres")
    except sqlglot.errors.ParseError as err:
        raise Rejected(f"SQL does not parse: {err}")
    statements = [s for s in statements if s is not None]
    if len(statements) != 1:
        raise Rejected("exactly one statement is allowed")
    tree = statements[0]
    if not isinstance(tree, exp.Select):
        raise Rejected("only SELECT statements are allowed")
    if tree.find(exp.Into) or tree.find(exp.Lock):
        raise Rejected("SELECT INTO and row locks are not allowed")
    if tree.find(*WRITE_NODES):
        raise Rejected("data-modifying statements are not allowed, even in a CTE")
    ctes = {c.alias.lower() for c in tree.find_all(exp.CTE)}
    for table in tree.find_all(exp.Table):
        name = table.name.lower()
        if name in ctes and not table.db:
            continue
        if table.db.lower() not in ALLOWED_SCHEMAS or name not in ALLOWED_TABLES:
            raise Rejected(f"table not allowed: {table.sql()}")
    for node in tree.find_all(exp.Func):
        name = func_name(node)
        if name.startswith(BLOCKED_PREFIXES) or name in BLOCKED_FUNCTIONS:
            raise Rejected(f"function not allowed: {name}")
    return tree.sql(dialect="postgres")

I ran this validator against a set of hostile inputs (a current sqlglot release on Python 3.13). It rejects these inputs:

• SELECT 1; DROP TABLE events (two statements)
• DELETE FROM events (not a SELECT)
• SELECT * FROM pg_catalog.pg_user (schema not allowed)
• SELECT pg_sleep(100) and SELECT pg_read_file('/etc/passwd') (blocked function prefix)
• SELECT * INTO t FROM events and SELECT * FROM events FOR UPDATE

A plain aggregate over events and a CTE query both pass.

My first version of the validator had two holes, which is why the code above has the WRITE_NODES check and the prefix list. It accepted WITH x AS (DELETE FROM events RETURNING *) SELECT * FROM x, because the outer statement is a SELECT and events is an allowed table. It also accepted query_to_xml, which runs a SQL string passed as an argument. The read-only role would have stopped both, which is the point of layering, but each hole shows why you test a validator with attacks and not only with good queries.

One design choice deserves attention. The function returns tree.sql(...), the re-rendered statement, and the caller must execute that string, not the original. Executing the normalized output closes a gap where your parser and the database disagree about what the text means. That kind of parser differential is a common source of bypasses.

The validator is deliberately strict. A top-level UNION is rejected because it is not a plain SELECT node, so legitimate queries sometimes fail. Loosen rules one at a time, with a test for each.

The cost of a false rejection is a retry. The cost of a false accept is an incident.

Step 5: Execute With a Row Cap and Retry on Errors

Now connect the pieces. The runner wraps the validated query in an outer LIMIT, executes it through llm_reader, and feeds validation or database errors back to the model for up to two repair attempts. The psycopg 3 driver is documented in the psycopg documentation.

# ask.py
import os, sys, json, time
import psycopg
from llm import generate_sql, MODEL
from validate import validate, Rejected

DSN = os.environ["LLM_READER_DSN"]   # postgresql://llm_reader:...@localhost/acme
ROW_CAP = 1000
MAX_ATTEMPTS = 3

def run_query(sql):
    capped = f"SELECT * FROM ({sql}) AS q LIMIT {ROW_CAP}"
    with psycopg.connect(DSN) as conn:
        conn.read_only = True
        with conn.cursor() as cur:
            cur.execute(capped)
            cols = [d.name for d in cur.description]
            return cols, cur.fetchall()

def ask(question):
    history = []
    for attempt in range(1, MAX_ATTEMPTS + 1):
        out = generate_sql(question, history)
        if out.get("cannot_answer"):
            return {"status": "cannot_answer", "reason": out.get("reason", "")}
        try:
            safe_sql = validate(out["sql"])
            cols, rows = run_query(safe_sql)
            return {"status": "ok", "sql": safe_sql, "columns": cols,
                    "rows": rows, "attempts": attempt, "model": MODEL,
                    "note": out.get("reason", "")}
        except (Rejected, psycopg.Error) as err:
            history += [
                {"role": "assistant", "content": json.dumps(out)},
                {"role": "user", "content":
                    f"That query failed: {err}. Return a corrected query."},
            ]
    return {"status": "failed", "reason": "no valid query after retries"}

if __name__ == "__main__":
    result = ask(" ".join(sys.argv[1:]))
    print(json.dumps(result, indent=2, default=str))

Run it with OLLAMA_MODEL and LLM_READER_DSN set, for example python ask.py "Revenue by day for the last 7 days". Because the code returns the SQL with the rows, every answer is auditable. Show both in your UI. A number without its query invites blind trust.

The retry loop is cheap and surprisingly effective for syntax errors and misspelled columns, because the database error tells the model exactly what is wrong. It does not fix a wrong interpretation. If the model counts rows when you meant visitors, the query runs fine and returns a confident wrong number. Retries cannot detect that, which brings us to evaluation.

Measure Accuracy with a Golden Set

The only honest way to know whether your text-to-SQL setup works is to test it on questions with known answers. Write 20 to 50 questions that your team actually asks, along with a reference query you have checked by hand. Compare result sets, not SQL text, because many different queries are correct.

# evaluate.py
import psycopg, os
from ask import ask

DSN = os.environ["LLM_READER_DSN"]

LAST_7_DAYS = ("occurred_at >= date_trunc('day', now()) - interval '7 days' "
               "AND occurred_at < date_trunc('day', now())")

GOLDEN = [
    ("How many page views did we get in the last 7 complete days, not counting today?",
     "SELECT count(*) FROM events WHERE event_name = 'page_view' AND " + LAST_7_DAYS),
    ("How many distinct visitors did we have in the last 7 complete days, not counting today?",
     "SELECT count(DISTINCT anonymous_id) FROM events WHERE " + LAST_7_DAYS),
    ("How many purchases did we have in the last 7 complete days, not counting today?",
     "SELECT count(*) FROM events WHERE event_name = 'purchase_completed' AND " + LAST_7_DAYS),
]

def rows_for(sql):
    with psycopg.connect(DSN) as conn, conn.cursor() as cur:
        cur.execute(sql)
        return sorted(map(tuple, cur.fetchall()))

passed = 0
for question, reference in GOLDEN:
    result = ask(question)
    ok = result["status"] == "ok" and sorted(map(tuple, result["rows"])) == rows_for(reference)
    passed += ok
    print("PASS" if ok else "FAIL", question)
print(f"{passed}/{len(GOLDEN)} correct")

This is called execution accuracy, and it is the standard way text-to-SQL research scores models. Run it whenever you change the model, the prompt or the schema. Record the pass rate with the model name.

Run it more than once, too: on my machine, two consecutive runs of the same three questions scored 2 of 3 and then 3 of 3, even at temperature 0. A prompt tweak that fixes one question often breaks two others, and only a test set shows that.

Pin the period in each question, as above. My first golden set asked “How many purchases have we had?” with no period, and the local model I tested failed it. My prompt said to always filter by occurred_at, so the model invented a one-day range for an all-time question.

The query ran, returned a number and was wrong. The prompt now sets a default period and asks the model to state it in reason, which ask.py returns as note.

Add every real failure to the set. In my experience building reporting tools, the questions that matter are the ones that already burned someone, such as a “users” question that silently counted anonymous browsers. Your golden set becomes the written memory of those lessons.

Failure Modes You Will See

Expect these in roughly this order:

• Ambiguous nouns. “Users” could mean anonymous visitors, signed-up users or buyers. Fix it with the definitions in the prompt, and make the model ask or state its assumption.
• Hallucinated columns. The model invents events.revenue. The database error plus a retry usually fixes it. Persistent cases need a better schema description.
• Invented time ranges. A rule like “always filter by time” makes the model pick dates when the question has none. Give it a default period and have it state the assumption.
• Time zone and boundary errors. “Last week” depends on the week start and on UTC. State the convention in the prompt and test boundary dates.
• Wrong grain. Joining sessions to events and then counting rows inflates results. Examples with count(DISTINCT ...) help, and so does a rule against joins when a single table works.
• Missing time filters. A full scan hits the timeout. The prompt rule and the five second limit contain it, and the table indexes from the tuning article decide how often it hurts.
• Confident wrongness. The query is valid, runs and answers a different question. Only evaluation and visible SQL catch this.

A mistake I have seen in production is handing a text-to-SQL tool to non-technical users with the SQL hidden, then discovering weeks later that a headline number double-counted sessions. The tool was never wrong in a way anyone could see. After that, we always showed the query and a plain-language restatement of what it computed.

Prompt injection through your own data

This pipeline only sends the question and the schema to the model, not result rows, so data cannot inject instructions. The risk appears if you later feed rows back to summarize them. A page_url or a properties value is attacker-controlled text. Treat any row content as data, never as instructions, and keep summarization separate from SQL generation.

Model Size, Speed and Cost

A 7B to 14B local model is enough for single-table questions and simple joins, and weak on multi-step logic. Bigger models improve accuracy, but they need more memory and respond slower on consumer hardware. Latency is usually a few seconds on a decent GPU and longer on CPU only. Measure it on your machine with the Ollama response timing fields before promising anything.

Local hosting trades convenience for control. You pay in hardware and tuning time, and you gain privacy, zero per-query fees and predictable behavior. Hosted models are often more accurate on hard queries, but they send your schema, and sometimes data, to a third party. For event data with user identifiers, that decision belongs to whoever owns privacy at your company.

How Real Systems Do This

Commercial BI tools ship natural-language querying on top of a governed semantic layer. They do not let a model write against raw tables. The model picks from named metrics and dimensions, and the system generates the SQL. That approach avoids the wrong-grain and wrong-definition problems by construction, and it is the direction discussed in the final article of this series.

Open-source projects in this space, such as Vanna and various SQL agents, follow the same shape you built: schema context, generation, execution and feedback. Academic benchmarks like Spider and BIRD score models on held-out databases, and BIRD leads with execution accuracy. Scores on those benchmarks are higher than what you will see on a messy real schema, so trust your own golden set more than a leaderboard.

If you chain these steps into planners, reviewers and report writers, you are building the system in multi-agent systems for data analysis. Keep the guardrails from this article in every agent that touches the database.

Decision Framework

1. Can the data leave your network? If not, use a local model and a local database.
2. Who asks the questions? Analysts who read SQL can use raw output. Non-technical users need a semantic layer, or answers with visible, explained queries.
3. How many tables does the model need? Under about ten, put the descriptions in the prompt. Beyond that, retrieve relevant tables per question.
4. What is the cost of a wrong answer? For exploration it is low. For finance or customer-facing numbers, require human review.
5. Do you have a golden set? If not, write one before you ship.
6. Is the read-only role in place with grants, timeout and row cap? If not, do not connect a model.

When NOT to Use This

• Fixed reports. If the dashboard asks the same ten questions, write the ten queries by hand and run them from the API. Generated SQL adds risk with no benefit, as in the SQL patterns from SQL queries for user analytics.
• Numbers that drive money or compliance. Revenue reporting and regulatory figures need reviewed, version-controlled queries. A model can draft them, but a person must approve them.
• Teams that can buy a governed BI tool. If your company already pays for a BI platform with a semantic layer and a natural-language feature, evaluate that before maintaining your own pipeline.

Common Mistakes

• Connecting the model to an admin or application role, so one bad statement can modify or leak data.
• Trusting regex checks for forbidden keywords, which miss functions such as pg_sleep and comment tricks.
• Running the original SQL string instead of the re-rendered, validated one.
• Dumping the full DDL into the prompt with no definitions, so metrics like visitors and users are guessed.
• Skipping the evaluation set, so a prompt change silently lowers accuracy.
• Hiding the generated SQL from users, which makes confident wrong answers invisible.

Key Takeaways

• Secure the database first: views, a read-only role, grants, a statement timeout and a row cap.
• Write a short text-to-SQL prompt with definitions, rules, today’s date and two to four examples.
• Request JSON output with a schema and set temperature to 0.
• Parse with a real SQL parser, allow-list tables, block dangerous functions, and execute the normalized SQL.
• Feed errors back to the model for a bounded number of retries.
• Score the text-to-SQL system on a golden set using execution accuracy, and rerun it after every change.
• Show users the SQL, because valid-looking wrong answers are the main risk.

FAQ

Can a local LLM write SQL accurately?

For single-table questions and simple joins, a 7B to 14B instruction-tuned model does reasonably well when given a clear schema description. Accuracy drops on multi-step logic and ambiguous metrics. Measure it on your own golden set instead of relying on benchmark scores.

Is text-to-SQL safe to run on a production database?

Only through a dedicated read-only role with table-level grants, a statement timeout and a row cap, preferably on a replica. Validation helps, but the database permissions are the actual boundary.

How do I stop an LLM from running DROP or DELETE?

Do not rely on the model or on keyword filters. Grant the role only SELECT on specific views, parse the SQL and accept a single SELECT statement, and execute the validated version. With no write privileges, a write fails even if everything else breaks.

Why use Ollama for text-to-SQL?

Ollama runs models locally and exposes a simple HTTP API with JSON-schema output, so your schema and queries stay on your machine. The trade-off is hardware requirements and typically lower accuracy than the largest hosted models.

Conclusion

Text-to-SQL can write analytics SQL, but it cannot be trusted to do so correctly or safely on its own. The reliable design contains it: a narrow schema, a read-only role, strict validation, visible queries and a test set that tells you when it drifts.

Rule of thumb: let the model draft the query, let the database enforce the limits, and let a human read the SQL before the number matters. Next, give the model your documents with a RAG pipeline for company data.

Last updated on 9 October 2026.