PRQL to SQL transformation on-the-fly through the Postgres protocol

Recently I was researching reverse proxies, and I was wondering how to write such a proxy in Rust. I found a little use case that could be interesting: a reverse proxy that sits in front of PostgreSQL, and understands enough about the client/server protocol to intercept queries and do something with them. I discovered PRQL, that claims to be "a modern language for transforming data"and "a simple, powerful, pipelined SQL replacement". So why not allow on-the-fly PRQL to SQL compilation inside the Postgres protocol?

So prqlproxy is a reverse proxy that transmits data between a PostgreSQL client and server, and verifies if when running a query it starts with a magic prefix, in this case "prql:". In this case it assumes everything after the colon is PRQL and uses the Rust compiler to generate SQL that is then passed on to the database server.

This means you can write PRQL queries from psql for example (using the Pagila sample database):

psql -h localhost -p 6142 -d pagila -U postgres

Password for user postgres:

psql (14.7 (Homebrew))

Type "help" for help.

pagila=# prql: from customer

pagila-# select [first_name, last_name]

pagila-# take 1..20;

first_name | last_name

------------+-----------

MARY | SMITH

PATRICIA | JOHNSON

LINDA | WILLIAMS

BARBARA | JONES

ELIZABETH | BROWN

JENNIFER | DAVIS

MARIA | MILLER

SUSAN | WILSON

MARGARET | MOORE

DOROTHY | TAYLOR

LISA | ANDERSON

NANCY | THOMAS

KAREN | JACKSON

BETTY | WHITE

HELEN | HARRIS

SANDRA | MARTIN

DONNA | THOMPSON

CAROL | GARCIA

RUTH | MARTINEZ

SHARON | ROBINSON

(20 rows)

pagila=# prql: I don't know what I'm doing;

ERROR: Unknown name `I`

Note above that PRQL errors are returned to the PostgreSQL client, in this case the server doesn't even get reached.

Of course, this is a very simple toy program, that only supports the simple query protocol (no binding of variables), but it was fun!

Code is on Github of course!