Show HN: PRQL in PostgreSQL

Nice work. A few months back, I experimented with having a DSL like PRQL in Postgres, but back then, I found the language a bit cumbersome; however, it was great as an idea. IMHO, the best &quot;data transformation&quot; language is jq and awk is second.<p>PRQL and EdgeQL (EdgeDB) are the most interesting ones to watch how they evolve, though.<p>I&#x27;ve also written a PG extension to make jq available in Postgres [0]<p>I believe Postgres, in general, will flourish as a host for DSL languages [1].<p>0: <a href="https:&#x2F;&#x2F;github.com&#x2F;Florents-Tselai&#x2F;pgJQ">https:&#x2F;&#x2F;github.com&#x2F;Florents-Tselai&#x2F;pgJQ</a> 1: <a href="https:&#x2F;&#x2F;tselai.com&#x2F;pgjq-dsl-database.html" rel="nofollow">https:&#x2F;&#x2F;tselai.com&#x2F;pgjq-dsl-database.html</a>

Damn, now my bachelor&#x27;s thesis will be less unique :)<p>I&#x27;m working on a new language that compiles directly to Postgres&#x27; post-analysis structs. It&#x27;s working out pretty well so far, but my chosen &quot;universal set&quot; (aggregation&#x2F;array&#x2F;subquery&#x2F;... as one thing) semantics are sometimes a pain to encode.

Very interesting, it looks a lot like the Elixir package Ecto that has a DSL for writing SQL queries. Obviously there are some differences here and I wonder if the compiler can do further optimisations than Ecto can but interesting to see they align quite a bit.

Related:<p><i>PRQL as a DuckDB Extension</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=39130736">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=39130736</a> - Jan 2024 (47 comments)<p><i>PRQL: Pipelined Relational Query Language</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=36866861">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=36866861</a> - July 2023 (209 comments)<p><i>Calculate the Digits of Pi with DuckDB and PRQL</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=35153824">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=35153824</a> - March 2023 (1 comment)<p><i>One Year of PRQL - a modern language for relational data</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34690560">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34690560</a> - Feb 2023 (1 comment)<p><i>PRQL: a simple, powerful, pipelined SQL replacement</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34181319">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=34181319</a> - Dec 2022 (215 comments)<p><i>Show HN: PRQL 0.2 – a better SQL</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=31897430">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=31897430</a> - June 2022 (159 comments)<p><i>PRQL – A proposal for a better SQL</i> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=30060784">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=30060784</a> - Jan 2022 (292 comments)

Nice to see extensions like this one developed in Rust&#x2F;pgrx. Reminds me of <a href="https:&#x2F;&#x2F;github.com&#x2F;tcdi&#x2F;plrust">https:&#x2F;&#x2F;github.com&#x2F;tcdi&#x2F;plrust</a>

Off-topic: does anyone know of SQL-to-SQL optimisers or simplifiers? I understand that databases themselves have query-optimisers, and that&#x27;s not what I&#x27;m talking about - I work with generated query systems and SQL macro systems that make fairly complex queries quite easy to generate, but often times come up with unnecessary joins&#x2F;subqueries etc.<p>PostgreSQL&#x27;s query-optimiser does handle these cases quite well for me once I explain and add the appropriate indexes, yet complex source queries carry undiscountable costs (longer planning times, missed optimisations e.g. predicate pushdowns).<p>I find myself needing to mechanically transform and simplify SQL every now and then, and it hardly seems something out of reach of automation, yet somehow I&#x27;ve never been able to find software that simplifies and transforms SQL source-to-source. When I look, I only find optimisers for SQL execution plans. It&#x27;s a bit hard to believe that such a thing doesn&#x27;t exist, given how significant the SQL ecosystem is.

You should make the first instance of “PQRL” in your readme a link to that project.

It looks a lot like Microsoft’s Kusto query language which is a pleasure to use. Piping is better than nesting and from-first is the way to go as it’s necessary for autocomplete.

Just out of curiosity, did PRQL evolve from some theoretical innovation or did arise bottom up from practical utilities?<p>Is it a new tool with great new powers or is it just syntactic sugar?

I often wonder if NL-SQL tasks would benefit from an intermediate query language that is more compatible with the next-logical token approach that is used to generate the code. Obviously there is less of this in the training set, but if it transpires in a testable way, you could generate training data yourself from known good sql queries? Are there any languages that have been designed specifically for this?

There are very interesting improvements to SQL, which are much more ergonomic, extend functionality, and provide higher-level abstractions. Also backward compatible. PRQL and Malloy immediately come to mind but there are more. Anybody has good explanations why they struggle to get wide adoption?

PRQL looks interesting enough to at least give it an honest try.<p>&gt; PRQL allows for powerful autocomplete, type-checking, and helpful error messages (in progress)<p>Without some kind of autocomplete though I&#x27;m a lot less motivated to do so.

Why is this PRQL extension for Postgres limited to Mac and Linux? What dependencies on Windows are the obstacles, and is there an expected solution in the near-term?

Is PRQL faster than the standard query model?

Can this be used inside Grafana?

Can someone explain to me why “Show HN” and “Ask HN” posts are always, <i>always</i> in hard-to-read light gray? Is there some cabal that automatically downvotes such posts? Is there something intrinsic to <i>The Algorithm</i> 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?<p>This is one of the great HN mysteries to me, and if anyone can shed some light on it, it would be much appreciated.

I don&#x27;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&#x2F;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-optimal language be it PRQL, Ecto, other ORMs, ... ?