I'm wondering if anyone has ever put together a package that uses SQL + glue to allow users to create custom SQL queries from an R function. My main question around this, is best practices on:
- where save the SQL files
- how to read them into R
I probably could save the SQL as R functions and place all the SQL into one long string, but the "major" benefit of saving them as text files (.sql) comes when trying to edit / update them in a text editor.
As of now, I am saving the sql files in the inst/
directory and calling: readr::read_file(paste0 path.package("PackageName"), "/file_name.sql"))
to load them. Not sure if this is the best/proper approach.
Does anyone have experience with this? This package would be for internal use only.
A simple example might be something like :
example.sql
SELECT * FROM {table}
query_function.R
query_function <- function(table_name) {
full_query <- glue::glue(
readr::read_file(paste0 path.package("PackageName"), "/example.sql"),
table = table_name
)
return(full_query)
}
usage.R
first_table <- query_function(table_name = "first_table")
second_table <- query_function(table_name = "second_table")
Any feedback or thoughts would be appreciated.