Show HN: PRQL in PostgreSQL

#1

Post by kaspermarstal » Mon, Feb 19, 2024, 11:27 AM UTC

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.

Show HN: PRQL in PostgreSQL
github.com

Re: Show HN: PRQL in PostgreSQL

#2

Post by andy_ppp » Mon, Feb 19, 2024, 12:11 PM UTC

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.

Re: Show HN: PRQL in PostgreSQL

#3

Post by lkuty » Mon, Feb 19, 2024, 12:26 PM UTC

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-optimal language be it PRQL, Ecto, other ORMs, ... ?

Re: Show HN: PRQL in PostgreSQL

#4

Post by murkt » Mon, Feb 19, 2024, 12:41 PM UTC

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…

PRQL is not an ORM in any kind of way, it’s the less quirky SQL, basically.

Re: Show HN: PRQL in PostgreSQL

#5

Post by davedx » Mon, Feb 19, 2024, 12:41 PM UTC

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…

SQL 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?

Re: Show HN: PRQL in PostgreSQL

#6

Post by hosteur » Mon, Feb 19, 2024, 12:41 PM UTC

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…

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

Re: Show HN: PRQL in PostgreSQL

#7

Post by snthpy » Mon, Feb 19, 2024, 12:48 PM UTC

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 you can probably express yourself much faster in PRQL.

Just try the following query in the online PRQL Playground (https://prql-lang.org/playground/) to find the longest track per album:

```prql

from tracks

group album_id (

  sort {-milliseconds}

  take 1

  )

```

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

Disclaimer: I'm a PRQL contributor.

Re: Show HN: PRQL in PostgreSQL

#8

Post by lkuty » Mon, Feb 19, 2024, 12:52 PM UTC

davedx wrote: post #5

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…

SQL 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?

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.

Re: Show HN: PRQL in PostgreSQL

#9

Post by hans_castorp » Mon, Feb 19, 2024, 12:59 PM UTC

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?

    select distinct on (album_id) *
    from tracks
    order by album_id, milliseconds desc;

Re: Show HN: PRQL in PostgreSQL

#10

Post by dewey » Mon, Feb 19, 2024, 1:02 PM UTC

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;

With the additional benefit that resources about SQL are everywhere and every question has been answered already multiple times. On top of that you are most likely to encounter SQL at a job than PRQL.