Can I attach one SQLite database to another with the DBI library in R?

Question

I am trying to attach one SQLite database to another. On the terminal, I can do this pretty simply with the ATTACH command, but I am working in an R script and I want to get it to work through a DBI connection. I have been able to attach with system calls, but it would be really nice to do it through a database connection.

DBI Connection try:

library(DBI)

NewDBFile <- "new.db"
archiveFile <- "archive.db"

con <- dbConnect(RSQLite::SQLite(), NewDBFile)

rs <- dbExecute(conn = con, paste0("ATTACH ", archiveFile, " AS archive;"))

dbDisconnect(con)

With this I get an "Error: no such column: [database name]"

Any ideas on how I would go about doing this?

Answer 1

Demo of ATTACH working in R:

con1 <- DBI::dbConnect(RSQLite::SQLite(), "dat1.sqlite3")
DBI::dbWriteTable(con1, "tbl1", data.frame(id=1:3, aa=1:3))
con2 <- DBI::dbConnect(RSQLite::SQLite(), "dat2.sqlite3")
DBI::dbWriteTable(con2, "tbl2", data.frame(id=1:3, bb=11:13))
DBI::dbExecute(con1, "attach database 'dat2.sqlite3' as dat2")
# [1] 0

DBI::dbGetQuery(con1, "
  select t1.*, t2.bb
  from tbl1 t1
    left join dat2.tbl2 t2 on t1.id=t2.id")
#   id aa bb
# 1  1  1 11
# 2  2  2 12
# 3  3  3 13

Incidentally, this does not show the attached tables in a single query:

DBI::dbGetQuery(con1, "select * from sqlite_master")
#    type name tbl_name rootpage                                                       sql
# 1 table tbl1     tbl1        2 CREATE TABLE `tbl1` (\n  `id` INTEGER,\n  `aa` INTEGER\n)

DBI::dbGetQuery(con1, "select * from dat2.sqlite_master")
#    type name tbl_name rootpage                                                       sql
# 1 table tbl2     tbl2        2 CREATE TABLE `tbl2` (\n  `id` INTEGER,\n  `bb` INTEGER\n)

---

Basic info for me:

packageVersion("DBI")
# [1] '1.1.1'
packageVersion("RSQLite")
# [1] '2.2.1'
R.version
#                _                           
# platform       x86_64-w64-mingw32          
# arch           x86_64                      
# os             mingw32                     
# system         x86_64, mingw32             
# status                                     
# major          4                           
# minor          0.5                         
# year           2021                        
# month          03                          
# day            31                          
# svn rev        80133                       
# language       R                           
# version.string R version 4.0.5 (2021-03-31)
# nickname       Shake and Throw