writing SQL code in RStudio


I am an ex SAS user. What I particularly liked about it, that it had a SQL compiler. I could write SQL code and get all the highlights, syntax errors and everything else that SAS had to offer but writing SQL. Had to migrate to R due to company change. Is there a good way to write SQL instead of R code? I tried sqldf package, its great, but its missing syntax highlights, error messages for missed commas etc.

Any suggestions, how can I make writing SQL easy and pleasant and possible?


I'm not aware of SQL syntax highlighting, but an alternative is to go fully R and let the SQL code be written under the hood via dbplyr:
A dplyr backend for databases • dbplyr (tidyverse.org)

Are you using the RStudio IDE or Workbench? If so, it has some sql support so you can write pure sql scripts with some facilities like autocompletion, output previews, etc and then pull the query results into R if needed with something like this.

results <- dbGetQuery(con, statement = readr::read_file(here('02-get-results.sql')))

Or if you are using Rmd or qmd files, you can include sql code chunks directly and the output will be automatically available on the working environment

Hi, Isabella Velasquez did a great write-up on SQL with RStudio: Working with databases and SQL in RStudio - Posit. It has all of the advice I would have shared with you today.