10 ms·
Show HN: PRQL in PostgreSQL
This extension let's you write PRQL functions in PostgreSQL.
When I first saw PRQL on Hacker News a few months ago, I was immediately captivated by the idea, yet equally disappointed that there was no integration for PostgreSQL. Having previous experience with writing PostgreSQL extensions in C, I thought this was a great opportunity to try out the pgrx framework and decided to integrate PRQL with PostgreSQL myself.
The maintainers of both PRQL and pgrx were very nice to work with. Thanks guys.
- qazxcvbnm 3y agoOff-topic: does anyone know of SQL-to-SQL optimisers or simplifiers? I understand that databases themselves have query-optimisers, and that's not what I'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/subqueries etc. PostgreSQL'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). 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'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's a bit hard to believe that such a thing doesn't exist, given how significant the SQL ecosystem is.
- andy_ppp 3y agoVery 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.
- lkuty 3y agoI 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-optimal language be it PRQL, Ecto, other ORMs, ... ?
- murkt 3y agoPRQL is not an ORM in any kind of way, it’s the less quirky SQL, basically.
- davedx 3y agoSQL is pretty great, but for some things (I thought the example in the readme was decent) it can be quite cumbersome. Stored procedures are very imperative (and hard to debug, depending on the platform IME). This seems like a more functional approach to stored procedures. I don't think it's suggested that this replaces SQL. Use the right tool (and abstraction) for the job?
- lkuty 3y agoMaybe. I already think in terms of transformations (relational algebra and its closure property) when I write SQL and use a lot of CTEs. But I guess the functional way might help people see what's going on.
- CuriouslyC 3y agoI think the debuggability is the #1 issue with database-as-a-platform. Using notices to debug functions is such a poor workflow that even though I'm bullish on putting stuff in PG I avoid writing complex code might need to be debugged as an integrated unit.
- hosteur 3y agoSQL is incompatible with many types of autocompletion. For example columns in a select statement are not known here you write FROM. This alone justifies PRQL in my opinion.
- CuriouslyC 3y agoThere are tools in most languages to deal with this. For example, take https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/. It introspects your database schema to generate types, and gives you autocompletion inside tagged template strings.
- 3y ago
- zoky 3y agoCan 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.
- murermader 3y agoI think I read here on HN some time ago that it is intentionally hard to read to discourage posts with text. I think the reasoning was that posting links to external blogs / websites is usually higher quality than someone creating a quick post on HN. Could be wrong though, just writing this from memory.
- throwaway143829 3y agoHN has some really non-obvious UX, between this and the mysterious green usernames I still don't understand. And the fact that only some users can downvote. Or the weird logic behind which words cannot appear in titles.
- quesera 3y agoGreen usernames are new accounts. Only users with 500 karma can downvote. Both are mechanisms to dull the potency of new users until they have a chance to learn how HN is expected to work. This might sound gatekeeping, and it literally is, but consider than HN signup is open and takes 15 seconds with no verification. HN likes the way HN works and these provide simple rate-limits on destructive or oblivious change.
- Terretta 3y ago> this might sound gatekeeping, and it literally is That term presumes unkept gates are preferable. I grew up farming. One keeps one's gates or both wildlife and livestock run amuck.
- billfruit 3y agoJust out of curiosity, did PRQL evolve from some theoretical innovation or did arise bottom up from practical utilities? Is it a new tool with great new powers or is it just syntactic sugar?
- xmonkee 3y agoit compiles to SQL so no extra powers. But it does make some common patterns more succinct. They have good documentation on the website. Personally, I was very excited about using it to write some complex queries in my application that does some fancy backtesting with sliding windows etc, but I reverted back to SQL pretty quickly because I found myself first thinking in SQL and translating back to PRQL :/
- ttfkam 3y agoYep, just syntactic sugar.
- philipodonnell 3y agoI 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?
- dvdkon 3y agoDamn, now my bachelor's thesis will be less unique :) I'm working on a new language that compiles directly to Postgres' post-analysis structs. It's working out pretty well so far, but my chosen "universal set" (aggregation/array/subquery/... as one thing) semantics are sometimes a pain to encode.
- klysm 3y agoI’m very, very interested in stuff like this. I think SQL is a bad API to the capabilities of Postgres and I want to be able to speak to it directly.
- dangoldin 3y agoNot to change your direction but something I've been toying around is being able to support Algebraic types when defining tables. That way you can offload a lot of the error checking to the database engine's type system and keep application code simpler.
- dvdkon 3y agoI'd like to do something like that too, if/when I ever get to replacing the DDL. In Postgres you could create custom types for tagged unions, but it might be better to translate table-level unions to a set of constraints, for performance and flexibility (you can't create referential integrity constraints using expressions IIRC).
- andyferris 3y agoSounds wonderful. I actually think this is the highest value thing anyone could contribute to Postgres (assuming it could handle foreign key constraints inside the sum types).
- buremba 3y agoSounds interesting! What's the benefit of compiling directly to Postgres's internal structs over compiling to SQL?
- 3y ago
- fforflo 3y agoNice 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 "data transformation" language is jq and awk is second. PRQL and EdgeQL (EdgeDB) are the most interesting ones to watch how they evolve, though. I've also written a PG extension to make jq available in Postgres [0] I believe Postgres, in general, will flourish as a host for DSL languages [1]. 0: https://github.com/Florents-Tselai/pgJQ https://github.com/Florents-Tselai/pgJQ 1: https://tselai.com/pgjq-dsl-database.html https://tselai.com/pgjq-dsl-database.html
- canadiantim 3y agoWould love to see EdgeQL become adopted beyond EdgeDB. I don't like the vendor lock-in with EdgeDB, but I think they're doing great work
- fforflo 3y agoYes, they are. Beyond the core database offering, I'd also like to underline the quality of their software engineering work. There are not many Python-powered databases out there; their codebase has some real gems from the setup.py to their core compiler and Postgres-based storage layer.
- 1st1 3y ago> Would love to see EdgeQL become adopted beyond EdgeDB We'll soon be announcing some interesting developments on that front, stay tuned :)
- ttfkam 3y ago"A jaw-dropping amount of effort has been spent attempting to bridge the gap between the relational paradigm of SQL and the object-oriented nature of modern programming languages. EdgeDB sidesteps this problem by modeling data in an object-relational way." All the best to the team. I however truly hope this isn't the direction the industry moves toward. I thought we learned our lesson from MongoDB. I still believe data is best modeled in sets, not objects. The solution isn't for databases to become more like object stores but for general purpose programming languages to be more amenable to seamless access of set-oriented data. More stuff like this: https://github.com/porsager/postgres https://github.com/porsager/postgres https://github.com/launchbadge/sqlx https://github.com/launchbadge/sqlx
- fkyoureadthedoc 3y agoPRQL looks interesting enough to at least give it an honest try. > PRQL allows for powerful autocomplete, type-checking, and helpful error messages (in progress) Without some kind of autocomplete though I'm a lot less motivated to do so.
- grepknfss 3y agoYou should make the first instance of “PQRL” in your readme a link to that project.
- kaspermarstal 3y agoGood suggestion, thanks
- dang 3y agoRelated: PRQL as a DuckDB Extension - https://news.ycombinator.com/item?id=39130736 https://news.ycombinator.com/item?id=39130736 - Jan 2024 (47 comments) PRQL: Pipelined Relational Query Language - https://news.ycombinator.com/item?id=36866861 https://news.ycombinator.com/item?id=36866861 - July 2023 (209 comments) Calculate the Digits of Pi with DuckDB and PRQL - https://news.ycombinator.com/item?id=35153824 https://news.ycombinator.com/item?id=35153824 - March 2023 (1 comment) One Year of PRQL - a modern language for relational data - https://news.ycombinator.com/item?id=34690560 https://news.ycombinator.com/item?id=34690560 - Feb 2023 (1 comment) PRQL: a simple, powerful, pipelined SQL replacement - https://news.ycombinator.com/item?id=34181319 https://news.ycombinator.com/item?id=34181319 - Dec 2022 (215 comments) Show HN: PRQL 0.2 – a better SQL - https://news.ycombinator.com/item?id=31897430 https://news.ycombinator.com/item?id=31897430 - June 2022 (159 comments) PRQL – A proposal for a better SQL - https://news.ycombinator.com/item?id=30060784 https://news.ycombinator.com/item?id=30060784 - Jan 2022 (292 comments)
- mikpanko 3y agoThere 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?
- chobanilover 3y agoWhen it comes to data stack tooling, organizations aren't always optimizing for a better way to do things as much as minimizing the worst possible scenario. New syntax is nice, but it means that analysts and engineers need to learn something new and are more likely to make mistakes that could bubble up to production. There's always an argument to be made why shiny new tool XYZ is better, but unless it's 100X better, organizations are reluctant to switch from something like vanilla PostgreSQL that they know works 100% of the time.
- chaps 3y agoNormally I would agree, except for the fact that this system works by converting PRQL to SQL. So it's not 100% throwing out the baby with the bath water, since there there are still means for newer engineers to learn SQL through this tooling!
- smabie 3y agoCan this be used inside Grafana?
- landingunless 3y agoNice to see extensions like this one developed in Rust/pgrx. Reminds me of https://github.com/tcdi/plrust https://github.com/tcdi/plrust
- kaspermarstal 3y agoYes, the PL/Rust code base was a very useful when developing this extension
- brikym 3y agoIt 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.
- dataspun 3y agoWhy 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?
- kaspermarstal 3y agoThis extension has been developed on top of pgrx and depends on the platforms that pgrx supports. From the pgrx readme: > Windows is not supported. It could be, but will require a bit of work with cargo-pgrx and figuring out how to compile pgrx's "cshim" static library.
- mt_ 3y agoIs PRQL faster than the standard query model?
- Sammi 3y agoPRQL compiles to SQL. Why would it be faster? The intention is rather for it to be simpler, as it uses a linear direction of data handling. SQL jumps back and forth with its order of operations and can be confusing in this way. PRQL also has a more modern syntax that reuses more universal concepts with fewer keywords to learn. In contrast to SQL which has a unique keyword, syntax, and behavior for everything.
- zokier 3y agoI think more relevant question would be is naive idiomatic prql faster than naive idiomatic sql? Of course you can tune any sql to hell and back, but the chances for some non-expert developer to land on anything nearly optimal sql are not so great. So if prql helps non-experts to get decent perf easier, I'd chalk that up as a win, and that is not so outlandish goal anymore.
- Sammi 3y agoThis should be possible.