This idea seems like it'd be better as an editor plugin that lets you write shorthand and have it automatically expanded into correct SQL rather than as a build time thing.
Yes the pipeline is more complex, there are more tools and more syntax to track, but the benefits are pretty clear (or we'd all be writing UI code in hand written asm)
While leaky abstractions are a huge problem, the thing about abstractions is that, if they are any good, the benefits and improvements to productivity outweigh the negatives. You just have to figure out if the gains PRQL could give you are worth the effort.
I rarely write SQL, so it's not worth it for me. But if PRQL were an actual query engine, not just a translation layer, and some database offered a native PRQL interface, I would immediately switch to it rather than to keep twisting my brain with SQL and it's inane syntax and rules.
(I was a full time DBA in one previous life, so I should be more comfortable with SQL than most.)
I don't want to appear rude, but unless I'm missing something, this is a pretty simple SQL query, of the kind anyone with mimimal SQL experience could write off the top of their head in seconds.
I like the idea of PRQL, but I think a better example is needed to sell it.
The same logic is applied to TypeScript vs JavaScript. Or C vs assembly. Or Nano vs vim. Etc etc.
It's a quandary though. SQL is clearly difficult for most people to get their heads around. It does require a different way of thinking about data, and you can get by with a minimal SQL knowledge for a long time, especially nowadays with ORMs.
But like so many other things, making the investment is worthwhile and pays off in small and large ways, forever (so far).
And... 'making the investment' takes time, and means that time is not able to be invested someplace else.
If the majority of your job is writing SQL or similar (data access, etc) then sure - yes, learn more of those tools. Some folks have a wider range of responsibilities that means you have to decide what to make more time investments in, and saying 'yes' to something is necessarily saying 'no' to other things.
But 20 hours spent learning SQL pays off for a lifetime. Most people watch more hours of TV than that per week.
We all make our choices, but very very few people could not find 20 hours somewhere in their lives, with zero net loss.
The problem this is resolving, if it successfully resolves anything at all, is that the SQL language is a mess of random keywords, inconsistent syntax requirements and generates some of the worst error messages known to man. It’s an attempt at making SQL a consistent, simple language — ideally exposing the data model more directly and with less noise
As for SQL being too much like English, making the syntax closer to a general purpose functional programming language isn't necessarily an improvement in my opinion.
It’s more about having a consistent, predictable and simple language. Being more functional-like or imperative-like or declarative-like or whatever is just a byproduct. I suppose LISP is the extreme of that goal, and probably too extreme, but SQL itself is on the other end, with every clause and function running its own subsyntax and special cases. I believe I’ve read the ANSI SQL standard defines something like 1700 terminals — it’s absolutely absurd. And of course every database extends that standard arbitrarily with a slew of new keywords.
There’s a reason every RDBMS can only report errors like “syntax error on line 1: <entire query>” and it’s not because the devs are completely incompetent
Here's the SQL it generates:
WITH table_0 AS ( SELECT , ROW_NUMBER() OVER ( PARTITION BY album_id ORDER BY milliseconds DESC ) AS _expr_0 FROM tracks ) SELECT FROM table_0 WHERE _expr_0 <= 1
If I understand PRQL correctly, it finds the longest song for each album? A simple concept, but not a simple MySQL query.
For those unfamiliar with Postgres, DISTINCT ON takes only one row for each of the groups based on the supplied columns. So in this case, it will return only one row per album_id.
Without an ORDER BY, DISTINCT ON chooses a random row (not actually, but you can’t rely on it.) Since we ORDER BY milliseconds DESC, the first row of each group will be the longest one.
Do you believe these cases are common enough to warrant discarding the tools and training available to SQL? Are you also certain that PRQL doesn't have corner cases where SQL is more concise and/or easier to understand?
WITH ranked_tracks AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY album_id
ORDER BY milliseconds DESC
) AS row_num
FROM tracks
)
SELECT *
FROM ranked_tracks
WHERE
row_num = 1
SELECT *
FROM tracks
WHERE
ROW_NUMBER() OVER (
PARTITION BY album_id
ORDER BY milliseconds DESC
) = 1
E.g. see: https://stackoverflow.com/questions/1313120/retrieving-the-l...
select distinct on (album_id) *
from tracks
order by album_id, milliseconds desc;It might be that the question was "the longest track across all albums" which indeed would require a LIMIT
A standard* method would be:
SELECT *
FROM tracks
QUALIFY row_number() over (partition by album_id order by milliseconds desc) = 1;
SELECT *
FROM (
SELECT *
,row_number() over (partition by album_id order by milliseconds desc) rn
FROM tracks
) x
WHERE x.rn = 1;
Ultimately I think it's a tool that lets the developer thinking about the problem in the way they prefer, rather than thinking about the problem in the way that best suits the problem at hand. Like insisting on writing documentation in LaTeX instead of Word or Markdown.
*: I believed this was in SQL 2023, but double checking it looks like it did not have make the final standard. I would be surprised if it didn't make it in the future, however.
And even then, it'll be another 6 years before your application vendor finally upgrades to it.
And even then, it'll be another 6 years before the database feature is allowed to be enabled.
And even then, your reporting software won't support it.
The bigger problem, is the "over use of tools to boost developers" the real problem, is putting poor developers into the pipeline. complex analytics is SQL is just simple, and lovely. The fact other struggle is not going to be helped by pretending the "code" looks more C like, they need to learn to think like a performant machine, and then be productive.
Since when is QUALIFY part of the SQL standard? So far I have only seen it as a proprietary feature in Terradata.
That should work
Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive.
Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.
from tracks
group album_id (
sort {-milliseconds}
take 1
)
Data query specification is all about getting the details right. This does not look simpler than the corresponding SQL to me though. All components must be present -- scope, group, limit, order.
SQL, for all its faults, is generally succinct at incorporating the required details. The PRQL sample here is succinct as well, but to me at least, not differentiating.
Currently much of my complicated SQL is generated by a LLM.
select
album_id
,first_value(id order by milliseconds desc) as longest_track_id
from tracks
group by album_id
You could do this:
select distinct
album_id
,first_value(id order by milliseconds desc) as longest_track_id
from tracks
Short answer: DX
Slightly longer answer: Developer productivity and experience, especially for EDA and interactively writing complex analytical queries.
Most people that have tried PRQL just find it more convenient to write their analytical queries in it. PRQL compiles to SQL so it can't express anything you can't already do in SQL, but you can probably express yourself much faster in PRQL.
Just try the following query in the online PRQL Playground (https://prql-lang.org/playground/) to find the longest track per album:
```prql
from tracks
group album_id (
```How long would it take you to write the SQL for that?
Disclaimer: I'm a PRQL contributor.