DBI::dbWriteTable isn't writing a SQLite table to disk

I'm just getting started with using DBI to access (local) SQLite databases, and I seem to be having basic problems reading and writing to disk. Here's a reprex:

library(DBI)

con = dbConnect(RSQLite::SQLite(), path = 'test.db')
dbWriteTable(conn = con, name = "Tbl_test", value = mtcars)
dbListTables(conn = con)
#> [1] "Tbl_test"
dbDisconnect(con)

con = dbConnect(RSQLite::SQLite(), path = 'test.db')
dbListTables(conn = con)
#> character(0)
dbDisconnect(con)

sessionInfo()
#> R version 4.0.0 (2020-04-24)
#> Platform: x86_64-apple-darwin17.0 (64-bit)
#> Running under: macOS Catalina 10.15.5
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] DBI_1.1.0
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.4.6    digest_0.6.25   magrittr_1.5    evaluate_0.14  
#>  [5] RSQLite_2.2.0   highr_0.8       rlang_0.4.6     stringi_1.4.6  
#>  [9] blob_1.2.1      vctrs_0.3.1     rmarkdown_2.1   tools_4.0.0    
#> [13] stringr_1.4.0   bit64_0.9-7     bit_1.1-15.2    xfun_0.13      
#> [17] yaml_2.2.1      compiler_4.0.0  pkgconfig_2.0.3 memoise_1.1.0  
#> [21] htmltools_0.4.0 knitr_1.28

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

The first bit appears to correctly add a new table using the data from mtcars. But nothing is written to disk in the working directory (or anywhere else, based on a Finder search). The second bit doesn't throw an error that it can't find the database file; it just quietly fails to find any tables.

Solution

You need to specify the dbname parameter when you connect to the DB.
If you don't, the DB will be created in memory and disappear as you disconnect.
path doesn't belong to SQLlite arguments and isn't taken into account : the DB is created in your working directory.

library(DBI)

con = dbConnect(RSQLite::SQLite(),dbname = 'test.db', path = "#?*_~")
dbWriteTable(conn = con, name = "Tbl_test", value = mtcars)
dbListTables(conn = con)
#> [1] "Tbl_test"
dbDisconnect(con)

dir(".")
#>[1] "test.db"  

con = dbConnect(RSQLite::SQLite(), dbname = 'test.db')
dbListTables(conn = con)
#> [1] "Tbl_test"
dbDisconnect(con)