<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/pool-package-on-cran/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://blog.rstudio.com/2017/11/17/pool-0-1-3/"> RStudio Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The <a rel="nofollow" href="https://github.com/rstudio/pool" target="_blank">pool package</a> makes it easier for Shiny developers to connect to databases. Up until now, there wasn’t a clearly good way to do this. As a Shiny app author, if you connect to a database globally (outside of the server function), your connection won’t be robust because all sessions would share that connection (which could leave most users hanging when one of them is using it, or even all of them if the connection breaks). But if you try to connect each time that you need to make a query (e.g. for every reactive you have), your app becomes a lot slower, as it can take in the order of seconds to establish a new connection. The <code>pool</code> package solves this problem by taking care of when to connect and disconnect, allowing you to write performant code that automatically reconnects to the database only when needed.</p>
<p>So, if you are a Shiny app author who needs to connect and interact with databases inside your apps, keep reading because this package was created to make your life easier.</p>
<h2>What the <code>pool</code> package does</h2>
<p>The <code>pool</code> package adds a new level of abstraction when connecting to a database: instead of directly fetching a connection from the database, you will create an object (called a “pool”) with a reference to that database. The pool holds a number of connections to the database. Some of these may be currently in-use and some of these may be idle, waiting for a new query or statement to request them. Each time you make a query, you are querying the pool, rather than the database. Under the hood, the pool will either give you an idle connection that it previously fetched from the database or, if it has no free connections, fetch one and give it to you. You never have to create or close connections directly: the pool knows when it should grow, shrink or keep steady. You only need to close the pool when you’re done.</p>
<p>Since <code>pool</code> integrates with both <code>DBI</code> and <code>dplyr</code>, there are very few things that will be new to you, if you’re already using either of those packages. Essentially, you shouldn’t feel the difference, with the exception of creating and closing a “Pool” object (as opposed to connecting and disconnecting a “DBIConnection” object). See <a rel="nofollow" href="https://github.com/rstudio/pool#usage" target="_blank">this copy-pasteable app</a> that uses <code>pool</code> and <code>dplyr</code> to query a MariaDB database (hosted on AWS) inside a Shiny app.</p>
<p>Very briefly, here’s how you’d connect to a database, write a table into it using <code>DBI</code>, query it using <code>dplyr</code>, and finally disconnect (you must have <code>DBI</code>, <code>dplyr</code> and <code>pool</code> installed and loaded in order to be able to run this code):</p>
<pre><code>conn &lt;- dbConnect(RSQLite::SQLite(), dbname = &quot;:memory:&quot;)
dbWriteTable(conn, &quot;quakes&quot;, quakes)
tbl(conn, &quot;quakes&quot;) %>% select(-stations) %>% filter(mag >= 6)
## # Source:   lazy query [?? x 4]
## # Database: sqlite 3.19.3 [:memory:]
##      lat   long depth   mag
##    </code></pre>