<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/dbplyr-1-1-0/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://blog.rstudio.org/2017/06/27/dbplyr-1-1-0/"> RStudio Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I’m pleased to announce the release of the <a rel="nofollow" href="http://github.com/hadley/dbplyr/" target="_blank">dbplyr</a> package, which now contains all dplyr code related to connecting to databases. This shouldn’t affect you-as-a-user much, but it makes dplyr simpler, and makes it easier to release improvements just for database related code.</p>
<p>You can install the latest version of dbplyr with:</p>
<pre><code><span>install.packages</span><span>(</span><span>&quot;dbplyr&quot;</span><span>)</span></code></pre>
<div>
<h2>DBI and dplyr alignment</h2>
<p>The biggest change in this release is that dplyr/dbplyr works much more directly with DBI database connections. This makes it much easier to switch between low-level queries written in SQL, and high-level data manipulation functions written with dplyr verbs.</p>
<p>To connect to a database, first use <code>DBI::dbConnect()</code> to create a database connection. For example, the following code connects to a temporary, in-memory, SQLite database, then uses DBI to copy over some data.</p>
<pre><code><span>con</span> <span>&lt;-</span> <span>DBI</span><span>:</span><span>:</span><span>dbConnect</span><span>(</span><span>RSQLite</span><span>:</span><span>:</span><span>SQLite</span><span>(</span><span>)</span>, <span>&quot;:memory:&quot;</span><span>)</span>
<span>DBI</span><span>:</span><span>:</span><span>dbWriteTable</span><span>(</span><span>con</span>, <span>&quot;iris&quot;</span>, <span>iris</span><span>)</span>
<span>#> [1] TRUE</span>
<span>DBI</span><span>:</span><span>:</span><span>dbWriteTable</span><span>(</span><span>con</span>, <span>&quot;mtcars&quot;</span>, <span>mtcars</span><span>)</span>
<span>#> [1] TRUE</span></code></pre>
<p>With this connection in hand, you can execute hand-written SQL queries:</p>
<pre><code><span>DBI</span><span>:</span><span>:</span><span>dbGetQuery</span><span>(</span><span>con</span>, <span>&quot;SELECT count() FROM iris&quot;</span><span>)</span>
<span>#> count()</span>
<span>#> 1 150</span></code></pre>
<p>Or you can let dplyr generate the SQL for you:</p>
<pre><code><span>iris2</span> <span>&lt;-</span> <span>tbl</span><span>(</span><span>con</span>, <span>&quot;iris&quot;</span><span>)</span>

<span>species_mean</span> <span>&lt;-</span> <span>iris2</span> <span>%>%</span>
  <span>group_by</span><span>(</span><span>Species</span><span>)</span> <span>%>%</span>
  <span>summarise_all</span><span>(</span><span>mean</span><span>)</span>

<span>species_mean</span> <span>%>%</span> <span>show_query</span><span>(</span><span>)</span>
<span>#> </span></code></pre></div>