Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: PRQL in PostgreSQL

github.com

Post reply on HN
Print view View original

11–20 of 142 posts

Re: Show HN: PRQL in PostgreSQL

#11

Post by lkuty »

snthpy wrote: post #7
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

This comes up every time PRQL makes it onto HackerNews and is a fair question: 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 yo…

Sorry to be that guy :-) The `introduction.prql` example on the playground gives an idea of the better readability of PRQL vs SQL and your small example an idea of the speed you may gain from writing PRQL vs SQL. It is interesting. Indeed, me writing the SQL would have taken more time than you writing the PRQL.

Re: Show HN: PRQL in PostgreSQL

#12

Post by baq »

lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

> Why limit ourselves with a sub-optimal language be it PRQL?

Actually why limit yourself with SQL...?

PRQL is a language compiled into SQL and makes certain hard-to-do things in SQL easy purely because it allows to streamline operations which SQL needs CTE joins or whatever hoop jumping to solve.

My favorite example which sounds easy but isn't - select the row which is MAX(...).

Re: Show HN: PRQL in PostgreSQL

#13

Post by GordonS »

snthpy wrote: post #7
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

This comes up every time PRQL makes it onto HackerNews and is a fair question: 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 yo…

> How long would it take you to write the SQL for that?

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.

Re: Show HN: PRQL in PostgreSQL

#14

Post by zoky »

Can someone explain to me why “Show HN” and “Ask HN” posts are always, always in hard-to-read light gray? Is there some cabal that automatically downvotes such posts? Is there something intrinsic to The Algorithm that penalizes such posts? Or do I just have really bad luck and always manage to click through on posts that have gained traction despite having gotten enough downvotes to put them at risk of sudden termination?

This is one of the great HN mysteries to me, and if anyone can shed some light on it, it would be much appreciated.

Re: Show HN: PRQL in PostgreSQL

#15

Post by vendiddy »

lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

The thing I hate most about SQL is lack of composability.

In most languages it's easy to pull out functions.

In SQL you end up with a giant hard to comprehend mess.

I think the underlying relational concepts in SQL are sound but I'd love to see ideas like PRQL that aim make SQL easier to write and maintain.

Stored procedures and functions are nice but don't allow the basic idea of breaking a large query apart into smaller logical components.

Re: Show HN: PRQL in PostgreSQL

#16

Post by hobs »

hans_castorp wrote: post #9
snthpy wrote: post #7

Earlier quoted context omitted.

This comes up every time PRQL makes it onto HackerNews and is a fair question: 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 yo…

> How long would it take you to write the SQL for that? select distinct on (album_id) * from tracks order by album_id, milliseconds desc;

You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.

Re: Show HN: PRQL in PostgreSQL

#17

Post by CuriouslyC »

snthpy wrote: post #7
lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

This comes up every time PRQL makes it onto HackerNews and is a fair question: 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 yo…

This seems like a micro-optimization to me. That's faster to write, but you pay for that with extra tooling and an abstraction layer that you now have to train or hire for, and I'm not sure that's a good payoff.

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.

Re: Show HN: PRQL in PostgreSQL

#18

Post by haspok »

lkuty wrote: post #3

I don't understand the need for libraries that abstract away SQL when you could just write SQL directly and have full access to the power of the language which is quite rich (recursive CTE, windowing, ... aka Modern SQL). You could also use stored procedures/functions for more complex stuff and e.g. JSON (or native types) to transfer data between the database and the application. Why limit ourselves with a sub-optima…

One notable reason is being database-agnostic. Like ORM's, if you can generate SQL you can generate database-specific SQL as well.

SQL is also quite verbose in places (JOINs are the most trivial example), and lack a decent amount of abstraction (CTEs are relatively low level).

Updating a large set of FK'd tables can be a nightmare (this is what ORMs shine at).

Finally, some modern additions are quite unreadable, Postgres' JSON syntax, for example.

I'm not saying that PRQL solves any of the above, but these are all legit problems with "plain" SQL.

Re: Show HN: PRQL in PostgreSQL

#19

Post by sph »

CuriouslyC wrote: post #17
snthpy wrote: post #7

Earlier quoted context omitted.

This comes up every time PRQL makes it onto HackerNews and is a fair question: 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 yo…

This seems like a micro-optimization to me. That's faster to write, but you pay for that with extra tooling and an abstraction layer that you now have to train or hire for, and I'm not sure that's a good payoff. 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.

You can apply this logic to choosing a high level language vs writing assembly code.

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.)

Re: Show HN: PRQL in PostgreSQL

#20

Post by hans_castorp »

hobs wrote: post #16
hans_castorp wrote: post #9

Earlier quoted context omitted.

> How long would it take you to write the SQL for that? select distinct on (album_id) * from tracks order by album_id, milliseconds desc;

You'd also need a LIMIT or a TOP, and you might need a subquery for that depending on your SQL of choice.

The question was "the longest track per album", so I'd expect one row per album as the result (which is what my query does). But adding a LIMIT would not do that.

It might be that the question was "the longest track across all albums" which indeed would require a LIMIT

Post reply on HN