dbPool()
is a drop-in replacement for DBI::dbConnect()
that
provides a shared pool of connections that can automatically reconnect
to the database if needed.
Usage
dbPool(
drv,
...,
minSize = 1,
maxSize = Inf,
onCreate = NULL,
idleTimeout = 60,
validationInterval = 60,
validateQuery = NULL
)
Arguments
- drv
A DBI Driver, e.g.
RSQLite::SQLite()
,RPostgres::Postgres()
,odbc::odbc()
etc.- ...
Arguments passed on to
DBI::dbConnect()
. These are used to identify the database and provide needed authentication.- minSize, maxSize
The minimum and maximum number of objects in the pool.
- onCreate
A function that takes a single argument, a connection, and is called when the connection is created. Use this with
DBI::dbExecute()
to set default options on every connection created by the pool.- idleTimeout
Number of seconds to wait before destroying idle objects (i.e. objects available for checkout over and above
minSize
).- validationInterval
Number of seconds to wait between validating objects that are available for checkout. These objects are validated in the background to keep them alive.
To force objects to be validated on every checkout, set
validationInterval = 0
.- validateQuery
A simple query that can be used to verify that the connetction is valid. If not provided,
dbPool()
will try a few common options, but these don't work for all databases.
Examples
# You use a dbPool in the same way as a standard DBI connection
pool <- dbPool(RSQLite::SQLite())
pool
#> <Pool> of SQLiteConnection objects
#> Objects checked out: 0
#> Available in pool: 1
#> Max size: Inf
#> Valid: TRUE
DBI::dbWriteTable(pool, "mtcars", mtcars)
dbGetQuery(pool, "SELECT * FROM mtcars LIMIT 4")
#> mpg cyl disp hp drat wt qsec vs am gear carb
#> 1 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
#> 2 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
#> 3 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
#> 4 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
# Always close a pool when you're done using it
poolClose(pool)
# Using the RMySQL package
if (requireNamespace("RMySQL", quietly = TRUE)) {
pool <- dbPool(
drv = RMySQL::MySQL(),
dbname = "shinydemo",
host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
username = "guest",
password = "guest"
)
dbGetQuery(pool, "SELECT * from City LIMIT 5;")
poolClose(pool)
}