SQLAI.ai

Simpler RAG approach for text-to-SQL to avoid hallucinations

If you've worked with AI, you'll quickly realize that there's an invisible line where the AI's knowledge runs out and it starts hallucinating. The solution to this is often RAG (retrieval augmented generation), so the user doesn't have to specify every little detail into each prompt manually. RAG is authoritative knowledge that is fed to the AI's prompt depending on the context. Most RAG setups are complicated and require prompt embedding and advanced RAG search, but in my case it can be solved much more simply.

Common RAG approach

My problem was that users sometimes experienced the AI making minor and repetitive errors when trying to generate text-to-sql with a database schema included in the prompt. It could be that the AI didn't quite understand which foreign key, which of database tables users_us or users_eu to use or to prevent large SQL queries with the LIMIT parameter. To solve it, the AI needs a list of general rules to observe.

If it is to be solved as many RAG guides describe then it will require the following steps:

  • create text-to-SQL rule list for a specific database schema
  • create a vector embedding of the rules
  • create a vector embedding of the user prompt for text-to-SQL generation
  • match the user prompt to the rule list
  • add any matched rules to the text-to-SQL prompt

The hardest part is undoubtedly matching the user prompt in a reliable and clear way; selecting the right rules and making the user aware of it.

Simpler RAG approach

Initially, I spent a lot of time researching advanced RAG setups to solve the user problem of AI not adhering to general database schema rules. It became too complicated; both for me to create and for users to understand and use. The simpler “RAG” solution is much simpler and offers clear improvements. It has the following steps:

  • create text-to-SQL rule list for a specific database schema
  • include text-to-SQL rule list when generating SQL queries for a specific database schema

Adding the rule list for a specific database schema looks like this:

Set custom database RAG rules

When the user generates text-to-SQL for a database schema, the rules can easily be added or removed from the prompt:

Generate text-to-SQL with simple RAG rules

How I use it

I had the same issues as my users, AI made the same general errors when generating text-to-SQL for my Prisma ORM database schema. AI didn't understand that tables and columns should be surrounded by quote characters because Prisma prefers tables and columns to be camel-cased. AI would generate the following:

            

              
            

            SELECT
  *
FROM
  Snippet
WHERE
  userId = x;

This SQL query fails because PostgreSQL requires tables and columns to be wrapped in quotation marks to respect camel case; “Snippet” and “userId”.

The solution to the repetitive problem is a simple database rule: Wrap tables and columns in quotes. Another problem I wanted to avoid was overloading the database server with large queries. For this I added the following database rule:

            

              
            

            SELECT
  *
FROM
  "Snippet"
WHERE
  "userId" = x
LIMIT
  100;

When I generate SQL queries for my connected database, I no longer often have to manually insert quotes or constraints into the generated SQL query, but can let AI do this throughout. I have users with over 20 database rules formulated in languages other than English who use it without any problems. In other words, it's relatively robust. It's a practical “low-tech” solution (manual RAG) that works.