3 ms·
To me it seems quite nice, but really just trivially different from SQL - like if Ruby was 'friendlier syntax that transpiles to Python', meh? You'd use whichev
by OJFord 3y ago
To me it seems quite nice, but really just trivially different from SQL - like if Ruby was 'friendlier syntax that transpiles to Python', meh? You'd use whichever you happened to learn first and not bother with the other. (That's often true even though it's more than that of course.)
The examples arbitrarily make SQL look more verbose:
SELECT
id,
first_name,
age
FROM
employees
ORDER BY
age
LIMIT
10
Yes! Of course I'd rather:
from employees
select {id, first_name, age}
sort age
take 10
..but wait, actually the SQL could've been:
select id, first_name, age
from employees
order by age
limit 10
and it's more verbose by a character or two... (no braces, but 'order by' vs 'sort')
- andrewl-hn 3y agoI use CTEs, window functions, and groupings all the time when I write reporting queries. These things tend to be much more verbose in raw SQL, and ORMs / Query Builders either do not support some of these features at all or do very poor job (like, force me to put raw SQL substrings in my code), or force to write DSLs that are even more verbose than raw SQL. Look at corresponding PRQL samples, and you may see an appeal. Also, I agree, no one should write SQL like this - screaming keywords, superficial newlines, etc. I don't think this style made sense ever, even in 1970s.
- pjot 3y agoAt least we can all agree on leading commas though!
- Pxtl 3y agoWhile there's some stuff in C#/LINQ/EF that's more verbose (left joins are often a nightmare) or not-supported, I'll always say that I prefer writing queries in EF than in SQL, at least when dealing with SQL features that are supported by EF (which is a lot of them, it's a very expressive dialect). But EF lets you start with FROM, lets you do whichever keywords you need in whichever order (instead of WHERE -> GROUP BY -> HAVING and you've got to CTE or Subquery if you want another GROUP BY). It also lets you access the members of a group because the objects are still treated as a graph instead of being pulverized into a flat table like SQL does. It also makes your FKs into first-class navigational properties of the table. Like, if I have an addressID and I want to get its country code? In MS SQL that's SELECT CountryCode FROM Country INNER JOIN ProvState ON ProvState.CountryID = Country.ID INNER JOIN Address ON Address.ProvStateID = ProvState.ID WHERE Address.ID = @AddressIDParam In EF that's db.Addresses .Where(a => a.ID == addressIDParam) .Select(a => a.ProvState.Country.CountryCode) EF has a hell of a lot of flaws, but linguistically I love it. Yes there's a lot of aliasing boilerplate in EF, but the ability to walk the foreign keys and the fact that you can put the select after the table name pays off so very well. Also there's a dialect of LINQ that looks more like SQL but it's kind of weird and I don't love it so I prefer to use the lambda syntax above. In that dialect, it's from a in db.Addresses where a == addressIDParam select a.ProvState.Country.CountryCode which is even more terse and SQL-y although I find it a weird linguistic mash-up.
- pragmatic 3y agoThe downside though is you have to grab the sql it’s generating somehow to try to optimize it, figure out what crazy cross apply madness it’s going or to figure out why it’s blowing sql servers recursion limit. I prefer to avoid linq syntax now. It’s a false economy.
- Pxtl 3y agoOh I agree the actual mapping framework where it converts the EF/LINQ is deeply flawed. But that's the underlying implementation. I just mean on a linguistic level, I tremendously prefer EF/LINQ/C# to SQL.
- 3y ago
- imtringued 3y agoI have some ideas how to implement a high performance ORM (yes that sounds strangey doesn't it?) that makes extensive use of CTEs, window functions and temporary tables. Writing reports in that ORM would be downright trivial. From my perspective the JPA speciation is downright harmful. There can never be a good JPA implementation. I don't understand how there are no Java ORMs that actually learned anything from the mess that Hibernate etc are. Take a look at how nasty even some of the most trivial concepts like calculated properties.are: https://blog.eyallupu.com/2009/07/hibernate-derived-properties.html?m=1 https://blog.eyallupu.com/2009/07/hibernate-derived-properti... In my hypothetical ORM design, calculated properties would be easy and fast by default. Also, the CORBA style lazy loading based on proxies is ridiculous. As if people truly wanted a landmine like that to blow up on them.
- hn_throwaway_99 3y agoI agree that I think the difficulty with the uptake will be "it's really just less annoying SQL", and it's hard to overtake a technology that's so ubiquitous when your fixes are really "nice to haves" vs something truly transformational. That said, it's not the succinctness that's an improvement, it's that the pipelined nature of PRQL really maps much better to how people should think about queries, and also how the server executes them! Something as trivially simple as putting the from clause first means I can now get much better typeahead/autocomplete support from dev tools. Heck, I already do this now in a slightly more annoying manner: I write "select * from some_table ..." first, then I go and type out the actual columns I want because once the from clause is correct my IDE will autocomplete/show errors for possible columns.
- mikea1 3y ago> the pipelined nature of PRQL really maps much better to how people should think about queries I disagree. Database engines take SQL and transform it into an execution plan that takes into consideration database metadata (size, storage, index analytics, etc.). Queries should be thought of with a _set based_ instead of _procedural_ approach to maximize the benefits of this abstraction - diving into the implementation details to guide the execution plan formation only when necessary. Also, the pipeline approach could be achieved with common table expressions (CTEs), right? That said, I think PRQL looks promising because it is a solid attempt to make RDBMS development more approachable. I also like that `from` comes before `select`: it is far more readable. A solid and modern IDE experience for PRQL could be a "killer app".
- wodenokoto 3y agoI disagree. I find it extremely hard to reason about large queries as set transformations, whereas it is much easier to break it down to "first this, then that". And this is long before I've even started writing my first line of SQL. So let me write it procedurally and have the optimization engine fix it for me, just like how it fixes my SQL. Even SQL queries are often better understood procedurally. Take this one [1]: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) ORDER BY article; That inner WHERE clause doesn't make sense in my opinion, unless you think of it procedurally as for each row in s1, ask do a search for the highest price amongst all items that share article number. [1] https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html https://dev.mysql.com/doc/refman/8.0/en/example-maximum-colu...
- jtsuken 3y agoBeing able to pick the source (i.e. the FROM clause) first is useful in itself, as you then get the benefit of sensible auto-complete suggestions.
- ravi-delia 3y agoThat's a really simple example though. I think the real pitch for PRQL is that the syntax is way more regular- the group operator just runs a normal pipeline on each group and multiple derivations with different operations between them don't need to all be plotted out in advance, just as an example. SQL suffers because it's not really composable, especially once you get outside of specific modern versions.
- viraptor 3y agoIt's not the tiny changes in the syntax/order that matter here. It's that if you want to limit the columns or do something else with them afterwards, in PRQL you append that text to the end. In SQL you'd have to wrap it around your original query, it inject joins in the middle. It doesn't read well anymore, because "how" you're doing things eclipses "what" you're doing. For a simple select, this just doesn't matter. But you also wouldn't bother with PRQL for simple selects.
- meitham 3y agoIn python I use sqlalchemy core to build the SQL for me, but I manage the transactions myself. Sqlalchemy query builder offers a similar role to what you described, as in I can add another select or where field after I have already added a sort criteria. It’s clever enough to order these correctly based on the SQL dialect.
- jatins 3y agoEven though a small example doesn't highlight the advantages well, I still prefer the PRQL syntax in this because that reads like map/filter code I'd write in some programming language. So the mental model seems easier to me
- krick 3y ago"Order by" is a much better name, by the way. I'm permanently slightly annoyed by the fact that in programming it's a custom to call ordering "sorting" for some unimaginable reason.