Show HN: PRQL in PostgreSQL

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.

> Would love to see EdgeQL become adopted beyond EdgeDB

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

    "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

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

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

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.

Normally 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!

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

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

Reformatted for readability (indent code blocks with two spaces):

  from tracks
  group album_id (
    sort {-milliseconds}
    take 1
  )

Editorializing:

Data query specification is all about getting the details right. This does not look simpler than the corresponding SQL to me though. All components must be present -- scope, group, limit, order.

SQL, for all its faults, is generally succinct at incorporating the required details. The PRQL sample here is succinct as well, but to me at least, not differentiating.

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

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.

> 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;

select id from tracks qualify row_number() over partition by (album_id order by milliseconds desc) =1

That should work

Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive.

Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.

In the real world, probably something like:

    select
      album_id 
      ,first_value(id order by milliseconds desc) as longest_track_id
    from tracks
    group by album_id

I agree the PRQL's pretty nice here, but I think such a generalised example (chances you actually want to `select *`?) overstates the advantage.

They can ask chatgpt to generate the SQL query and use the SQL output that everyone is familiar with rather than use an abstraction that is prone to versioning and behavioral changes and will consume everyone else's time to go learn a new language and become profficient in it.

I have a feeling this language will be more familiar to programmers who think functionally. I say this because it seems to consist of transformations applied to data and of `derive`, which defines new variables later used in other transformations.

This would have been awesome for me 2 years ago.

Currently much of my complicated SQL is generated by a LLM.

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.

Won't this just lead to developers wrecking performance because they don't understand what's happening?

Your statement about breaking large queries apart is wrong. You can write queries with CTES to improve readability, and extract CTEs into functions that can be selected as queries get too large and unwieldly. SQL is just as composable as any other language.

The thing that's lacking right now is the tooling for managing/testing/deploying database code. There are solutions out there and the supabase folks have been working to make things better but database first development still has some hurdles in terms of DX.

Composition is available in sql, but works a bit different than in a procedural language. In sql you express sets of data, composition consists of defining subsets that you compose into more complex sets. Views and CTEs are the tools for composition in SQL.

There are tools in most languages to deal with this. For example, take https://jawj.github.io/zapatos/. It introspects your database schema to generate types, and gives you autocompletion inside tagged template strings.

It's not a real problem in a practical sense. Yes, you have to write the FROM clause before autocomplete happens even though the SELECT must appear before it. Fortunately, however, text editiors used as IDEs allow for out-of-sequence code editing. You can just enter the FROM and move back to complete the SELECT.

It's like complaining that you have to know the variable name you're going to assign something to before you start writing the expression that will set the value.

  int y = x * 2;

The idea is to evaluate the expression and store it, but the expression doesn't actually read that way left-to-right. Wouldn't it make more sense for it to be:

  x * 2 assign to new int y;

Technically, that's written more in execution order. In practice it just isn't that big of a deal. It only trips up beginners.

Jetbrains tools (Datagrip, IntelliJ IDEA, PyCharm) deal well with this if you leave a placeholder column before FROM, and write the FROM part with proper joins. You can go back to the columns and autocomplete goes just fine. You can amend our extended any part and the autocomplete logic adapts well.

Database agnosticism is so 2010. There's very little reason to choose a DB other than postgres, and if you have a reason to choose a specific niche db you're not probably not going to be migrating away from it any time soon.

CTEs are a first step in structuring queries to make them decomposable. You can extract CTEs to functions and mark them stable and it's logically equivalent to the original query.

How are joins verbose? It’s pretty straight to the point: combine these 2 tables on these columns… what do you want to remove to make it less verbose?

Can you expand on this? I have always seen SQL as a DSL for set theory. How are you seeing things "mathematically" that do not comport with set manipulation in SQL?

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

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

> Actually why limit yourself with SQL...?

Because it's everywhere, has extensive documentation and tutorials, all database tools support it, all relational engines support it, some non-relational engines support it, all programming languages have library support for it, it can be accessed through command line tools as well as graphical interfaces, etc.

You think an industry is going to give up 50 years of infrastructure because some (typically junior) devs think the syntax is "kinda icky"?

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

If you look earlier in the comments you will see queries that have "DISTINCT ON" in them. It solves the problem that sounds easy, but actually is pretty easy if you know SQL.

1. Notice language is complicated for some tasks

2. Propose newer, simpler language to take care of these

3. Newer, simpler language lacks features of original language

4. Newer language adds features, making it more complicated

5. GOTO 1