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

Question

I am using the testthat library for unit testing in a R project. I want to test code that relies on database queries, but not test the actual queries themselves. In other words, I'd like to mock the database connections and queries (having them return a pre-determined data set or to hit a test database).

I know there are plenty of gems in Ruby, and other equivalents in other languages, that provide this functionality. Is there anything like it for R? Or how should I go about accomplishing it?

some_file.R:

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

From 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 packages handy for this, is testthat::with_mock() my best bet?

Answer 1

Just mock the sqlQuery function 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)
          }
)