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?

Taffeta

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)
          }
)

Burned

Answer 2

If you're willing to believe that SQLite speaks the same SQL as other databases (it does - mostly), then you could mock up a local SQLite database and make calls to it. An advantage is you can use something like DB Browser for SQLite to easily edit the database contents and test results.

That would be implemented something to the effect of...

    # SQLite support & init
    library(DBI)
    library(RSQLite)

    putSQLiteHere <- "myRsqlite.sqlite" # could also be ":memory:"
    mySQLiteDB <- dbConnect(RSQLite::SQLite(),putSQLiteHere)

sqlQuery <- function(some_query) {
    data <- dbGetQuery(mySQLiteDB,some_query)
}

p.s. Here's a more complete example of using SQLite with R...

Triparted