From the blog

Text-to-SQL in Elixir: Let Users Query Postgres in Plain English, Safely

By Liam Killingback ·

Text-to-SQL in Elixir: Let Users Query Postgres in Plain English, Safely

Every SaaS eventually gets the same request: “Can I just ask it how many orders we shipped to Germany last month?” Building a report for every question does not scale, and handing customers a SQL console is out of the question. Text-to-SQL sits in between. The user types a question, a language model writes the query, your app runs it and shows the answer.

The model part is the easy bit. A decent model writes correct SQL for a well-described schema most of the time. The hard part, and the part almost every tutorial skips, is the boundary around it: what stops a generated query from deleting a table, reading another tenant’s rows or running for ten minutes. Most text-to-SQL guides are written for Python and LangChain, and they treat that boundary as a footnote. This guide does text-to-SQL in Elixir with Phoenix, Ecto and Req, and treats the boundary as the main event.

By the end you will have:

  • a schema description the model can actually use, generated from your Ecto schemas
  • a model call that returns SQL as structured JSON, not prose
  • a three-layer safety boundary: a policy check, a read-only Postgres role, and a read-only transaction with a timeout and a row cap
  • a retry loop that feeds Postgres errors back to the model
  • a LiveView that shows the question, the SQL and the result table

How text-to-SQL works

The whole pipeline is five steps:

  1. Describe the schema. The model cannot query tables it does not know about, so you send it a compact description of the tables and columns it is allowed to use.
  2. Generate. Send the question and the description, get back one SQL statement.
  3. Check. Reject anything that is not a single read query.
  4. Run. Execute it with the least privilege you can arrange, a timeout and a row limit.
  5. Show. Render the rows, and show the SQL too, so the user can see what was asked of the database.

Steps 3 and 4 are where your engineering time should go. Treat the model like an untrusted user who happens to write SQL well. Anything the user types can steer what the model produces (see our post on prompt injection in Elixir for how easily that happens), so “the prompt says only write SELECT queries” is a request, not a control.

Step 1: Describe the schema from your Ecto schemas

You could dump information_schema into the prompt. Do not. It is noisy, it exposes tables you never meant to expose (password hashes, tokens, audit logs) and it burns tokens. Instead, keep an explicit allow list of schemas and generate the description from them, so it never drifts from your code:

defmodule MyApp.TextToSQL.Schema do
  @moduledoc "The tables and columns the model is allowed to know about."

  # Only these. Never users, tokens, or anything with secrets in it.
  @exposed [MyApp.Sales.Order, MyApp.Sales.OrderLine, MyApp.Catalog.Product]

  # Columns that exist but must never reach the model.
  @hidden ~w(internal_notes stripe_customer_id)a

  def describe do
    Enum.map_join(@exposed, "\n\n", &describe_schema/1)
  end

  def tables, do: Enum.map(@exposed, & &1.__schema__(:source))

  defp describe_schema(schema) do
    columns =
      schema.__schema__(:fields)
      |> Enum.reject(&(&1 in @hidden))
      |> Enum.map_join("\n", fn field ->
        source = schema.__schema__(:field_source, field)
        "  #{source} #{format_type(schema.__schema__(:type, field))}"
      end)

    "TABLE #{schema.__schema__(:source)}\n#{columns}"
  end

  defp format_type({:parameterized, {Ecto.Enum, %{on_load: values}}}),
    do: "enum(#{values |> Map.keys() |> Enum.join(", ")})"

  defp format_type({:parameterized, {mod, _}}), do: inspect(mod)
  defp format_type(type), do: to_string(type)
end

(The parameterized type shape has changed between Ecto versions, so check what __schema__(:type, field) returns on yours and adjust format_type/1.)

Two things improve accuracy more than any prompt trick:

  • Add a line of meaning where names are ambiguous. status on an order means nothing to a model until you say the values are pending, paid, shipped and refunded. A short @comments map merged into the description pays for itself fast.
  • Expose views, not raw tables, when the raw shape is awkward. A sales_summary view that already joins orders, lines and products is easier for the model to use correctly, and easier for you to lock down.

Step 2: Generate SQL with structured output

Ask for JSON with a fixed shape so you never have to scrape SQL out of a markdown fence. With OpenAI’s structured outputs and Req:

defmodule MyApp.TextToSQL.Generator do
  alias MyApp.TextToSQL.Schema

  @system """
  You translate questions into a single PostgreSQL SELECT statement.
  Use only the tables and columns listed. Never modify data.
  If the question cannot be answered from these tables, return an empty sql
  string and say why in explanation.
  """

  def generate(question, previous_error \\ nil) do
    body = %{
      model: Application.fetch_env!(:my_app, :sql_model),
      messages: [
        %{role: "system", content: @system <> "\n\n" <> Schema.describe()},
        %{role: "user", content: user_message(question, previous_error)}
      ],
      response_format: %{
        type: "json_schema",
        json_schema: %{
          name: "sql_query",
          strict: true,
          schema: %{
            type: "object",
            properties: %{
              sql: %{type: "string"},
              explanation: %{type: "string"}
            },
            required: ["sql", "explanation"],
            additionalProperties: false
          }
        }
      }
    }

    with {:ok, %{status: 200, body: resp}} <-
           Req.post("https://api.openai.com/v1/chat/completions",
             json: body,
             auth: {:bearer, System.fetch_env!("OPENAI_API_KEY")},
             receive_timeout: 30_000
           ),
         [%{"message" => %{"content" => content}} | _] <- resp["choices"],
         {:ok, %{"sql" => sql, "explanation" => why}} <- Jason.decode(content) do
      {:ok, String.trim(sql), why}
    else
      {:ok, %{status: status}} -> {:error, {:http, status}}
      {:error, reason} -> {:error, reason}
      _ -> {:error, :bad_response}
    end
  end

  defp user_message(question, nil), do: question

  defp user_message(question, error) do
    """
    #{question}

    Your previous query failed with this Postgres error. Fix it:
    #{error}
    """
  end
end

Keep the model name in config. Text-to-SQL is a task where a smaller, cheaper model is often good enough once the schema description is tight, and you will want to change it without a deploy. If you prefer Claude, the same shape works with tool use; our Claude API in Elixir post covers structured output there.

Step 3: The policy check (layer one)

The first layer is a cheap check in Elixir that throws out the obvious problems before anything touches the database. It is not a SQL parser and you should not pretend it is one. Its job is to make the common bad cases fail fast with a clear message:

defmodule MyApp.TextToSQL.Policy do
  @forbidden ~w(insert update delete merge drop alter truncate create grant revoke
                copy call do execute prepare listen notify vacuum reindex cluster
                lock refresh comment security set reset)

  def check(""), do: {:error, :empty}

  def check(sql) do
    stripped = strip_comments(sql)
    normalised = stripped |> String.trim() |> String.trim_trailing(";") |> String.trim()
    lowered = String.downcase(normalised)
    words = Regex.scan(~r/[a-z_]+/, lowered) |> List.flatten() |> MapSet.new()

    cond do
      String.contains?(normalised, ";") ->
        {:error, :multiple_statements}

      not Regex.match?(~r/\A(select|with)\b/, lowered) ->
        {:error, :not_a_read_query}

      bad = Enum.find(@forbidden, &MapSet.member?(words, &1)) ->
        {:error, {:forbidden_keyword, bad}}

      Regex.match?(~r/\bpg_|\binformation_schema\b/, lowered) ->
        {:error, :system_catalog}

      Regex.match?(~r/\b(set_config|current_setting|dblink\w*|lo_\w+)\s*\(/, lowered) ->
        {:error, :forbidden_function}

      true ->
        {:ok, normalised}
    end
  end

  defp strip_comments(sql) do
    sql
    |> String.replace(~r{/\*.*?\*/}s, " ")
    |> String.replace(~r/--[^\n]*/, " ")
  end
end

This will occasionally reject a valid query (a column named comment trips the keyword list, as does a string literal containing delete). That is the right way round: a false rejection costs a retry, a false acceptance could cost you data. Rename the column in a view, or narrow the list once you have seen real traffic.

Note the WITH case. A data-modifying CTE (WITH x AS (DELETE FROM orders RETURNING *) SELECT ...) starts with WITH, which is exactly why the keyword scan runs on the whole statement and not just the first word. And it is why this layer is never the only one.

Step 4: Least privilege in Postgres (layers two and three)

The real guarantees come from Postgres, because Postgres actually parses the SQL.

A role that can only read what you exposed

Create a dedicated role with SELECT on the exposed tables or views and nothing else:

defmodule MyApp.Repo.Migrations.CreateAiReaderRole do
  use Ecto.Migration

  def up do
    execute "CREATE ROLE ai_reader LOGIN PASSWORD 'change-me-in-runtime-config'"
    execute "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ai_reader"
    execute "GRANT USAGE ON SCHEMA public TO ai_reader"
    execute "GRANT SELECT ON orders, order_lines, products TO ai_reader"
    execute "ALTER ROLE ai_reader SET statement_timeout = '5s'"
  end

  def down do
    execute "REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ai_reader"
    execute "REVOKE USAGE ON SCHEMA public FROM ai_reader"
    execute "DROP ROLE ai_reader"
  end
end

(Set the real password with ALTER ROLE from a release task or your infrastructure tooling rather than committing it in a migration.)

Then give it its own Ecto repo, so generated SQL can never run on the connection pool your app writes with:

defmodule MyApp.ReadRepo do
  use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgres, read_only: true
end

# config/runtime.exs
config :my_app, MyApp.ReadRepo,
  url: System.fetch_env!("AI_READER_DATABASE_URL"),
  pool_size: 5

read_only: true removes the write functions from the repo module. It does nothing for raw SQL passed to query/3, which is why the database role matters: even if every Elixir-side check failed, ai_reader has no permission to change anything.

A small pool is deliberate. It caps how many generated queries can run at once, so a burst of curious users cannot starve the rest of the database.

A read-only transaction with a timeout and a row cap

Run each query inside a transaction that is read-only, has its own timeout and wraps the statement in an outer LIMIT:

defmodule MyApp.TextToSQL.Runner do
  alias MyApp.ReadRepo

  @max_rows 200

  def run(sql) do
    ReadRepo.transaction(fn ->
      ReadRepo.query!("SET TRANSACTION READ ONLY")
      ReadRepo.query!("SET LOCAL statement_timeout = '5s'")

      case ReadRepo.query("SELECT * FROM (#{sql}) AS ai_query LIMIT #{@max_rows + 1}") do
        {:ok, %Postgrex.Result{columns: columns, rows: rows}} ->
          {columns, Enum.take(rows, @max_rows), length(rows) > @max_rows}

        {:error, %Postgrex.Error{postgres: %{message: message}}} ->
          ReadRepo.rollback({:postgres, message})

        {:error, other} ->
          ReadRepo.rollback(other)
      end
    end)
  end
end

Three details matter here:

  • SET TRANSACTION READ ONLY has to be the first statement in the transaction, which it is. Postgres then refuses any write, including one hidden inside a CTE, with cannot execute ... in a read-only transaction.
  • Wrapping the query as a subquery enforces the row cap no matter what LIMIT the model wrote, and it fails outright if the model somehow produced something that is not a query. Fetching one extra row tells you whether the result was truncated, so the UI can say so.
  • Postgrex sends queries through the extended protocol, which does not accept several statements in one call. That is a fourth, accidental layer against SELECT 1; DROP TABLE orders. Do not rely on it, but it is nice to have.

Multi-tenant apps: row-level security

If your tables hold several customers’ data, a correct query can still read the wrong customer’s rows. Do not try to solve this by asking the model to add WHERE account_id = 42. Use Postgres row-level security keyed on a session setting, and set it inside the same transaction:

ReadRepo.query!("SELECT set_config('app.account_id', $1, true)", [to_string(account_id)])

With a policy like USING (account_id = current_setting('app.account_id')::bigint) on each exposed table, and RLS enabled for ai_reader, the database filters every row the model’s SQL touches. The true argument makes the setting local to the transaction, so it cannot leak to the next checkout of the connection.

There is one catch, and it is why the policy check blocks set_config and current_setting: a session setting is something the generated SQL could, in principle, change itself. Blocking those functions closes the obvious route. If tenant isolation is a hard requirement, go one step further and give the policy something the query cannot forge, such as a SECURITY DEFINER function that resolves the tenant, or a separate role per tenant for your largest customers.

Step 5: Retry with the error

Models get column names or join keys wrong now and then. Feeding the Postgres error back usually fixes it on the next attempt. Cap the attempts so a hopeless question does not loop:

defmodule MyApp.TextToSQL do
  alias MyApp.TextToSQL.{Generator, Policy, Runner}

  @max_attempts 3

  def ask(question), do: attempt(question, nil, 1)

  defp attempt(_question, last_error, attempt) when attempt > @max_attempts,
    do: {:error, {:gave_up, last_error}}

  defp attempt(question, last_error, attempt) do
    with {:ok, sql, explanation} <- Generator.generate(question, last_error),
         {:ok, safe_sql} <- policy(sql),
         {:ok, {columns, rows, truncated?}} <- Runner.run(safe_sql) do
      {:ok, %{sql: safe_sql, explanation: explanation, columns: columns,
              rows: rows, truncated?: truncated?}}
    else
      {:error, {:postgres, message}} -> attempt(question, message, attempt + 1)
      {:error, {:policy, reason}} -> attempt(question, "Rejected: #{inspect(reason)}", attempt + 1)
      {:error, reason} -> {:error, reason}
    end
  end

  defp policy(sql) do
    case Policy.check(sql) do
      {:ok, safe} -> {:ok, safe}
      {:error, :empty} -> {:error, :unanswerable}
      {:error, reason} -> {:error, {:policy, reason}}
    end
  end
end

An empty sql means the model said the question cannot be answered from the schema. Surface its explanation to the user rather than retrying; it is usually right.

Step 6: The LiveView

Generation takes a second or two and the query might take a few more, so run the work in a task and keep the LiveView responsive. This assumes a {Task.Supervisor, name: MyApp.TaskSupervisor} child in your application’s supervision tree:

defmodule MyAppWeb.AskLive do
  use MyAppWeb, :live_view

  def mount(_params, _session, socket) do
    {:ok, assign(socket, question: "", result: nil, error: nil, loading: false, task: nil)}
  end

  def handle_event("ask", %{"question" => question}, socket) do
    task = Task.Supervisor.async_nolink(MyApp.TaskSupervisor, fn -> MyApp.TextToSQL.ask(question) end)
    {:noreply, assign(socket, question: question, loading: true, error: nil, task: task.ref)}
  end

  def handle_info({ref, outcome}, %{assigns: %{task: ref}} = socket) do
    Process.demonitor(ref, [:flush])

    socket =
      case outcome do
        {:ok, result} -> assign(socket, result: result)
        {:error, reason} -> assign(socket, result: nil, error: humanise(reason))
      end

    {:noreply, assign(socket, loading: false, task: nil)}
  end

  def handle_info({:DOWN, ref, :process, _pid, _reason}, %{assigns: %{task: ref}} = socket) do
    {:noreply, assign(socket, loading: false, task: nil, error: "Something went wrong. Try rephrasing.")}
  end

  defp humanise(:unanswerable), do: "That question cannot be answered from the data available."
  defp humanise({:gave_up, _}), do: "I could not write a working query for that. Try being more specific."
  defp humanise(_), do: "Something went wrong. Try again in a moment."
end
<form phx-submit="ask">
  <input name="question" value={@question} placeholder="How many orders shipped to Germany last month?" />
  <button disabled={@loading}><%= if @loading, do: "Thinking...", else: "Ask" %></button>
</form>

<p :if={@error} class="text-red-600"><%= @error %></p>

<div :if={@result}>
  <details>
    <summary>Show the SQL</summary>
    <pre><code><%= @result.sql %></code></pre>
  </details>

  <table>
    <thead><tr><th :for={col <- @result.columns}><%= col %></th></tr></thead>
    <tbody>
      <tr :for={row <- @result.rows}><td :for={cell <- row}><%= cell %></td></tr>
    </tbody>
  </table>
  <p :if={@result.truncated?}>Showing the first 200 rows.</p>
</div>

Always offer the SQL. Users who know SQL will check it, and showing your working is what makes people trust the numbers. If you would like the model to also write a one-sentence answer (“You shipped 412 orders to Germany in September”), make that a second call over the result rows, not part of the first; mixing the two makes the SQL worse.

Testing the boundary

The boundary deserves adversarial tests that never call a model. Feed the policy and the runner the queries an attacker would want the model to write:

defmodule MyApp.TextToSQL.PolicyTest do
  use ExUnit.Case, async: true
  alias MyApp.TextToSQL.Policy

  for sql <- [
        "DELETE FROM orders",
        "SELECT 1; DROP TABLE orders",
        "WITH gone AS (DELETE FROM orders RETURNING *) SELECT * FROM gone",
        "SELECT * FROM pg_shadow",
        "/* harmless */ UPDATE products SET price = 0",
        "SELECT set_config('app.account_id', '1', false)"
      ] do
    test "rejects #{sql}" do
      assert {:error, _} = Policy.check(unquote(sql))
    end
  end

  test "accepts a plain aggregate" do
    assert {:ok, _} = Policy.check("SELECT count(*) FROM orders WHERE status = 'shipped';")
  end
end

Then write one integration test per layer that bypasses the layer above it: call Runner.run/1 directly with an UPDATE hidden in a CTE and assert it fails inside the read-only transaction, and connect as ai_reader and assert a SELECT on users is refused. If a layer only works because the one above it caught the problem first, you do not have three layers.

For the model side, a small eval set of real questions with expected answers catches regressions when you change the prompt or the model. Our LLM evals in ExUnit post shows how to keep those out of the default test run.

Honest tradeoffs

  • It is not always right. A query can run cleanly and still answer a slightly different question than the one asked (“orders” versus “order lines”). Showing the SQL helps, a curated view layer helps more, and for numbers that end up in board decks a human should check them.
  • Fixed reports are often better. If ninety percent of questions are the same five, build those five as normal pages. Text-to-SQL earns its place on the long tail.
  • Cost and latency are real but small. One generation call per question, plus retries. With a compact schema description the prompt stays small, and a cheaper model is often enough.
  • The policy check is a filter, not authorisation. Who may see which rows is decided by the database role and row-level security, never by the prompt and never by a regex.

Skip the plumbing

If you want this working today instead of building it, the PHX Data Agent Starter ships it: a chat widget in Phoenix LiveView that answers questions about your Postgres data, a SQL policy that only lets SELECT and WITH through, execution inside a READ ONLY transaction, a retry loop that corrects failed SQL, and adversarial tests for writes, DDL, statement stacking and comment-hidden mutations. It also exposes the same tool over the Model Context Protocol, so Claude Desktop and other MCP clients can query your data through the same boundary (our guide to building an MCP server in Elixir explains how that side works). It runs with local models through Ollama, so your data does not have to leave your network.

Summary

Text-to-SQL in Elixir comes down to one generation call and a lot of care around it:

  1. Describe an allow-listed set of tables, generated from your Ecto schemas, with a line of meaning where names are vague.
  2. Ask for SQL as structured JSON, with the model name in config.
  3. Reject anything that is not one read query, cheaply, in Elixir.
  4. Run it as a dedicated read-only role, in a READ ONLY transaction, with a timeout, an outer row cap and row-level security for tenants.
  5. Retry with the Postgres error, a few times at most.
  6. Show the SQL alongside the result.

Phoenix makes the last mile pleasant: the work runs in a supervised task, the LiveView stays responsive, and the whole feature is a few hundred lines you can read in one sitting.

If you are planning more than one Phoenix product, the Builder Pass gives you every PhxTemplates starter, including the AI ones, for a single price.

Back to blogs