如何在R中进行数据库连接/查询以进行单元测试

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

我正在使用testthat库在R项目中进行单元测试。我想测试依赖于数据库查询的代码,但不测试实际的查询本身。换句话说,我想模拟数据库连接和查询(让它们返回预定的数据集或命中测试数据库)。

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?

我知道Ruby中有很多宝石,以及其他语言中的其他宝石,它们提供了这种功能。 R有什么类似的吗?或者我该如何完成它?

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?

如果没有方便的包,是testthat :: with_mock()我最好的选择?

2 个解决方案

#1

Just mock the sqlQuery function by returning a simulated result:

只需通过返回模拟结果来模拟sqlQuery函数:

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

#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.

如果您愿意相信SQLite与其他数据库(大多数情况下)使用相同的SQL,那么您可以模拟本地SQLite数据库并对其进行调用。一个优点是您可以使用DB Browser for SQLite等方法轻松编辑数据库内容和测试结果。

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...

附:这是一个使用SQLite和R的更完整的例子...

#1