The knitr SQL Engine Grows Up

Note

This is one of a few posts on new features from a recent four-day knitr triage sprint; see that post for the full details of the sprint.

knitr’s sql engine lets you write a SQL query in a code chunk, run it against a DBI connection, and drop the result into your document. It has been a bit bare-bones for a long time. This cycle a group of contributors pushed it a long way forward. If you write reports against a database, several of these are going to feel like they should have existed all along.

Show every statement, not just the first

A sql chunk with several statements separated by semicolons used to be submitted all at once, and depending on the DBI backend, results after the first statement could silently vanish. The new sql.interlaced option splits the chunk into individual statements, runs them in order, and emits the source and result of each as an alternating sequence of blocks—the way an R chunk echoes each expression next to its output:

```{sql, connection=con, sql.interlaced=TRUE}
SELECT count(*) FROM flights;
SELECT count(*) FROM airlines;
```

The splitting is done by a small pure-R helper that knows to ignore semicolons inside string literals, quoted identifiers, and comments, so no external SQL parser is needed. If a statement fails, execution stops there (respecting the error option) (#2093).

Bring your own result function

By default the engine calls DBI to execute the query and collect the result. But sometimes you don’t want to collect eagerly—you want a lazy handle you can keep processing with dplyr. The new sql.result.fun option lets you substitute your own function(conn, query) for the built-in execution:

```{sql, connection=con, output.var="tbl",
     sql.result.fun=function(conn, query) dplyr::tbl(conn, dbplyr::sql(query))}
SELECT * FROM flights
```

Combined with output.var, the lazy object is assigned to a variable without collecting it, while the chunk still gets the sql engine’s syntax highlighting. You get to keep writing real SQL and hand the result off to dplyr downstream (#1778).

Statements that change data

Not every SQL statement returns rows. INSERT, UPDATE, DELETE and friends return a count of affected rows, and the engine now knows the difference. It recognizes more keywords (ALTER, GRANT, MERGE, TRUNCATE, CALL, …) as non-SELECT statements and routes them to DBI::dbExecute() instead of dbGetQuery(), which avoids spurious warnings from some drivers. When the automatic detection guesses wrong (think SELECT ... INTO or UPDATE ... RETURNING), override it with sql.is_statement (#1896).

The affected-row count is now available too. Capture it with output.var, and optionally report it with a template via sql.statement.msg, where {n} is the count:

```{sql, connection=con, sql.statement.msg="Rows affected: {n}"}
DELETE FROM flights WHERE dep_delay IS NULL
```

By default no such message is shown, so existing documents are unaffected (#2050, #2051).

Passing extra arguments to DBI

Finally, sql.args is a named list forwarded to the underlying DBI functions, so you can set any backend-specific argument without the engine needing to know about it:

```{sql, connection=con, sql.args=list(immediate=TRUE)}
CREATE TEMP TABLE foo AS SELECT 1 AS x
```

These are passed only when supplied, so DBI’s own defaults otherwise stand (#2128).

There’s more I haven’t shown (dollar-quoted strings no longer trip the interpolation code, for instance)—see the changelog. Everything here is in the development version; the backlog summary has the install command.