Question about package combining SQL + glue

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.

1 Like

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

To use a file you put in inst from inside your package, use system.file(..., package = "PackageName"). See https://r-pkgs.org/data.html#data-extdata

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

2 Likes

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

2 Likes

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