rows_insert does not work with the pool package

[stevepowell99]

rows_insert does not work for me with the pool package even using today's dev version of dbplyr and R version 4.2.0. The other packages are up to date on CRAN. Maybe my R is too old?

library(pool)
library(DBI)
library(dplyr)

pool <- dbPool(RSQLite::SQLite())
copy_to(pool, mtcars, "mtcars", temporary = FALSE)
rows_insert(pool,tibble(cyl=3),copy=T)

I'd expect to get the mtcars database with an extra row with just cyl=3. Instead I get this error:
Error in UseMethod("rows_insert") :
no applicable method for 'rows_insert' applied to an object of class "c('Pool', 'R6')"

[mgirlich]

The first argument of rows_insert() must be a table, i.e. either a local data frame or a database table e.g. created via tbl(con, ...).
In your case you might want to use mtcars_db <- copy_to(pool, mtcars, "mtcars", temporary = FALSE).

[stevepowell99]

Sorry I messed up the example. It should be this. Everything is the same, but using pool I get the error and with DBI I don't.

library(pool)
library(DBI)
library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

# works fine
conn <- DBI::dbConnect(RSQLite::SQLite())
copy_to(conn, mtcars, "mtcars", temporary = FALSE)
mt <- tbl(conn,"mtcars")
rows_append(mt,tibble(cyl=3),copy=T)

# now with pool
conn <- dbPool(RSQLite::SQLite())
copy_to(conn, mtcars, "mtcars", temporary = FALSE)
mt <- tbl(conn,"mtcars")
rows_append(mt,tibble(cyl=3),copy=T)
# this last line gives the error 

[mgirlich]

I fixed the issue that no method get_col_types() is found for Pool (resp. this is now called db_col_types()).

But then the Pool package still needs to add a method db_copy_to.Pool() (I already opened an issue: rstudio/pool#172).
But mind that this still won't work for Pool as rows_*(copy = TRUE) uses a temporary table. Due to the design of Pool temporary tables aren't supported (as they are coupled to the connection and Pool might give you a new connection).

So the best thing for you would be to checkout the connection from pool. Otherwise, this can't work I'm afraid.

[stevepowell99]

thanks.
You say I could "checkout the connection from pool" - what would this look like? Is there a tidyverse-friendly way to do what I wanted to do in the example using rows_append etc and the pool package?
I thought rows_append, rows_delete etc are (one) canonical way in the tidyverse to change an underlying SQL database. And I though the pool package is for most cases the recommended tidyverse way to make SQL connections. The tidyverse is a game-changer for me, I'd like to keep everything in the tidyverse family if at all possible.

[mgirlich]

You can read about checking out a connection in the documentation.
Note that you might not even need to use {pool}. Usually, you only need to use pool if you have multiple connections to the database, e.g. when you create a shiny app that has many users at the same time. If you only run a script locally, there is no need to use pool.