testthat: set up database connection available to all tests

Question

My R package modifies data in a remote DB and I'd like to write some tests with testthat.

I am aware that I could mock the DB but I'd rather simply use of one our dev DB.

How can I make a db connection available to all tests that need it, while making sure any connection created is destroyed? It seems obvious that connecting should happen in a setup and disconnecting in a teardown, but I haven't managed.

I have tried to put the following code in tests/testthat.R or in a helper file tests/testthat/helper-_, to no avail.

setup({
  # db_connect is just a basic wrapper around RMariaDB::dbConnect with logging
  db_con <- db_connect(conf$database, loglevel = "none")
})

teardown({
  # db_connect is just a basic wrapper around DBI::dbDisconnect with logging
  db_disconnect(db_con = db_con, loglevel = "none")
})

My initial tests are:

tests
├── testthat
│   ├── helper-_.R
│   ├── test-connect.R
│   └── test-questions.R
└── testthat.R

After the first file (where all tests pass), I get Error in DBI::dbDisconnect(db_con) : object 'db_con' not found which indicates the teardown is happening but the db_con is not found.

After that, all tests requiring db_con fail with object 'db_con' not found.

Do I have to create a helper file for each file where db_con is needed? Or do I have to explicitly source a common helper file?

Is there a way I can set up the connection once somewhere and have it available to all tests and destroyed at the end?

Answer 1

From the testthat docs

Code in a setup() block is run immediately in a clean environment

I believe this means that if you want to save whatever objects are created in your setup environment, then you need to place them in the global environment

setup({
  db_con <- db_connect(conf$database, loglevel = "none")
  assign("db_con", db_con, envir = .GlobalEnv)
})

Then in your teardown() method, it will be able to find the connection

teardown({
  db_disconnect(db_con = db_con, loglevel = "none")
  # Can also remove it from the global environment after disconnect
  rm(db_con, envir = .GlobalEnv)
})

It's not ideal to mess with the global environment, but as long as you name things carefully and remove them when you're done, it shouldn't be a problem.

It seems like setup() was designed more for reading/writing tempfiles/tempdirs than for creating global objects to be used by all tests, but I could be mistaken.

Helpful example in the wild that I came across while researching this question: https://github.com/ropensci/Rpolyhedra/blob/3675a3a6eb8b2807f26fb2ebc929b9f5072681db/tests/testthat/test_package_lib.R#L7

Answer 2

EDIT 21-07-07 : As of January 14, 2021 there is the package pool that was made to solve this problem in the following fashion :

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

The goal of the pool package is to abstract away the logic of connection management and the performance cost of fetching a new connection from a remote database

---

EDIT : dbDisconnect in test-connect_init. This structure works best in workflows that take in data from data base (once or few times).

Disclaimer : what follows has been tested successfully with Impala.

I have opted for the sourcing way, creating a connect_init.R function that is called in script and test :

Organisation

R
??? utils
|   ??? connect_init.R
|   ??? df_import.R
??? clean
|   ??? data_clean.R
tests
??? testthat
?   ??? test-connect.R
?   ??? test-import.R
?   ??? test-clean.R
??? testthat.R

Processes

connect_init.R

connect_init <- function(params) DBI::dbConnect(...)

data_clean.R

[...]
con <- connect_init(params)
rqt <- "select * from db.tab"
dframe <- DBI::dbGetQuery(conn = con, rqt)

# --- when import finished
DBI::dbDisconnect(con)

Tests

test-connect.R

context("test-connect")

test_that("connexion to Impala doable",
        res <- mypkg::connect_init(params)
        testthat::expect_true(attributes(res)$class[1] == "Impala")
        DBI::dbDisconnect(res)
})

test-import.R

context("test-import")

test_that("import from Impala doable", {

        res <- mypkg::df_import(paramsbis)

        testthat::expect_s3_class(object = res, class = "data.frame")
        testthat::expect_true(nrow(res) > 0)
})

Then open and close connexion when used in other tests. I am very interested in other way to test this + feedback on how to improve this part.

Should we store minimal sample data so that non-connexion tests won't fail in case of network/db issues ?

Answer 3

As of testthat verion 3, setup() and teardown() were deprecated.

It is now recommended to use on.exit() or the withr package for any test fixtures. You can learn more in testthat's vignette('test-fixtures'). If your tests are all in the same file this is even simpler than the setup/teardown method actually.

Essentially this code should appear at the beginning of the file:

db_con <- db_connect(conf$database, loglevel = "none")

#Set connection to terminate after all tests are run    
withr::defer(db_disconnect(db_con = db_con, loglevel = "none"))

You can place all of your tests after.

In my experience, your code should be able to find the db_con object with just these lines when automatic tests are run, but if you run into a problem, you can always build a test environment to avoid using the global environment like this:

#Build environment for any variables need in many tests
testEnv <- new.env()

#Open Connection
testEnv$db_con <- db_connect(conf$database, loglevel = "none")

#Set connection to terminate after all tests are run
withr::defer(db_disconnect(db_con = testEnv$db_con, loglevel = "none"))

#Clear the test environment (This could be added to the statement above)
withr::defer(rm(list=ls(envir = testEnv), envir = testEnv))