Alternative to using SQLite in testing tools

[ChemiKyle]

While REDCap uses MySQL/MariaDB, we've used an in-memory SQLite database for ease of use in testing

redcapcustodian/tests/testthat/test-redcap.R

Line 1 in 9c643e3

conn <- dbConnect(RSQLite::SQLite(), dbname = ":memory:")

Syntax mismatches have already complicated schemas and are now making porting existing functions difficult (#12 (comment)).

I'm looking into seeing if we can stand up a MariaDB database entirely in memory to avoid the need for local or containerized solutions. Another alternative may be to port all SQL commands to R equivalents and let DBI handle the command fabrication.

RAM MariaDB

Ideally we would be able to create an entirely in-memory MariaDB instance, this unfortunately is not as simple as dbConnect(RMariaDB::MariaDB(), dbname = ":memory:").

It appears MariaDB supports in-memory tables (see: https://stackoverflow.com/a/54849686/7418735 and https://mariadb.com/kb/en/memory-storage-engine/).

[pbchase]

I have been using duckdb for this purpose. it's data types are richer than SQLite and closer to MySQL's. I did a lot of this conversion in PR #143 and is eliminated 4 long-standing intermittent test failures. They were all caused by sqlite DBs accidentally appear on disk instead of in memory. Running library(dotenv) would change the sqlite behavior and tests would persist data on disk, then read it, and trip up on the unexpected database content.