Set Global Database Connection for SQL chunk in Rnotebook

Question

Is there a way to set a global database connection in a Rnotebook so you don't have to set the database connection for each SQL chunk?

Currently I have to set it as follows for each SQL chunk:

```{sql connection = my_connection}
select * from my_table
```

I would like to just be able to do the following:

```{sql}
select * from my_table
```

Answer 1

From Rmarkdown docs:

Setting a Default Connection

If you have many SQL chunks, it may be helpful to set a default for the connection chunk option in the setup chunk, so that it is not necessary to specify the connection on each individual chunk. You can do this as follows:

```{r setup}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
knitr::opts_chunk$set(connection = "db")
```

So, in your example, set a hook with

```{r setup}
# insert your my_connection declaration here
knitr::opts_chunk$set(connection = "my_connection")
``` 

and put that chunk before all other chunks.