R SQL databases: a practical connection guide

June 9, 2026 · 8 min read ·Updated June 9, 2026 ·beginner

rsqldatabasesdbidbplyrdata

R and SQL have always worked well together, and the modern R interface makes the connection short and predictable. This guide is the orienting tour: which package to reach for, what each piece does, and the patterns that keep you out of trouble. The deeper guides on DBI, dbplyr, and SQLite cover each piece in detail.

Why connect R to SQL?

Three reasons usually bring people here. Your data has outgrown RAM, the database already exists and is the source of truth, or you need a query to be repeatable and reviewable by people who think in SQL. R has solid answers for all three, and once you have the right mental model, the actual code stays short.

A small concrete example first. The following runs anywhere with no setup, because it uses an in-memory SQLite database:

library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
on.exit(dbDisconnect(con), add = TRUE)

That on.exit() line matters. Forgetting dbDisconnect() leaks the connection. Do that in a long-running process and you eventually hit “too many connections” and the database quietly stops accepting new clients.

The four-package mental model

The R SQL interface is layered. From your code down to the database:

DBI, the front-end. Defines the verbs (dbConnect, dbGetQuery, dbDisconnect, …) and the contract every driver implements.
A backend driver (RSQLite, RPostgres, RMariaDB, odbc, bigrquery, duckdb). One per database engine. Pick the one that matches your database.
dbplyr, which is optional but useful. Translates dplyr verbs (filter, mutate, summarise, joins) into SQL and runs them on the database. Returns a lazy table you can collect() when you want the rows in R.
pool, also optional. Manages a set of DBI connections so multiple concurrent users do not block each other. Mostly relevant inside Shiny apps.

If you are writing a one-off script against a local SQLite file, you only need DBI and RSQLite. Everything else is “when you outgrow that”.

Reading R SQL data into R

Two functions do almost all the reading:

# Whole table as a data.frame
dbReadTable(con, "iris")
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width   Species
#> 1           5.1         3.5          1.4         0.2    setosa
#> 2           4.9         3.0          1.4         0.2    setosa
#> ...

# Arbitrary SELECT
dbGetQuery(con, "SELECT Species, AVG(`Sepal.Length`) AS avg_sepal
                 FROM iris GROUP BY Species")
#>      Species avg_sepal
#> 1     setosa     5.006
#> 2 versicolor     5.936
#> 3  virginica     6.588

dbReadTable() is fine for small lookup tables. For anything that involves a WHERE, JOIN, or aggregation, write the SQL. dbGetQuery() is the workhorse; it runs a SELECT and returns the result as a data.frame. For very large result sets, dbSendQuery() plus dbFetch() lets you pull rows in chunks (see the common-mistakes section below).

dplyr-style with dbplyr

If you prefer to stay in dplyr, dbplyr translates your verbs into SQL and defers execution until you call collect():

library(dplyr)
library(dbplyr)

lazy <- tbl(con, "iris") |>
  filter(Species == "versicolor") |>
  group_by(Species) |>
  summarise(n = n(), avg_petal = mean(Petal.Length))

show_query(lazy)
#> <SQL>
#> SELECT `Species`, COUNT(*) AS `n`, AVG(`Petal.Length`) AS `avg_petal`
#> FROM `iris`
#> WHERE (`Species` = 'versicolor')
#> GROUP BY `Species`

collect(lazy)
#> # A tibble: 1 × 3
#>   Species        n avg_petal
#>   <chr>      <int>     <dbl>
#> 1 versicolor    50      4.26

show_query() is the key habit. Different backends translate dplyr differently, and the pipeline that “works on my machine” can behave differently on SQL Server or Snowflake. Read the SQL before you trust it. dbplyr 2.5.1 (September 2025) tightened translations for SQL Server, Redshift, Snowflake, and Postgres, but the rule still holds: verify on the backend you actually run against.

Writing data back

dbWriteTable() creates the table and inserts the rows in one call. Always pass row.names = FALSE; the default has caught everyone at least once:

dbWriteTable(con, "mtcars", mtcars, row.names = FALSE)

# Append to an existing table
dbWriteTable(con, "mtcars", head(mtcars, 3), row.names = FALSE, append = TRUE)

# INSERT only; fail if the table does not exist
DBI::dbAppendTable(con, "mtcars", head(mtcars, 3), row.names = FALSE)

dbExecute() is the function for ad-hoc DDL/DML: CREATE, INSERT, UPDATE, DELETE. It returns the affected row count and does not return a result set. If you need the new auto-generated primary key back, use dbGetQuery() for the INSERT ... RETURNING * form on Postgres, or read last_insert_id on MySQL.

Safe queries

Never build SQL by concatenating strings, even with “trusted” inputs. The two safe options:

# Option 1: DBI::sqlInterpolate() for simple cases
DBI::sqlInterpolate(
  con,
  "SELECT * FROM iris WHERE Species = ?sp",
  sp = "setosa"
)
#> <SQL> SELECT * FROM iris WHERE Species = 'setosa'

# Option 2: glue::glue_sql() when you need a vector (the `*` collapses)
glue::glue_sql(
  "SELECT * FROM iris WHERE Species IN ({species*})",
  species = c("setosa", "versicolor"),
  .con = con
)
#> <SQL> SELECT * FROM iris WHERE Species IN ('setosa', 'versicolor')

sqlInterpolate() quotes scalars; glue_sql() adds the IN (...) vector handling. Plain glue::glue() does not escape, so use it for non-SQL strings only. Both functions call dbQuoteLiteral() under the hood, which is the only thing standing between your data and a SQL-injection bug.

Transactions

When several writes must succeed or fail together, wrap them in dbWithTransaction(). If any statement in the block errors, the whole thing rolls back:

DBI::dbWithTransaction(con, {
  dbExecute(con, "UPDATE accounts SET balance = balance - 100 WHERE id = 1")
  dbExecute(con, "UPDATE accounts SET balance = balance + 100 WHERE id = 2")
})
# Both succeed, or both roll back.

For finer control, dbBegin(), dbCommit(), and dbRollback() are the underlying verbs. Prefer the dbWithTransaction() wrapper unless you really need that level of control. The same wrapper is what pool::poolWithTransaction() calls internally, so the same pattern works against a pool.

When you need a pool

A DBIConnection is single-user. In a Shiny app, two simultaneous sessions hitting the same connection block each other. pool fixes that by handing out connections from a small set:

library(pool)

pool <- dbPool(
  RPostgres::Postgres(),
  host = "db", port = 5432,
  dbname = "app", user = "app",
  password = Sys.getenv("DB_PW"),
  minSize = 1, maxSize = 5
)
onStop(function(pool) poolClose(pool))

The onStop() line is the single most important line in any pool-using script. Forgetting poolClose() leaks connections the same way forgetting dbDisconnect() does, just multiplied by maxSize. For one-off scripts and RMarkdown reports, you do not need a pool. It is only meaningful when multiple concurrent users share a process.

Choosing an R SQL backend

Situation	Package
Local file, prototype, tests	`RSQLite` (file or `:memory:`)
PostgreSQL in production	`RPostgres`
MySQL or MariaDB in production	`RMariaDB`
SQL Server, Snowflake, Redshift, anything with an ODBC driver	`odbc` + a system DSN
Google BigQuery	`bigrquery`
OLAP-style analytics on a local file	`duckdb`

The front-end (DBI and dbplyr) stays the same across all of them. The driver is what changes. The choice usually comes down to which database engine your team already runs; there is rarely a reason to switch engines for R’s sake. The odbc option is the right one whenever your database ships a vendor ODBC driver, which is most enterprise systems. bigrquery is special: BigQuery is columnar and HTTP-based, so the package wraps the REST API rather than talking a wire protocol.

Common mistakes

Forgetting dbDisconnect(). Wrap it in on.exit() or use poolClose(). The error is silent at first and only shows up when connection counts creep toward the database’s limit.

String-built SQL. paste() and plain glue() are SQL-injection vectors. Use sqlInterpolate() or glue_sql() even when the input comes from your own ETL.

dbGetQuery() on an INSERT. It silently returns no rows. Use dbExecute() for non-SELECT statements.

Missing row.names = FALSE in dbWriteTable(). The default writes a row.names column or errors on stricter backends. Make it the first argument you type, and consider a thin wrapper around dbWriteTable() that bakes it in for your whole team.

collect() on a million-row table. You will OOM. Read big tables in chunks with dbSendQuery() and dbFetch():

res <- dbSendQuery(con, "SELECT * FROM big_table")
repeat {
  chunk <- dbFetch(res, n = 10000)
  if (nrow(chunk) == 0) break
  process(chunk)
}
dbClearResult(res)

The 10,000-row batch size is a starting point; tune it to your memory budget.

dbplyr differences across backends. show_query() before you ship. Postgres, SQL Server, and Snowflake translate paste() and window functions differently, and a pipeline that runs in 200 ms on SQLite can hit a five-second wall-clock on a remote warehouse.

Credentials in code. password = "secret" in version control is a leak. Use Sys.getenv("DB_PW"), the keyring package, or .Renviron.

Conclusion

R SQL connections live in a small, layered ecosystem: a front-end (DBI), a driver that matches your database, and two optional helpers (dbplyr for dplyr-style code, pool for shared processes). The R-side code is rarely the hard part. The hard part is picking the right driver for the database you already have and remembering the small handful of safety rules. The three guides below go deeper on each layer.