Talking to Your SQL Database: Text-to-SQL Automation in Practice

In recent years, the capabilities of large language models (LLMs) have continued to evolve, and their range of applications has steadily expanded. This versatility has been achieved by fine-tuning large foundation models for specific tasks (e.g., following instructions) or domains (e.g., legal texts). These models demonstrate outstanding performance in generating structured output such as code, JSON, or SQL. This article focuses on a practical business example in the context of text-to-SQL.

In modern organizations, large volumes of business and proprietary data are stored in relational databases, and accessing this data efficiently often requires trained personnel as well as a solid understanding of data structures and data management principles. However, many employees who need to work with this data lack sufficient SQL skills, which hinders efficient data utilization. A typical real-world scenario might look like this: A company’s marketing department needs last year’s sales figures for Product A across all branches in Hamburg and Berlin.

This is precisely where text-to-SQL systems come into play, bridging the gap between queries formulated in natural language and the extraction of information from relational databases. The quality achieved by today’s LLMs makes SQL generation a reliable process (see, for example, Reference [1] for a current overview). In the business contexts described, we therefore see a high degree of relevance for text-to-SQL systems, which combine a user-friendly interface with natural language capabilities while simultaneously enabling access to a wide variety of existing databases, including data warehouses and data marts.

In this blog post, we summarize our findings from the development and testing of a standardized text-to-SQL use case based on a sales database. We describe the building blocks that make up the overall system, delve into the corresponding prompt engineering, and outline a typical conversation flow. Our work has shown that the use of a powerful LLM in the user application for SQL generation enables interaction with the database on various levels. 

Direct Interactions with the Database Schema

L0: The immediate level. 

The user query relates to questions about the database schema or raw table contents. Examples include: “Show me the current database schema” or “Return all values in the ‘brand’ column.”
 

Natural-Language Queries and SQL Generation

L1: The query level.
This is the conversational level that is particularly relevant for use cases where specific information queries are made based on the schema. An example:
“Show me all product names in the ‘Cosmetics’ category that customers from Hamburg purchased last month.”

By combining information from the database schema with the query terms, the LLM is able to generate the corresponding SQL statement, which is then forwarded by an orchestrator to the query engine. In the resulting query string from our Product A example, the tables and columns “customer,” “category,” “product.name,” “customer.address,” and “sales.date”—among others—are addressed, along with the necessary joins and quantifiers. 

Context-Aware and Referential Queries

L2: The referential level.
A key advantage of conversational memory is the ability to easily reference previous queries, so that the entire query does not need to be re-entered when only minor adjustments are required. Let’s return to the example above: The same information regarding category and time period is needed, but this time for the Berlin branch. Simply entering “Repeat the last query for our Berlin branch” is sufficient.

Depending on the length of the conversation window and the complexity of the queries, interaction with the database at the SQL level is possible in a conversational format.

L2-type communication also occurs when users provide feedback to control or correct generated SQL code. By accounting for different database dialects, the LLM can adapt well to the specific characteristics of a database system; however, errors may still occur during the generation process. As soon as a problem is detected through strict syntax and formatting checks, a correction prompt can be provided and supplemented with linter output, such as:
“Your last query has the following problem: <linter output>. Try to correct it.”

This approach has worked well in the rare instances where syntax or formatting issues arose. In a production environment, however, users should not be confronted with the display of SQL corrections. During the development phase, however, a careful analysis of the linter output helped refine the system prompt, the schema-linking strategy, and the postprocessing steps. For more complex database schemas or highly complex queries, these aspects will become even more important.

In summary, the use of powerful LLMs in SQL-driven business applications makes information easily accessible through text-to-SQL systems, thereby enabling efficient knowledge discovery, greater data autonomy, and better resource utilization.

Our goal is to demonstrate how to build a robust and extensible text-to-SQL workflow—starting with a user query, through SQL generation and validation, and ending with the delivery of the corresponding database results. In real-world client projects, the following conditions typically apply:

 The LLM is accessed via an API; neither self-hosting nor fine-tuning is considered. Customers generally do not have the necessary hardware to further refine models and want to become productive quickly.

 We do not have any gold standards or ground truths available for any given client database. This means that, at the outset, no general validations are performed based on metrics such as Execution Accuracy (EXE), Exact Set Matching Accuracy (ESM), or other metrics (see Reference [2] for a detailed analysis of these quality metrics). Validations can be performed at a later stage if there is a need to generate reference SQL queries and data. We enable subsequent testing by generating an abstract syntax tree (AST) from the SQL query and corresponding reference data from the customer database for each user query.

The following figure outlines the workflow.

The user on the left side of the figure continuously interacts with the system via a dialog window, where prompts are entered and results are displayed. The conversational memory tracks both user requests and the generated SQL queries, enabling a conversation at the L2 level. For each query, the LLM receives the system prompt along with the conversation history to date. Specific information such as the database schema, database type, and SQL dialect is included in the system prompt when the system starts. In our implementation, validation messages from the syntax checkers and linters are also returned for monitoring and debugging purposes. Once the SQL query has passed all checks, the AST and the retrieved data are stored to enable additional verification against a customer-provided gold standard.

After longer conversations, it is recommended to clear the conversational memory to avoid potential distortions that may have accumulated in a chain of queries. Since context lengths are limited, we use a conversation window with a length of 20. The following components were used for our demonstration of the business use case:

 Chainlit as the UI and orchestrator

 GPT-4o-mini as the generative LLM

 Dockerized MS SQL Server as the SQL engine, including the necessary database drivers

 Microsoft Contoso dataset as a starting point with anonymized, realistic customer data

 SQLglot as a strict, SQL-dialect-aware syntax checker and AST generator

 SQLFluff as a powerful, SQL-dialect-aware linter for detecting formatting issues and other hidden errors, even in syntactically correct SQL statements

As mentioned earlier, fine-tuning a model is not the most effective approach when the goal is to get it into production quickly. Given the capabilities of today’s LLMs, the model can instead be controlled through well-thought-out prompt engineering that follows a few fundamental principles relevant to SQL generation. As described in [1], prompt engineering can be divided into three phases: preprocessing, inference, and postprocessing. When designing our system prompt to achieve high-quality SQL, we largely follow these principles, even though some of the aspects described in [1] do not apply to our production use case. Below, we outline our design principles for the LLM system prompt.

 During the preprocessing phase, clues about the expected query structure or descriptions of its abstract representation are typically included in the prompt. We deliberately choose not to do this; instead, we list a set of rules that the generator must follow in order to generate high-quality SQL queries from natural language. In addition, we request the output in a structured format to gain insights into the generation process itself. To this end, we provide a sample JSON file that the LLM should use whenever generating a response. The JSON structure template is as follows; the placeholders are filled in by the LLM:

 The second component of preprocessing is schema linking. When the system starts up, we query the database schema and, after correcting spaces and applying some formatting, insert it into the prompt template. It is recommended to explain the individual components of the schema to the LLM in terms of their function (table or column) and their data type. We have found that a more detailed elaboration of the database schema was not necessary to achieve good results, although our investigation should not be considered exhaustive. Advanced aspects from the literature can be incorporated as soon as deficiencies in the generated SQL queries are observed. We believe that having reasonable confidence in the LLM’s capabilities reduces the risk of overloading the system prompt with numerous fine-grained rules, which could otherwise lead to undesirable side effects.

 In the inference phase, chain-of-thought (CoT) and decompositional workflows are cited in the literature as proven methods for guiding the LLM through the SQL generation step. As previously mentioned, we have defined a list of rules to follow or avoid during the construction process. A good set of rules is developed by first running the generation process without any restrictions and then carefully analyzing the results for a wide variety of queries. This approach allows us to identify and avoid common errors and recurring pitfalls. While this procedure is not strictly identical to a Chain-of-Thought process, there is some overlap. More complex CoT or decomposition methods may become relevant as queries increase in complexity when combined with large databases.

 The postprocessing phase focuses on self-correction and consistency. By analyzing semantic and formatting issues identified by SQLFluff, we were able to define guidelines that were integrated into the system prompt. In some systems, self-correction loops run automatically in the background until SQL correctness is achieved. Due to the associated costs of multiple LLM calls, we did not implement this approach. Instead, in the current implementation, we directly relay any issues that arise to users, thereby enabling L2 correction prompts. By repeatedly bringing these issues to light, we were able to continuously improve the rule set and further refine the system prompt offline. During an active conversation, the LLM can remember issues as soon as it receives a correction prompt and avoid errors in the further course of information retrieval. Another promising approach we intend to pursue in the future is the self-consistency method described in [3]. In this method, multiple SQL strings are generated from a natural-language query by setting a temperature T > 0 in the LLM call. The set of SQL strings is then evaluated by the LLM, and the highest-rated query is forwarded to the query engine. This approach also incurs higher costs due to multiple LLM inference calls. For more complex scenarios, self-consistency has proven to be very effective in practice; however, it should always be accompanied by careful cost monitoring.
 

As described above, Level 0 refers to queries that directly concern the database structure or raw table contents and are sometimes necessary to reveal specific details of the database.

By maintaining the conversation history, a brief follow-up question is possible, which internally and automatically leads to an expanded query (the order in which tuples are output is arbitrary).

As an example of a real-world query, we want to identify customers who made a purchase at a specific store within a specific time period.

Now let’s run a follow-up query to find the customers for the Hamburg store, assuming that the LLM can infer the missing information from the conversation memory.

Interestingly, an error was observed in the generated query (despite its syntactic correctness), which stems from the assumption that the “Store” table has a “City” column—which is not actually the case. Here, the LLM’s internal world knowledge—most likely the assumption that “Hamburg” is a city—competes with the information stored in the schema, which states that no “City” column exists in the “Store” table. From the database’s perspective, it seems somewhat inconsistent to store data points for “Hamburg” in the “Store.State” column. Strictly speaking, however, “Hamburg” is a city-state in Germany, which may justify this modeling.

Now that the problem has been identified, we can ensure that an L2 conversation immediately produces correct results by explicitly clarifying that “Hamburg” is a store state and not a store city, we can immediately generate correct results in an L2 conversation (note the appearance of German-sounding customer names in the database results, which indicates the accuracy of the data).

In summary, the examples shown are not very complex, but they demonstrate that interacting with an SQL database in natural language can be achieved in a short amount of time without significant obstacles. Internally, we have also conducted successful benchmarks with more complex queries, in which, for example, nested SELECT statements were generated from more sophisticated natural-language queries.
 

 The prompt structure has a significant impact on the quality of the SQL.
Adherence to established prompting rules from the literature has a significant impact on the correctness and completeness of the generated SQL statements. To make it as understandable as possible for the LLM, the database schema should be provided in a structured format. It is helpful, but not mandatory, to provide structured examples of queries; additional requirements can be formulated in natural language. If structured JSON output is required, a sample JSON must be provided to define the expected key-value pairs.

 Syntax validation tools are indispensable.
If user queries are imprecise, syntactically incorrect or low-quality SQL code may be generated. In such cases, syntax, semantics, and format checks are required. If automation through self-correction is the goal, the LLM repeats syntactically incorrect SQL statements along with their error descriptions until validation is successful. An alternative is the self-consistency method, in which multiple SQL strings are evaluated.

 Conversational context simplifies interaction and improves accuracy.
Context enables users to refer to previous queries, reuse complex SQL queries with minor modifications, and supports error detection and correction both during development and in production.

 Software engineering discipline pays off in GenAI projects as well.
Even in LLM-driven applications, clean engineering supports scalability, adaptability, and long-term maintainability.

Looking ahead, we plan to:

 to integrate open-source models and evaluate their text-to-SQL capabilities in combination with any customer databases to reduce costs.

 the use of a framework such as vLLM (https://docs.vllm.ai/en/v0.6.2/) for self-hosting suitable models of sufficient quality in customer systems, which is becoming increasingly important in terms of data protection and data sovereignty.

 The expansion of schema-linking and postprocessing approaches to support even larger and structurally diverse data stores, such as star schemas, snowflake schemas, or data vaults with hubs, links, and satellites.

 Support for scenarios involving multiple databases, which requires greater effort in preprocessing and dynamic schema generation to keep the LLM focused on the actual query structure.

 Adapting the service so that it can be seamlessly integrated into an MCP (Model Context Protocol) environment.

Our text-to-SQL sample project demonstrates that natural-language queries can be implemented in a practical and reliable manner when combined with sound engineering principles. It bridges the gap between human intent and structured data, enabling organizations to unlock the full value of their databases through conversational interfaces.

[1] L. Shi et al., "A Survey on Employing Large Language Models for Text-to-SQL Tasks," arXiv:2407.15186 (2025).
[2] B. Ascoli et al., ETM: Modern Insights into Perspectives on Text-to-SQL Evaluation in the Age of Large Language Models, arXiv:2407.07313 (2025).
[3] X. Wang et al., Self-Consistency Improves Chain-of-Thought Reasoning in Language Models, arXiv:2203.11171 (2022).