dbConnect within function when capturing a fixture

[sebastian-fox]

Brief description of the problem

I'm trying to use start_db_capturing() with a function, where the dbConnect() function occurs inside that function. I'm getting an error There was no dbname, so I don't know where to look for mocks.

The kind of database backend you are trying to test

SQLite database

This reprex is from the getting started vignette.

library(DBI)
library(dittodb)

mean_delays <- function(group_col) {
  con <- dbConnect(RSQLite::SQLite())
  on.exit(dbDisconnect(con))

  query <- glue::glue(
    "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
    "WHERE arr_delay > 0 GROUP BY {group_col}"
  )

  return(dbGetQuery(con, query))
}

start_db_capturing()
out <- mean_delays("month")

Error in get_dbname(list(...)) : There was no dbname, so I don't know where to look for mocks.

[jonkeane]

Ah, good catch! I'll try and put a fix together to catch this situation. In the mean time, this should work if you supply a name for the SQLite() connection (even explicitly ":memory:").

Also thanks to the issue I also realized that getting started vignette should also explain that you'll need the nyc flights data in whatever database you're connecting to there (you'll get a Error: no such table: nycflights13.flights if the database doesn't have the flights table already)

[sebastian-fox]

Great, thank you. I'm looking forward to getting started with the package :)

[sebastian-fox]

I just tried providing ":memory:" to SQLite() but got the same error:

library(DBI)
library(dittodb)

mean_delays <- function(group_col) {
  con <- dbConnect(RSQLite::SQLite(":memory:"))
  on.exit(dbDisconnect(con))

  query <- glue::glue(
    "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
    "WHERE arr_delay > 0 GROUP BY {group_col}"
  )

  return(dbGetQuery(con, query))
}

start_db_capturing()
out <- mean_delays("month")

Have I not followed what you were suggesting correctly?

[jonkeane]

Ah, yeah you need the ":memory:" as an argument to dbConnect:

library(DBI)
library(dittodb)

mean_delays <- function(group_col) {
  con <- dbConnect(RSQLite::SQLite(), ":memory:")
  on.exit(dbDisconnect(con))

  query <- glue::glue(
    "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
    "WHERE arr_delay > 0 GROUP BY {group_col}"
  )

  return(dbGetQuery(con, query))
}

start_db_capturing()
out <- mean_delays("month")

But this won't work right out of the box — you will need to make sure the database you're connecting to has the nycflights13 data data in it. You could do this with SQLite by running nycflights13_create_sqlite(location = "nycflights") before the rest of the commands here, and then call:

library(DBI)
library(dittodb)

mean_delays <- function(group_col) {
  con <- dbConnect(RSQLite::SQLite(), "nycflights")
  on.exit(dbDisconnect(con))

  query <- glue::glue(
    "SELECT {group_col}, AVG(arr_delay) as mean_delay from nycflights13.flights ",
    "WHERE arr_delay > 0 GROUP BY {group_col}"
  )

  return(dbGetQuery(con, query))
}

start_db_capturing()
out <- mean_delays("month")

[sebastian-fox]

Thank you - I'll check out the changes when I get a chance. Really appreciate it :)

[jonkeane]

Thank you again for pointing this out! It helped me update the docs to be clearer.