SQL chunks in RMarkdown

sql_chunks.Rmd

---
title: "SQL chunks in Rmd"
output: html_document
editor_options: 
  chunk_output_type: inline
---

## Set up the database table

```{r setup, message = FALSE}
library(dplyr)
library(dbplyr)

conn <- src_memdb()        # create a SQLite database in memory
con <- conn$con            # needed for SQL chunk
copy_to(conn, storms, overwrite = TRUE)
```

## View the file

```{sql connection=con}
select * from storms limit 5
```

## Passing in a single variable

```{r}
storm_status <- "hurricane"
```

```{sql connection=con}
select * from storms where status = ?storm_status limit 5;
```

## Passing in a column name

```{r}
col_name <- glue::glue_sql("status")
```

```{sql connection=con}
select * from storms where ?col_name = 'hurricane' limit 5;
```

## Passing in a vector

```{r}
types <- c("hurricane", "tropical depression")
storm_status <- glue::glue_sql("{types*}", .con = con)
```

```{sql connection=con}
select * from storms where status IN (?storm_status) limit 5;
```

## Using SQL files

```{r}
types <- c("hurricane", "tropical depression")
storm_status <- glue::glue_sql("{types*}", .con = con)
```


```{sql connection=con, code=readLines("storm.sql"), output.var="storm_preview"}
```

```{r}
storm_preview
```

storm.sql

-- !preview conn=src_memdb()$con

SELECT * FROM storms WHERE status IN (?storm_status) LIMIT 5;