bcho/prql

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement

PRQL

Pipelined Relational Query Language, pronounced "Prequel".

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.

PRQL can be as simple as:

from employees
filter country == "USA"                       # Each line transforms the previous result
aggregate [                                   # `aggregate` reduces each column to a value
  max salary,
  min salary,
  count,                                      # Trailing commas are allowed
]

Here's a fuller example of the language;

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

For more on the language, more examples & comparisons with SQL, visit prql-lang.org. To experiment with PRQL in the browser, check out PRQL Playground.

Current Status - February 2023

PRQL is being actively developed by a growing community. It's ready to use by the intrepid, either as part of one of our supported extensions, or within your own tools, using one of our supported language bindings.

PRQL continues to evolve toward the 0.5 Milestone. The CHANGELOG.md gives more information.

PRQL still has some minor bugs and some missing features, and probably is only ready to rolled out to non-technical teams for fairly simple queries. We're exploring where to focus further development; we welcome use-cases. Here's our current Roadmap and our longer-term set of Milestones.

Get involved

To stay in touch with PRQL:

• Follow us on Twitter
• Join us on Discord
• Star this repo
• Contribute — join us in building PRQL, through writing code (send us your use-cases!), or inspiring others to use it.
• See the development documentation for PRQL. It's easy to get started — the project can be built in a couple of commands, and we're a really friendly community!

Explore

• PRQL Playground — experiment with PRQL in the browser.
• PRQL Book — the language documentation.
• dbt-prql — write PRQL in dbt models.
• Jupyter magic — run PRQL in Jupyter, either against a DB, or a Pandas DataFrame / CSV / Parquet file through DuckDB.
• pyprql Docs — the pyprql documentation, the Python bindings to PRQL, including Jupyter magic.
• PRQL VS Code extension
• prql-js — JavaScript bindings for PRQL.

Contributors

Many thanks to those who've made our progress possible: