Have an article explaining when (or how to decide) to use DBI vs dplyr about db.rstudio.com HOT 6 OPEN

While thinking through whether or not dplyr should have append_to() as an analog to copy_to() I realised one absolute principle of dplyr is that it never mutates data in place - it always creates a copy with the modifications. That means that append_to() is explicitly out of scope, and in general if you want to modify existing data you should be using DBI, not dplyr.

from db.rstudio.com.

Maybe we a Best Practice article called something like "Using dplyr and DBI with databases" could help with this. With an example, the article would show which function to use as the user progresses in the analysis. It would be an expanded version of the example in this RViews article: https://rviews.rstudio.com/2017/05/17/databases-using-r/

from db.rstudio.com.

Why would you be writing to a database table as part of an analysis?

from db.rstudio.com.

The example will start with a database that already has data we want to work with, as it is in most real cases. So, I see two scenarios we need to address:

• The user has a small data set in R that they want to join to the data in the database

• A query that results form dplyr code takes to long to re-run every time, so a temp table is needed

from db.rstudio.com.

Maybe instead of setting strict guidelines, we just do a comparison of methods between the two and leave it to the reader to decide. For example, I'm fairly certain that some users who want to do a select star will use dbgetquery even though there are many other ways to print a table.

from db.rstudio.com.

That's why I was thinking of an example. To me, this is a softer way to suggest a guideline that the user can take or leave.

from db.rstudio.com.