data("testdata", package="dbtest")
dbtest
uses testthat
to automate testing of dbplyr
translations by running the tests against live database connections. Tests are written in YAML files, and connections are either passed explicitly, read from system DSNs, or configured in YAML files. Further, it is possible to execute a test suite against multiple database connections.
To install dbtest
, you can install the latest version from GitHub:
devtools::install_github("rstudio/dbtest")
The first step to use dbtest
is to set up a DBI connection object. There are many ways you can do this.
Sometimes, a system already has a handful of DSNs (Data Source Names) set up that make connection easy. In usual DBI, the connection might look like DBI::dbConnect(odbc::odbc(), "My Data Source")
. On a linux operating system, these are usually defined in /etc/odbc.ini
or ~/.odbc.ini
. If you have DSNs defined on your system, you can utilize them by using the value "dsn"
as your connection. dbtest
will use all of your DSNs and execute tests against them.
dbtest::test_database("dsn")
Every database has different connection parameters. To make database connections easy to automate, dbtest
will read a YAML file and pass the named parameters into dbConnect
to create a DBI connection. Note that the config
package is used, so you must label the set of connections and refer to it with R_CONFIG_ACTIVE=mylabel
. Otherwise, the default
heading will be selected. An example might look like:
default: pg: drv: !expr odbc::odbc() Driver: PostgreSQL Host: postgres.example.com Port: 5432 Database: postgres UID: user PWD: password mssql: drv: !expr odbc::odbc() Driver: SQLServer Server: mssql.example.com Port: 1433 UID: user PWD: password oracle: drv: !expr odbc::odbc() Driver: Oracle Host: oracle.example.com Port: 1521 SVC: xe UID: user PWD: password
Notice that the names of the various databases are different. This corresponds to the parameters that different database providers expect in the dbConnect
function. Notice also that drv
is !expr odbc::odbc()
. This allows execution of R code to provide the necessary DBI driver to support the connection.
You can test this behavior and create connection objects manually with:
cfg <- config::get(file = "./path/to/conn.yml") do.call(dbConnect, cfg$pg) do.call(dbConnect, cfg$mssql) do.call(dbConnect, cfg$oracle)
Or you can use the config file and dbtest
to execute tests against all of these database connections with:
dbtest::test_database("./path/to/conn.yml")
The most straightforward way to interactively use dbtest
is to provide a DBI connection object directly to dbtest::test_database
.
con <- DBI::dbConnect(odbc::odbc(), "My DSN") dbtest::test_database(con)
If you are familiar with dbplyr
and already have a tbl_sql
object (which combines a DBI connection object with a reference to a database table), you can pass that object to test_database
as well. In this case, tests will be executed directly against that tbl_sql
object.
con <- DBI::dbConnect(odbc::odbc(), "PostgreSQL") dbWriteTable(con, "mytesttable", iris) my_tbl_sql <- dplyr::tbl(con, "mytesttable") dbtest::test_database(my_tbl_sql) dbDisconnect(con)
Once you have decided how you are going to provide connection objects to dbtest
, the usage is fairly straightforward. You use test_database
. test_database
is also vectorized to make it easier for testing multiple databases.
test_database
takes the following arguments:
dbtest
by executing dbtest::all_tests()
If you want to use specific test files included in dbtest
, you can reference them explicitly with dbtest::pkg_test("character-basic.yml")
, for instance. This is what we will do for ease of use.
Finally, dbtest
provides reporting functions that make it easier to analyze and explore the results of your tests. This is where the rubber meets the road on improving the development process with a test suite that increases quality and ensures reliability.
# ensure existence of a conn.yml file if (!fs::file_exists("conn.yml")) { writeLines('default: sqlite: drv: !expr RSQLite::SQLite() path: ":memory:" sqlite2: drv: !expr RSQLite::SQLite() path: ":memory:" ', "conn.yml") }
test_output <- dbtest::test_database( "conn.yml", dbtest::pkg_test( c("character-basic.yml", "math-basic.yml") ) ) dbtest::plot_summary(test_output) dbtest::plot_tests(test_output)
If you want to see more specific details about a failure, in particular, you can use get_dbtest_detail
:
dbtest::get_dbtest_detail(test_output)
Writing test files in YAML can be a bit strange, because what dbtest
expects is text. For instance, do not use the !expr
trick that the config
package uses above for a connection object. Rather, you specify a verb and then arbitrary text that will be interpreted as R code. This text will get picked up into the testing process, which will do the following:
dplyr
chain focused on the verb you selecteddplyr
chain against the databasedplyr
chain against a local copy of the same datatestthat::expect_equal
Currently supported verbs are:
An example might be most illustrative. Let's say that we want to test the base R functions tolower
and toupper
and how they get translated into SQL.
First, we would define a test YAML file like:
test_file <- fs::path_temp("test-file.yml") yaml::write_yaml( list( setNames( list(list( "mutate" = "tolower(fld_character)" , "group_by" = "tolower(fld_character)" )) , "test-tolower" ) , setNames( list(list( "mutate" = "toupper(fld_character)" , "group_by" = "toupper(fld_character)" )) , "test-toupper" )) , file = test_file )
r test_file
raw_file <- readLines(test_file) cat("<pre>") cat(paste(raw_file, collapse="<br>")) cat("</pre>")
When executed against databases, it might look like:
test_results <- dbtest::test_database("conn.yml", test_file) dbtest::plot_tests(test_results)[[1]]
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.