`tbl()` not behaving in lazy manner, any alternatives?

[SpikyClip]

tbl() does appear to make a connection with the database, and I am hoping there is a solution to make it lazy to improve loading speeds of a shiny app.

For context, I have a shiny app that builds several base queries in a global list in global.R to avoid repeating work across multiple users. As the app is hosted on shinyapps.io (which I believe is hosted in NA) and my database is in Australia, there's some lag I can assume is attributed to internet speed (It is much faster locally run). On shinyapps.io it can take 23-45 seconds to call all the tbl() objects which is a huge chunk of time in the initial loading time.

I've tried adding vars = null which I got from this page as I can only assume the delay is caused by retrieving the column names, but this does not appear to help as the column names still appear in the object list.

Is there any way to avoid the initial fetching of column names?

#' Get module queries as list
#'
#' @param pool connection object
#' @param module_name name of module as specified in `golem-config.yml` file
#'
#' @return list of queries filtered to module
utils_queries__get_all_queries <- function(pool) {
    queries <- list()
    tables <- get_golem_config("tables")
    schema <- get_golem_config(
        "schema",
        config = Sys.getenv("GOLEM_CONFIG_ACTIVE")
    )

    for (table in names(tables)) {
        resolved_table_name <- glue("{schema}.{tables[[table]]$table_name}")
        queries[[table]] <- tbl(pool, I(resolved_table_name), vars = NULL)
    }

    return(queries)
}

[lschneiderbauer]

From the docs of tbl_sql() which you cite:

vars: Provide column names as a character vector to avoid retrieving them from the database. Mainly useful for better performance when creating multiple tbl objects.

vars = NULL is the default, which means, that vars = NULL means "fetch the column names". You need to pass a character vector of column names in order to avoid that.

[SpikyClip]

Thanks, not sure how I missed that. I've just tested that it works, though I can't supply a dummy list since it throws errors with all the dplyr verbs (which in hindsight is obvious, it needs to know the exact column names).

Practically it's probably less annoying for me to switch shinyapps hosts to something closer to my database than trying to maintain an up-to-date static list of all the column names for all the tables in my app.