What is NL2SQL and how does it work?

NL2SQL refers to the automatic translation of a question in natural language into an SQL query that can be executed on a relational basis. Unlike a generic chatbot that responds in free text, an NL2SQL system produces a structured artifact, SQL, that executes against real data and returns a verifiable result line by line.

The term “text-to-SQL” comes from academic research in natural language processing. “NL2SQL” is the most used abbreviation on the product and technical documentation side. Both refer to the same problem: bridging the gap between a question asked in everyday language and the precise syntax expected by a SQL engine.

NL2SQL is distinguished from a conversational agent connected to a database in the broad sense. The first produces a readable and auditable query; the second can chain several tool calls (search, calculation, writing) without necessarily resulting in a unique and inspectable SQL. A properly designed NL2SQL system remains within this deliberately restricted scope: translate, validate, execute, return a result.

A reliable NL2SQL pipeline always follows the same sequence, regardless of the provider: the question goes through a language model, then the produced SQL is validated before execution, never after. The Aurabase implementation, verified in the aura-aiservice code, illustrates each of these steps with concrete rules rather than an abstract description.

1.Question is received with the actual schematic of the base

The system associates the question in natural language with the schema of the queried database: table names, columns, types. This pattern must come from introspection of the actual basis, not from a description provided by the caller. An implementation that accepts a customer-declared schema would open the door to questions about non-existent tables, or to bypassing isolation between projects. The Aurabase engine explicitly rejects (400 error) any schema field sent in the request, rather than silently ignoring it.

2.An LLM generates a candidate SQL

The language model receives the question and schema in its prompt, then produces a candidate SQL query along with a short explanation. Aurabase treats three providers equally with a dedicated native client: OpenAI, Anthropic (Claude) and Gemini. This candidate SQL is at this stage only a proposal, never directly executed.

3.The candidate SQL is validated before execution, not after

This is the step that distinguishes a serious NL2SQL system from a simple LLM call followed by naive execution. The generated SQL is parsed into a syntax tree (AST) rather than inspected by a keyword search, which is easily bypassed. The Aurabase implementation, with the sqlparserlibrary, only allows simple SELECT queries: CTE/WITH, subqueries, UNIONs, window functions and locking clauses (FOR UPDATE) are explicitly rejected, as are any SQL functions outside a whitelist of ten functions (count, sum, avg, min, max, lower, upper, coalesce, date_trunc, now).

4.Committed query runs with a row cap

Validated SQL receives an LIMIT if it does not already have one: 100 lines by default with Aurabase, 1000 maximum, both values configurable on the server side. A request beyond the cap is explicitly denied rather than silently reduced. The response indicates whether this LIMIT was added by the server, so the caller knows if the SQL executed differs from that produced by the model.

-- Question: "How many orders this month for premium customers?"
SELECT count(*) FROM orders
WHERE customer_plan = 'premium'
  AND created_at >= date_trunc('month', now())
LIMIT 100  -- added by server, missing from generated SQL

The full detail of this pipeline, with each HTTP call and each JSON response, is covered in our step-by-step tutorial for building an NL2SQL endpoint on Postgres.

NL2SQL is not intended to replace hand-written SQL everywhere. It covers a specific scope: ad hoc, one-off questions asked by someone who does not know SQL or who simply wants to save time on a simple query.

• Ad hoc exploration of a dashboard by a non-technical person (support, product, management).
• Rapid prototyping of a feature that queries the database without writing a dedicated API route for every possible question.
• Limited analytical self-service: count, filter, simply aggregate, without giving direct access to the database to the end user.

Hand-written SQL remains preferable as soon as the question goes beyond this scope. An AST-validated implementation like the one described above excludes CTEs, subqueries and window functions by construction, for security reasons. An analysis that structurally needs these constructions, cohorts, advanced temporal windowing, does not go through NL2SQL: it is coded directly. This is an accepted compromise, the security of the system comes before the completeness of the SQL generated.

Three risks systematically recur in an NL2SQL implementation, with different responses depending on the maturity of the system.

SQL injection via prompt or question

An LLM can be manipulated to produce malicious SQL if the question itself contains an "ignore previous statements and..." injection attempt. The defense is not to trust the prompt, but to validate the SQL produced independently of what was requested, exactly step 3 of the pipeline described above. The topic deserves dedicated treatment: see Securing NL2SQL Against SQL Injection for precise attack vectors and countermeasures.

Hallucination of non-existent tables or columns

The model may invent a table or column name that is plausible but missing from the actual schema, especially on large or poorly documented schemas. An implementation that validates the generated SQL against the actual database schema rejects the query with an explicit message, listing the tables actually available, rather than letting a raw SQL error pass back to the user.

Cost and latency of model calls

Each NL2SQL question triggers a call to the language model, with its own cost and latency, in addition to SQL execution time. This cost mounts quickly if NL2SQL serves as the default layer for repetitive questions, which would benefit from being cached or exposed as a standard report rather than retranslated each time.

Two architectures produce a similar visible result, but with very different guarantees. Native NL2SQL integrates generation, validation and execution directly into the backend layer which already knows the project's schema and access rights: this is the logic described above for Aurabase, where the aura-ai service shares the infrastructure and schema isolation with the rest of the backend.

An assembled NL2SQL combines a generic LLM service, a connector to the database, and a build-it-yourself validation layer. Nothing prevents this approach from being secure, but every guarantee, server-side introspected schema, AST validation, row cap, isolation tenant, must be implemented and maintained by the team putting these bricks together, rather than provided by the platform.

The landscape of NL2SQL tools, native and assembled, open source and commercial, is compared in detail in our NL2SQL 2026 tool comparison.

NL2SQL and RAG (retrieval-augmented generation) answer two different families of questions, often confused because both rely on an LLM connected to a database.

NL2SQL targets structured and relational data: how many, when, what proportion, questions that naturally translate into SELECT, GROUP BY, aggregations. The RAG targets unstructured content: documents, notes, support tickets, where the answer does not fit in a table row but requires finding a relevant passage by semantic similarity, vector search on pgvector, HNSW index, before giving it in context to the model.

The two capabilities can coexist in the same project and combine in an agent who chooses one or the other depending on the question asked. The Aurabase native AI pillar details how the two mechanisms work together, and our RAG pipeline guide on pgvector covers the implementation of the second.