SQL Tests in R Packages

Does anyone have any references or examples of methods to test SQL queries that are used inside an R package? For example, if I want to write SQL templates and then pass in variables using glue, how should I write unit tests for those SQL queries? Note, this would be for an internal package not something that would go to CRAN.

The question is what aspect of the queries are you trying to test?

If you are trying to test that the queries are formed as you would expect you can compare the query to the expected string with expect_equal().

If you are trying to test that the query returns the results you expect you would either need to run the query against the database directly, stand up a local database with test data using something like SQLite, or use mock tests for the queries.

Also if you are using glue with SQL be sure to use glue_sql()!

1 Like

Thanks for the response (and glue_sql() recommendation). I envision it being a combination of the two so I will start with simple string comparisons with expect_equal() and will need to do a deeper dive into setting up a local database / using mock tests.

You might also be interested in dittodb, an R package that provides "A Test Environment for Database Requests".

1 Like

Awesome, thank you for the recommendation. I'll definitely check that out.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.