PRQL

https://prql-lang.org/book
Admin
PRQL is a modern, declarative programming language for transforming data that provides a simple and...
Tokens:98,838
Snippets:991
Trust Score:8.8
Update:4 days ago
Show doc for...
Tokens:
Raw
### Install via Winget

Source: https://prql-lang.org/book/project/integrations/prqlc-cli.html

Installation command for Windows users using Winget.

```bash
winget install prqlc
```

--------------------------------

### Install via Homebrew

Source: https://prql-lang.org/book/project/integrations/prqlc-cli.html

Installation command for macOS users using Homebrew.

```bash
brew install prqlc
```

--------------------------------

### Install cargo-insta

Source: https://prql-lang.org/book/project/contributing/development.html

Installs the cargo-insta testing framework used for snapshot testing.

```bash
cargo install cargo-insta
```

--------------------------------

### Compile documentation examples to test SQL

Source: https://prql-lang.org/book/project/contributing/development.html

Compiles all examples from documentation (Website, README, PRQL Book) to verify they produce expected SQL and catch regressions.

```shell
cargo insta test --accept
```

--------------------------------

### Install pyprql

Source: https://prql-lang.org/book/project/integrations/jupyter.html

Install the pyprql package to enable PRQL support in Jupyter.

```bash
pip install pyprql
```

--------------------------------

### Install prqlc via npm

Source: https://prql-lang.org/book/project/bindings/javascript.html

Install the prqlc package using npm.

```bash
npm install prqlc
```

--------------------------------

### Install and load PRQL extension in DuckDB

Source: https://prql-lang.org/book/project/integrations/duckdb.html

Use these commands to install and load the PRQL community extension, then execute a PRQL query against a CSV file.

```sql
INSTALL prql FROM community;
LOAD prql;
-- Once the extension is loaded, you can write PRQL queries
from (read_csv 'https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/invoices.csv')
filter invoice_date >= @2009-02-01
take 5;
```

--------------------------------

### Install via Cargo

Source: https://prql-lang.org/book/project/integrations/prqlc-cli.html

Installation commands for installing from crates.io or a local repository.

```bash
# From crates.io
cargo install prqlc
```

```bash
# From a local PRQL repository
cargo install --path prqlc/prqlc
```

--------------------------------

### Run setup-dev task

Source: https://prql-lang.org/book/project/contributing/development.html

Runs the project's setup-dev task to install dependencies via Taskfile.yaml.

```bash
task setup-dev
```

--------------------------------

### Install prqlc via pip

Source: https://prql-lang.org/book/project/bindings/python.html

Install the prqlc Python package from PyPI using pip.

```bash
pip install prqlc
```

--------------------------------

### SQL: Translation of comprehensive parentheses example

Source: https://prql-lang.org/book/reference/syntax/operators.html

Equivalent SQL generated from the PRQL example above, showing how each PRQL expression translates to SQL operations like BETWEEN, SUM OVER, MIN OVER, and ORDER BY.

```SQL

__
SELECT
  *,
  distance BETWEEN 0 AND 20 AS is_proximate,
  SUM(distance) OVER () AS total_distance,
  MIN(COALESCE(distance, 5)) OVER () AS min_capped_distance,
  distance / 40 AS travel_time,
  ROUND(distance, 1 + 1) AS distance_rounded_2_dp,
  distance >= 100 AS is_far,
  distance BETWEEN -100 AND 0,
  distance BETWEEN -100 AND 0 AS is_negative,
  AVG(distance) OVER () AS average_distance
FROM
  employees
ORDER BY
  distance DESC

```

--------------------------------

### PRQL loop example

Source: https://prql-lang.org/book/reference/stdlib/transforms/loop.html

An example of using the loop transform to increment a value until a condition is met.

```prql
from [{n = 1}]
loop (
    filter n<4
    select n = n+1
)

# returns [1, 2, 3, 4]
```

--------------------------------

### Install prql-compiler via Homebrew

Source: https://prql-lang.org/book/project/changelog.html

Use the Homebrew package manager to install the PRQL compiler on macOS or Linux.

```bash
brew install prql-compiler
```

--------------------------------

### SQL translation for SQLite division example

Source: https://prql-lang.org/book/reference/stdlib

SQL translation of the SQLite division example, showing how PRQL compiles / and // for SQLite.

```SQL

__
WITH table_0 AS (
  SELECT
    13 AS x,
    5 AS y
)
SELECT
  (x * 1.0 / y) AS quotient,
  CAST(ABS(x * 1.0 / y) AS INTEGER) * SIGN(x) * SIGN(y) AS int_quotient
FROM
  table_0
```

--------------------------------

### Global sort and take

Source: https://prql-lang.org/book/reference/stdlib/transforms/group.html

Example of finding the first employee to join across the entire table.

```prql
from employees
sort join_date
take 1
```

```sql
SELECT
  *
FROM
  employees
ORDER BY
  join_date
LIMIT
  1
```

--------------------------------

### Install prqlr package

Source: https://prql-lang.org/book/project/bindings/r.html

Use this command to install the prqlr package from CRAN.

```R
install.packages("prqlr")
```

--------------------------------

### Take transform with ranges in SQL

Source: https://prql-lang.org/book/reference/syntax/ranges.html

The equivalent SQL output for the PRQL take range example.

```sql
__
SELECT
  *
FROM
  orders
ORDER BY
  value DESC,
  created_at
LIMIT
  10 OFFSET 100
```

--------------------------------

### SQL output for PRQL comment example

Source: https://prql-lang.org/book/reference/syntax/comments.html

The SQL generated from the PRQL comment example, showing the equivalent SELECT query with AVG(salary) from employees.

```sql
SELECT
  AVG(salary)
FROM
  employees
```

--------------------------------

### Start interactive mode

Source: https://prql-lang.org/book/project/integrations/prqlc-cli.html

Running prqlc compile without arguments starts an interactive session where you can input PRQL and compile it to SQL by pressing Ctrl-d or Ctrl-z.

```bash
prqlc compile
```

--------------------------------

### PRQL and SQL Salary Calculation Example

Source: https://prql-lang.org/book/reference/stdlib/index.html

Demonstrates calculating gross salary and rounding it, alongside retrieving the current timestamp in both PRQL and the resulting SQL.

```prql
__
from employees
derive {
  gross_salary = (salary + payroll_tax | as int),
  gross_salary_rounded = (gross_salary | math.round 0),
  time = date.now,  # current timestamp; or use s"NOW()" for a dialect-specific s-string
}
```

```sql
__
SELECT
  *,
  CAST(salary + payroll_tax AS int) AS gross_salary,
  ROUND(CAST(salary + payroll_tax AS int), 0) AS gross_salary_rounded,
  CURRENT_TIMESTAMP AS time
FROM
  employees
```

--------------------------------

### PRQL round and cast syntax example

Source: https://prql-lang.org/book/project/changelog.html

Demonstrates the updated parameter order for round and cast functions, allowing for consistent piping.

```prql
derive [
  gross_salary = (salary + payroll_tax | as int),
  gross_salary_rounded = (gross_salary | round 0),
]
```

--------------------------------

### Development commands with uv and task

Source: https://prql-lang.org/book/project/bindings/python.html

Commands for installing development dependencies, running tests, type checking, and using the task runner.

```bash
# Install development dependencies and run tests
uv run pytest

# Run type checking
uv run ty check

# Or use the task runner
task test
```

--------------------------------

### SQL parameter syntax

Source: https://prql-lang.org/book/reference/syntax/parameters.html

Example of the compiled SQL output using a positional parameter.

```sql

__
SELECT
  *
FROM
  employees
WHERE
  id = $1


```

--------------------------------

### Left Join Example

Source: https://prql-lang.org/book/reference/stdlib/transforms/join.html

A basic left join between employees and positions tables.

```prql
from employees
join side:left positions (employees.id==positions.employee_id)
```

```sql
SELECT
  employees.*,
  positions.*
FROM
  employees
  LEFT OUTER JOIN positions ON employees.id = positions.employee_id
```

--------------------------------

### Compile PRQL to SQL using Compiler class

Source: https://prql-lang.org/book/project/bindings/php.html

PHP example using Prql\Compiler\Compiler to compile a PRQL query and output the result.

```php
<?php

use Prql\Compiler\Compiler;

$prql = new Compiler();
$result = $prql->compile("from employees");

echo $result->output;
```

--------------------------------

### Basic PRQL Query and SQL Compilation

Source: https://prql-lang.org/book/index.html

A simple example demonstrating how PRQL filters data and aggregates columns compared to the equivalent SQL query.

```prql

__
from tracks
filter artist == "Bob Marley"  # Each line transforms the previous result
aggregate {                    # `aggregate` reduces each column to a value
  plays    = sum plays,
  longest  = max length,
  shortest = min length,       # Trailing commas are allowed
}
```

```sql

__
SELECT
  COALESCE(SUM(plays), 0) AS plays,
  MAX(length) AS longest,
  MIN(length) AS shortest
FROM
  tracks
WHERE
  artist = 'Bob Marley'
```

--------------------------------

### Grouped sort and take

Source: https://prql-lang.org/book/reference/stdlib/transforms/group.html

Example of finding the first employee to join within each department using the group transform.

```prql
from employees
group role (
  sort join_date  # taken from above
  take 1
)
```

```sql
WITH table_0 AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY role
      ORDER BY
        join_date
    ) AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_0
WHERE
  _expr_0 <= 1
```

--------------------------------

### PRQL example: division and integer division for MySQL

Source: https://prql-lang.org/book/reference/stdlib

PRQL query targeting sql.mysql that computes quotient and int_quotient using / and // operators on a literal table.

```PRQL

__
prql target:sql.mysql

from [{x = 13, y = 5}]
select {
  quotient = x / y,
  int_quotient = x // y,
}
```

--------------------------------

### Compile PRQL in Rust

Source: https://prql-lang.org/book/project/integrations/prqlc-cli.html

Example of using the prqlc library in Rust to compile a PRQL string to a specific SQL dialect.

```rust
#![allow(unused)]
fn main() {
// In a file src/main.rs

use prqlc::{compile, Options, DisplayOptions, Target, sql::Dialect};

let prql = "from employees | select {name, age}";
let opts = &Options {
    format: false,
    target: Target::Sql(Some(Dialect::SQLite)),
    signature_comment: false,
    display: DisplayOptions::Plain,
    ..Default::default()
};
let sql = compile(&prql, opts).unwrap();
assert_eq!("SELECT name, age FROM employees", sql);
}
```

--------------------------------

### Define a module structure with nested namespaces

Source: https://prql-lang.org/book/reference/spec/modules.html

This example illustrates the conceptual module structure using the 'module' keyword and curly braces to define nested namespaces and declarations.

```prql
__
module my_playlists {
    let bangers = ... # a declaration

    module soundtracks {
        let movie_albums = ... # another declaration
    }
}
```

--------------------------------

### PRQL example: division and integer division for SQLite

Source: https://prql-lang.org/book/reference/stdlib

PRQL query targeting sql.sqlite that computes quotient and int_quotient using / and // operators on a literal table.

```PRQL

__
prql target:sql.sqlite

from [{x = 13, y = 5}]
select {
  quotient = x / y,
  int_quotient = x // y,
}
```

--------------------------------

### PRQL Number Literals

Source: https://prql-lang.org/book/reference/syntax/literals.html

Example demonstrating various number literal formats in PRQL, including underscores for readability, scientific notation, and base prefixes.

```prql

__
from numbers
select {
    small = 1.000_000_1,
    big = 5_000_000,
    huge = 5e9,
    binary = 0b0011,
    hex = 0x80,
    octal = 0o777,
}

```

--------------------------------

### Root module example with nested modules

Source: https://prql-lang.org/book/reference/spec/modules.html

Shows a root module containing a std module with function definitions and a default_db module for inferred tables and CTEs, plus a main query selecting from tracks.

```prql
module project {
	module std {
		let sum = a -> ...
		let mean = a -> ...
	}

	module default_db {
		# all inferred tables and defined CTEs
	}

	let main = (
		from t = tracks
		select [track_id, title]
	)
}
```

--------------------------------

### Build prqlc JavaScript bindings

Source: https://prql-lang.org/book/project/bindings/javascript.html

Build the Node, bundler, and web packages.

```bash
npm run build
```

--------------------------------

### Basic usage of prqlc.compile

Source: https://prql-lang.org/book/project/bindings/python.html

Shows how to import prqlc, define a PRQL query, create CompileOptions, and compile to SQL with and without options. The example uses a join and group/aggregate.

```python
import prqlc

prql_query = """
    from employees
    join salaries (==emp_id)
    group {employees.dept_id, employees.gender} (
      aggregate {
        avg_salary = average salaries.salary
      }
    )
"""

options = prqlc.CompileOptions(
    format=True, signature_comment=True, target="sql.postgres"
)

sql = prqlc.compile(prql_query)
sql_postgres = prqlc.compile(prql_query, options)
```

--------------------------------

### Run website components locally

Source: https://prql-lang.org/book/project/contributing/development.html

Use these task commands to run the main website, the PRQL online book, or the PRQL playground locally.

```bash
# Run the main website
task web:run-website
# Run the PRQL online book
task web:run-book
# Run the PRQL playground
task web:run-playground
```

--------------------------------

### Define and use multiple utility functions

Source: https://prql-lang.org/book/reference/declarations/functions.html

Demonstrates defining several functions and using them in a pipeline.

```PRQL

__
let is_adult = col -> col >= 18
let writes_code = col -> (col | in ["PRQL", "Rust"])
let square = col -> (col | math.pow 2)
let starts_with_a = col -> (col | text.lower | text.starts_with("a"))

from employees
select {
    first_name,
    last_name,
    hobby,
    adult = is_adult age,
    age_squared = square age,
}
filter ((starts_with_a last_name) && (writes_code hobby))
```

```SQL

__
WITH table_0 AS (
  SELECT
    first_name,
    last_name,
    hobby,
    age >= 18 AS adult,
    POW(age, 2) AS age_squared
  FROM
    employees
)
SELECT
  first_name,
  last_name,
  hobby,
  adult,
  age_squared
FROM
  table_0
WHERE
  LOWER(last_name) LIKE CONCAT('a', '%')
  AND hobby IN ('PRQL', 'Rust')
```

--------------------------------

### Identifier examples

Source: https://prql-lang.org/book/reference/syntax/keywords.html

Examples of valid PRQL identifiers including alphanumeric characters, underscores, and the dot lookup operator.

```prql

__
hello

_h3llo

hello.world

```

--------------------------------

### Enable nix experimental features

Source: https://prql-lang.org/book/project/bindings/php.html

Commands to enable nix flakes by creating a nix.conf file with the experimental-features setting.

```bash
mkdir -p ~/.config/nix
echo "experimental-features = nix-command flakes" >> ~/.config/nix/nix.conf
```

--------------------------------

### SQL translation of PRQL expression example

Source: https://prql-lang.org/book/reference/syntax/operators.html

The SQL translation of the PRQL expression example, using a CTE and POW for exponentiation. The filter becomes a WHERE clause with AND and <>.

```SQL
WITH table_0 AS (
  SELECT
    diameter * 3.14159 AS circumference,
    POW(diameter / 2, 2) AS area,
    color
  FROM
    foo
)
SELECT
  circumference,
  area,
  color
FROM
  table_0
WHERE
  circumference > 10
  AND color <> 'red'
```

--------------------------------

### SQL translation of PRQL derive example

Source: https://prql-lang.org/book/reference/stdlib

SQL translation of the PRQL derive example, showing the generated SELECT statement with CAST, ROUND, and CURRENT_TIMESTAMP.

```SQL

__
SELECT
  *,
  CAST(salary + payroll_tax AS int) AS gross_salary,
  ROUND(CAST(salary + payroll_tax AS int), 0) AS gross_salary_rounded,
  CURRENT_TIMESTAMP AS time
FROM
  employees
```

--------------------------------

### Test prqlc JavaScript bindings

Source: https://prql-lang.org/book/project/bindings/javascript.html

Run the test suite.

```bash
npm test
```

--------------------------------

### Load default Nix development shell

Source: https://prql-lang.org/book/project/contributing/development.html

Enters the default Nix development environment.

```bash
nix develop
```

--------------------------------

### SQL: Equivalent output for PRQL r-string example

Source: https://prql-lang.org/book/reference/syntax/r-strings.html

The SQL generated by the PRQL compiler for the r-string example. It shows the literal backslash and tab characters in the normal_string value and the escaped backslashes in the raw_string value.

```sql
SELECT
  *,
  '\	' AS normal_string,
  '\\\t' AS raw_string
FROM
  artists

```

--------------------------------

### text.starts_with

Source: https://prql-lang.org/book/reference/stdlib/text.html

Returns true if the column starts with the given substring.

```APIDOC
## text.starts_with

### Description
Returns true if `col` starts with `sub`.

### Parameters
- `sub` (string) - Required - The substring to check for at the start.
- `col` (string) - Required - The column to check.

### Returns
Boolean
```

--------------------------------

### Reference module declarations

Source: https://prql-lang.org/book/reference/spec/modules.html

Demonstrates how to reference declarations from within a module from the outside.

```prql
module my_playlists

let great_tracks = my_playlists.bangers

let movie_scores = my_playlists.soundtracks.movie_albums
```

--------------------------------

### derive single column

Source: https://prql-lang.org/book/reference/stdlib/transforms/derive.html

Example of adding a single calculated column to a table.

```prql
from employees
derive gross_salary = salary + payroll_tax
```

```sql
SELECT
  *,
  salary + payroll_tax AS gross_salary
FROM
  employees
```

--------------------------------

### Use s-strings for from transform

Source: https://prql-lang.org/book/project/changelog.html

Example of using an s-string to define a from transform in PRQL.

```prql
from s"SELECT * FROM employees WHERE foo > 5"
```

--------------------------------

### Build with development profile

Source: https://prql-lang.org/book/project/bindings/javascript.html

Build with PROFILE=dev for faster, less optimized builds.

```bash
PROFILE=dev npm run build
```

--------------------------------

### Filter with ranges in SQL

Source: https://prql-lang.org/book/reference/syntax/ranges.html

The equivalent SQL output for the PRQL range filter example.

```sql
__
SELECT
  *,
  latitude >= 0 AS is_northern
FROM
  events
WHERE
  created_at BETWEEN DATE '1776-07-04' AND DATE '1787-09-17'
  AND magnitude BETWEEN 50 AND 100
```

--------------------------------

### Project module file (_project.prql)

Source: https://prql-lang.org/book/reference/spec/modules.html

Declares the top-level modules for a project: employees, sales, and util.

```prql
# _project.prql

module employees
module sales
module util
```

--------------------------------

### PRQL parameter syntax

Source: https://prql-lang.org/book/reference/syntax/parameters.html

Example of using a positional parameter in a PRQL filter clause.

```prql

__
from employees
filter id == $1

```

--------------------------------

### SQL order non-guarantee

Source: https://prql-lang.org/book/reference/stdlib/transforms/sort.html

An example of an SQL query that does not guarantee row order after a join.

```sql
WITH albums_sorted AS (
  SELECT *
  FROM albums
  ORDER BY title
)
SELECT *
FROM albums_sorted
JOIN artists USING (artist_id)
```

--------------------------------

### Compile PRQL to SQL using PrqlCompiler

Source: https://prql-lang.org/book/project/bindings/dotnet.html

Demonstrates how to use the PrqlCompiler.Compile method with custom PrqlCompilerOptions to convert PRQL code into SQL.

```csharp
using Prql.Compiler;

var options = new PrqlCompilerOptions
{
    Format = false,
    SignatureComment = false,
};
var sql = PrqlCompiler.Compile("from employees", options);
Console.WriteLine(sql);
```

--------------------------------

### Get current timestamp with date.now

Source: https://prql-lang.org/book/reference/stdlib/date.html

Retrieves the current date and time as a timestamp for filtering.

```prql

__
from test_tables
filter test_time < date.now
```

```sql

__
SELECT
  *
FROM
  test_tables
WHERE
  test_time < CURRENT_TIMESTAMP
```

--------------------------------

### Build and test PHP bindings

Source: https://prql-lang.org/book/project/bindings/php.html

Commands to build the PHP bindings and run tests using the task runner.

```bash
task build-php
task test-php
```

--------------------------------

### SQL Number Literals Compilation

Source: https://prql-lang.org/book/reference/syntax/literals.html

The corresponding SQL output for the PRQL number literal example.

```sql

__
SELECT
  1.0000001 AS small,
  5000000 AS big,
  5000000000.0 AS huge,
  3 AS "binary",
  128 AS hex,
  511 AS octal
FROM
  numbers


```

--------------------------------

### PRQL select with column concatenation

Source: https://prql-lang.org/book/reference/stdlib/transforms/select.html

Example of selecting and renaming a column using string concatenation.

```prql
from employees
select name = f"{first_name} {last_name}"
```

```sql
SELECT
  CONCAT(first_name, ' ', last_name) AS name
FROM
  employees
```

--------------------------------

### Join with Multiple Conditions

Source: https://prql-lang.org/book/reference/stdlib/transforms/join.html

A join example demonstrating the use of multiple conditions including a filter.

```prql
from tracks
join side:left artists (
  # This adds a `country` condition, as an alternative to filtering
  artists.id==tracks.artist_id && artists.country=='UK'
)
```

```sql
SELECT
  tracks.*,
  artists.*
FROM
  tracks
  LEFT OUTER JOIN artists ON artists.id = tracks.artist_id
  AND artists.country = 'UK'
```

--------------------------------

### Apply window function with group in PRQL

Source: https://prql-lang.org/book/reference/stdlib/transforms/window.html

Demonstrates using the group transform to partition window functions by department.

```prql
from employees
group department (
  sort age
  derive {rnk = rank age}
)
```

--------------------------------

### PRQL datetime cast example

Source: https://prql-lang.org/book/reference/syntax/literals.html

Shows a timestamp literal cast to a datetime type using <datetime>.

```PRQL
derive pi_day = @2017-03-14T15:09:26.535898<datetime>
```

--------------------------------

### Function implementation with target and representation annotations

Source: https://prql-lang.org/book/reference/spec/type-system.html

Illustrates the made-up syntax for implementing a function for a specific target and parameter representations, using #[target(sql.sqlite)] and #[repr(int8)].

```prql
# using a made-up syntax

#[target(sql.sqlite)]
func std.int8.add
    #[repr(int8)] x
    #[repr(int8)] y
    -> s"{x} + {y}"
```

--------------------------------

### SQL recursive CTE equivalent

Source: https://prql-lang.org/book/reference/stdlib/transforms/loop.html

The SQL output generated by the PRQL loop example using a recursive CTE.

```sql
WITH RECURSIVE table_0 AS (
  SELECT
    1 AS n
),
table_1 AS (
  SELECT
    n
  FROM
    table_0
  UNION
  ALL
  SELECT
    n + 1
  FROM
    table_1
  WHERE
    n < 4
)
SELECT
  n
FROM
  table_1 AS table_2
```
Tokens:
Raw