dittodb

dittodb is a package that makes testing against databases easy. When writing code that relies on interactions with databases, testing has been difficult without recreating test databases in your continuous integration (aka CI) environment, or resorting to using SQLite databases instead of the database engines you have in production. Both have their downsides: recreating database infrastructure is slow, error prone, and hard to iterate with. Using SQLite works well, right up until you use a feature (like a full outer join) or has quirks that might differ from your production database. dittodb solves this by recording database interactions, saving them as mocks, and then replaying them seamlessly during testing. This means that if you can get a query from your database, you can record the response and reliably reproduce that response in tests.

dittodb is heavily inspired by {httptest}, if you've used {httptest} before, you'll find many of the interactions similar.

A quick example

Say we have a database with some {nycflights} data in it and we are writing functions that query this data that we want to test.

For example, we have the simple function that retrieves one airline:

get_an_airline <- function(con) {
  return(dbGetQuery(con, "SELECT carrier, name FROM airlines LIMIT 1"))
}

But we want to make sure that this function returns what we expect. To do this, we first record the response we get from the production database:

{.tabset}

RMariaDB

start_db_capturing()

con <- DBI::dbConnect(
  RMariaDB::MariaDB(),
  dbname = "nycflights"
)

get_an_airline(con)
DBI::dbDisconnect(con)

stop_db_capturing()

RPostgres

start_db_capturing()

con <- DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = "nycflights"
)

get_an_airline(con)
DBI::dbDisconnect(con)

stop_db_capturing()

RSQLite

start_db_capturing()

con <- DBI::dbConnect(RSQLite::SQLite(), "nycflights")

get_an_airline(con)
DBI::dbDisconnect(con)

stop_db_capturing()

{.tabset}

This will run the query from get_an_airline(), and save the response in a mock directory and file. Then, when we are testing, we can use the following:

RMariaDB

with_mock_db({
  con <- DBI::dbConnect(
    RMariaDB::MariaDB(),
    dbname = "nycflights"
  )
  
  test_that("We get one airline", {
    one_airline <- get_an_airline(con)
    expect_s3_class(one_airline, "data.frame")
    expect_equal(nrow(one_airline), 1)
    expect_equal(one_airline$carrier, "9E")
    expect_equal(one_airline$name, "Endeavor Air Inc.")
  })
})

RPostgres

with_mock_db({
  con <- DBI::dbConnect(
    RPostgres::Postgres(),
    dbname = "nycflights"
  )
  
  test_that("We get one airline", {
    one_airline <- get_an_airline(con)
    expect_s3_class(one_airline, "data.frame")
    expect_equal(nrow(one_airline), 1)
    expect_equal(one_airline$carrier, "9E")
    expect_equal(one_airline$name, "Endeavor Air Inc.")
  })
})

RSQLite

with_mock_db({
  con <- DBI::dbConnect(RSQLite::SQLite(), "nycflights")
  
  test_that("We get one airline", {
    one_airline <- get_an_airline(con)
    expect_s3_class(one_airline, "data.frame")
    expect_equal(nrow(one_airline), 1)
    expect_equal(one_airline$carrier, "9E")
    expect_equal(one_airline$name, "Endeavor Air Inc.")
  })
})

All without having to ever set a database up on Travis or GitHub Actions