Dbplyr: in_schema to use the same connection for two different databases

Question

For reasons I am not going to explain here, I need to use the same connection object to read two databases in dbplyr. I found some online resources, but I am not getting this right. Please have a look at the reprex below. Can anyone tell me what I am doing wrong? Many thanks!

library(tidyverse)
library(DBI) # main DB interface
library(dbplyr) # dplyr back-end for DBs
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql
library(RSQLite)


##create the databases

df1 <- tibble(x=1:20,y=rep(c("a", "b"), 10))


df2 <- tibble(x=101:120,y=rep(c("d", "e"), 10))


con <- dbConnect(drv=RSQLite::SQLite(), dbname="db1.sqlite")

dbWriteTable(con,"mydata1",df1, overwrite=T)

dbDisconnect(con) # closes our DB connection



con <- dbConnect(drv=RSQLite::SQLite(), dbname="db2.sqlite")

dbWriteTable(con,"mydata2",df2, overwrite=T)

dbDisconnect(con) # closes our DB connection


## Now that I have created the two databases, I try reading them with the same connection object

con <- dbConnect(drv=RSQLite::SQLite())


db1 <- tbl(con, in_schema("db1.sqlite","mydata1"))
#> Error: no such table: db1.sqlite.mydata1
db2 <- tbl(con, in_schema("db2.sqlite","mydata2"))
#> Error: no such table: db2.sqlite.mydata2


### but this fails miserably. How to fix it?

^{Created on 2020-12-24 by the reprex package (v0.3.0)}

If you write a native SQL command (not via R or dbplyr) can you access both tables at once? — Simon.S.A., Dec 28 '20 at 03:21

score 0 · Answer 1 · answered Jan 04 '21 at 23:07

There is nothing wrong with your R syntax. I use essentially the same approach with multiple databases in an SQL Server environment.

This is a database configuration problem. Your two databases are not setup so that a single query can access a table in both databases.

For SQLite it looks like this can be done in native SQL (not via R) using the ATTACH command (see this accepted answer). You might also find this question helpful. Both these questions involve writing SQL directly, which I would recommend for your initial attempt as it avoids the additional layer of having R involved.

If you want to do this only via R then it probably looks something like the following:

con <- dbConnect(drv=RSQLite::SQLite(), dbname="db1.sqlite")

dbExecute(con, "attach 'db2.sqlite' as db2;")

# test query
test_query = paste("SELECT COUNT(*) AS num FROM db1.mydata1","\n"
                   "UNION ALL","\n"
                   "SELECT COUNT(*) AS num FROM db2.mydata2")
dbGetQuery(con, test_query)

If the last command works then you know that a SQL query can (now) touch both databases so you can then try to use in_schema to connect to both.

Dbplyr: in_schema to use the same connection for two different databases

1 Answers1