Tutorial: build an NL2SQL endpoint on Postgres

An endpoint that receives a natural language question, transforms it into a validated and bounded SQL query, then returns the result. The engine never executes generated SQL without control: each query goes through syntactic validation before reaching the database.

The question goes through a configured LLM — OpenAI, Anthropic (Claude) or Gemini, the three native providers — which generates a candidate SQL. This SQL is never executed as is: it passes through a validator which parses its syntax tree (sqlparser), only allows simple SELECT queries, and adds a bounded LIMIT if one is missing.

The validator explicitly rejects CTE/WITH, subqueries, UNIONs, locking clauses (FOR UPDATE), and any function outside of a whitelist (count, sum, avg, min, max, lower, upper, coalesce, date_trunc, now). Multi-table joins are supported.

Question→LLM (OpenAI/Claude/Gemini)→AST validation (sqlparser)→Bounded LIMIT→SELECT execution

The queried diagram is never provided by your request: it is introspected from the real base of the project. A schema, allowed_schema or schema_context field sent in the request body is explicitly refused (400 error) rather than silently ignored — the server alone decides what actually exists.

With the JavaScript SDK, the Aurabase client exposes aura.ai.nl2sql(). The signature is nl2sql(question, options): the schema is not part of it, it is introspected on the server side.

import { aura } from '@/lib/aurabase'

export async function POST(req: Request) {
  const { question } = await req.json()

  const { data, error } = await aura.ai.nl2sql(
    question,
    { limit: 50 }
  )

  if (error) return Response.json({ error }, { status: 400 })
  return Response.json(data)
}

In raw HTTP, the endpoint is POST /v1/ai/{project_id}/nl2sql, authenticated by the project API key.

curl -X POST https://<votre-gateway>/v1/ai/<project_id>/nl2sql \
  -H "apikey: <votre-cle-api>" \
  -H "Content-Type: application/json" \
  -d '{
    "question": "Combien de commandes ont été passées ce mois-ci par des clients premium ?",
    "limit": 50
  }'

Question sent: “How many orders were placed this month by premium customers? ". Here is the form of the SQL actually rendered (table names and columns depend on your schema):

{
  "data": {
    "sql": "SELECT count(*) FROM orders WHERE customer_plan = 'premium' AND created_at >= date_trunc('month', now()) LIMIT 50",
    "explanation": "Compte les commandes de ce mois pour les clients premium.",
    "confidence": 0.85,
    "tables": ["orders"],
    "columns": ["customer_plan", "created_at"],
    "limit": 50,
    "limit_injected": false
  },
  "meta": null
}

limit_injected indicates whether the LIMIT comes from the template or was added by the server. confidence is a heuristic on the form of the response (well-formed SQL block or not) — not a measure of semantic correctness of the generated SQL. A poorly worded question returns an explicit error rather than hallucinated SQL: for example, if the generated SQL queries a table not in your schema, the message names the tables actually available.

Three checks before deploying: is the row ceiling (LIMIT) adapted to your volume, does the Postgres role used by the engine remain restricted to the project schema, and do the sensitive tables have an active RLS policy — NL2SQL queries the same database as the rest of your application, it does not have extended access rights by default.

• The default line cap is configurable on the server side; a requested value above the server cap is explicitly denied rather than silently reduced.
• Access to the system catalog (pg_catalog, information_schema) and non-project schemas is blocked by the validator, regardless of your RLS policies.
• RLS remains your last line of defense on sensitive tables: the validator limits the form of the SQL, not the business rights on the data.

The engine is strictly readable: only SELECT requests are accepted. Any attempt atINSERT, UPDATE, DELETE, DROP, CREATE or ALTER generated by the template is rejected before execution — this is not a prompt convention, it is a rule imposed at the syntax tree level.

Other structural limits: no subqueries, no CTE/WITH, no UNION, and a closed whitelist of ten SQL functions. A question that naturally calls for a subquery (“customers who have never ordered”) must be reformulated to fit into a simple SELECT, or handled differently on the application side.

NL2SQL covers structured questions about your relational data. For questions about unstructured content (documents, notes, tickets), Aurabase's native RAG relies on pgvector and an HNSW search. Both capabilities — and how to combine them in an agent — are detailed on the Native AI on Postgres page.