Deploy a packaged plumber API to Posit Connect with a pooled database connection

Posit Professional & Hosted Products Posit Connect
, ,
1

I've made an R package (lets call it {myapi}) that defines a plumber API. I've made a separate repo (lets call it {myapi-deploy}) that handles the deployment of the plumber API to Posit Connect. I am having trouble hitting endpoints that use a pooled database connection (defined with pool::dbPool()). I can successfully call endpoints that don't require a pool object. I can make a successful database connection on Posit Connect outside of a plumber deployment context.

The {myapi-deploy} repo contains two scripts.

dir/entrypoint.R:

library(myapi)
library(plumber)
library(pool)
library(noctua)

pool <- dbPool(
  drv = noctua::athena(),
  catalog = "x",
  schema = "x",
  s3_staging_dir = 'x',
  rstudio_conn_tab = FALSE,
  minSize = 1,
  maxSize = 100,
  idleTimeout = 6000
)

plumb_api(package = "myapi",  name = "x") |>
  pr_set_debug(TRUE) |>
  plumber::pr_hook("exit", function(){
    poolClose(pool)
  })

deploy.R:

library(rsconnect)

deployAPI(
  "dir",
  account = "x",
  server = "x"
  )

When I attempt to use an endpoint that depends on the pool object, I get an error like:

{
  "error": "500 - Internal server error",
  "message": "Error in dplyr::tbl(pool, \"x\"): object 'pool' not found\n"
}

When I run dir/entrypoint.R in full and use plumber::pr_run() to deploy locally, the pool object is initiated and the API calls work. How can I ensure that the pool object is initiated correctly at runtime on Posit Connect when deploying with rsconnect::deployAPI()?

2

I believe that the issue is that the pool object is defined outside the plumber execution environment.

Maybe something like that?

library(myapi)
library(plumber)
library(pool)
library(noctua)

pool <- dbPool(
  drv = noctua::athena(),
  catalog = "x",
  schema = "x",
  s3_staging_dir = 'x',
  rstudio_conn_tab = FALSE,
  minSize = 1,
  maxSize = 100,
  idleTimeout = 6000
)

pr <- plumb_api(package = "myapi",  name = "x") |>
  pr_set_debug(TRUE) |>
  plumber::pr_hook("exit", function(){
    poolClose(pool)
  }) 

pr$environment$pool <- dbPool(
  drv = noctua::athena(),
  catalog = "x",
  schema = "x",
  s3_staging_dir = 'x',
  rstudio_conn_tab = FALSE,
  minSize = 1,
  maxSize = 100,
  idleTimeout = 6000
)

pr
3

Thanks for the suggestion - when I run this, I get the following error:

Error in (function ()  : unused argument (base::quote(<environment>))
4

Bruno was on the right track: the pool variable can be found during local development but not once deployed because of differences in the search paths (this is the explanation I got from one of the plumber maintainers).

Defining this function that super-assigns the pool object:

and then invoking it in the entrypoint file:

should have the desired result.

5

There, I actually tested it this time. A global assignation would also work, personally they stress me out.

library(plumber)
library(pool)
library(RSQLite)

f <- function() {
  pool <- dbPool(RSQLite::SQLite())
  list(
    get = function() {
      pool
    },
    close = function() {
      poolClose(pool)
    }
  )
}

f1 <- f()

pr <- pr() |>
  pr_set_debug(TRUE) |>
  pr_get("dapool", handler = function() {
    print(f1$get())
    "OK"
  }) |>
  plumber::pr_hook("exit", f1$close)


pr$run()