Introduction

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

Let’s get started with an example:

PRQL

1. from employees
2. filter start_date > @2021-01-01               # Clear date syntax
3. derive [                                      # `derive` adds columns / variables
4. gross_salary = salary + (tax ?? 0),         # Terse coalesce
5. gross_cost = gross_salary + benefits_cost,  # Variables can use other variables
6. ]
7. filter gross_cost > 0
8. group [title, country] (                      # `group` runs a pipeline over each group
9. aggregate [                                 # `aggregate` reduces each group to a value
10. average gross_salary,
11. sum_gross_cost = sum gross_cost,          # `=` sets a column name
12. ]
13. )
14. filter sum_gross_cost > 100_000               # `filter` replaces both of SQL's `WHERE` & `HAVING`
15. derive id = f"{title}_{country}"              # F-strings like Python
16. derive country_code = s"LEFT(country, 2)"     # S-strings allow using SQL as an escape hatch
17. sort [sum_gross_cost, -country]               # `-country` means descending order
18. take 1..20                                    # Range expressions (also valid here as `take 20`)

SQL

1. WITH table_1 AS (
2. SELECT
3. title,
4. country,
5. salary + COALESCE(tax, 0) + benefits_cost AS _expr_0,
6. salary + COALESCE(tax, 0) AS _expr_1
7. FROM
8. employees
9. WHERE
10. start_date > DATE '2021-01-01'
11. )
12. SELECT
13. title,
14. country,
15. AVG(_expr_1),
16. SUM(_expr_0) AS sum_gross_cost,
17. CONCAT(title, '_', country) AS id,
18. LEFT(country, 2) AS country_code
19. FROM
20. table_1 AS table_0
21. WHERE
22. _expr_0 > 0
23. GROUP BY
24. title,
25. country
26. HAVING
27. SUM(_expr_0) > 100000
28. ORDER BY
29. sum_gross_cost,
30. country DESC
31. LIMIT
32. 20

As you can see, PRQL is a linear pipeline of transformations — each line of the query is a transformation of the previous line’s result.

You can see that in SQL, operations do not follow one another, which makes it hard to compose larger queries.