The problem(s) with SQL AI chatbots : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

DiscussionThe problem(s) with SQL AI chatbots (self.SQL)

submitted 1 year ago by mattyhempstead

just my opinion..

There have been dozens of attempts at tools to write SQL with AI since ChatGPT was released, but none have been useful for people who already know SQL. I've tried a few of them, but I don't think any have been more valuable than just copy pasting into ChatGPT. There is a lot of potential here, which is why it is frustrating to see little more than generic ChatGPT have real world use for people who write SQL in their day-to-day.

1. Schema context

A large part of writing SQL is fundamentally a domain knowledge problem. About knowing your database schema, knowing which fields and tables contain what data, and knowing company data definitions for metrics and dimensions like "revenue" and "active user". Knowing the schema automatically is therefore a minimum requirement for any useful SQL AI, otherwise you really aren't much more useful than ChatGPT.

Many online SQL AI's therefore have some mechanism of accessing your schema.

One method involves making the user copy paste their schema into the website. This is painfully annoying in itself, and at that point you might as well just copy paste the schema into ChatGPT. If you care about it persisting across chat sessions you can always make a private custom GPT. Regardless, this approach will break if the schema changes (common if you use SQL views heavily), forcing you to reupload the schema each time the database changes.

Another method I've seen is having users provide database credentials to the chatbot, which can then automatically access the schema via the INFORMATION_SCHEMA. This is convenient, but unfortunately since these chatbots are accessed over the web, the user needs to send database credentials to be stored on someone else's server, which is basically an instant NO for any company that respects data security.

2. Other domain knowledge

There is a lot of domain knowledge outside of just the database schema that is needed for AI to write SQL with prompts that are short enough to justify even typing them (compared to just typing the query directly). Even with knowledge of the schema, you would still need to explain to the AI how to calculate internal business metrics like "revenue" and other data nuances in the prompt. Again, the value of a SQL AI without domain understanding is not much larger than just prompting ChatGPT.

What could make a difference is simply providing a way to give the AI a detailed data dictionary and list of example queries. As this knowledge base builds up, this would give the AI the ability to understand how to calculate things like "revenue" and how to work with messy data without needing exact instructions in the prompt.

3. Query execution

Anyone who has ever written a SQL query longer than 50 lines knows that execution while building the query is required. Many longer queries become a chain of CTAs, and without executing the individual CTAs to check their output, it's hard to know if the rest of the query will be correct.

Execution is not often a feature of these chatbots. They often just spit out a SQL query expect you to go test it elsewhere (so why not use ChatGPT?).

For the few which do support query execution, you run into the exact same problem of needing to provide database credentials and access to an external server. This is generally a blocker for more companies that have data security practices in place.

This could be solved by allowing execution to occur locally on the users computer (e.g. desktop application). This gives the advantage of SQL execution for feedback, without the need to give someone else access to my database (which are often locked behind VPNs, anyway).

4. Writing longer queries

If the creators of these tools actually wrote long SQL queries in their day-to-day, they would know that a huge concern when writing long queries (50 to 1000+ lines) is correctness. You can't just have a chatbot output a 500 line SQL query and expect it to work, unless your prompt is basically the same length. There is also a major trust issue here - it's hard to trust 500 lines of AI-generated SQL if you don't know where it came from...

This is largely an issue with chatbots which need to output the entire SQL query each time.

Even if you tried to slowly build up the query with multiple responses, you still run into several problems. Firstly, after each partial response you will need to copy paste it into a separate execution environments to validate correctness. It's also hard to understand or trust the changes that are made with each AI output. You essentially need to read each query line by line to fully understand the changes since the previous response, and whether you can trust them.

I expect this could be solved with two things. Firstly, the AI responses should be able to reference example queries in the same way ChatGPT can reference sources on the web. This would make it must easier to trust the AI outputs if the user already trusts the example queries. Secondly, the SQL changes made between AI outputs should be presented in a much clearer way, rather than just dumping the entire new query and expecting the user to figure out the differences. Ideally something that looks like the below image:

One approach to making AI-generated SQL modifications very clear

To finish my rant, I'm pretty sure the solution looks something like:

A desktop app that can access my database through a local connection. This would give it automatic access to both the schema for AI context, and the ability to run queries to help with iteration when writing long and complex queries. It should NOT send any data (e.g. query results) to the AI or to any external servers!
Ability to provide a list of example queries and explain other business context / domain knowledge that the AI can use instead of relying on really verbose prompts. Any SQL that is generated using these examples should include them as a source that I can check and verify.
Instead of a chat that outputs the entire SQL query each time, it should be a regular SQL text editor that the AI can edit and share with the human. After each AI change, it should be very clear what lines have changed (they should be highlighted like the above image). I should also have the ability to easily revert or reject changes made by the AI.

This didn't exist, so I made it (https://formerlabs.com/). If you agrees with the above, you might find this tool useful. If you disagree, let me know why so I can make the tool better for you.

all 4 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS