R Academy · Lesson

dbplyr: SQL via dplyr Syntax

Write dplyr code that translates to SQL and runs on the database.

What Is dbplyr?

dbplyr is an R package that translates dplyr verbs into SQL automatically. Instead of writing raw SQL, you write familiar dplyr code and dbplyr converts it to the correct SQL dialect for your database backend. The query runs in the database — not in R memory.

# install.packages(c('dbplyr', 'DBI', 'RSQLite'))
library(DBI)
library(dbplyr)
library(dplyr)

# dbplyr sits between dplyr and your database:
# Your dplyr code -> dbplyr -> SQL -> Database -> result

# Supported backends: PostgreSQL, MySQL, SQLite,
#                     SQL Server, BigQuery, Snowflake, ...

cat('dbplyr translates dplyr to SQL')

Connecting to a Database

dbplyr works on top of a DBI connection. You establish the connection with DBI::dbConnect() using the appropriate driver package, then pass that connection object to dbplyr functions.

library(DBI)
library(dplyr)
library(dbplyr)

# SQLite example (no server needed — great for demos)
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Write a test table to the in-memory database
copy_to(con, nycflights13::flights, 'flights',
        temporary = FALSE, overwrite = TRUE)

# PostgreSQL example (real server):
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

cat('Connected to database')

All lessons in this course

← Back to R Academy