Where to place DB connections within plumber APIs?

Posit Professional products Posit Connect
,
1

Hello!

We've been recently wondering internally what's the better way of defining DB connections within plumber API:

  1. At the top of the script before any endpoint is defined - this way we're sparing some time on each call and the connection is shared, but isn't there a performance issue attached to this because a connection is always left open? Should we perhaps use pool here similarly like in shiny apps?

  2. Within each respective endpoint - results in each call being slightly longer, but at the end of end of it we can disconnect and make sure no open connections are left

What's your experience and advice on that? Thank you!

1 Like
2

Hi,

Interesting issue!

I have not been using database connections in plumber, but frequently in Shiny, and my approach always was to open and close in the script when transactions were needed. This is especially handy if there is an issue with the connection between the script and database, because if the connection is only defined once at the top and it breaks, you have to restart the whole app to restore it again. I read about pool and they have indeed some nice solutions to both issues of one or many connections.

In my view, I think it depends on the number of times you have to call the database and the number of people estimated to use the app at any given time. If you can group your transactions in the script together and your app/api is used infrequently, I would think the on demand connection is best and safest. On the other hand, if you have many users and can't group transactions in the script, the constant opening and closing might interfere with performance (never tested it formally though).

It seems that implementing the pool package is a good idea, though I don't fully understand yet what it does in long idle times.

Definitely interested what others think about this topic!
Grtz,
PJ

3

You could use the pool package and define an exit handler for when the Plumber process is terminated.

library(pool)
library(plumber)

# Create pool connection
pool <- dbPool('DB connection stuff here')

pr <- plumb("plumber.R")

# Close your pool connection on exit
pr$registerHooks(
  list(
    "exit" = function() {
      poolClose(pool)
    }
  )
)
5 Likes
4

Hi @jdb and thank you for your reply! Is that a solution you use in your applications as well?

5

It is the solution we're currently testing. Not sure how many connections you're expecting, but it seems to be working for small needs (5-10 connections at most).