Using sql language engine with Quarto

[dmkaplan2000]

Hi,

I am just starting to play around with quarto. I use extensively the SQL language engine in Rmarkdown documents, so I wanted to give this a try, but there is currently no documentation on SQL in the Quarto documentation (at least none that I could find). As # is not used for comments in SQL, I was curious how best to pass chunk options. I was surprised to find that the following two chunks both worked equivalently:

```{sql}
--| connection: db
--| echo: false
SELECT * FROM trials
```

```{sql}
#| connection: "db"
#| echo: false
SELECT * FROM trials
```

My first question is which of these two is the "best" way to use chunk options in SQL chunks? I would prefer pure SQL (--|), but #| is consistent with R and python chunks. More generally, for other Rmarkdown languages, am I correct to presume that #| will always work, but the native comment character (e.g., ; or % are used in some languages) will only work if special work has been done to "teach" quarto about that language?

Second question: are there other ways of passing chunk options? For example, can block quotes (/*...*/) be used somehow?

Final question: I was surprised that connection: db and connection: "db" worked, but connection: !expr db did not. When is !expr necessary and when is it not appropriate? Presumable db and "db" both get the string "db" passed to the SQL language engine and not the R object db, whereas !expr db passes the text that appears on the command line when print(db) is executed. Is this correct? Is there any way or need to be able to pass actual R objects as chunk options or is every chunk option in quarto eventually converted to a string?

Thanks,
David

[dmkaplan2000]

And as an additional question, presumably someone "taught" quarto about SQL comments, but this is currently not a documented feature, correct? Was this done just for SQL, or are there other secret languages quarto knows about?

[cscheid]

For the sake of completeness, the full list is here: https://github.com/quarto-dev/quarto-cli/blob/main/src/core/lib/partition-cell-options.ts#L273-L316

[jimjam-slam]

Glad I perused this list to learn about that absolutely cursed APL comment character 😆

[dmkaplan2000]

Thanks, this is very helpful. Do you have an example of how the block comments would work for, e.g., C/C++? Would it be:

/*| echo: false */
/*| eval: true */

or

/*
| echo: false
| eval: true
*/

?

[dmkaplan2000]

Also, regarding comment characters, this is probably low on the totem pole of priorities, but it would be nice to support both --| and /*|...*/ for SQL.

[AlanFeder]

Hi,

One related follow-up question -- do sql chunks only work if the underlying engine for the rest of the notebook is knitr/R? Or also if it's jupyter/Python?

Thanks

[cscheid]

SQL code cells do currently require the knitr engine, yes. If you want to intermingle python and SQL code cells, you can force the engine to be knitr

engine: knitr

but note that this will require R, knitr and reticulate to be installed and available.

[AlanFeder]

Thank you. Do you think this functionality will be added someday to the jupyter engine?

[cscheid]

We would love to do that and it's on our plans, but there are other design decisions that need to happen before, like how to properly support these kinds of "cross-language code cells". The main fundamental obstacle is that sql requires R library support for now (to detect the underlying database, handle connection and authentication, etc). There are of course python-centric solutions as well, but we'd like to figure out how to make these as language-agnostic as we can.

Concretely speaking, my guess is we're at least a year away from that feature.

[AlanFeder]

Thank you for the great explanation!

[dmkaplan2000]

Regarding my final comment with respect to the use of !expr, I tried the following:

#| connection: db
#| connection: "db"
#| connection: !expr db
#| connection: !expr "db"
#| connection: !expr (db)
#| connection: !expr ("db")

The first two work, the next three fail, but the last one works again. Apparently "db" just gets stripped of the quotes and executed, but ("db") is recognized as a R command that is executed as is returning to the SQL language engine "db".