I don't need your query language • Hacker News Board

Re: I don't need your query language

#231

Post by summarity » Sat, Jun 17, 2023, 5:24 PM UTC

rbalicki wrote: post #223

erikpukinskis wrote: post #215

Earlier quoted context omitted.

> You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query) For anyone (like me) who is not quite able to visualize this, here is an example: SELECT json_agg(trips) FROM ( SELECT json_agg( json_build_object( 'recorded_at', created_at, 'latitude', latitude, 'longitude', longitude ) ) as trips FROM data_tracks GROUP by t…

As someone who would like to use SQL to return a tree-like structure, how well does this scale (in terms of readability, performance, etc.) when the query is extremely large and nested? For example, if we are attempting to replace GraphQL with some sort of SQL. I'm super unfamiliar with this space, and would love to know whether it is a feasible/worthy goal to replace GraphQL queries with generated SQL.

Very well, I run huge nested CTEs with json aggregation in SQLite for findsight.ai and there’s really not any noticeable performance overhead.

A good editor (DataGrip) helps.

Re: I don't need your query language

#232

Post by DaiPlusPlus » Sat, Jun 17, 2023, 5:26 PM UTC

yencabulator wrote: post #189

nicoburns wrote: post #43

Earlier quoted context omitted.

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

The crap mini-language of Postgres functions to manipulate JSON makes otherwise reasonable queries unreadable, though.

That “crap mini-language” is now in ISO SQL. Now we’re stuck with it forever. Even in Oracle :o

Re: I don't need your query language

#233

Post by amai » Sat, Jun 17, 2023, 5:38 PM UTC

SQL got the order of key words wrong. Instead of

„Select … from …“

it would be much better to write

„From … select …“.

PRQL got that right: https://prql-lang.org/

Re: I don't need your query language

#234

Post by tester756 » Sat, Jun 17, 2023, 5:39 PM UTC

DaiPlusPlus wrote: post #20

tester756 wrote: post #9

Earlier quoted context omitted.

C#'s LINQ (query syntax, not methods) got it right var result = from s in stringList where s.Contains("Tutorials") select s;

or just `var result = stringList.Where( s => s.Contains("Tutorials") )` I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join` - but I hardly ever do that in Linq anyway. Also, in both my code and yours, `result` will be a lazy-evaluated `IEnumerable ` which may be undesirable - which means it's probably a good idea to use `.ToLi…

chill,

I've been just showing that *approach* of starting SQL query from "from" part is viable, because that's how Microsoft timplemented in one of two LINQ "API"s"

I'm not trying to convince anyone to use LINQ's Query Syntax.

Re: I don't need your query language

#235

Post by kmeisthax » Sat, Jun 17, 2023, 5:43 PM UTC

DaiPlusPlus wrote: post #165

0x445442 wrote: post #162

Earlier quoted context omitted.

These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications. What was once a single API call for the application, backed by a SQL query joining over tables has been replaced by multiple micro service calls. It’s now the application’s responsibility to hold these MS call results in memory and then relate them once all the data has been fe…

> These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications This is utter nonsense. There is absolutely nothing about microservices architecture that relates to performing JOINs in SQL.

thatsthejoke.jpg

Re: I don't need your query language

#236

Post by valenterry » Sat, Jun 17, 2023, 5:45 PM UTC

blipvert wrote: post #169

XorNot wrote: post #5

Earlier quoted context omitted.

FROM users SELECT name, id, location WHERE name LIKE 'a%'; You know I think you're right.

I might be wrong, but there’s nothing to stop a front end UI from accepting this syntax and then writing out canonical SQL to do the actual query, is there?

Exactly, it puzzles me that tooling doesn't do that...

Re: I don't need your query language

#237

Post by amai » Sat, Jun 17, 2023, 5:48 PM UTC

hosteur wrote: post #58

I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This would allow autocompleting col names in editors. Other than that I quite like sql being the standard db query language.

Have a look at https://prql-lang.org/. They got this right.

Re: I don't need your query language

#238

Post by tikhonj » Sat, Jun 17, 2023, 6:05 PM UTC

Having used (a lot) of Hive SQL, I absolutely do need your query language. SQL is fundamentally incapable of expressing any sort of abstraction, so non-trivial queries quickly become completely incomprehensible, unmaintainable and bug-prone.

Learning a new language is a one-time, up-front cost. Dealing with an awkward, inexpressive query language that integrates poorly with my main language, my types or my interface description languages is an ongoing source of painful friction. Learning something new should not be nearly the barrier to adoption that it seems to be for most people!

SQL's shortcomings seem so clear and omnipresent that I legitimately do not understand why everybody seems so drawn to it. Are the usage patterns for code against a transactional database so different from the sort of Hive queries I've had to deal with for data engineering and machine learning? Is everybody happy with abstractions layered over SQL like ORMs?

Why can't we have, I don't know, some typed variant of Datalog or something instead?

Re: I don't need your query language

#239

Post by 9dev » Sat, Jun 17, 2023, 6:09 PM UTC

tikhonj wrote: post #238

Having used (a lot) of Hive SQL, I absolutely do need your query language. SQL is fundamentally incapable of expressing any sort of abstraction, so non-trivial queries quickly become completely incomprehensible, unmaintainable and bug-prone. Learning a new language is a one-time, up-front cost. Dealing with an awkward, inexpressive query language that integrates poorly with my main language, my types or my interface…

Not that I fundamentally disagree with you, but SQL has been the dominant query language for more than half a century. This lends it some credence for being a quite passable solution, don’t you think?

Re: I don't need your query language

#240

Post by alexchamberlain » Sat, Jun 17, 2023, 6:12 PM UTC

tikhonj wrote: post #238

Having used (a lot) of Hive SQL, I absolutely do need your query language. SQL is fundamentally incapable of expressing any sort of abstraction, so non-trivial queries quickly become completely incomprehensible, unmaintainable and bug-prone. Learning a new language is a one-time, up-front cost. Dealing with an awkward, inexpressive query language that integrates poorly with my main language, my types or my interface…

Aren't Views SQL's answer to an abstraction?