dbplyr

dbplyr is an R package that provides a database backend for dplyr, enabling the familiar dplyr verbs to operate on relational databases by translating them into SQL. It acts as an interface between R’s tidyverse and SQL databases, allowing large datasets to be manipulated in the database rather than loaded into memory. The translation layer constructs lazy query objects that represent a sequence of operations; the actual work is performed by the database when results are requested.

Key concepts include tbl_dbi objects, created by tbl() or copy_to(), which point to a database table. When you apply verbs such as filter, select, arrange, mutate, group_by, or summarise to a tbl_dbi, dbplyr converts these into equivalent SQL expressions. The generated SQL can be inspected with show_query(), and results can be retrieved with collect(). If a transformation cannot be translated, dbplyr may fall back to in-R evaluation or raise an error.

Usage example: after establishing a DBI connection, you can reference a remote table with tbl(con, "sales"), build a pipeline like tbl(con, "sales") %>% filter(country == 'US') %>% group_by(product) %>% summarise(total = sum(amount)) %>% arrange(desc(total)) %>% collect() to bring results into R.

dbplyr supports a range of DBI-backed backends (for example PostgreSQL, MySQL/MariaDB, SQLite) and works with any backend that provides SQL translation through DBI. It is maintained as part of the tidyverse ecosystem and serves as the primary interface for translating dplyr code to database queries.