How to make database connection / query in R for unit tests

Question

I use the testthat library for unit testing in project R. I want to check the code, which depends on the database queries, but not check the answers themselves. In other words, I would like to mock database connections and queries (so that they return a predefined dataset or end up in a test database).

I know that in Ruby there are many gems and other equivalents in other languages ​​that provide this functionality. Is there anything similar for R? Or how do I do this?

some_file.R:

 sqlQuery <- function(some_query) { chnl <- odbcConnect(get.db.name()) data <- sqlQuery(chnl, query) } 

From the test file:

 test_that("test query", { dataset <- sqlQuery("SELECT * FROM some_database_table") #How to make this not actually hit the production database? expect_equal(nrow(dataset), 2) } ) 

If there are no suitable packages for this, is it better to testthat::with_mock() ?

Answer 1

Just mock sqlQuery by returning a simulated result:

 library(testthat) sqlQuery <- function(some_query) { chnl <- odbcConnect(get.db.name()) data <- sqlQuery(chnl, query) } with_mock(sqlQuery = function(some_query) { if (grepl("SELECT * FROM some_database_table", some_query, fixed = TRUE)) return(mtcars[1:2,]) # could also be loaded from file via "load" after using "save" once return(mtcars) # default return value }, { # Calls the mocked function now... dataset <- sqlQuery("SELECT * FROM some_database_table") expect_equal(nrow(dataset), 2) } )