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