News for hackers. Stuff that matters.
Re:Getting AI to write good SQL by flysand7 on Friday May 16, 2025 @06:31PM (#44010346)
Having written more SQL than any other programming language by now, every time I've tried to use AI to write the query for me, I'd spend way more time getting the output right than if I'd just written it myself.

As a quick aside there's one thing I wish SQL had that would make writing queries so much faster. At work we're using a DSL that has one operator that automatically generates joins from foreign key columns, just like

    credit.CLIENT->NAME
And you got clients table automatically joined into the query. Having to write ten to twenty joins for every query is by far the worst thing, everything else about writing SQL is not that bad.
Re:Getting AI to write good SQL by emptysea on Friday May 16, 2025 @07:33PM (#44010730)
That's one of the features of EdgeQL:

    select Movie {
      id,
      title,
      actors: {
        name
      }
    };

https://docs.geldata.com/learn/edgeql#select-objects

Although I think good enough language server / IDE could automatically insert the join when you typed `credit.CLIENT->NAME`

Re:Getting AI to write good SQL by efromvt on Friday May 16, 2025 @07:37PM (#44010752)
(Shameless plug) writing the same joins over and over (and refactoring when you update stuff) was one of my biggest boilerplate annoyances with SQL - I’ve tried to fix that while still keeping the rest of SQL in https://trilogydata.dev/
Re:Getting AI to write good SQL by galenmarchetti on Friday May 16, 2025 @08:36PM (#44011084)
yeah we’re doing something similar under the hood at AstroBee. it’s way way way easier to handle joins this way.

imo any hope of really leveraging llms in this context needs this + human review on additions to a shared ontology/semantic layer so most of the nuanced stuff is expressed simply and reviewed by engineering before business goes wild with it

Re:Getting AI to write good SQL by emmelaich on Friday May 16, 2025 @10:24PM (#44011577)
I'd like there to be a function or macro for a bunch of joins, say

    DEFINE products_by_order AS orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id
You could make it visible to the DB rather than just a macro so it could optimise it by caching etc. Sort of like a view but on demand.
Re:Getting AI to write good SQL by icedchai on Friday May 16, 2025 @11:32PM (#44011833)
Sounds like a CTE?
Re:Getting AI to write good SQL by aradox66 on Saturday May 17, 2025 @02:06PM (#44015959)
Can't recommend dbt highly enough to get over the SQL boilerplate problem. After using dbt for a few years it's unimaginable to work without it.
Re:Getting AI to write good SQL by johnthescott on Sunday May 18, 2025 @07:22AM (#44020501)
In PostgreSQL i tend to use NATURAL JOINs in the FROM clause to whittle down the distracting joins in the more relavent WHERE clause.