# dittodb v0.1.3

0

0th

Percentile

## A Test Environment for Database Requests

Testing and documenting code that communicates with remote databases can be painful. Although the interaction with R is usually relatively simple (e.g. data(frames) passed to and from a database), because they rely on a separate service and the data there, testing them can be difficult to set up, unsustainable in a continuous integration environment, or impossible without replicating an entire production cluster. This package addresses that by allowing you to make recordings from your database interactions and then play them back while testing (or in other contexts) all without needing to spin up or have access to the database your code would typically connect to.

# 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 {.tabset}

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:

start_db_capturing()

con <- DBI::dbConnect(
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())

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:

with_mock_db({
con <- DBI::dbConnect(
dbname = "nycflights"
)

test_that("We get one airline", {
one_airline <- get_an_airline()
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()
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())

test_that("We get one airline", {
one_airline <- get_an_airline()
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

## Functions in dittodb

 Name Description driver-specifc-mock-connections Driver-specific mock classes make_path make a mock path hash Make a (short) hash from a string with_mock_path Run the DBI queries in an alternate mock directory mock-db-methods Methods for interacting with DB mocks instead of an actual database mockPaths Set an alternate directory for mock API fixtures nycflights13_create_sqlite Create an in-memory SQLite database for testing nycflights_sqlite An SQLite connection to a subset of nycflights13 use_dittodb Use dittodb in your tests sanitize_table_id Sanitize Table id set_dittodb_debug_level Set dittodb's debug level redact_columns Redact columns from a dataframe with the default redactors mockdb Run DBI queries against a mocked database nycflights13_create_sql Create a standardised database for testing capture_requests Capture and record database transactions and save them as mocks get_redactor Get the current redactor expect_sql Detect if a specific SQL statement is sent get_type Get the type of an SQL statement db_path_sanitize Switch illegal characters for legal ones dittodb_debug_level Get the dittodb debug level and evaluate if it is above a level get_dbname Get the dbname from a connection call .dittodb_env an environment for dittodb storing state check_for_pkg Check if a package is installed No Results!