testthat: set up database connection available to all tests

user_3498910 0
Viewed 1377

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?

testthat database-connection r
3 Answers

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

user_113814930

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 ?

user_109834700

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))
user_133136460
Related