Pool and Shiny best practices for getting custom queries from database

shiny
database
databases
#1

Looking for some best practices using the pool package to connect to a database and get data using dbGetQuery.

I've been using something like this:

pool <- pool::dbPool(
  drv = drv,  
  url = "url",
  user = "username",
  password = "password"
)

onStop(function() {
  poolClose(pool)
})

conn <- poolCheckout(pool)

data_from_table <- dbGetQuery(conn, 
  "
  select * from table
  "
)

poolReturn(conn)

Is there a better way? Am I using pool correctly and to its full potential? Am I safely creating and closing connections?

Thanks!

#2

Have you checked the Pool, Best Practices and Advanced areas/sections on db.rstudio.com? Those sections should assist or at least, point you in the right direction.

#3

@BassK is right, the site will have articles to help you with this. I must highlight the Run Queries Safely for you specific case because it looks like you plan to use straight SQL and not dbplyr for your project, and SQL injection may become a real security problem for you.

#4

Thanks for the feedback. Run Queries Safely was a helpful reminder about the dangers of quoted SQL!

Maybe not clear in my question, but I am actually most interested in feedback on how to responsibly manage connections to a database through a Shiny application. When to create a pool object, when to checkout connections from the pool with poolCheckout(), when to return connections to the pool with poolReturn(), and when to close the pool using poolClose().

#5

Oh ok, if you are not able to find what you need, would you mind opening a GitHub Issue for the db.rstudio.com website (https://github.com/rstudio/db.rstudio.com/issues)? This will help me remember to either expand on that in the existing pool page, or add a Best Practice page for that. Thanks.

#6

Yup - see here https://github.com/rstudio/db.rstudio.com/issues/64, and let me know in the issue if you need any clarification. Thanks!