Getting AI to write good SQL

#1

Post by richards » Fri, May 16, 2025, 9:10 PM UTC

Getting AI to write good SQL
cloud.google.com

Re: Getting AI to write good SQL

#2

Post by mritchie712 » Fri, May 16, 2025, 9:43 PM UTC

the short answer: use a semantic layer.

It's the cleanest way to give the right context and the best place to pull a human in the loop.

A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU.

With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of SQL.

We[0] use cube[1] for this. It's the best open source semantic layer, but there's a couple closed source options too.

My last company wrote a post on this in 2021[2]. Looks like the acquirer stopped paying for the blog hosting, but the HN post is still up.

0 - https://www.definite.app/

1 - https://cube.dev/

2 - https://news.ycombinator.com/item?id=25930190

Re: Getting AI to write good SQL

#3

Post by westurner » Fri, May 16, 2025, 9:50 PM UTC

From "Show HN: We open sourced our entire text-to-SQL product" (2024) https://news.ycombinator.com/item?id=40456236 :

> awesome-Text2SQL: https://github.com/eosphoros-ai/Awesome-Text2SQL

> Awesome-code-llm > Benchmarks > Text to SQL: https://github.com/codefuse-ai/Awesome-Code-LLM#text-to-sql

Re: Getting AI to write good SQL

#4

Post by cloudking » Fri, May 16, 2025, 9:53 PM UTC

This is pretty simple in any foundation model, provide a well commented schema and ask for the query

Re: Getting AI to write good SQL

#5

Post by tibbar » Fri, May 16, 2025, 9:54 PM UTC

cloudking wrote: post #4

This is pretty simple in any foundation model, provide a well commented schema and ask for the query

Step 1: Your schema has thousands of tables and there aren't many comments.

Step 2...

Re: Getting AI to write good SQL

#6

Post by mousetree » Fri, May 16, 2025, 10:02 PM UTC

Out of all the AI tools and models I’ve tried, the most disappointing is the Gemini built into BigQuery. Despite having well named columns with good descriptions it consistently gets nowhere close to solving the problem.

Re: Getting AI to write good SQL

#7

Post by john2x » Fri, May 16, 2025, 10:07 PM UTC

tibbar wrote: post #5

cloudking wrote: post #4

This is pretty simple in any foundation model, provide a well commented schema and ask for the query

Step 1: Your schema has thousands of tables and there aren't many comments. Step 2...

Use AI to generate the comments of course

Re: Getting AI to write good SQL

#8

Post by fsndz » Fri, May 16, 2025, 10:15 PM UTC

cloudking wrote: post #4

This is pretty simple in any foundation model, provide a well commented schema and ask for the query

the smolagents library is also pretty nice to do the scaffolding around the model. Text to sql seems simple in demos, but to make it work in real life complex cases is very hard: https://medium.com/thoughts-on-machine-learning/build-a-text...

Re: Getting AI to write good SQL

#9

Post by quantadev » Fri, May 16, 2025, 10:25 PM UTC

cloudking wrote: post #4

This is pretty simple in any foundation model, provide a well commented schema and ask for the query

I agree. There's really no magic to it any more. The table create DDL commands are a very precise description of the tables, so almost nothing more is ever needed. You can just describe in detail what query you need, and any decent LLM can do it just fine.

Re: Getting AI to write good SQL

#10

Post by quantadev » Fri, May 16, 2025, 10:27 PM UTC

mousetree wrote: post #6

Out of all the AI tools and models I’ve tried, the most disappointing is the Gemini built into BigQuery. Despite having well named columns with good descriptions it consistently gets nowhere close to solving the problem.

Having proper constraints and foreign keys that are clear is generally all that's needed in my experience. Are you sure your tables have well defined constraints, so that the AI can be absolutely 100% sure how everything links up? SQL is very precise, but only if you're utilizing constraints and foreign key definitions well.