Getting AI to write good SQL • Hacker News Board

Re: Getting AI to write good SQL

#171

Post by palmfacehn » Sat, May 17, 2025, 8:20 AM UTC

floren wrote: post #138

e3bc54b2 wrote: post #132

Earlier quoted context omitted.

The worse part is not even being trolled at AI roundabout. The worse part is gaslighting by people who then go on to imply that I'm dumb to not be able to 'guide' the model 'towards the solution', whatever the fuck that means. And this is after telling me that model is so smart to just know what I want. Claude and Gemini are pretty decent at providing a small and tight function definition with well defined parameters…

Remember, if AI couldn't solve your problem, you were probably using the wrong model. Did you try with o5-selfsuck-20250523-512B?

My least favorite part of this trend is the ageism. "Crusty curmudgeons are not up-to date with the latest bloat if they think RTFM is still a thing", "Oh, you didn't like ORMs? Did you try letting an AI generate code for your ORM?"

Maybe in the future all of these assistants will offer something amazing, but in my experience, there is more time invested in prompting that just reading the relevant documentation and having a coherent design.

My suspicion is that many, (but not all please no flames) of the biggest boosters of AI coding are simply inexperienced. If this is true, it makes sense that they wouldn't recognize the numerous foot-guns in AI generated code.

Re: Getting AI to write good SQL

#172

Post by dcrimp » Sat, May 17, 2025, 8:25 AM UTC

I wonder if, for a given dialect (and even DDL), you could use that token masking technique similar to how that Structured Outputs [1] thing went:

Quote: "While sampling, after every token, our inference engine will determine which tokens are valid to be produced next based on the previously generated tokens and the rules within the grammar that indicate which tokens are valid next. We then use this list of tokens to mask the next sampling step, which effectively lowers the probability of invalid tokens to 0. Because we have preprocessed the schema, we can use a cached data structure to do this efficiently, with minimal latency overhead."

I.e. mask any tokens that would produce something that isn't valid SQL in the given dialect, or further, a valid query for the given schema. I assume some structured outputs capability is latent to most assistants nowadays, so they probably already have explored this

[1] https://openai.com/index/introducing-structured-outputs-in-t...

Re: Getting AI to write good SQL

#173

Post by hakanito » Sat, May 17, 2025, 8:39 AM UTC

M4v3R wrote: post #167

hakanito wrote: post #164

The game changer for me will be when AI stops hallucinating SDK methods. I often find myself asking ”show me how to do advanced concept X in somewhat niche Y sdk”, and while it produces confident answers, 90% of the time it is suggesting SDK methods that do not exist, so a lot of time is wasted just arguing about that

The current method of solving this is providing the AI with the documentation of the SDKs your code uses. Current LLMs have quite big context windows so you can feed them a lot of documentation. Some tools can even crawl multipage documentation and index them for the use of LLMs.

How do you do that practically/reliably? Would be great to just paste a link to the SDK Github repo, but doesn't seem to work (yet) in my experience

Re: Getting AI to write good SQL

#174

Post by jstummbillig » Sat, May 17, 2025, 8:51 AM UTC

hakanito wrote: post #173

M4v3R wrote: post #167

Earlier quoted context omitted.

The current method of solving this is providing the AI with the documentation of the SDKs your code uses. Current LLMs have quite big context windows so you can feed them a lot of documentation. Some tools can even crawl multipage documentation and index them for the use of LLMs.

How do you do that practically/reliably? Would be great to just paste a link to the SDK Github repo, but doesn't seem to work (yet) in my experience

Simple way would be to use either Sonnet 3.7/5 or Gemini 2.5 pro in windsurf/cursor/aider and tell it to search the web, when you know an SDK is problematic (usually because it's new and not in the training set).

That's all it takes to get reliably excellent results. It's not perfect, but, at this point, 90% hallucinations on normal SDK usage strongly suggests poor usage of what is the current state of the art.

Re: Getting AI to write good SQL

#175

Post by DonHopkins » Sat, May 17, 2025, 8:52 AM UTC

nashashmi wrote: post #12

AI text to regex solutions would be incredibly handy.

Oh, so an AI assisted number of problems increaser?

https://blog.codinghorror.com/regular-expressions-now-you-ha...

https://blog.codinghorror.com/parsing-html-the-cthulhu-way/

https://en.wikiquote.org/wiki/Jamie_Zawinski

Re: Getting AI to write good SQL

#176

Post by AdrianB1 » Sat, May 17, 2025, 8:57 AM UTC

strict9 wrote: post #98

AdrianB1 wrote: post #21

In real life I find using AI for SQL dangerous. It allows people that don't know what they do to write queries that can significantly impact servers. In my world databases are relatively big for most developers, but not huge. Sometimes when I want to fine tune a query I am challenging AI to provide a better solution. I give it the already optimized query and I ask for better. I never got a better answer, sometimes be…

It should never be at the point where some random person can impact a server. That's what read replicas with read-only access are for. Production db servers should not be open to random queries and usage by people. That's only for the app to use.

How it should be and how it is, that depends on who is the decision maker. If the decision maker is a technical person, there is no gap, but in my case the decision maker is a non-technical manager with no competence to make such decisions, but that is the way the company is organized. So letting people use AI to dig through a 1 TB database is not a good idea, while not using AI prevents them to even try. Security by oblivion.

Re: Getting AI to write good SQL

#177

Post by DonHopkins » Sat, May 17, 2025, 8:58 AM UTC

jimbokun wrote: post #125

skydhash wrote: post #59

Earlier quoted context omitted.

A cheat sheet is just a web search away.

So is an LLM.

So is a real html parser.

https://blog.codinghorror.com/parsing-html-the-cthulhu-way/

https://en.wikipedia.org/wiki/Beautiful_Soup_(HTML_parser)

Re: Getting AI to write good SQL

#178

Post by todotask2 » Sat, May 17, 2025, 9:05 AM UTC

Those days, we have many types of database tools—ORMs, query builders, and more. AI can help reduce the complexity and avoid lock-in to a specific tech stack. I love to write raw SQL.

Re: Getting AI to write good SQL

#179

Post by jinjin2 » Sat, May 17, 2025, 9:06 AM UTC

mritchie712 wrote: post #2

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…

I agree that using a semantic layer is the best way to get better precision. It is almost like a cheatsheet for the AI.

But I would never use one that forced me to express my queries in JSON. The best implementations integrate right into the database so they become an integral part of regular your SQL queries, and as such also available to all your tools.

In my experience, from using the Exasol Semantic Layer, it can be a totally seamless experience.

Re: Getting AI to write good SQL

#180

Post by RadiozRadioz » Sat, May 17, 2025, 9:07 AM UTC

fooker wrote: post #101

RadiozRadioz wrote: post #22

Earlier quoted context omitted.

This comment appears frequently and always surprises me. Do people just... not know regex? It seems so foreign to me. It's not like it's some obscure thing, it's absolutely ubiquitous. Relatively speaking it's not very complicated, it's widely documented, has vast learning resources, and has some of the best ROI of any DSL. It's funny to joke that it looks like line noise, but really, there is not a lot to learn to u…

Regex, especially non standard (and non regular) extensions can be pretty tricky to grok. http://alf.nu/RegexGolf?world=regex&level=r00

/foo/

took me 25.75 seconds, including learning how the website worked. I actually solved it in ~15 seconds, but I hadn't realized I got the correct answer becuase it was far too simple.

This website is much better https://regexcrossword.com/challenges/experienced/puzzles/e9...