Provide example on maintaining database connection

[dpmccabe]

I'm setting up an API with Plumber and data stores in a Postgres database. Could the 5.4.4 External Data Store section of the documentation be improved to describe this use case?

The standard procedure in an R script is to open a database connection at the beginning and close it at the end:

dbConnect(
  PostgreSQL(),
  host = "etc.",
  ...
)
# do things
dbDisconnect(conn)

In a Shiny app, you similarly need to close the connection when the app stops:

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

What would be the equivalent for a script that you load with plumb? If you fail to close the connection each time you open one, you'll eventually run out. When developing, I need to stop and restart Plumber many times, and it doesn't take long for this limit to be reached:

> pr <- plumber::plumb("plumber.R")
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (cannot allocate a new connection -- maximum of 16 connections already opened)
In addition: Warning message:
In (function (e)  : You have a leaked pooled object.

Is there something like a post-hook I could add that would close the connection when I stop plumber? Alternatively, can I create the database connection (or pool of connections) outside of my API script? Thanks!

[dpmccabe]

Just saw section 5.5. Oops.

[harrismcgehee]

Where do you set up the connection? In the plumber.R or in an entrypoint.R?

When I place it in the plumber.R - it can't find the pool object when I do the exit hook.

[dpmccabe]

I put it in the script that calls plumb:

library(plumber)

pool <- dbPool(
  drv = PostgreSQL(),
  host = Sys.getenv("DB_HOST"),
  port = 5432,
  dbname = Sys.getenv("DB_NAME"),
  user = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD"),
  minSize = 1,
  maxSize = 3,
  idleTimeout = 600000
)

pr <- plumb("plumber.R")

pr$registerHooks(
  list(
    "exit" = function() {
      poolClose(pool)
    }
  )
)

[harrismcgehee]

Are you using Rstudio Connect? I think I need to use entrypoint.R to do the hook registration. So would you put it in there?

[dpmccabe]

I've never used RStudio Connect, but whatever script you have your plumb() call in would be where you open/close the DB connection. Note that registerHooks is a method on the plumb object, which is why it wasn't working when you had it in plumber.R.

[mscbuck]

I actually do have a follow-up question to this (though this specific question is closed)....

I have a lot of my Plumber API's set up as system services, and nightly I have those system services restart to re-load the APIs so that they can take advantage of newer data that I pre-load. Does anyone know if a "systemctl restart plumber.service" terminates these files correctly that the registerHook connection closing would take place, or would this cause hanging connections? AKA, does the service restart close R safely?