Testing 'DBI' 'Backends'

A helper that tests 'DBI' back ends for conformity to the interface.

This package provides a considerable set of test cases which you can easily incorporate in your DBI driver package.


Install from CRAN via


or the development version using


In your driver backage, add DBItest to the Suggests:. Then, enable the tests by running


from your package's directory. This enables testing using testthat (if necessary) and creates, among others, a file test-DBItest.R in the tests/testthat directory. Replace its entire contents by the following:

DBItest::make_context(Kazam(), NULL)

(This assumes that Kazam() returns an instance of your DBIDriver class. Additional arguments to dbConnect() are specified as named list instead of the NULL argument to make_context().)

The skip argument to test_all() allows specifying skipped tests.

See the package's documentation and the feature list for a description of the tests.


DBItest 1.5-2 (2018-01-26)

  • Fix test that fails with "noLD".
  • Fix NOTEs on R-devel.

DBItest 1.5-1 (2017-12-10)

  • Remove "cannot_forget_disconnect" test that fails on R-devel (#150).

DBItest 1.5 (2017-06-18)

Finalize specification. Most tests now come with a corresponding prose, only those where the behavior is not finally decided don't have a prose version yet (#88).

New tests

  • Test behavior of methods in presence of placeholders (#120).
  • Test column name mismatch behavior for appending tables (#93).
  • Test that dbBind() against factor works but raises a warning (#91).
  • Test roundtrip of alternating empty and non-empty strings (#42).
  • Test multiple columns of different types in one statement or table (#35).
  • Test field.types argument to dbWriteTable() (#12).
  • Added tests for invalid or closed connection argument to all methods that expect a connection as first argument (#117).
  • Enabled test that tests a missing dbDisconnect().
  • Add test for unambiguous escaping of identifiers (rstats-db/RSQLite#123).
  • Reenable tests for visibility (#89).
  • Fix and specify 64-bit roundtrip test.
  • 64-bit integers only need to be coercible to numeric and character (#74).
  • Added roundtrip test for time values (#14).
  • Added tweaks for handling date, time, timestamp, ... (#53, #76).
  • Test that dbFetch() on update-only query returns warning (#66).

Adapted tests

  • NULL is a valid value for the row.names argument, same as FALSE.
  • A column named row_names receives no special handling (#54).
  • A warning (not an error anymore) is expected when calling dbDisconnect() on a closed or invalid connection.
  • row.names = FALSE is now the default for methods that read or write tables.
  • Add NA to beginning and end of columns in table roundtrip tests (#24).
  • Stricter tests for confusion of named and unnamed SQL parameters and placeholders (#107).
  • Also check names of all returned data frames.
  • The return value for all calls to dbGetQuery(), dbFetch(), and dbReadTable() is now checked for consistency (all columns have the same length, length matches number of rows) (#126).
  • Removed stress tests that start a new session.
  • Allow hms (or other subclasses of difftime) to be returned as time class (#135, @jimhester).
  • Test that dates are of type numeric (#99, @jimhester).
  • Replace POSIXlt by POSIXct (#100, @jimhester).
  • Use "PST8PDT" instead of "PST" as time zone (#110, @thrasibule).
  • Added tests for support of blob objects (input and output), but backends are not required to return blob objects (#98).
  • The logical_return, date_typed and timestamp_typed tweaks are respected by the bind tests.
  • Fixed tests involving time comparison; now uses UTC timezone and compares against a difftime.
  • Tests for roundtrip of character values now includes tabs, in addition to many other special characters (#85).
  • Make sure at least one table exists in the dbListTables() test.
  • Fix roundtrip tests for raw columns: now expecting NULL and not NA entries for SQL NULL values.
  • Fix expect_equal_df() for list columns.
  • Testing that a warning is given if the user forgets to call dbDisconnect() or dbClearResult() (#103).
  • Numeric roundtrip accepts conversion of NaN to NA (#79).


  • Fix R CMD check errors.
  • Internal consistency checks (#114).
  • Skip patterns that don't match any of the tests now raise a warning (#84).
  • New test_some() to test individual tests (#136).
  • Use desc instead of devtools (#40).
  • All unexpected warnings are now reported as test failures (#113).
  • DBItest_tweaks class gains a $ method, accessing an undefined tweak now raises an error.
  • The arguments of the tweaks() function now have default values that further describe their intended usage.
  • New with_closed_connection(), with_invalid_connection(), with_result() and with_remove_test_table() helpers, and expect_visible(), expect_inbisible_true(), and expect_equal_df() expectations for more concise tests.

DBItest 1.4 (2016-12-02)

DBI specification

  • Use markdown in documentation.
  • Description of parametrized queries and statements (#88).
  • New hidden DBIspec-wip page for work-in-progress documentation.
  • Get rid of "Format" and "Usage" sections, and aliases, in the specs.


  • Not testing for presence of max.connections element in dbGetInfo(Driver) (rstats-db/DBI#56).
  • Test multi-row binding for queries and statements (#96).
  • New ellipsis check that verifies that all implemented DBI methods contain ... in their formals. This excludes show() and all methods defined in this or other packages.
  • Refactored bind_ tests to use the new parameter_pattern tweak (#95).
  • Rough draft of transaction tests (#36).
  • New fetch_zero_rows test, split from fetch_premature_close.
  • The "compliance" test tests that the backend package exports exactly one subclass of each DBI virtual class.
  • Document and enhance test for dbDataType("DBIDriver", "ANY") (#88).
  • Minor corrections for "bind" tests.


  • Isolate stress tests from main test suite (#92).
  • Refactor test specification in smaller modules, isolated from actual test execution (#81). This breaks the documentation of the tests, which will be substituted by a DBI specification in prose.
  • Align description of binding with code.
  • Refactor tests for dbBind(), test is run by BindTester class, and behavior is specified by members and by instances of the new BindTesterExtra class.
  • The skip argument to the test_() functions is again evaluated with perl = TRUE to support negative lookaheads (#33).
  • Use dbSendStatement() and dbExecute() where appropriate.
  • Avoid empty subsections in Rd documentation to satisfy R CMD check (#81).

DBItest 1.3 (2016-07-07)

Bug fixes

  • Fix read_table test when the backend actually returns the data in a different order.

New tests

  • Test dbDataType() on connections (#69, #75, @imanuelcostigan).
  • Check returned strings for UTF-8 encoding (#72).
  • Repeated dbBind() + dbFetch() on the same result set (#51).


  • tweaks() gains an ... as first argument to support future/deprecated tweaks (with a warning), and also to avoid unnamed arguments (#83).
  • testthat now shows a more accurate location for the source of errors, failures, and skips (#78).
  • Aggregate skipped tests, only one skip() call per test function.
  • Indicate that some tests are optional in documentation (#15).


  • New constructor_relax_args tweak, currently not queried.
  • The ctx argument is now explicit in the test functions.
  • Change underscores to dashes in file names.
  • Remove testthat compatibility hack.
  • New all_have_utf8_or_ascii_encoding() which vectorizes has_utf8_or_ascii_encoding().
  • Test on AppVeyor (#73).
  • Work around regression in R 3.3.0 (fix scheduled for R 3.3.1) which affected stress tests.

DBItest 1.2 (2016-05-21)

  • Infrastructure
    • Support names for contexts (@hoesler, #67).
    • The skip argument to the test functions is now treated as a Perl regular expression to allow negative lookahead. Use skip = "(?!test_regex).*" to choose a single test to run (#33).
    • Added encoding arguments to non-ASCII string constants (#60, @hoesler).
  • Improve tests
    • simultaneous_connections test always closes all connections on exit (@hoesler, #68).
    • More generic compliance check (@hoesler, #61).
    • Update documentation to reflect test condition (@imanuelcostigan, #70).
  • testthat dependency
    • Import all of testthat to avoid R CMD check warnings.
    • Compatibility with dev version of testthat (#62).
  • Improve Travis builds
    • Use container-based builds on Travis.
    • Install RPostgres and RMySQL from rstats-db.
    • Install DBI and testthat from GitHub.

Version 1.1 (2016-02-12)

  • New feature: tweaks
    • New argument tweaks to make_context() (#49).
    • New tweaks(), essentially constructs a named list of tweaks but with predefined and documented argument names.
    • constructor_name, respected by the constructor.* tests.
    • strict_identifier, if TRUE all identifier must be syntactic names even if quoted. The quoting test is now split, and a part is ignored conditional to this tweak. The roundtrip_quotes tests also respects this tweak.
    • omit_blob_tests for DBMS that don't have a BLOB data type.
    • current_needs_parens -- some SQL dialects (e.g., BigQuery) require parentheses for the functions current_date, current_time and current_timestamp.
    • union, for specifying a nonstandard way of combining queries. All union queries now name each column in each subquery (required for bigrquery).
  • New tests
    • dbGetInfo(Result) (rstats-db/DBI#55).
    • dbListFields() (#26).
    • New package_name test in test_getting_started().
  • Improved tests
    • Stress test now installs package in temporary library (before loading DBI) using R CMD INSTALL before loading DBI (rstats-db/RSQLite#128, #48).
    • Row count is now tested for equality but not identity, so that backends can return a numeric value > 2^31 at their discretion.
    • Call dbRemoveTable() instead of issuing DROP requests, the latter might be unsupported.
    • Use subqueries in queries that use WHERE.
    • Test that dbClearResult() on a closed result set raises a warning.
    • Expect a warning instead of an error for double disconnect (#50).
    • Move connection test that requires dbFetch() to test_result().
    • Split can_connect_and_disconnect test.
    • Expect DBI to be in Imports, not in Depends.
  • Removed tests
    • Remove test for dbGetException() (rstats-db/DBI#51).
  • Bug fixes
    • Fix broken tests for quoting.
  • Self-testing
    • Test RPostgres, RMySQL, RSQLite and RKazam as part of the Travis-CI tests (#52).
    • Travis CI now installs rstats-db/DBI, updated namespace imports (dbiCheckCompliance(), dbListResults()).
    • Use fork of testthat.
  • Utilities
    • Return test results as named array of logical. Requires hadley/testthat#360, gracefully degrades with the CRAN version.
  • Internal
    • Refactored the get_info_() tests to use a vector of names.
    • Use versioned dependency for DBI
    • Use unqualified calls to dbBind() again

Version 1.0 (2015-12-17)

  • CRAN release
    • Eliminate errors on win-builder
    • Satisfy R CMD check
    • Use LGPL-2 license
    • Add RStudio as copyright holder
    • Move devtools package from "Imports" to "Suggests"

Version 0.3 (2015-11-15)

  • Feature-complete, ready for review
  • Tests from the proposal
    • Add missing methods to compliance check
    • Add simple read-only test (#27)
    • Add stress tests for repeated load/unload (with and without connecting) in new R session (#2),
    • Migrate all tests from existing backends (#28)
    • Refactor data_ tests to use a worker function test_select()
    • Test tables with NA values above and below the non-NA value in data_ tests
    • Test return values and error conditions for dbBind() and dbClearResult() (#31)
    • Test vectorization of dbQuoteString() and dbQuoteIdentifier() (#18)
    • Test that dates have integer as underlying data type (#9)
    • Roundtrip tests sort output table to be sure (#32)
    • Test NA to NULL conversion in dbQuoteString(), and false friends (#23)
    • Enhance test for dbQuoteIdentifier() (#30)
  • Style
    • Avoid using data.frame() for date and time columns (#10)
    • Use expect_identical() instead of expect_equal() in many places (#13)
    • Catch all errors in on.exit() handlers via expect_error() (#20).
    • Combine "meta" tests into new test_meta() (#37)
  • Documentation
    • New "test" vignette (#16)
    • Add package documentation (#38)
  • Same as 0.2-5

Version 0.2 (2015-11-11)

  • Tests from the proposal
    • SQL
    • Metadata
    • DBI compliance (not testing read-only yet)
  • Migrate most of the tests from RMySQL
  • Test improvements
    • Test BLOB data type (#17)
    • Check actual availability of type returned by dbDataType() (#19)
  • Testing infrastructure
    • Disambiguate test names (#21)
    • Use regex matching for deciding skipped tests, skip regex must match the entire test name
  • Documentation
    • Document all tests in each test function using the new inline documentation feature of roxygen2
    • Improve documentation for test_all(): Tests are listed in new "Tests" section
    • Add brief instructions to README
  • Move repository to rstats-db namespace
  • Same as 0.1-6

Version 0.1 (2015-10-11)

  • First GitHub release
  • Builds successfully on Travis
  • Testing infrastructure
    • Test context
    • Skipped tests call skip()
    • Function test_all() that runs all tests
  • Tests from the proposal
    • Getting started
    • Driver
    • Connection
    • Results
  • Code formatting is checked with lintr
  • Same as 0.0-5

Reference manual

It appears you don't have a PDF plugin for this browser. You can click here to download the reference manual.


1.7.0 by Kirill Müller, a year ago

https://dbitest.r-dbi.org, https://github.com/r-dbi/DBItest

Report a bug at https://github.com/r-dbi/DBItest/issues

Browse source code at https://github.com/cran/DBItest

Authors: Kirill Müller [aut, cre] , RStudio [cph] , R Consortium [fnd]

Documentation:   PDF Manual  

Task views: Databases with R

LGPL (>= 2.1) license

Imports blob, callr, DBI, desc, hms, lubridate, methods, R6, rlang, testthat, withr

Suggests debugme, devtools, knitr, lintr, rmarkdown, RSQLite

Suggested by DatabaseConnector, RGreenplum, RMariaDB, RPostgres, RSQLite, bigrquery, duckdb, odbc, sergeant.

See at CRAN