Show HN: PRQL in PostgreSQL

fforflo · 2 years ago

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 "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 1: https://tselai.com/pgjq-dsl-database.html

canadiantim · 2 years ago

Would 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 · 2 years ago

Yes, 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 · 2 years ago

> Would love to see EdgeQL become adopted beyond EdgeDB

We'll soon be announcing some interesting developments on that front, stay tuned :)

ttfkam · 2 years 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/launchbadge/sqlx

dvdkon · 2 years ago

Damn, 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.

dangoldin · 2 years ago

Not 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 · 2 years ago

I'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 · 2 years ago

Sounds 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 · 2 years ago

Sounds interesting! What's the benefit of compiling directly to Postgres's internal structs over compiling to SQL?

dvdkon · 2 years ago

There's little direct benefit, since the internal structs pretty closely model SQL. But having the language compiler a part of the Postgres process does help. It gives you easy access to the database's structure, so you know the type of every identifier, what columns tables have, what functions are available, etc. You can then do your own (better) error reporting and, more importantly, move away from SQL's semantics.

For example, I want to have universal broadcasting of operators on subquery results, array values, and aggregated columns. To do this, I need to know which of these the operand expressions represent, which is slow or impossible with transpilation.

klysm · 2 years ago

I’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.

andy_ppp · 2 years ago

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.

dang · 2 years ago

PRQL: Pipelined Relational Query Language - 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 - March 2023 (1 comment)

One Year of PRQL - a modern language for relational data - 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 - Dec 2022 (215 comments)

Show HN: PRQL 0.2 – a better SQL - 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 - Jan 2022 (292 comments)

landingunless · 2 years ago

Nice to see extensions like this one developed in Rust/pgrx. Reminds me of https://github.com/tcdi/plrust

kaspermarstal · 2 years ago

Yes, the PL/Rust code base was a very useful when developing this extension

qazxcvbnm · 2 years ago

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

grepknfss · 2 years ago

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

kaspermarstal · 2 years ago

Good suggestion, thanks

brikym · 2 years ago

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.