Connecting to DB in Rmd with .Renviron variables

[MeganBeckett]

Hi there

Really hoping we can get this up and running for our organisation! It's really great.

I have an issue with RMarkdown documents which get the database connection variables from the .Renviron file (ie. DB_PASS = Sys.getenv("DB_PASS"). When trying to render the post with knowledge_repo add .... it fails with an error as does not get the details for connecting to the DB to get the data in order to create the report.

If I hard code the DB connection details into the file, then it works. But, we obviously don't want to do this as then have to commit the DB details to the repo. Standard practice in R is to have these in a local .Renviron file.

I have tried sourcing the .Renviron file in the terminal before adding the post and whilst I can then echo $DB_PASSWORD in the terminal and it prints out, when rendering the post, it still fails at the DB connection step as it's not getting the correct details.

Any advice?

[bulam]

Hi Megan,

I think the issue might be that when knowledge repo runs R to render your post, it runs an R session from the terminal, which might be using a different .Renviron file than your RStudio session.

To check if this is the case, could you do

1. Sys.getenv() in RStudio
2. Open up an R session in the terminal by simply running R in the terminal and also do Sys.getenv()?

You can then compare the outputs to see if the files are different. The output from step 2 will probably be missing the DB_PASS variable.

If that's the case, the next thing to look at would be why RStudio and the command line R are using different .Renvirons.

Where is your .Renviron file with the db details stored? Per here, when R starts, it discovers the .Renviron in the places below in this order:

1. User specified path .Renviron visible with Sys.getenv("R_ENVIRON_USER")
2. Current folder ./.Renviron
3. User root folder ~/.Renviron

I personally have just one .Renviron file set in my user root folder at ~/.Renviron (same place as my .bash_profile) and was able to load a database password stored in the .Renviron file both from RStudio and command line R.

[MeganBeckett]

Hi Bill

Thanks for the quick feedback.

To respond, if I run Sys.getenv() in RStudio with the project open and in an R session in the terminal, when starting R from within the project folder, then the outputs are the same. This is because my .Renviron file is within the project folder.

The reason I have the .Renviron in the project folder is that I have many projects and each has it's own DB variables needed to connect to various databases.

If I do the same but run an R session from the terminal in my root directory, then I don't get the same output, and DB_PASS is obviously missing.

So, I realised that I just need to run the knowledge_repo commands from the R project root directory (previously I was doing it within the folder that the report was in within this project folder, so one level too deep, and it then doesn't find .Renviron in that folder as it normally does when starting an R session with a .Rproj file).

Thanks for your help and the pointer about the order in which .Renviron files are discovered. This makes all the difference.