What's the attraction?
For the folks building and supporting PRQL, SQL just has a few too many warts and the popularity of tools like Pandas, dplyr, Polars, LINQ, ... shows that for analytical work we often like to work with our data differently. Other frameworks and languages feel that we should throw out Relational Algebra as well but we feel that's like throwing the baby out with the bathwater.
PRQL's core tenets are that Relational Algebra is foundational and a great abstraction but what's needed is a modern, ergonomic language that allows us to work with data the way most of us conceive of it - as pipelines of transformations.
Personally I feel much more productive in PRQL. I can start with a dataset and append one transformation at a time, writing from top to bottom as I go along without having to jump back and forth between the SELECT and the WHERE and the GROUP BY clause etc... .
Also, if I want to take out a step, I can just comment out that line and the rest of the pipeline usually still works. This might seem like a minor thing but in my experience it's those kind of ergonomics that make the difference in actual day to day work rather than fancy theoretical features you only use once in a blue moon. It's therefore worth noting that this was an intentional design decision. You try and take out some steps from your SQL query and see how well the rest of your query holds up.
Stepping through the second example on that page I know how I could do the same in sql, and I also know that it would be harder for most people to follow.
Question about that example though - why is there an aggregate without an assignment to a variable (average gross_salary) like the other examples?
aggregate {
average total,
...
}
Does that help?
I once tabled a proposal whether each query should be forced to end with a `select` to make the final column list explicit. This could be generated by the compiler. It was felt that that wasn't necessary though and would also be somewhat arbitrary since you also need to know what columns are available at each previous step of the pipeline if you want to be able to make modifications. As the tooling improves, you could perhaps be shown the current list of columns as you hover over each line?
With SQL I have to look all over the place. And it's not just that FROM should come before SELECT, it's that if I'm doing, say, an aggregation with a group by, normal SQL doesn't really have anything to make me think about the ungrouped rows, and then merge them together to get the aggregate values. With PRQL I can just go top to bottom, and for me it's much easier to reason about (i.e. first get all the rows, then group by some specific columns, then take aggregates, etc.)
And I say this as someone who spends about half my days in SQL at present.
1. The SQL that I've seen output by PRQL isn't monstrous, at least no more than it would be coding by hand.
2. I do agree that I don't think PRQL will have much uptake until it is baked in to DB systems, at least as a plugin. One reason (among many) that I hate ORM systems is that with native SQL it's very easy for me to just capture a SQL statement from my DB logs and then search my code for an offending statement. But given how PRQL is transpiled, I don't see any reason it couldn't be essentially part of the query planner, so that query logs etc. are reported in PRQL and not SQL. E.g. the query planner already takes text (SQL) -> Query Plan, it seems rather straightforward to go text (PRQL) -> text (SQL) -> Query Plan, with the SQL intermediate just being an implementation detail I don't have to care about as a DB user.
SELECT substring('PostgreSQL' from 8 for 3);
SELECT substring('PostgreSQL', 8, 3); -- PostgreSQL-only syntax
SELECT trim(both from 'yxSQLxx', 'xyz');
SELECT extract(day from timestamp '2001-02-16 20:38:40');
Maybe if SQL would give me that monumental ask of trailing commas, perhaps I would hate it less.
with PRQL I see that the order will be explicit and set up by developer, so any footguns will be evident.
things like predicate push down, optimization fence, variable rewrite, etc are not needed to be relied upon, because pipeline of query is more explicit in PRQL
also since it is new lang, it can be naturally extended into ETL type data pipelines
also because PRQL can be translated into query execution plan - it can be converted into non-SQL languages like MongoDB or pandas / spark / etc, eliminating SQL altogether for distributed nosql engines
Looking at the examples and the sql generated in the playground you’re just going to have the query planner doing its thing to decide how to execute eventually anyway.
Language arch allows in the future to replace sql for querying data
For people who can write error-free and complex portable SQL queries by heart this may not be really interesting. From experience, however, that is not a skill most developers have.
From the website for instance this is a nightmare to do in SQL:
from employees group role (sort join_date take 1) from employees
group role (
sort join_date
take 1
)
from employees | group role (sort join_date | take 1)
Take the 1st employee
by (earliest) join_date
for each role
from the set of employeesSELECT * FROM employees ORDER BY join_date LIMIT 1 BY role
select earliest_joiner.* from employees as earliest
left join employees as earlier on
earlier.role = earliest.role
and earlier.join_date < earliest.join_date
where earlier.id is null
order by earliest.join_dateThis is indeed a sticky problem, one that usually requires a subselect or other workaround to address the non-determinism of group by + order by; i.e. one cannot simply "select * from employees group by role order by join_date limit 1" and be guaranteed to get the expected ordering.
https://clickhouse.com/docs/en/sql-reference/statements/sele...
However, the syntax can be a bit clunky from time to time. I'm very fortunate that I use Postgres only in the past 5-7 years, so CTEs, and things like Timescale or JSONB, or PlV8 / PlRust for stored procedures are often on the menu. Yet, simple things still require repeating fragments of code from time to time, and complex grouping or window functions code often looks too convoluted.
And as I wrote in my GP comment: I (and many other developers) already use pipelines for data manipulation in code - beyond databases. Say, we got data from an API endpoint, or we mixing data from several data sources. SQL is familiar because I see it often, PRQL is familiar because I use similar pipelining concepts often in my no-SQL code. Would I use PRQL for super simple queries? Probably not. Would I be upset if one of my coworkers used it for simple queries? No, why would I? Would I try implementing complex reporting query using PRQL? Yeah, I would. Partially because I suspect I would get to my data in desired form quicker with pipelines, and partially because I know: if I get stuck I would convert my intermediary query to SQL and pick up from there.
For me PRQL looks better then most DSLs for ORMs or Query Builders in most languages. Adding a new tool to a project may be annoying, but depending on a project I'd rather pay this price once if it makes my less SQL-savvy team mates more comfortable. "Yet another syntax" is obviously the main point against, but from the application developer perspective ORMs / Query Builders often force one to learn "yet another syntax" anyway. "Don't use ORM / Builder" is an often voiced opinion and yet in practice we work with them all the time, too.
So, I view PRQL not as an alternative to SQL but as a third database communication dialect in addition to SQL and my ORM / Query Builder DSLs.