What The Heck Is PRQL?

Introduction

When I did my first “What The Heck Is…?” on DuckDB a few months ago, I expected that to be a one-off, but then I ran across Malloy and did another one. Now I’ve run across PRQL (pronounced Prequel) which stands for “Pipelined Relational Query Language”, and I’m wondering what the heck it is exactly, so this is looking like a series as long as I keep finding things I want to know what the heck they are. At first glance, PRQL seems very similar to Malloy, so join me won’t you while we explore what the heck PRQL is.

Digging In

From the PRQL website, they describe it this way:

“PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement. Like SQL, it’s readable, explicit, and declarative. Unlike SQL, it forms a logical pipeline of transformations and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it compiles to SQL.” continuing..

“PRQL consists of a curated set of orthogonal transformations, which are combined together to form a pipeline. That makes it easy to compose and extend queries. The language also benefits from modern features, such syntax for dates, ranges and f-strings as well as functions, type checking and better null handling.”

Let’s look at a couple of screenshots of examples of PRQL, I’m using screenshots to preserve the formatting and syntax coloring, I wasn’t pleased with the results in my piece about Malloy. The resulting SQL, here is a basic example that reads from the employees’ table and shows the id, name, and age, sorted by age and limited to 10:

How about this example where we read from the employee table and do some date math:

I love the brevity of this example, which really illustrates how simple you can make a task with PRQL:

And just one more that illustrates the all-important JOIN:

Ecosystem

There is a concise set of documentation for everything you can read online. In addition to that, there is a robust, growing list of tools and integrations:

• Integrations
• dbt — Allows writing PRQL in dbt models.
• Jupyter/IPython — PyPrql contains a Jupyter extension, which can also be used with DuckDB
• Visual Studio Code — Extension for syntax highlighting
• Prefect — Adds PRQL models to your Prefect workflows
• Tools
• Playground — In-browser (WASM) playground that compiles PRQL to SQL interactively
• PyPrql — Python TUI to connect to databases, includes a native interactive console for Jupyter/IPython
• Bindings
• prql-python — Python bindings for prql-compiler
• prql-js — JavaScroipt bindings for prql-compiler
• prql-compiler — PRQL compiler library (written in Rust)

Let’s take a little look at the playground while we’re at it, I’m a big fan of all these projects illustrating cool uses of WASM of late. This first example is joining multiple tables to look at average salaries per department. The PRQL is less than half the code and arguably, easier to read:

This next one is more complex, and while only a few lines shorter based on the formatting for readability, I’d argue is very readable. Obviously, you need to get accustomed to the syntax, but I like where this is headed.

Summary

SQL is certainly ubiquitous, but it isn’t without its problems, especially around scaling and reusability. Between Malloy and PRQL in particular, there are now some compelling alternatives to look at. Yes, as I said with Malloy, it’s another language, but it is generally easy to read, write and understand. I think they probably could have just used some SQL keywords like “LIMIT” instead of “TAKE” and maybe kept “SELECT” instead of “DERIVE”, but I get what they are doing. I would have liked to have seen bindings for golang and Julia, but I imagine those will show up when someone is interested enough.

I really appreciated the WASM playground they provided, and it’s probably something a SQL person would want to keep handy as a reference to make sure things are being translated as expected. This article was really meant to just bring your attention to something you might find useful Is it for you? Try it and see, and big kudos to the core developers Aljaž Mur Eržen, Maximilian Roos, and Charlie Sanders for their hard work, and make sure to follow the project on GitHub and Twitter as well.