Using SQL in RStudio

library(dplyr)
library(dbplyr)

conn <- src_memdb() # create a SQLite database in memory
copy_to(conn, 
        storms,     # this is a dataset built into dplyr
        overwrite = TRUE)
tbl(conn, sql("SELECT * FROM storms LIMIT 5"))

# Source:   SQL [?? x 13]
# Database: sqlite 3.30.1 [:memory:]
  name   year month   day  hour   lat  long status category  wind pressure
  <chr> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <chr>  <chr>    <int>    <int>
1 Amy    1975     6    27     0  27.5 -79   tropi… -1          25     1013
2 Amy    1975     6    27     6  28.5 -79   tropi… -1          25     1013
3 Amy    1975     6    27    12  29.5 -79   tropi… -1          25     1013
4 Amy    1975     6    27    18  30.5 -79   tropi… -1          25     1013
5 Amy    1975     6    28     0  31.5 -78.8 tropi… -1          25     1012
# … with 2 more variables: ts_diameter <dbl>, hu_diameter <dbl>

1. Preview a .sql file

When you open a new .sql file in RStudio, it automatically populates the file with the following code:

-- !preview conn=DBI::dbConnect(RSQLite::SQLite())

SELECT 1

I’d always blindly deleted the header, but it turns out that all this time, RStudio had been trying to tell me that I can actually preview queries in RStudio! If you supply it with a working database connection (no spaces around the =!) and click the Preview button, a SQL Results tab pops up next to the Console with the query results.

If you’ve run the previous dplyr/dbplyr code chunk in the same session, you’ll have both the connection and the data you need to test out this preview function yourself! Just create a .sql file with the following code:

-- !preview conn=src_memdb()$con

SELECT * FROM storms LIMIT 5

Note that unlike in the tbl() example above, you have to pull out the connection from the src_memdb() using $con. This seems to be a special case; other connections seem to work without extra fussing. For the rest of this post, I’ll refer to this connection as con with one “n” (with the exception of the gif below, where I gave it the lengthier name conn_isolated).

con <- src_memdb()$con

2. SQL chunks in RMarkdown

I generally prefer to show RMarkdown output in the console ¹ (and it looks like I’m not the only one). This means that when I run code in an .Rmd file, it feels more or less the same as when I run an .R file: the plots show up in the plots pane, code is run in the console, and so on.

While you can use SQL chunks with this setting, there is NO chunk preview option. You must trust your queries and knit the file to make sure everything runs. You get the syntax highlighting razzle-dazzle but alas– no preview.

It is in this very specific case where inline mode wins big time. SQL previews magically become an option, allowing you to interact with your beautifully colored SQL code.

SQL output as a variable

When using SQL chunks, you can specify an output variable using the output.var chunk option with the variable name as a string. ²

In inline mode, the preview will no longer appear when running the SQL chunk, but the variable will appear in your environment. Think of it like a regular variable assignment in R, where no output is printed when a variable is assigned (storm_preview <- "blah").³

```{sql connection=con, output.var="storm_preview"}
SELECT * FROM storms LIMIT 5;
```

```{r}
storm_preview
```

Providing query parameters

Adding a single variable is quite simple. Add ? before the variable in the SQL chunk and just make sure that the R variable has the same name.

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

```{sql connection=con}
SELECT * FROM storms WHERE status = ?storm_status LIMIT 5;
```

Sometimes, you cannot simply pass in a parameter. The glue_sql() function is great at managing some of the magic for you. To pass in a column name as a parameter, for example, you can just wrap the string in glue_sql() and it just works.

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

```{sql connection=con}
SELECT * FROM storms WHERE ?col_name = 'hurricane' LIMIT 5;
```

Note: Generally, you will also need to specify the .con (connection) so that glue will know what type of SQL syntax you need.

Now, what if you want to provide multiple parameters? Adding an * to the end of the variable collapses the vector into a single comma-separated expression, rather than outputting a vector of SQL expressions. This behavior is somewhat reminiscent of args and kwargs in Python, which uses * as an unpacking operator.

types <- c("hurricane", "tropical depression")

# without an asterisk
glue::glue_sql("{types}", .con = con)

<SQL> 'hurricane'
<SQL> 'tropical depression'

# with an asterisk
glue::glue_sql("{types*}", .con = con)

<SQL> 'hurricane', 'tropical depression'

Of course, to pass these parameters to our SQL chunk, we need to make sure we have the appropriate parentheses around ?storm_status for the IN to run.

```{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;
```