Question about package combining SQL + glue

[kentm]

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.

[cderv]

@kentm

As of now, I am saving the sql files in the inst/ directory

I would exactly do that and put them into a sql dir.

@kentm

and calling: readr::read_file(paste0 path.package("PackageName"), "/file_name.sql")) to load them. Not sure if this is the best/proper approach.

To use a file you put in inst from inside your package, use system.file(..., package = "PackageName"). See R Packages (2e) - 7  Data

That would mean

readr::read_file(system.file("file_name.sql", package = "PackageName"))

You can have a look at glue_sql also to run queries safely

Hope it helps

[kentm]

Awesome, thanks for the feedback and advice. Looks like I was on the right path and this addresses some of my concerns.