Use PRQL with knitr

Loading {prqlr} makes the prql code chunks executable in R Markdown and Quarto Markdown files.

Output formats

It can either output the results of the actual query execution or display a SQL query compiled from a PRQL query.

Use with {DBI} connections

If using with database connections (set the name of {DBI} connection to the connection chunk option), PRQL code chunks pass the output SQL to {knitr}’s SQL engine and behaves like SQL code chunks. So, usage is the same as for SQL code chunks.

For example, let’s render an R Markdown file named test.Rmd with the following contents with the knitr::knit() function.

```{r}
#| echo: false

library(DBI)
library(prqlr)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
```

```{prql}
#| connection: con

from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```

After rendering, a Markdown file named test.md is generated with the following contents.


``` elm
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```


| cyl|  mpg| mpg_int|
|---:|----:|-------:|
|   8| 18.7|      19|
|   8| 14.3|      14|
|   8| 16.4|      16|

Note that the syntax highlighting of Elm is the best for PRQL, so the output code blocks are marked as elm. This can be changed by setting the lang chunk option.

Without database connections

PRQL code chunks without the connection option will generate SQL code blocks.

```{r}
#| echo: false

library(prqlr)
```

```{prql}
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```

The R Markdown file above will be converted to the Markdown file below.


``` elm
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```

```sql
SELECT
  cyl,
  mpg,
  ROUND(mpg, 0) AS mpg_int
FROM
  mtcars
WHERE
  cyl > 6
LIMIT
  3

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)
```

Engine options

Compiler options

We can pass some options of prql_compile() via the chunk option engine-opts (or engine.opts).

Note that the format option is always TRUE for PRQL code chunks.

```{r}
#| echo: false

library(prqlr)
```

## YAML-style

```{prql}
#| engine-opts:
#|   target: sql.mssql
#|   signature_comment: false

from mtcars
take 3
```

## R-style

```{prql engine.opts=list(target="sql.mssql", signature_comment=FALSE)}
from mtcars
take 3
```

Parameterized PRQL code blocks

The experimental engine option use_glue can be used to insert R variables into PRQL queries.

Characters enclosed in {{ and }} are replaced by the glue::glue() function before being passed to the prql_compile() function.

```{r}
#| echo: false

library(prqlr)

cyl_min <- 6
derive_or_select <- "derive"
```

```{prql}
#| engine-opts:
#|   use_glue: true

from mtcars
filter cyl > {{cyl_min}}
select {cyl, mpg}
{{derive_or_select}} {mpg_int = math.round 0 mpg}
take 3
```

The R Markdown file above will be converted to the Markdown file below.


``` elm
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```

```sql
SELECT
  cyl,
  mpg,
  ROUND(mpg, 0) AS mpg_int
FROM
  mtcars
WHERE
  cyl > 6
LIMIT
  3

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)
```

Use query strings in R code blocks

With the experimental use_glue option, the query string can even be defined completely in R side.

```{r}
#| echo: false

library(prqlr)

prql_query <- "from mtcars
select cyl"
```

```{prql}
#| engine-opts:
#|   use_glue: true

{{prql_query}}
```

This example outputs the following.


``` elm
from mtcars
select cyl
```

```sql
SELECT
  cyl
FROM
  mtcars

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)
```

Set special info string to output SQL code blocks

When making SQL code blocks output without a DB connection, the info_string option can be used to control the info string of the output code block. This is convenient when we want to add special attributes that are used in Quarto Markdown files. The default value is "sql".

For example, the following R Markdown file includes a PRQL code block with the info_string option will be converted to…

```{r}
#| echo: false

library(prqlr)
```

```{prql}
#| engine-opts:
#|   info_string: '{.sql filename="SQL"}'
from mtcars
take 3
```

This Markdown file.


``` elm
from mtcars
take 3
```

```{.sql filename="SQL"}
SELECT
  *
FROM
  mtcars
LIMIT
  3

-- Generated by PRQL compiler version:0.13.0 (https://prql-lang.org)
```