Databases with DBI
|
This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production. The reference material consulted while preparing these pages is listed in the section bibliography. |
Checked against R 4.6.1 (2026-06-24) on 2026-10-20; R 4.6.2 is scheduled for 2026-10-29. Every database example uses DBI 1.3.0 with RSQLite 3.53.3, which bundles SQLite 3.53.3 and needs no database server. The dbplyr sections also use dplyr 1.2.1 and dbplyr 2.6.0, and print the tibble 3.3.1 data frames they return; integers beyond 32 bits come back as bit64 4.8.6 objects; lintr 3.4.0 was used only to check the scripts. The other backend packages (RPostgres, RMariaDB, odbc, duckdb) and the packages RODBC, sqldf, keyring and config are not installed here, and askpass and glue are installed but not used: all of them are named in the text, described from their own documentation, and never run. Data usually lives in a database for a reason: it is bigger than memory, or other people and programs share it. R, on the other hand, keeps everything in memory. The DBI package is the answer to how the two talk to each other, and this page is about it: one set of functions (dbConnect(), dbGetQuery(), dbExecute(), dbWriteTable() and so on) that behaves the same whether the database behind it is SQLite, PostgreSQL, MariaDB, DuckDB or anything reachable through ODBC, and about the dbplyr package, which writes the SQL for you from dplyr code.
Every example is a script that was run with Rscript on R 4.6.1 with warnings printed as they happen (options(warn = 1)), and the lines that start with #> are what it printed. The scripts also pass the default lintr linters. The names books, loans, shelf_today, loan_days and the book() constructor come from the running library-shelf scenario described on the section index: a script that needs them starts with source("scenario.R"), and books and loans are the data frames that every example copies into a database. Each script opens its own connection and closes it before it ends. The databases are in memory, or in a file made with tempfile() that the script deletes, and no example prints the name of a temporary file. Where a message contains quotation marks, they are the typographic quotes R prints in a UTF-8 locale.
This page is about the R side of databases. Writing SQL (selecting, joining, grouping, defining tables, transactions) is the subject of the SQL Reference, and the queries below are only as long as the R function being shown needs; the SQL transactions page explains what the transaction functions in the section on transactions below do in the database. The data frames that go in and come out are explained in Data Frames, the dates in Dates and Times, the S4 system that DBI is built on in S4 Classes and Methods, the condition system used to catch errors in Conditions and Error Handling, and the dplyr verbs that dbplyr translates in The tidyverse and dplyr.
Why talk to a database from R
R Data Import/Export gives the reasons in the first section of its chapter on relational databases (R Data Import/Export: Why use a database?). Everything R manipulates is resident in memory, and several copies of the data can exist during a function call, so data objects of more than a few hundred megabytes can make R run out of memory. R does not easily support concurrent access: if several users read and update the same data, the changes of one are not visible to the others. R can store data between sessions, but in a format that is specific to R. A relational database management system is designed to do all of these things well: fast access to selected parts of large tables, summaries and cross-tabulations computed where the data is, a more organised way to store data than a rectangular grid, and concurrent access from many clients with security constraints. The manual’s examples of the statistical use of a database are to extract a 10% sample, to cross-tabulate into a multi-dimensional table, and to extract the data group by group for separate analysis.
The pattern that follows from this is to push the work that the database is good at (filtering, joining, aggregating) into the query, and to bring only the result into R. The rest of this page is the machinery for doing that safely. For data that fit in memory and live in files, the routes are in Reading and Writing Text Data and Serialization: RDS, RData and JSON; for data that arrive over the network, in HTTP and Web APIs.
The manual’s overview of relational databases also states two points that matter for every query below (R Data Import/Export: SQL queries). The more comprehensive R interfaces generate SQL behind the scenes for common operations, but direct use of SQL is needed for complex operations in all of them. And, unlike the rows of a data frame, the rows of a table are best thought of as unordered: without an ORDER BY, the order of the result is indeterminate. A script that depends on the order of rows must say so in the query.
DBI: one interface, many backends
DBI stands for DataBase Interface. Its home page says that the package separates the connection to the database management system into a front-end and a back-end: DBI defines the interface, and packages such as RPostgres, RMariaDB, RSQLite and odbc implement it for a particular system (DBI package documentation: DBI). The interface is similar in spirit to the database interfaces of Perl, Java, Python and Microsoft ODBC: connect and disconnect, create and execute statements, extract results, handle errors, ask for metadata, and manage transactions. The R Data Import/Export manual describes the same split as a proposal for a unified front-end package with a back-end per system (R Data Import/Export: R interface packages); that proposal is now the way R talks to databases.
In R terms DBI is a set of S4 generics and a handful of virtual S4 classes. A generic such as dbGetQuery() is defined once, in DBI. Each backend package defines classes that extend DBI’s (SQLiteConnection extends DBIConnection) and writes methods for them. When you call dbGetQuery(con, sql), S4 dispatch looks at the class of con, finds the method of the backend that created it, and runs that. The diagram shows the layers, with dbplyr above DBI.
Source: DBI package documentation: DBI
The class names in the diagram are the ones the backends' own documentation uses (for example the DBI page of dbBind() lists the methods RSQLite::dbBind("SQLiteResult"), RPostgres::dbBind("PqResult"), RMariaDB::dbBind("MariaDBResult"), odbc::dbBind("OdbcResult") and duckdb::dbBind("duckdb_result") (DBI package documentation: dbBind)). Three kinds of object take part, and every DBI backend has all three:
-
a driver (
DBIDriver), made by a constructor such asRSQLite::SQLite(), that knows how to open connections; -
a connection (
DBIConnection), returned bydbConnect(), that you pass to nearly every other function and must close; -
a result (
DBIResult), returned bydbSendQuery()anddbSendStatement(), that stands for one query in progress, from which rows are fetched, and that must be cleared.
| Database | Backend package | Driver constructor | Status on this page |
|---|---|---|---|
SQLite |
RSQLite |
|
Installed (3.53.3); every example runs against it. |
PostgreSQL |
RPostgres |
|
Not installed; one labelled fragment, never run. |
MariaDB, MySQL |
RMariaDB |
|
Not installed; fragment, never run. |
Any database with an ODBC driver |
odbc |
|
Not installed; fragment, never run. |
DuckDB |
duckdb |
|
Not installed; fragment, never run. |
Source: DBI package documentation: DBI
The DBI home page names RPostgres, RMariaDB, RSQLite and odbc among the backends and says that odbc is for databases that you can access via ODBC. The specification that every backend implements is the DBI specification (DBI package documentation: DBI specification); that is why the code in the rest of this page, written against SQLite, changes only in the line that creates the connection when the database changes. What does not carry over is the SQL itself, which has dialects, and a few details that the specification leaves to the backend; those are called out where they appear (the format of the parameter placeholders, the column types, the date support).
The first script asks R itself how the layers are arranged. is() lists the classes of an object from the most specific to the most general; showMethods() lists the S4 methods that exist for a generic.
library(DBI)
drv <- RSQLite::SQLite()
con <- dbConnect(drv, ":memory:")
# RSQLite's classes extend the base classes that DBI defines.
is(drv)
#> [1] "SQLiteDriver" "DBIDriver" "DBIObject"
is(con)
#> [1] "SQLiteConnection" "DBIConnection" "DBIObject"
extends("SQLiteResult", "DBIResult")
#> [1] TRUE
isVirtualClass("DBIConnection")
#> [1] TRUE
# dbListTables() is a DBI generic; RSQLite wrote the method for its class.
isGeneric("dbListTables")
#> [1] TRUE
showMethods("dbListTables", classes = "SQLiteConnection")
#> Function: dbListTables (package DBI)
#> conn="SQLiteConnection"
#>
# dbGetQuery() has one method for every DBIConnection: it calls
# dbSendQuery(), dbFetch() and dbClearResult() for you.
showMethods("dbGetQuery", classes = c("SQLiteConnection", "character"))
#> Function: dbGetQuery (package DBI)
#> conn="DBIConnection", statement="character"
#>
dbDisconnect(con)Source: R documentation: showMethods
The driver and the connection each have the backend class first and the DBI class after it, and both end in DBIObject, the root of the hierarchy. dbListTables() has a method written for the backend’s own class, SQLiteConnection. dbGetQuery() is the other case: the method that showMethods() lists is the one defined for any DBIConnection, which a SQLiteConnection inherits, because the definition is the same for every backend. The DBI article on advanced usage describes what it does: dbGetQuery() works by calling dbSendQuery(), dbFetch() and dbClearResult() behind the scenes (DBI package documentation: Advanced DBI usage). Those three are what you call yourself when you want to read a result in pieces, as in the section on chunked reads below. In DBI 1.3.0 many of these generics are of the kind "nonstandardGeneric", a change its NEWS lists among the breaking changes because a package that tests the generic’s definition could notice; ordinary users do not.
Connecting and disconnecting
A database in memory
dbConnect() is the first call of every session. Its first argument is the driver object; the remaining arguments are the backend’s. For SQLite the one that matters is dbname, the file that holds the database, or the special name ":memory:" for a database that lives in RAM and disappears when the connection is closed (RSQLite package documentation: SQLite). That makes it the right database for experiments, tests and the examples on this page.
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
con
#> <SQLiteConnection>
#> Path: :memory:
#> Extensions: TRUE
dbIsValid(con)
#> [1] TRUE
dbListTables(con)
#> character(0)
dbDisconnect(con)
dbIsValid(con)
#> [1] FALSE
con
#> <SQLiteConnection>
#> DISCONNECTED
# A closed connection cannot be used again.
tryCatch(dbListTables(con), error = \(e) conditionMessage(e))
#> [1] "Invalid or closed connection"
# Close the connection even when the code in between fails.
with_shelf_db <- function(dbname, fun) {
con <- dbConnect(RSQLite::SQLite(), dbname)
on.exit(dbDisconnect(con), add = TRUE)
fun(con)
}
kept <- NULL
result <- tryCatch(
with_shelf_db(":memory:", \(con) {
kept <<- con
stop("something went wrong")
}),
error = \(e) conditionMessage(e)
)
result
#> [1] "something went wrong"
dbIsValid(kept)
#> [1] FALSE
# A connection cannot be saved: after a round trip through a file it is dead.
con <- dbConnect(RSQLite::SQLite(), ":memory:")
path <- tempfile(fileext = ".rds")
saveRDS(con, path)
restored <- readRDS(path)
dbIsValid(restored)
#> [1] FALSE
tryCatch(dbListTables(restored), error = \(e) conditionMessage(e))
#> [1] "bad_weak_ptr"
dbDisconnect(con)
unlink(path)The print method of a connection says what it is connected to: <SQLiteConnection>, the Path, and whether loadable Extensions are enabled (they are by default). It never prints a memory address, so a connection can be shown in a log. After dbDisconnect(), dbIsValid() is FALSE, the object prints as DISCONNECTED, and any function that needs the connection fails with Invalid or closed connection. Calling the functions on a closed connection is a programming error and not a recoverable condition, which is why the function with_shelf_db() at the end of the script opens the connection, registers the closing with on.exit() and only then runs the code it was given. The code fails with an error, the connection is closed anyway, and dbIsValid(kept) confirms it. The last block of the script shows that a connection is not a value that can be stored: after saveRDS() and readRDS() the restored object is not valid (dbIsValid() is FALSE) and using it fails with the unhelpful bad_weak_ptr, because the connection lives in memory that belongs to the session. Open a new connection in each session. add = TRUE keeps any other on.exit() handler of the function (R documentation: on.exit).
Closing is not optional. RSQLite’s documentation notes that connections are cleaned up automatically after they are deleted and the garbage collector reclaims them, but that dbDisconnect() terminates a connection early, and that it will not actually close until all open result sets have been closed (a warning says so; see the section on chunked reads). The RPostgres documentation puts it the same way: manually disconnecting is not necessary, but still recommended, and deleting the object that holds a connection disconnects it at the next garbage collection with a warning (RPostgres package documentation: Postgres). For a server database each forgotten connection is also a session the server keeps open until it times out.
Disconnecting is documented on its own page (DBI package documentation: dbDisconnect).
A database in a file
A file name as dbname makes a database that survives the session, can be opened by another R session or program, and is created if it does not exist. The next script uses a file from tempfile(), writes the books table to it, closes the connection, opens the file again, and removes it at the end. It also shows the read-only flag, and dbCanConnect(), which answers TRUE or FALSE instead of signalling an error.
source("scenario.R")
library(DBI)
path <- tempfile(fileext = ".sqlite")
file.exists(path)
#> [1] FALSE
con <- dbConnect(RSQLite::SQLite(), path)
dbWriteTable(con, "books", books, row.names = FALSE)
dbDisconnect(con)
file.exists(path)
#> [1] TRUE
# A second connection, even from another R session, sees the same data.
con <- dbConnect(RSQLite::SQLite(), path)
dbListTables(con)
#> [1] "books"
dbGetQuery(con, "SELECT COUNT(*) AS n FROM books")
#> n
#> 1 16
dbDisconnect(con)
# SQLITE_RO opens the file read-only.
ro <- dbConnect(RSQLite::SQLite(), path, flags = RSQLite::SQLITE_RO)
tryCatch(
dbExecute(ro, "DELETE FROM books"),
error = \(e) conditionMessage(e)
)
#> [1] "attempt to write a readonly database"
dbDisconnect(ro)
# dbCanConnect() answers TRUE or FALSE instead of failing.
isTRUE(dbCanConnect(RSQLite::SQLite(), path))
#> [1] TRUE
missing_path <- tempfile(fileext = ".sqlite")
isTRUE(dbCanConnect(
RSQLite::SQLite(), missing_path,
flags = RSQLite::SQLITE_RO
))
#> [1] FALSE
unlink(path)
file.exists(path)
#> [1] FALSEThe arguments of dbConnect() for an SQLite driver, as documented in version 3.53.3, are these.
| Argument | Meaning |
|---|---|
|
The database file. |
|
|
|
|
|
The R type for 64-bit integers: |
|
Whether SQLite loadable extensions are enabled ( |
|
|
|
Advanced options: the number of pages SQLite keeps in memory, and the SQLite operating-system interface. |
The read-only connection in the script gets the error text of SQLite itself, attempt to write a readonly database. dbCanConnect() is the DBI generic added in DBI 1.0.0 for checking, before a long job, that the settings work. Because a file database is shared, a second connection (or a second R process) can read it while the first one is open; what happens when two of them write at once is up to SQLite’s locking, which RSQLite documents under its own functions (its NEWS for 2.2.4 introduces sqliteSetBusyHandler() to configure what SQLite does when the database is locked), and one reason to prefer a server database when many users write.
Other databases
A server database needs more than a file name: where it is, which database on it, and who you are. The argument names differ by backend, as the references of RPostgres (dbname, host, port, user, password), RMariaDB (dbname, host, username, password), odbc (dsn, or driver, server, database, uid, pwd) and duckdb (dbdir, read_only) show. The fragment below is the shape of those calls, with every setting read from the environment (the next section explains why). It is a fragment that was only linted: the four packages are not installed here and no database server is available, so it was never run.
# Fragment, never run: RPostgres, RMariaDB, odbc and duckdb are not installed
# here, and no database server is available. The settings come from the
# environment, not from the code.
library(DBI)
connect_shelf_postgres <- function() {
dbConnect(
RPostgres::Postgres(),
dbname = Sys.getenv("SHELF_DB_NAME"),
host = Sys.getenv("SHELF_DB_HOST"),
user = Sys.getenv("SHELF_DB_USER"),
password = Sys.getenv("SHELF_DB_PASSWORD")
)
}
connect_shelf_mariadb <- function() {
dbConnect(
RMariaDB::MariaDB(),
dbname = Sys.getenv("SHELF_DB_NAME"),
host = Sys.getenv("SHELF_DB_HOST"),
username = Sys.getenv("SHELF_DB_USER"),
password = Sys.getenv("SHELF_DB_PASSWORD")
)
}
connect_shelf_odbc <- function() {
dbConnect(
odbc::odbc(),
dsn = Sys.getenv("SHELF_DB_DSN"),
uid = Sys.getenv("SHELF_DB_USER"),
pwd = Sys.getenv("SHELF_DB_PASSWORD")
)
}
connect_shelf_duckdb <- function() {
dbConnect(duckdb::duckdb())
}
# The rest of the code does not change with the backend.
count_loans <- function(connect) {
con <- connect()
on.exit(dbDisconnect(con), add = TRUE)
dbGetQuery(con, "SELECT COUNT(*) AS n FROM loans")$n
}The last function, count_loans(), is the point of the fragment: it takes the function that makes the connection and does not care which database it reaches. The other documentation pages for the arguments used above are RMariaDB package documentation: dbConnect, odbc package documentation: dbConnect and duckdb package documentation: duckdb. DuckDB, like SQLite, is embedded in the R package; its dbdir argument defaults to a database kept in RAM.
Credentials stay out of the code
A password written in a script ends up in version control, in a shared project folder, in a screenshot of the editor and in the history of every machine the script was copied to. The DBI introduction makes the same point about its own example, which has a password in the code: that should be avoided for databases with secured access, and one way is to keep the credential in the system’s credential store and query it with the keyring package (DBI package documentation: Introduction to DBI). The rules for this page and for any script that connects to a real server are:
-
Read every connection setting that differs between machines (host, database name, user, password) with
Sys.getenv(), never from a literal in the script. The values live in environment variables, set by the environment that runs the script (a shell profile, a deployment secret store, a CI secret) or by a.Renvironfile. -
A
.Renvironfile holdsNAME=valuelines that R turns into environment variables when it starts. R looks for it in the current directory first and then in the user’s home directory, unless the variableR_ENVIRON_USERnames another file (R documentation: Startup). A project-level.Renvironholds the names of the settings, and the file must be excluded from version control (listed in.gitignore).readRenviron()loads a file into a running session. How R reads startup files is the subject of Sessions, Workspaces and Startup. -
Some backends read the usual environment variables of their database themselves. RPostgres documents that
hostandportdefault to thePGHOSTandPGPORTvariables, anduserandpasswordtoPGUSERandPGPASSWORDor to the matching line of the~/.pgpassfile. RMariaDB can read authentication parameters from a section of a MariaDB option file (default.fileandgroup), and odbc can take the connection from a data source name (dsn) configured outside R and listed byodbcListDataSources(). In each case the secret is kept outside the R code. -
For interactive use, ask for the secret when it is needed instead of storing it: the askpass package prompts for a password, and keyring reads and writes the operating system’s credential store (macOS Keychain, the Windows Credential Store, the Linux Secret Service). The config package returns the values for the current environment (development, test, production), which is useful as long as the secrets in the configuration are placeholders for environment variables. These three packages are named here from their CRAN descriptions and are not run.
-
Make the script fail early and clearly when a setting is missing, and never print the value.
A template for the settings of the fragment above can live in the repository under another name (for example .Renviron.example) with the names and no secrets; each developer copies it to .Renviron, which is not committed, and fills in the values on their own machine:
SHELF_DB_NAME=shelf
SHELF_DB_HOST=db.example.com
SHELF_DB_USER=shelf_reader
SHELF_DB_PASSWORD=Source: R documentation: Startup
The host db.example.com is a placeholder, not a real server. The next script is the helper that goes with it. Nothing sets SHELF_DB_PASSWORD in this session, so the helper stops with a message that names the variable and shows no value, and it shows what Sys.getenv() returns for a variable that is not set.
# Read connection settings from environment variables; never from the code.
db_setting <- function(name) {
value <- Sys.getenv(name)
if (!nzchar(value)) {
stop("environment variable ", name, " is not set", call. = FALSE)
}
value
}
# Nothing sets SHELF_DB_PASSWORD in this session, so the helper stops early
# with a message that names the variable but never shows a value.
tryCatch(db_setting("SHELF_DB_PASSWORD"), error = \(e) conditionMessage(e))
#> [1] "environment variable SHELF_DB_PASSWORD is not set"
# Sys.getenv() returns "" for an unset variable, or the value of unset =.
Sys.getenv("SHELF_DB_PASSWORD")
#> [1] ""
Sys.getenv("SHELF_DB_PASSWORD", unset = NA)
#> [1] NA
# A non-secret setting can be set for the session with Sys.setenv().
Sys.setenv(SHELF_DB_NAME = "shelf")
db_setting("SHELF_DB_NAME")
#> [1] "shelf"
Sys.unsetenv("SHELF_DB_NAME")Source: R documentation: Sys.getenv
An unset variable is the empty string, not an error, which is why the helper tests it with nzchar(); with unset = NA the missing value is distinguishable from an empty one. Sys.setenv() changes the environment of the running R process and of the child processes it starts, and the change is gone when the session ends (R documentation: Sys.setenv). Setting a variable that holds a real password with Sys.setenv() inside a script defeats the purpose, because the value is then in the script; the example sets only the non-secret database name.
Tables: writing, listing, reading and removing
The functions that treat a whole table as a data frame are the shortest way in and out of a database, and the manual presents them as convenient interfaces to read, write, test and delete tables: dbWriteTable() copies a data frame into a table, dbReadTable() copies a table into a data frame, and dbListTables(), dbListFields(), dbExistsTable() and dbRemoveTable() look after the rest (R Data Import/Export: Packages using DBI). The next script copies books into a database, writes to it again in the ways that fail and succeed, reads it back, and shows what happens to row names.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
# Copy a data frame into the database as a table.
dbWriteTable(con, "books", books, row.names = FALSE)
dbListTables(con)
#> [1] "books"
dbListFields(con, "books")
#> [1] "title" "author" "isbn" "year" "genre" "pages"
dbExistsTable(con, "books")
#> [1] TRUE
dbExistsTable(con, "shelves")
#> [1] FALSE
# Writing to an existing table is an error unless you say what you mean.
tryCatch(
dbWriteTable(con, "books", books, row.names = FALSE),
error = \(e) conditionMessage(e)
)
#> [1] "Table books exists in database, and both overwrite and append are FALSE"
dbWriteTable(con, "books", books, row.names = FALSE, overwrite = TRUE)
dbWriteTable(con, "books", books[1:2, ], row.names = FALSE, append = TRUE)
dbGetQuery(con, "SELECT COUNT(*) AS n FROM books")
#> n
#> 1 18
# Read it back: a plain data frame, with the same columns.
dbWriteTable(con, "books", books, row.names = FALSE, overwrite = TRUE)
back <- dbReadTable(con, "books")
class(back)
#> [1] "data.frame"
dim(back)
#> [1] 16 6
identical(names(back), names(books))
#> [1] TRUE
all.equal(back, books)
#> [1] TRUE
# Row names are dropped unless you ask for them.
named <- books[1:3, c("title", "year")]
rownames(named) <- c("b1", "b2", "b3")
dbWriteTable(con, "named", named)
dbListFields(con, "named")
#> [1] "title" "year"
dbWriteTable(con, "named", named, row.names = TRUE, overwrite = TRUE)
dbListFields(con, "named")
#> [1] "row_names" "title" "year"
dbReadTable(con, "named", row.names = "row_names")
#> title year
#> b1 The Dispossessed 1974
#> b2 Pride and Prejudice 1813
#> b3 the quiet streets 2019
# Remove a table; removing a missing one is an error unless told otherwise.
dbRemoveTable(con, "named")
tryCatch(
dbRemoveTable(con, "named"),
error = \(e) conditionMessage(e)
)
#> [1] "no such table: named"
dbRemoveTable(con, "named", fail_if_missing = FALSE)
dbDisconnect(con)Three points in that output deserve a sentence each.
-
A table that already exists is never silently replaced.
dbWriteTable()stops withTable books exists in database, and both overwrite and append are FALSE.overwrite = TRUEreplaces the table with the new data;append = TRUEadds the rows to the existing table, which is why the table had 18 rows after the second write. Do not pass both. In a script that must be safe to run twice, decide which of the two it means. -
dbReadTable()returns a plain data frame, andall.equal()finds no difference with the originalbooks, including theNAvalues (SQLNULL). The rows of a table have no order of their own, so a script that needs the original order must store a key and sort by it. -
Row names are not written unless you ask. The script passes
row.names = FALSEforbooks, and writing the data framenamedwithout the argument produced only the columnstitleandyear, because DBI’s default is to leave row names out.row.names = TRUEadds a column calledrow_names, anddbReadTable(row.names = "row_names")turns it back into row names. The R Data Import/Export manual’s example for the older RMySQL package (which maps row names to arow_namesfield) is where many tutorials get the habit of expecting them to be kept.
Table names are quoted for you: since RSQLite 1.1, functions that take a table name quote it with dbQuoteIdentifier(), so a name with a space or a reserved word is safe, and a name you quoted yourself has to be marked with DBI::SQL() to avoid being quoted twice (RSQLite package documentation: NEWS).
Creating a table first
dbWriteTable() guesses the column types and writes the data in one call. When the table must have particular types, constraints or keys, create it with SQL (see SQL data definition) or with dbCreateTable(), and then add rows with dbAppendTable(). Both are new generics of DBI 1.0.0 (2018-05-02), listed in its NEWS next to dbCanConnect() and dbIsReadOnly() (DBI package documentation: NEWS).
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
# dbCreateTable() makes an empty table from named column types ...
dbCreateTable(
con, "members",
c(member_id = "INTEGER", name = "TEXT", joined = "TEXT")
)
dbListFields(con, "members")
#> [1] "member_id" "name" "joined"
dbReadTable(con, "members")
#> [1] member_id name joined
#> <0 rows> (or 0-length row.names)
# ... and dbAppendTable() adds the rows of a data frame and returns the count.
new_members <- data.frame(
member_id = 1:2,
name = c("Ana Ruiz", "Ben Okafor"),
joined = c("2024-01-05", "2024-02-11")
)
dbAppendTable(con, "members", new_members)
#> [1] 2
dbReadTable(con, "members")
#> member_id name joined
#> 1 1 Ana Ruiz 2024-01-05
#> 2 2 Ben Okafor 2024-02-11
# The data frame's columns must exist in the table.
tryCatch(
dbAppendTable(con, "members", data.frame(zzz = 1)),
error = \(e) conditionMessage(e)
)
#> [1] "table members has no column named zzz"
# Table names can be a structured Id(), here with the schema SQLite calls main.
dbReadTable(con, Id(schema = "main", table = "members"))
#> member_id name joined
#> 1 1 Ana Ruiz 2024-01-05
#> 2 2 Ben Okafor 2024-02-11
# dbListObjects() lists what the database contains, schemas included.
objects <- dbListObjects(con)
objects$is_prefix
#> [1] FALSE TRUE
vapply(objects$table, \(id) id@name[[1L]], character(1))
#> [1] "members" "main"
dbDisconnect(con)dbAppendTable() returns the number of rows it added, and a data frame whose columns are not in the table is an error from SQLite (table members has no column named zzz). A table that lives in a schema other than the default is addressed with Id(): Id(schema = "main", table = "members") is the structured name that DBI, since version 0.8, quotes correctly for each backend, and dbListObjects() (which came with it) lists what is in the database, schemas included. SQLite calls its default schema main and the one for temporary tables temp; in the output of the script is_prefix is TRUE for main, which is a schema to look inside, and FALSE for the table members. The first RSQLite release with the version number aligned to SQLite (3.52.0) added dbListObjects() support for attached databases with a schema prefix (DBI package documentation: dbListObjects, DBI package documentation: Id).
Column types: from R classes to SQLite and back
The R Data Import/Export manual lists the types a DBMS commonly offers (floating point, integer, character and varying character, boolean, date, time, timestamp, text and blob) and says that the more comprehensive R interface packages hide the type conversion from the user (R Data Import/Export: Overview of RDBMSs). Hidden does not mean absent. SQLite has only a few storage classes and lets a column hold any of them, and the conversion has consequences for logical, date-time and factor columns. dbDataType() asks the backend which column type it would use for an R value; called on one column at a time it tells you what dbWriteTable() will declare.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
samples <- data.frame(
n = 1:2,
x = c(1.5, NA),
label = c("a", NA),
flag = c(TRUE, NA),
day = c(shelf_today, NA),
genre = factor(c("fiction", "poetry"))
)
# The SQLite column type chosen for each R column.
vapply(samples, \(column) dbDataType(con, column), character(1))
#> n x label flag day genre
#> "INTEGER" "REAL" "TEXT" "INTEGER" "REAL" "TEXT"
# On the whole data frame the method reports DBI's generic names instead.
dbDataType(con, samples)
#> n x label flag day genre
#> "INT" "DOUBLE" "TEXT" "SMALLINT" "DATE" "TEXT"
dbWriteTable(con, "samples", samples, row.names = FALSE)
back <- dbReadTable(con, "samples")
str(back)
#> 'data.frame': 2 obs. of 6 variables:
#> $ n : int 1 2
#> $ x : num 1.5 NA
#> $ label: chr "a" NA
#> $ flag : int 1 NA
#> $ day : num 20634 NA
#> $ genre: chr "fiction" "poetry"
# SQLite stores a Date as a number: days since 1970-01-01.
dbGetQuery(con, "SELECT day, typeof(day) AS stored_as FROM samples")
#> day stored_as
#> 1 20634 real
#> 2 NA null
as.Date(back$day)
#> [1] "2026-06-30" NA
dbDisconnect(con)
# extended_types = TRUE maps DATE, TIME and TIMESTAMP columns to R classes.
con <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
vapply(samples, \(column) dbDataType(con, column), character(1))
#> n x label flag day genre
#> "INTEGER" "REAL" "TEXT" "INTEGER" "DATE" "TEXT"
dbWriteTable(con, "samples", samples, row.names = FALSE)
str(dbReadTable(con, "samples"))
#> 'data.frame': 2 obs. of 6 variables:
#> $ n : int 1 2
#> $ x : num 1.5 NA
#> $ label: chr "a" NA
#> $ flag : int 1 NA
#> $ day : Date, format: "2026-06-30" NA
#> $ genre: chr "fiction" "poetry"
dbDisconnect(con)
# Integers that do not fit in 32 bits come back as bit64::integer64 ...
con <- dbConnect(RSQLite::SQLite(), ":memory:")
big <- dbGetQuery(con, "SELECT 9007199254740993 AS big")
class(big$big)
#> [1] "integer64"
big
#> big
#> 1 9007199254740993
dbDisconnect(con)
# ... unless bigint asks for another type, which can lose precision.
con <- dbConnect(RSQLite::SQLite(), ":memory:", bigint = "numeric")
sprintf("%.0f", dbGetQuery(con, "SELECT 9007199254740993 AS big")$big)
#> [1] "9007199254740992"
dbDisconnect(con)What the output shows, as a table:
| R column | SQLite column type declared | What dbReadTable() returns |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Four consequences follow.
-
A logical column comes back as 0, 1 and
NA; convert withas.logical(). A factor comes back as text; rebuild the factor on the R side withfactor(x, levels = ...)if the level order matters (see Factors). -
A
Dateis stored as a number, and converting it back is up to you:as.Date(x)on the numbers works in R 4.3.0 and later, which accepts a numeric argument withoutorigin(R NEWS: Changes in R 4.3.0); before it,as.Date(x, origin = "1970-01-01")was required. Worse, SQL date functions applied to such a column read the number as something else (SQLite’sjulianday()treats it as a Julian day number), which is why the analysis near the end of this page stores dates as ISO text. -
extended_types = TRUE(an argument ofdbConnect()that appeared in RSQLite 2.2.3 and 2.2.4 and, from 2.2.6, also changes whatdbDataType()anddbWriteTable()produce) is the fix. With it, columns declaredDATE,TIMEorDATETIME/TIMESTAMPcome back asDate,hmsandPOSIXctin UTC; the script shows thedaycolumn declaredDATEand returned as aDate. If a stored value cannot be mapped, anNAis returned with a warning. The RSQLite reference table says how the value is read: a numeric value is a count of days (forDATE) or seconds (for the other two) since 1970-01-01, and a text value must be in ISO format (2020-01-23,12:34:56, or both separated by a space). -
SQLite integers can be 64 bits wide. Values that do not fit an R integer come back as
bit64::integer64by default, which keeps every digit, as the 16-digit example prints. Thebigintargument ofdbConnect()chooses another type;"numeric"is a plain double and, as the last line shows, rounds a value that needs more than 53 bits.
Calling dbDataType() on the whole data frame, as the script does once, is a trap in RSQLite: the data frame method reports generic type names (INT, DOUBLE, SMALLINT, DATE and TIMESTAMP, whatever the connection), while the columns that dbWriteTable() actually creates are the ones in the table above. Check a table’s real declaration with PRAGMA table_info(tablename) or read one column at a time, as the script does.
Running queries
dbGetQuery(): a SELECT, the whole result
dbGetQuery() sends a query, reads the whole result into a data frame, and releases the result. It is the function to use for a SELECT whose result fits in memory.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# dbGetQuery() sends a SELECT and returns the whole result as a data frame.
dbGetQuery(con, "SELECT title, year FROM books WHERE year > 2015 ORDER BY year")
#> title year
#> 1 Midnight Ledger 2016
#> 2 Notes on Gravity 2017
#> 3 Salt and Static 2018
#> 4 the quiet streets 2019
#> 5 Orchard Logic 2020
#> 6 Loud Roofs 2021
#> 7 Ten Winters 2022
#> 8 THE LONG TABLE 2023
# No rows still gives a data frame with the right columns.
str(dbGetQuery(con, "SELECT title, year FROM books WHERE year > 3000"))
#> 'data.frame': 0 obs. of 2 variables:
#> $ title: chr
#> $ year : int
# SQL NULL becomes NA; COUNT(column) does not count the NULLs.
dbGetQuery(con, "SELECT COUNT(*) AS n, COUNT(pages) AS with_pages FROM books")
#> n with_pages
#> 1 16 14
# n = limits the number of rows fetched.
dbGetQuery(con, "SELECT title FROM books ORDER BY title", n = 3)
#> title
#> 1 the quiet streets
#> 2 A Map of Salt
#> 3 Garden of Small Fires
# dbExecute() runs a statement that changes data and returns the row count.
dbExecute(con, "UPDATE books SET genre = LOWER(TRIM(genre))")
#> [1] 16
dbExecute(con, "DELETE FROM books WHERE year IS NULL")
#> [1] 1
dbExecute(con, "CREATE INDEX books_genre ON books (genre)")
#> [1] 0
dbGetQuery(con, "SELECT genre, COUNT(*) AS n FROM books GROUP BY genre")
#> genre n
#> 1 classic 2
#> 2 fiction 4
#> 3 mystery 2
#> 4 non-fiction 3
#> 5 poetry 1
#> 6 science fiction 3
# A SELECT sent through dbExecute() changes nothing.
dbExecute(con, "SELECT 1")
#> [1] 0
# Only the first statement of a multi-statement string runs.
tryCatch(
dbGetQuery(con, "SELECT 1 AS a; SELECT 2 AS a"),
warning = \(w) conditionMessage(w)
)
#> [1] "Ignoring remaining part of query: SELECT 2 AS a"
dbDisconnect(con)SQL NULL is NA, and COUNT(pages) counts only the rows where pages is not null: 14 of 16, for the two missing page counts of the scenario. A query with no rows still gives a data frame with the columns and their types, so code that does nrow(result) == 0 needs no special case. The argument n limits the rows fetched; when a query has no ORDER BY, which rows those are is up to the database.
dbExecute(): statements that change data
UPDATE, DELETE, INSERT and the statements that define tables return no rows. dbExecute() sends one and returns the number of rows it affected, which is the thing to check: 0 rows deleted is the usual sign of a WHERE that matches nothing. dbExecute() and dbSendStatement() are the DBI 0.5 names for work that older code did with dbGetQuery() and dbSendQuery(); RSQLite’s NEWS says new code should use them where appropriate. In the output above, UPDATE reported 16 rows, DELETE 1 row (the book without a year) and CREATE INDEX 0. Sending a SELECT through dbExecute() is not an error; it returns 0.
SQLite runs one statement per call. If the string holds two, RSQLite runs the first and warns Ignoring remaining part of query: SELECT 2 AS a. That is a property of this backend and a useful accident against stacked-statement tricks, but not a defence (see the section on parameters, and the contrast in the older tutorials section); other backends may run all the statements. The function is documented in DBI package documentation: dbExecute.
Reading a result in chunks
When a result is too big to read at once, or when only the first rows are needed, take the three steps that dbGetQuery() performs by itself, and keep control of the middle one: dbSendQuery() sends the query and returns a result object, dbFetch(res, n =) fetches up to n rows from it (all the remaining ones if n is omitted or -1), and dbClearResult() releases it. dbHasCompleted() says whether all the rows have been fetched, dbGetRowCount() how many were fetched so far, and dbColumnInfo() what the columns are. The DBI article on advanced usage gives the loop on this pattern: fetch chunk by chunk while dbHasCompleted() is false.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "loans", loans, row.names = FALSE)
# dbSendQuery() starts the query; dbFetch(n =) pulls rows a chunk at a time.
res <- dbSendQuery(con, "SELECT loan_id, member FROM loans ORDER BY loan_id")
dbHasCompleted(res)
#> [1] FALSE
dbColumnInfo(res)
#> name type
#> 1 loan_id character
#> 2 member character
sizes <- integer()
while (!dbHasCompleted(res)) {
chunk <- dbFetch(res, n = 25)
sizes <- c(sizes, nrow(chunk))
}
sizes
#> [1] 25 25 10
dbGetRowCount(res)
#> [1] 60
dbClearResult(res)
dbIsValid(res)
#> [1] FALSE
# Put the cleanup in on.exit() so an error in the loop still releases it.
count_in_chunks <- function(con, sql, chunk_size) {
res <- dbSendQuery(con, sql)
on.exit(dbClearResult(res), add = TRUE)
total <- 0L
while (!dbHasCompleted(res)) {
total <- total + nrow(dbFetch(res, n = chunk_size))
}
total
}
count_in_chunks(con, "SELECT * FROM loans", 25L)
#> [1] 60
# A result that is never cleared makes dbDisconnect() complain.
res <- dbSendQuery(con, "SELECT * FROM loans")
tryCatch(dbDisconnect(con), warning = \(w) conditionMessage(w))
#> [1] "There are 1 result in use. The connection will be released when they are closed"
dbClearResult(res)
dbDisconnect(con)
dbIsValid(con)
#> [1] FALSEThe 60 loans came out as chunks of 25, 25 and 10 rows, and the loop stopped without a fourth, empty fetch because dbHasCompleted() turned TRUE when the last row was read. dbSendQuery() is documented for queries that return rows: its page describes the data retrieval flow, and dbSendStatement() has the matching flow for statements that change data (DBI package documentation: dbSendQuery, DBI package documentation: dbHasCompleted, DBI package documentation: dbClearResult). A result that is never cleared holds the connection open: dbDisconnect() warns with There are 1 result in use. The connection will be released when they are closed, and clearing it is what lets the connection go. Put dbClearResult() in on.exit(), as count_in_chunks() does, so that an error halfway through the loop cannot leave it behind. Chunking limits what R holds in memory at once; the database still evaluates the query.
Parameterised queries
A query that contains a value taken from a variable has two ways to get it in. One is to build the SQL text with the value inside; the other is to leave a placeholder in the SQL and to send the value separately. The second is the rule, because it makes SQL injection impossible by construction. In the DBI documentation’s words, parameterised (or prepared) queries and statements serve two purposes besides safety: the same query can be run more than once with different values, and the database may cache intermediate information such as the execution plan (DBI package documentation: dbBind). The DBI article on advanced usage names two ways to avoid injection from user-supplied parameters, quoting and parameterised queries, and then shows parameterised queries as the way to push the substitution to the DBMS (DBI package documentation: Advanced DBI usage).
The attack, in one paragraph: a function that pastes genre into "... WHERE genre = '" ... "'" is correct for the values its author thought of, and a value that contains a quotation mark ends the string literal early and the rest of the value is read as SQL. The value comes from a form field, a file or a web request, so the person who types it decides part of the query. A parameter is sent to the database as data, after the SQL text has been parsed, so it can never become SQL. The demonstration with the real output is in the section on older tutorials, where the pattern belongs.
Placeholders and params
dbGetQuery(), dbSendQuery(), dbExecute() and dbSendStatement() take a params argument (partial matching on the older name param still works in RSQLite, but DBI 1.1.0 changed its examples to params). It is a list with one element per placeholder in the SQL, matched in order, or matched by name for the named style. The DBI documentation does not specify the placeholder syntax; each backend has its own, and DBI passes the SQL through unchanged (DBI package documentation: Advanced DBI usage):
| Placeholder style | Backends that use it, per the dbBind() page |
|---|---|
|
RMariaDB and RSQLite |
|
RPostgres and RSQLite |
|
RSQLite |
RSQLite accepts all three styles. Code that must run on more than one backend should therefore keep the SQL string in one place per backend, check the backend’s documentation for its placeholder style (the odbc package is not listed on that page), or use dbplyr, which writes the SQL for the connection it is given.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# A ? placeholder is filled from params, in order; the SQL never contains
# the value, so the value cannot change the meaning of the query.
dbGetQuery(
con, "SELECT title, year FROM books WHERE genre = ?",
params = list("Mystery")
)
#> title year
#> 1 Midnight Ledger 2016
#> 2 Salt and Static 2018
dbGetQuery(
con, "SELECT title, year FROM books WHERE genre = ? AND year > ?",
params = list("Mystery", 2016)
)
#> title year
#> 1 Salt and Static 2018
# RSQLite also accepts named placeholders (:name and $name).
dbGetQuery(
con, "SELECT title, year FROM books WHERE genre = :genre AND year > :after",
params = list(genre = "Mystery", after = 2016)
)
#> title year
#> 1 Salt and Static 2018
# A vector runs the query once per element and stacks the results ...
dbGetQuery(
con, "SELECT title, genre FROM books WHERE genre = ?",
params = list(c("Mystery", "Poetry"))
)
#> title genre
#> 1 Midnight Ledger Mystery
#> 2 Salt and Static Mystery
#> 3 Garden of Small Fires Poetry
#> 4 Paper Moons Poetry
# ... so it is not an IN list. For IN, write one placeholder per value.
genres <- c("Mystery", "Poetry")
placeholders <- paste(rep("?", length(genres)), collapse = ", ")
sql <- paste0(
"SELECT title, genre FROM books WHERE genre IN (", placeholders, ")"
)
sql
#> [1] "SELECT title, genre FROM books WHERE genre IN (?, ?)"
dbGetQuery(con, sql, params = as.list(genres))
#> title genre
#> 1 Midnight Ledger Mystery
#> 2 Garden of Small Fires Poetry
#> 3 Paper Moons Poetry
#> 4 Salt and Static Mystery
# NA is sent as NULL.
dbGetQuery(con, "SELECT ? IS NULL AS is_null", params = list(NA_character_))
#> is_null
#> 1 1
# Placeholders only stand for values, never for table or column names.
dbGetQuery(
con, "SELECT ? AS column_name FROM books LIMIT 2",
params = list("title")
)
#> column_name
#> 1 title
#> 2 title
# Mismatches are reported with a message that names the problem.
tryCatch(
dbGetQuery(con, "SELECT title FROM books WHERE year = ?"),
error = \(e) conditionMessage(e)
)
#> [1] "Query needs to be bound before fetching"
tryCatch(
dbGetQuery(
con, "SELECT title FROM books WHERE year = ?",
params = list(2019, 2020)
),
error = \(e) conditionMessage(e)
)
#> [1] "Query requires 1 params; 2 supplied."
tryCatch(
dbGetQuery(
con, "SELECT title FROM books WHERE year = :year",
params = list(yeer = 2019)
),
error = \(e) conditionMessage(e)
)
#> [1] "No value given for placeholder year"
tryCatch(
dbGetQuery(
con, "SELECT title FROM books WHERE year = ? AND genre = ?",
params = list(c(2019, 2020), "Fiction")
),
error = \(e) conditionMessage(e)
)
#> [1] "Parameter 2 does not have length 2."
tryCatch(
dbGetQuery(
con, "SELECT title FROM books WHERE year = ?",
params = list(year = 2019)
),
error = \(e) conditionMessage(e)
)
#> [1] "Cannot use named parameters for anonymous/numbered placeholders"
dbDisconnect(con)Reading the output:
-
A value in
paramsis never part of the SQL.genre = ?with"Mystery"finds the two mysteries, and a value that contains quotation marks or SQL keywords would be matched as that text. -
A vector in
paramsis not one value: it is one run of the query per element, and the results are stacked. Two genres gave the four rows of two runs (all the mysteries, then all the poetry). With several parameters, the vectors must have the same length; recycling is not done (Parameter 2 does not have length 2.). -
So a vector is not an
INlist. ForWHERE genre IN (...)write one placeholder per value (paste(rep("?", n), collapse = ", ")) and pass the values as a list, as the script does; the text you paste is only question marks, never data. -
NAis sent as SQLNULL. -
A placeholder stands for a value, not for a name.
SELECT ? AS column_namereturned the string"title"twice, not the columntitle. Table and column names are quoted withdbQuoteIdentifier()(next section). -
The mismatches are reported by the backend with texts that name the problem:
Query needs to be bound before fetching(a placeholder and noparams),Query requires 1 params; 2 supplied.,No value given for placeholder year(a name that does not match), andCannot use named parameters for anonymous/numbered placeholderswhen a named list is given for?placeholders.
Bulk inserts and dbBind()
dbExecute() accepts params too, and the vectors make a bulk insert: one INSERT statement, many rows, one call, safe by the same rule. RSQLite’s NEWS describes executing the same prepared query or statement efficiently many times by passing a data-frame-like object. When one query is to be run many times with different values, dbSendQuery() once and dbBind() for each value lets the database reuse its preparation of the query: dbBind(res, params) binds new values to the result, then dbFetch() reads that run’s rows. dbSendStatement() plays the same role for a statement, and dbGetRowsAffected() reports how many rows the last binding changed.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# dbExecute(params =) with vectors inserts one row per element.
dbExecute(con, "
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
joined TEXT
)
")
#> [1] 0
dbExecute(
con, "INSERT INTO members (name, joined) VALUES (?, ?)",
params = list(
c("Ana Ruiz", "Ben Okafor", "Dev Patel"),
c("2024-01-05", "2024-02-11", "2024-03-02")
)
)
#> [1] 3
dbReadTable(con, "members")
#> member_id name joined
#> 1 1 Ana Ruiz 2024-01-05
#> 2 2 Ben Okafor 2024-02-11
#> 3 3 Dev Patel 2024-03-02
# The database enforces its own rules, and says which one failed.
tryCatch(
dbExecute(
con, "INSERT INTO members (name) VALUES (?)",
params = list("Ana Ruiz")
),
error = \(e) conditionMessage(e)
)
#> [1] "UNIQUE constraint failed: members.name"
tryCatch(
dbExecute(con, "INSERT INTO members (name) VALUES (NULL)"),
error = \(e) conditionMessage(e)
)
#> [1] "NOT NULL constraint failed: members.name"
# dbBind() runs a prepared query again with new values.
res <- dbSendQuery(con, "SELECT title, year FROM books WHERE genre = :genre")
by_genre <- lapply(c("Mystery", "Poetry"), \(g) {
dbBind(res, list(genre = g))
dbFetch(res)
})
by_genre
#> [[1]]
#> title year
#> 1 Midnight Ledger 2016
#> 2 Salt and Static 2018
#>
#> [[2]]
#> title year
#> 1 Garden of Small Fires NA
#> 2 Paper Moons 2005
#>
dbClearResult(res)
# dbSendStatement() + dbBind() does the same for statements that change data.
st <- dbSendStatement(con, "UPDATE members SET joined = ? WHERE name = ?")
dbBind(st, list("2025-01-01", "Ana Ruiz"))
dbGetRowsAffected(st)
#> [1] 1
dbBind(st, list(
c("2025-02-01", "2025-03-01"),
c("Ben Okafor", "Dev Patel")
))
dbGetRowsAffected(st)
#> [1] 2
dbClearResult(st)
dbReadTable(con, "members")
#> member_id name joined
#> 1 1 Ana Ruiz 2025-01-01
#> 2 2 Ben Okafor 2025-02-01
#> 3 3 Dev Patel 2025-03-01
dbDisconnect(con)The UNIQUE and NOT NULL constraints belong to the table definition, and violating them is an error with the name of the table and column (UNIQUE constraint failed: members.name). That is how a database protects data that several programs write: the rule lives with the data, and the R code only has to handle the error (see Conditions and Error Handling). Binding a vector to dbBind() runs the query or statement once per element, exactly like params, and dbGetRowsAffected() after the second binding reports 2 for the two updates.
Quoting identifiers and values
A placeholder cannot stand for a table name, a column name, a sort direction or an IN list. When those come from a variable, they have to be built into the SQL text, and that is the one place where quoting matters. DBI provides the functions that do it for the connected database, because the quoting rules differ between databases (the output below shows backticks for SQLite).
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# Values: dbQuoteString() and dbQuoteLiteral() return text that is safe to
# paste into SQL for this database.
dbQuoteString(con, "O'Brien")
#> <SQL> 'O''Brien'
dbQuoteString(con, c("fiction", NA))
#> <SQL> 'fiction'
#> <SQL> NULL
dbQuoteLiteral(con, 3L)
#> <SQL> 3
dbQuoteLiteral(con, shelf_today)
#> <SQL> '2026-06-30'
dbQuoteLiteral(con, TRUE)
#> <SQL> 1
dbQuoteLiteral(con, NA_character_)
#> <SQL> NULL
# Names: dbQuoteIdentifier() for table and column names, which a placeholder
# cannot stand for.
dbQuoteIdentifier(con, "my table")
#> <SQL> `my table`
dbQuoteIdentifier(con, "select")
#> <SQL> `select`
table_name <- "books"
sql <- paste("SELECT COUNT(*) AS n FROM", dbQuoteIdentifier(con, table_name))
sql
#> [1] "SELECT COUNT(*) AS n FROM `books`"
dbGetQuery(con, sql)
#> n
#> 1 16
# sqlInterpolate() fills ?name placeholders by quoting each value.
sql <- sqlInterpolate(
con, "SELECT title FROM books WHERE genre = ?genre AND year > ?after",
genre = "Mystery", after = 2016
)
sql
#> <SQL> SELECT title FROM books WHERE genre = 'Mystery' AND year > 2016
is(sql, "SQL")
#> [1] TRUE
dbGetQuery(con, sql)
#> title
#> 1 Salt and Static
# A hostile value stays one string literal.
hostile <- "x'; DROP TABLE books; --"
sqlInterpolate(
con, "SELECT title FROM books WHERE title = ?title",
title = hostile
)
#> <SQL> SELECT title FROM books WHERE title = 'x''; DROP TABLE books; --'
dbGetQuery(
con, "SELECT title FROM books WHERE title = :title",
params = list(title = hostile)
)
#> [1] title
#> <0 rows> (or 0-length row.names)
dbListTables(con)
#> [1] "books"
# The values you supply must match the placeholders.
tryCatch(
sqlInterpolate(
con, "SELECT title FROM books WHERE genre = ?genre",
year = 1
),
error = \(e) conditionMessage(e)
)
#> [1] "Supplied values don't match named vars to interpolate"
# SQL() marks text you already know is safe SQL.
SQL("year > 2000")
#> <SQL> year > 2000
dbDisconnect(con)-
dbQuoteIdentifier(con, name)makes a table or column name safe, including names with spaces or names that are reserved words. Use it whenever a name is not a constant in your source. -
dbQuoteString(con, x)anddbQuoteLiteral(con, x)do the same for a value.dbQuoteLiteral(), added in DBI 0.8, handles many more R types thandbQuoteString(), including dates ('2026-06-30', as DBI 1.1.1 formats them: year-month-day, without a time zone), logicals, numbers and missing values. The doubled quote in'O''Brien'is the SQL way to escape one. -
sqlInterpolate(con, sql, ...)replaces?nameplaceholders in a SQL string with quoted values: use it where the SQL must be built as text, for instance to hand a query to a function that takes a string and has noparamsargument. Its documentation was extended in DBI 1.1.0 and the function has useddbQuoteLiteral()since then. The hostile value in the script,x'; DROP TABLE books; --, becomes one string literal and the table is still there. Supplying a name that is not in the SQL is an error (Supplied values don't match named vars to interpolate). -
SQL()marks text as already being SQL, so that DBI does not quote it a second time. Never use it on text you did not write. -
The glue package’s
glue_sql()does the same quoting for any variable written between braces in the SQL string and appears in the DBI article on advanced usage; glue is not used here.
Quoting is the second line of defence, not the first. A placeholder is simpler to get right than a function that must be called on every variable, and it works for every backend in the same way. Use params for values and dbQuoteIdentifier() for names, and nothing else. The reference page is DBI package documentation: sqlInterpolate, and the one for the value functions is DBI package documentation: dbQuoteString.
Transactions
A transaction groups statements so that they all take effect or none does. Without one, a failure halfway through a series of INSERT statements leaves the database with half of them. DBI offers dbWithTransaction() for the common case, and dbBegin(), dbCommit() and dbRollback() for control by hand (DBI package documentation: dbWithTransaction, DBI package documentation: transactions). The database does the work; what a transaction guarantees is explained in the SQL transactions page.
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE members (name TEXT NOT NULL UNIQUE)")
#> [1] 0
count_members <- function() {
dbGetQuery(con, "SELECT COUNT(*) AS n FROM members")$n
}
# dbWithTransaction() commits when the code finishes, and returns its value.
dbWithTransaction(con, {
dbExecute(con, "INSERT INTO members (name) VALUES ('Ana Ruiz')")
dbExecute(con, "INSERT INTO members (name) VALUES ('Ben Okafor')")
"committed"
})
#> [1] "committed"
count_members()
#> [1] 2
# An error rolls the whole block back and is passed on.
tryCatch(
dbWithTransaction(con, {
dbExecute(con, "INSERT INTO members (name) VALUES ('Dev Patel')")
dbExecute(con, "INSERT INTO members (name) VALUES ('Ana Ruiz')")
}),
error = \(e) conditionMessage(e)
)
#> [1] "UNIQUE constraint failed: members.name"
count_members()
#> [1] 2
# dbBreak() leaves the block and rolls back without an error.
dbWithTransaction(con, {
dbExecute(con, "INSERT INTO members (name) VALUES ('Eva Lindqvist')")
dbBreak()
})
count_members()
#> [1] 2
# The same steps by hand: dbBegin(), then dbCommit() or dbRollback().
dbBegin(con)
dbExecute(con, "DELETE FROM members") # two rows deleted ...
#> [1] 2
count_members() # ... and gone, inside the transaction
#> [1] 0
dbRollback(con)
count_members()
#> [1] 2
# Transactions do not nest, and there is nothing to commit outside one.
tryCatch(
dbWithTransaction(con, dbWithTransaction(con, 1)),
error = \(e) conditionMessage(e)
)
#> [1] "cannot start a transaction within a transaction"
tryCatch(dbCommit(con), error = \(e) conditionMessage(e))
#> [1] "cannot commit - no transaction is active"
dbDisconnect(con)-
dbWithTransaction(con, code)starts a transaction, evaluatescode, commits if it finishes, and returns the value of the code ("committed"). Ifcodesignals an error, it rolls back and re-throws it, so the failed insert of a duplicate name left the table unchanged (two members) and the error message was still delivered totryCatch(). DBI 1.3.0 (2026-02-11) made it roll back also when the code is interrupted. -
dbBreak()inside the block stops it and rolls back without an error;dbWithTransaction()then returnsNULLinvisibly, which is why the call printed nothing. -
By hand,
dbBegin(), the statements, and thendbCommit()to keep them ordbRollback()to discard them. TheDELETEinside the transaction is visible to the connection that made it (the count was 0) and gone after the rollback (2 again). Other connections do not see the deletion until it is committed. -
Transactions do not nest in SQLite, and the messages from the database say so:
cannot start a transaction within a transaction, andcannot commit - no transaction is activewhen there is nothing to commit. A function that opens a transaction cannot call another that opens one; decide which of the two owns it. -
Outside a transaction a database normally commits each statement by itself (autocommit), so statements issued without
dbBegin()take effect one by one.
A rollback restores the database; it does not undo what the R code did meanwhile. A script that wrote a file or sent a message in the middle of the transaction cannot take that back.
Errors and warnings
An error from the database arrives as an ordinary R error whose message is the database’s own text. The condition has class simpleError and no call (conditionCall() is NULL), so there is nothing to parse except the message, and tryCatch(..., error = \(e) conditionMessage(e)) is how the examples capture it. The text depends on the backend: the messages below are SQLite’s, and PostgreSQL or MariaDB word the same mistakes differently (code that reacts to a particular failure has to test the text of the backend it uses). The script collects the messages for the typical mistakes of a first week with SQL.
source("scenario.R")
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# The text comes from SQLite itself; the condition is a plain R error.
e <- tryCatch(dbGetQuery(con, "SELECT * FROM shelves"), error = \(e) e)
class(e)
#> [1] "simpleError" "error" "condition"
conditionMessage(e)
#> [1] "no such table: shelves"
is.null(conditionCall(e))
#> [1] TRUE
queries <- c(
"SELECT * FROM shelves",
"SELECT nope FROM books",
"SELECT FROM books",
"SELEC title FROM books",
"SELECT title FROM books WHERE",
"SELECT 'unclosed",
"INSERT INTO books (title) VALUES (1, 2)"
)
messages <- vapply(queries, \(q) {
tryCatch(
{
dbGetQuery(con, q)
"no error"
},
error = \(e) conditionMessage(e)
)
}, character(1))
data.frame(query = queries, message = unname(messages))
#> query message
#> 1 SELECT * FROM shelves no such table: shelves
#> 2 SELECT nope FROM books no such column: nope
#> 3 SELECT FROM books near "FROM": syntax error
#> 4 SELEC title FROM books near "SELEC": syntax error
#> 5 SELECT title FROM books WHERE incomplete input
#> 6 SELECT 'unclosed unrecognized token: "'unclosed"
#> 7 INSERT INTO books (title) VALUES (1, 2) 2 values for 1 columns
# An unhandled error prints like any R error.
result <- try(dbGetQuery(con, "SELECT nope FROM books"), silent = TRUE)
cat(result)
#> Error : no such column: nope
# A second query while a result is still open: a warning, then it proceeds.
open_result <- dbSendQuery(con, "SELECT * FROM books")
seen <- character()
withCallingHandlers(
dbGetQuery(con, "SELECT 1 AS one"),
warning = \(w) {
seen <<- c(seen, conditionMessage(w))
invokeRestart("muffleWarning")
}
)
#> one
#> 1 1
seen
#> [1] "Closing open result set, pending rows"
dbIsValid(open_result)
#> [1] FALSE
dbDisconnect(con)| Message | What it means |
|---|---|
|
A name that is wrong or not yet created. Check with |
|
SQLite found a word it did not expect, and it names the word. The mistake is at or just before it. |
|
The statement stopped in the middle, often a trailing |
|
A quoted string that is never closed. When the SQL was built by pasting a value that contains a quotation mark, this is what you see. |
|
A mismatch between the columns named and the values given in an |
|
A constraint of the table rejected the row (see the section on bulk inserts). |
|
A write on a connection opened with |
|
The connection was disconnected (see the section on connecting). |
The warnings come from the result handling, not from SQL. Closing open result set, pending rows is what RSQLite says when a second query is sent on a connection while an earlier result still has rows to fetch: the old result is closed (the script shows dbIsValid(open_result) turning FALSE). There are 1 result in use. The connection will be released when they are closed is the warning of dbDisconnect(); and Ignoring remaining part of query: ... is the one about a second statement in the same string. All three are bugs in the calling code, and the cure is the same: one result at a time per connection, and every result cleared (on.exit(dbClearResult(res))). Turn warnings into errors during testing with options(warn = 2) if you want them to stop the script (see Conditions and Error Handling).
dbplyr: dplyr verbs as SQL
dbplyr is the database backend for dplyr: it lets a remote table be used as if it were an in-memory data frame, by converting the dplyr code into SQL (dbplyr package documentation: dbplyr). It sits on top of DBI, as the diagram shows: dbplyr writes the SQL and calls the DBI generics to send it, so any DBI backend with a dbplyr dialect works, and the same dplyr verbs (The tidyverse and dplyr) apply to a database table and to a data frame. You do not need to call library(dbplyr): dplyr loads it when it sees a database table. The next script attaches dplyr the ordinary way, so the masking message is part of the output: dplyr’s filter() and lag() mask the ones in the stats package, and intersect(), setdiff(), setequal() and union() mask those of base. Scripts that do not want the message use suppressPackageStartupMessages(library(dplyr)) or library(dplyr, warn.conflicts = FALSE).
source("scenario.R")
library(DBI)
library(dplyr)
#> Attaching package: ‘dplyr’
#>
#> The following objects are masked from ‘package:stats’:
#>
#> filter, lag
#>
#> The following objects are masked from ‘package:base’:
#>
#> intersect, setdiff, setequal, union
# Store the dates as ISO text so SQL date functions can read them.
loans_text <- loans
loans_text$loan_date <- format(loans$loan_date)
loans_text$return_date <- format(loans$return_date)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
dbWriteTable(con, "loans", loans_text, row.names = FALSE)
# tbl() makes a lazy reference to a table: no rows are read into R.
loans_db <- tbl(con, "loans")
class(loans_db)
#> [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
#> [4] "tbl_lazy" "tbl"
colnames(loans_db)
#> [1] "loan_id" "isbn" "member" "loan_date" "return_date"
nrow(loans_db)
#> [1] NA
loans_db
#> # A query: ?? x 5
#> # Database: sqlite 3.53.3 [:memory:]
#> loan_id isbn member loan_date return_date
#> <chr> <chr> <chr> <chr> <chr>
#> 1 L001 9780141439518 "Hugo Silva" 2025-01-14 2025-02-04
#> 2 L002 9780306406157 "Ben Okafor" 2025-01-16 2025-01-26
#> 3 L003 9780002000291 "Dev Patel" 2025-01-21 2025-01-22
#> 4 L004 9780005000571 "Eva Lindqvist" 2025-01-26 2025-02-21
#> 5 L005 978030640615 "Ben Okafor" 2025-02-19 2025-03-17
#> 6 L006 9780004000404 "Hugo Silva" 2025-02-28 2025-03-17
#> 7 L007 9780005000571 "Grace Liu" 2025-03-11 2025-04-04
#> 8 L008 9780001000100 "ana ruiz " 2025-03-27 2025-04-06
#> 9 L009 9780001000100 "Hugo Silva" 2025-03-30 2025-04-30
#> 10 L010 9780306406157 "Hugo Silva" 2025-04-06 2025-04-27
#> # ℹ more rows
# Verbs only build a query; nothing is sent until you look at the result.
per_member <- loans_db |>
filter(!is.na(return_date)) |>
group_by(member) |>
summarise(returned = n()) |>
arrange(desc(returned), member)
class(per_member)
#> [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
#> [4] "tbl_lazy" "tbl"
show_query(per_member)
#> <SQL>
#> SELECT `member`, COUNT(*) AS `returned`
#> FROM `loans`
#> WHERE (NOT((`return_date` IS NULL)))
#> GROUP BY `member`
#> ORDER BY `returned` DESC, `member`
# collect() runs the query and brings the result into R as a tibble.
result <- collect(per_member)
class(result)
#> [1] "tbl_df" "tbl" "data.frame"
head(result, 3)
#> # A tibble: 3 × 2
#> member returned
#> <chr> <int>
#> 1 Hugo Silva 9
#> 2 Ana Ruiz 8
#> 3 Chloe Martin 8
# A lazy table is not a data frame.
tryCatch(loans_db$member, error = \(e) conditionMessage(e))
#> [1] "The `$` method of <tbl_lazy> is for internal use only.\nℹ Use `dplyr::pull()` to get the values in a column."
loans_db |> distinct(member) |> arrange(member) |> head(3) |> pull(member)
#> [1] "" "Ana Ruiz" "Ben Okafor"
# Functions dbplyr does not know are sent to the database unchanged ...
loans_db |>
filter(!is.na(return_date)) |>
mutate(held = julianday(return_date) - julianday(loan_date)) |>
filter(held > loan_days) |>
summarise(overdue = n()) |>
show_query()
#> <SQL>
#> SELECT COUNT(*) AS `overdue`
#> FROM (
#> SELECT *, julianday(`return_date`) - julianday(`loan_date`) AS `held`
#> FROM `loans`
#> WHERE (NOT((`return_date` IS NULL)))
#> ) AS `q01`
#> WHERE (`held` > 21)
# ... so a function the database lacks fails when the query runs.
books_db <- tbl(con, "books")
tryCatch(
books_db |> filter(grepl("moon", title)) |> collect(),
error = \(e) conditionMessage(e)
)
#> [1] "Failed to collect lazy table.\nCaused by error:\n! no such function: grepl"
# SQL aggregates drop NULLs; dbplyr says so once per session unless you
# pass na.rm = TRUE.
captured <- character()
mean_pages <- withCallingHandlers(
books_db |> summarise(mean_pages = mean(pages)) |> collect(),
warning = \(w) {
captured <<- c(captured, conditionMessage(w))
invokeRestart("muffleWarning")
}
)
cat(captured, sep = "\n")
#> Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
mean_pages
#> # A tibble: 1 × 1
#> mean_pages
#> <dbl>
#> 1 268.
books_db |> summarise(mean_pages = mean(pages, na.rm = TRUE)) |> collect()
#> # A tibble: 1 × 1
#> mean_pages
#> <dbl>
#> 1 268.
# A value from R is escaped and written into the SQL text.
hostile <- "x'; DROP TABLE books; --"
books_db |> filter(genre == hostile) |> show_query()
#> <SQL>
#> SELECT *
#> FROM `books`
#> WHERE (`genre` = 'x''; DROP TABLE books; --')
# Any SQL can be the source of a lazy table.
tbl(con, sql("SELECT genre, COUNT(*) AS n FROM books GROUP BY genre")) |>
filter(n > 2) |>
collect()
#> # A tibble: 1 × 2
#> genre n
#> <chr> <int>
#> 1 Non-fiction 3
dbDisconnect(con)The script shows the five things that make dbplyr different from working with a data frame.
-
The table is lazy.
tbl(con, "loans")has a class that starts withtbl_SQLiteConnection; it knows the column names (colnames()), but not the number of rows (nrow()isNA), because no row has been read. Printing it sends a query that fetches the first ten rows and says so:# A query: ?? x 5(the number of rows is unknown) and# Database: sqlite 3.53.3 [:memory:]. -
Verbs build a query; nothing runs.
filter(),group_by(),summarise()andarrange()each add a layer to the lazy query, whose class is stilltbl_SQLiteConnection.show_query()prints the SQL it would send: the!is.na(return_date)becameNOTreturn_date IS NULL,n()becameCOUNT(*), and thedesc()becameDESC(dbplyr package documentation: show_query). -
collect()runs it. The result is an ordinary tibble in R, with the classtbl_df,tbl,data.frame; everything aftercollect()is local R (dbplyr package documentation: collect). Do the filtering, joining and aggregating beforecollect(), and the summarising or plotting after it. A lazy table is not a data frame:loans_db$memberis an error (The $ method of <tbl_lazy> is for internal use only) that points todplyr::pull(), which runs the query and returns one column as a vector. -
Functions are translated, or passed through. dbplyr translates a fixed set of R functions into the SQL of the connection (for SQLite,
n(),is.na(), the arithmetic and comparison operators and many more). A function it does not know is sent unchanged, which is whyjulianday(), an SQLite function, works inmutate(). The same rule makesgrepl()fail, because SQLite has no such function: the error arrives when the query runs, wrapped by dbplyr’sFailed to collect lazy table.and ending with the database’s own words. Which R functions are translated is in the translation articles (dbplyr package documentation: Function translation). A variable from R, likeloan_daysin the script, is evaluated in R and its value is put in the SQL:held > 21. -
SQL aggregates drop
NULL. SQL’sAVGskips missing values whatever you ask. dbplyr says so once, as a warning (Missing values are always removed in SQL aggregation functions.), and stops when you passna.rm = TRUE. The warning is shown once every eight hours, which is why it was captured in the script instead of being left to appear.
The last line of the script is the bridge between the two worlds: tbl(con, sql("SELECT ...")) takes any SQL as the source of a lazy table, and the verbs that follow are added on top of it. Use it for a query that is easier to write in SQL than in dplyr, or one that dbplyr cannot express (dbplyr package documentation: sql). The reverse is also true. show_query(), or in dbplyr 2.6.0 last_sql() (which returns the most recent query dbplyr generated), gives the SQL of a pipeline to read or to paste into a SQL client.
Which of DBI and dbplyr to use is a matter of the task and of the reader:
| Situation | DBI (SQL text) | dbplyr (dplyr verbs) |
|---|---|---|
The analysis is already written in SQL, or uses vendor features dbplyr has no translation for |
|
|
The question is a filter, a join and a summary |
Works; the SQL is yours to write and maintain |
Shorter, reads as the rest of the tidyverse code, checked with |
Writes: creating tables, inserts, updates, transactions |
|
Not its job (dbplyr has some row-update verbs; see its documentation) |
Values from the user |
|
A local variable is escaped and written into the SQL text (the script’s |
Same code on several databases |
SQL dialects differ |
The translation targets each backend’s dialect |
The overdue analysis, twice
The shelf scenario’s central question is which loans were overdue: a returned loan is overdue when it was held for more than loan_days (21) days. The scenario’s reference result is that 25 of the returned loans exceed 21 days. The next script puts books and loans in a database with date columns, answers the question by genre in SQL, answers it again with dbplyr verbs, checks that the two are the same, and checks both against base R on the data frames that never left R.
Two details of the data matter. The isbn of loans is 13 bare digits, and the isbn of books is sometimes hyphenated, so the join compares REPLACE(b.isbn, '-', '') with l.isbn. And the genres of books have nine spellings of six genres, so the grouping key is LOWER(TRIM(genre)). Both cleanings are done in the query, the way the data frame code of Data Frames does them in R. The tables are created with DATE columns and the dates are appended as ISO text, so SQLite’s julianday() can compute with them and, with extended_types = TRUE, R still receives Date columns.
source("scenario.R")
library(DBI)
library(dplyr)
#> Attaching package: ‘dplyr’
#>
#> The following objects are masked from ‘package:stats’:
#>
#> filter, lag
#>
#> The following objects are masked from ‘package:base’:
#>
#> intersect, setdiff, setequal, union
# Dates are stored as ISO text in DATE columns; extended_types = TRUE hands
# them back to R as Date, and SQL date functions still work on them.
con <- dbConnect(RSQLite::SQLite(), ":memory:", extended_types = TRUE)
dbExecute(con, "
CREATE TABLE books (
title TEXT, author TEXT, isbn TEXT,
year INTEGER, genre TEXT, pages INTEGER
)
")
#> [1] 0
dbAppendTable(con, "books", books)
#> [1] 16
loans_text <- loans
loans_text$loan_date <- format(loans$loan_date)
loans_text$return_date <- format(loans$return_date)
dbExecute(con, "
CREATE TABLE loans (
loan_id TEXT PRIMARY KEY, isbn TEXT, member TEXT,
loan_date DATE, return_date DATE
)
")
#> [1] 0
dbAppendTable(con, "loans", loans_text)
#> [1] 60
str(dbReadTable(con, "loans")[c("loan_date", "return_date")])
#> 'data.frame': 60 obs. of 2 variables:
#> $ loan_date : Date, format: "2025-01-14" "2025-01-16" ...
#> $ return_date: Date, format: "2025-02-04" "2025-01-26" ...
# 1. The question in SQL. The loan period is a parameter, not text.
overdue_sql <- "
SELECT COALESCE(LOWER(TRIM(b.genre)), 'unknown') AS genre,
COUNT(*) AS returned,
SUM(julianday(l.return_date) - julianday(l.loan_date) > ?) AS overdue
FROM loans AS l
LEFT JOIN books AS b ON REPLACE(b.isbn, '-', '') = l.isbn
WHERE l.return_date IS NOT NULL
GROUP BY COALESCE(LOWER(TRIM(b.genre)), 'unknown')
ORDER BY overdue DESC, genre
"
sql_result <- dbGetQuery(con, overdue_sql, params = list(loan_days))
sql_result
#> genre returned overdue
#> 1 fiction 19 8
#> 2 science fiction 13 5
#> 3 classic 6 3
#> 4 non-fiction 5 3
#> 5 poetry 5 3
#> 6 mystery 4 2
#> 7 unknown 1 1
colSums(sql_result[c("returned", "overdue")])
#> returned overdue
#> 53 25
# 2. The same question with dbplyr verbs.
books_key <- tbl(con, "books") |>
transmute(
isbn_key = sql("REPLACE(isbn, '-', '')"),
genre = coalesce(tolower(trimws(genre)), "unknown")
)
overdue_db <- tbl(con, "loans") |>
filter(!is.na(return_date)) |>
left_join(books_key, by = c("isbn" = "isbn_key")) |>
mutate(
genre = coalesce(genre, "unknown"),
held = julianday(return_date) - julianday(loan_date)
) |>
group_by(genre) |>
summarise(returned = n(), overdue = sum(held > loan_days, na.rm = TRUE)) |>
arrange(desc(overdue), genre)
db_result <- collect(overdue_db)
as.data.frame(db_result)
#> genre returned overdue
#> 1 fiction 19 8
#> 2 science fiction 13 5
#> 3 classic 6 3
#> 4 non-fiction 5 3
#> 5 poetry 5 3
#> 6 mystery 4 2
#> 7 unknown 1 1
isTRUE(all.equal(sql_result, as.data.frame(db_result)))
#> [1] TRUE
# 3. A check in base R, on the data frames that never left R.
books_clean <- data.frame(
isbn = gsub("-", "", books$isbn),
genre = tolower(trimws(books$genre))
)
returned <- merge(
loans[!is.na(loans$return_date), ], books_clean,
by = "isbn", all.x = TRUE
)
returned$genre[is.na(returned$genre)] <- "unknown"
returned$overdue <- as.integer(returned$return_date - returned$loan_date) >
loan_days
c(returned = nrow(returned), overdue = sum(returned$overdue, na.rm = TRUE))
#> returned overdue
#> 53 25
by_genre <- tapply(returned$overdue, returned$genre, sum, na.rm = TRUE)
all(by_genre[sql_result$genre] == sql_result$overdue)
#> [1] TRUE
# Which loan has no matching book? The short ISBN of loan L005.
dbGetQuery(con, "
SELECT l.loan_id, l.isbn
FROM loans AS l
LEFT JOIN books AS b ON REPLACE(b.isbn, '-', '') = l.isbn
WHERE b.isbn IS NULL
")
#> loan_id isbn
#> 1 L005 978030640615
dbDisconnect(con)The three results agree. The query keeps the loan period out of the SQL text (> ? with params = list(loan_days)), so the number lives in one place, in the scenario, in R. Of 53 returned loans, 25 are overdue: 8 fiction, 5 science fiction, and 3 each for classic, non-fiction and poetry, 2 for mystery, and 1 with an unknown genre. That last row is the lesson in the join. The loan L005 has a one-digit-short ISBN (978030640615), so no book matches it. An inner JOIN would have dropped the loan, and the total would have been 24 and not 25, with no error to say why. The LEFT JOIN keeps it and gives it the genre unknown, which makes the data problem visible. The last query of the script finds exactly that loan. The scenario’s other deliberate defects behave as they do in R: the loan with the impossible date 2025-02-30 has NULL dates, julianday() of NULL is NULL, the comparison is NULL, and SUM ignores it, so it counts as returned and not overdue; the loan returned before it was borrowed has a negative duration and is not overdue; the 7 unreturned loans are excluded by the WHERE (the question here is about returned loans; for the unreturned, shelf_today takes the place of the return date).
The dbplyr version builds the cleaning step as a lazy table (books_key), joins the loans to it, and ends in the same GROUP BY. sql("REPLACE(isbn, '-', '')") is a SQL fragment that dbplyr does not translate, and everything else is verbs. show_query() on overdue_db would show the nested query it generated.
Other backends in short
-
PostgreSQL (RPostgres): a server database that several people and programs share. The settings come from the environment (
PGHOSTand the others, or~/.pgpass), parameters can be written$1,$2, anddbConnect()hastimezoneandtimezone_outarguments for time zones (RPostgres package documentation: Postgres). -
MariaDB and MySQL (RMariaDB):
dbConnect()takesdbname,username,passwordandhost, and can read a MariaDB option file. Its CRAN description says it implements a DBI-compliant interface to MariaDB and MySQL, and the legacy RMySQL package points to it for a modern MySQL client (RMariaDB package documentation: dbConnect). -
ODBC (odbc): one package for every database with an ODBC driver, from a data source name or a driver and its settings. It is a DBI backend, so everything on this page applies (odbc package documentation: dbConnect).
-
DuckDB (duckdb): an embedded analytical database management system; like SQLite it needs no server.
dbConnect(duckdb::duckdb())keeps the data in RAM, anddbdirnames a file (duckdb package documentation: duckdb). -
Others, such as bigrquery, are listed on the DBI site.
Two newer parts of DBI are named here and not used, because they need packages that are not installed. DBI 1.2.0 (2023-12-20) added a family of Arrow generics (dbGetQueryArrow(), dbSendQueryArrow(), dbFetchArrow(), dbReadTableArrow(), dbWriteTableArrow() and others) with default implementations built on nanoarrow, and the DBI documentation labels these functions experimental (DBI package documentation: dbGetQueryArrow). DBI 1.3.0 added support for OpenTelemetry through the otel and otelsdk packages. For an Arrow-based workflow, the packages that talk Arrow directly are described at https://arrow.apache.org/docs/r/ (the arrow package is not installed here and is not run).
Pitfalls
The R FAQ has no entry about databases (a search of the FAQ finds none), so these come from the DBI and RSQLite documentation and from the examples above.
-
Building SQL by pasting values. Use
params =,dbQuoteIdentifier()for names andsqlInterpolate()where text is unavoidable. See the section on older tutorials for the output of a pasted query. -
Forgetting to disconnect, or to clear a result. Close in
on.exit(); clear inon.exit(). Both produce a warning in RSQLite and a held server session elsewhere. -
A vector in
paramsis a loop, not a list of values. Use one placeholder per value forIN. -
Assuming the types survive. Logical columns return as integers, factors as text and dates as numbers unless
extended_types = TRUEand the column is declaredDATE. -
Assuming the order. A table has no order; add
ORDER BYwhen the order matters, including forLIMITandn =. -
overwrite = TRUEon the wrong table. It replaces whatever is there. -
Comparing text with the wrong case or blanks.
'Fiction','fiction'and'Fiction 'are three different values to SQL. Clean at the source or in the query (LOWER(TRIM(x))). -
Pulling everything and then filtering in R.
dbReadTable()followed by a filter reads the whole table. Filter in the query. -
NAandNULLin comparisons.x = NULLis never true in SQL; useIS NULL. In dbplyr,is.na()becomesIS NULL. -
Secrets in the script. Environment variables,
.Renviron, askpass or keyring; never a literal. -
Saving a connection. A connection that went through
saveRDS()andreadRDS()is not valid (dbIsValid()isFALSE, and using it fails with the unhelpfulbad_weak_ptr); open a new one in each session (see Serialization: RDS, RData and JSON). -
Sharing one connection between processes. A worker of a parallel computation should open its own connection (see Parallel and Asynchronous Computing).
Version notes
The entries come from the NEWS files of the packages and from the R NEWS; each package’s NEWS lists its own dates.
| Version | Change |
|---|---|
DBI 0.8 (2018-02-24) |
Schema support: |
DBI 1.0.0 (2018-05-02) |
New generics |
DBI 1.1.0 (2019-12-15) |
|
DBI 1.1.1 (2021-01-04) |
|
DBI 1.2.0 (2023-12-20) |
Arrow generics ( |
DBI 1.3.0 (2026-02-11) |
OpenTelemetry support; |
RSQLite 1.1 (2016-11-25) |
Rewritten in C++ with Rcpp; new prepared-query strategy with |
RSQLite 2.0 (2017-06-18) |
64-bit integers returned as |
RSQLite 2.1.1 (2018-05-05) |
|
RSQLite 2.2.3 and 2.2.4 (2021) |
|
RSQLite 3.52.0 (2026-05-09) |
The version number is aligned with the bundled SQLite version; |
RSQLite 3.53.2 (2026-06-14) |
Optional HTTP/HTTPS virtual file system for read-only remote databases ( |
RSQLite 3.53.3 (2026-06-28) |
Bundled SQLite 3.53.3. The version checked on this page. |
dbplyr 2.5.0 (2024-03-19) |
Simpler qualified table names: |
dbplyr 2.6.0 |
First-edition backends no longer work; |
R 4.3.0 |
|
R 4.6.0 |
|
Source: DBI package documentation: NEWS
The dates of the RSQLite and DBI releases are those printed in their NEWS pages (RSQLite package documentation: NEWS, dbplyr package documentation: NEWS); the dbplyr 2.6.0 announcement is on the Posit open source blog (blog material, dbplyr 2.6.0 announcement). The R changes are in R NEWS: Changes in R 4.3.0 and R NEWS: Changes in R 4.6.0. R-devel (the upcoming 4.7.0) is not relevant to this page; DBI and RSQLite release independently of R.
Where older tutorials differ
The R Data Import/Export manual’s chapter on relational databases is the official text, and parts of it describe the state of DBI when it was written. Its example uses the RMySQL package, dbDriver("MySQL"), a query built with paste(), and fetch(); it maps data frame row names to a row_names field; and it describes the DBI front-end as a proposal. The RMySQL package’s own CRAN description now calls it a legacy DBI interface and points to RMariaDB for a modern MySQL client. Many blog posts and answers follow the manual, so the patterns recur. The script below shows the one that matters most with real output, and it is the contrast the rest of the page is built on.
A pasted query, and the same function with a parameter
source("scenario.R")
library(DBI)
# The R Data Import/Export manual still shows a driver chosen by name. In a
# session where the backend package has not been loaded, that fails ...
tryCatch(dbDriver("SQLite"), error = \(e) conditionMessage(e))
#> [1] "Couldn't find driver SQLite. Looked in:\n* global namespace\n* in package called SQLite\n* in package called RSQLite"
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "books", books, row.names = FALSE)
# An older tutorial pastes the user's text into the SQL. Do not copy this.
find_by_genre_unsafe <- function(con, genre) {
sql <- paste0("SELECT title FROM books WHERE genre = '", genre, "'")
dbGetQuery(con, sql)
}
find_by_genre_unsafe(con, "Poetry")
#> title
#> 1 Garden of Small Fires
#> 2 Paper Moons
# A name with an apostrophe breaks the query ...
tryCatch(
find_by_genre_unsafe(con, "O'Brien"),
error = \(e) conditionMessage(e)
)
#> [1] "near \"Brien\": syntax error"
# ... and a crafted value changes what the query means: every book comes back.
nrow(find_by_genre_unsafe(con, "x' OR '1'='1"))
#> [1] 16
# RSQLite runs only the first statement of a string, which stops a stacked
# DROP TABLE here; that is a property of this backend, not a defence.
tryCatch(
find_by_genre_unsafe(con, "x'; DROP TABLE books; --"),
warning = \(w) conditionMessage(w)
)
#> [1] "Ignoring remaining part of query: DROP TABLE books; --'"
dbListTables(con)
#> [1] "books"
# The same function with a parameter: the value is never part of the SQL.
find_by_genre <- function(con, genre) {
dbGetQuery(
con, "SELECT title FROM books WHERE genre = ?",
params = list(genre)
)
}
find_by_genre(con, "Poetry")
#> title
#> 1 Garden of Small Fires
#> 2 Paper Moons
nrow(find_by_genre(con, "x' OR '1'='1"))
#> [1] 0
nrow(find_by_genre(con, "O'Brien"))
#> [1] 0
# ... and works once RSQLite is loaded, as the line above has just done.
dbDriver("SQLite")
#> <SQLiteDriver>
# fetch() is the pre-DBI name of dbFetch().
res <- dbSendQuery(con, "SELECT title FROM books ORDER BY title")
fetch(res, n = 2)
#> title
#> 1 the quiet streets
#> 2 A Map of Salt
dbClearResult(res)
dbDisconnect(con)The first function builds the SQL by pasting genre between quotation marks, which is what the manual’s paste("select row_names, Murder from arrests", "where Rape > 30 ...") pattern becomes when one value is variable. It works for "Poetry". A value with an apostrophe breaks it (near "Brien": syntax error), which is the harmless way this fails. A value that closes the string and continues the logic (x' OR '1'='1) makes the WHERE true for every row: all 16 books came back for a genre that does not exist. That is SQL injection: the person who supplies the value rewrote the query. RSQLite stopped the third attempt, the stacked DROP TABLE, only because it runs a single statement per call and warned about the rest (Ignoring remaining part of query: ...); that is not protection, and a backend that executes every statement in the string would not have stopped it. The rewritten function with params = list(genre) returns the two poetry books for "Poetry" and nothing for the other two values, because they are only text to compare with. sprintf() has the same problem as paste() when the format has a %s; sprintf("... %d", n) with a number is safe only because %d will not accept text.
The last lines of the script are two other habits from the manual’s era. dbDriver("SQLite") chooses the driver by name; it fails, in a session where RSQLite has not been loaded, with Couldn't find driver SQLite, followed by the places it looked, and works after the package is loaded. fetch() is the name the older DBI used for what is now dbFetch(); RSQLite still exports it.
Other things that changed
| Older tutorial writes | Current practice | Why | Since |
|---|---|---|---|
|
|
A parameter is data and never SQL; see the output above. |
RSQLite 1.1 (2016) |
|
|
RMySQL’s CRAN description calls it a legacy DBI interface and points to RMariaDB; RPostgres describes itself as a fully DBI-compliant C++-backed interface. The older packages are still on CRAN. |
n/a |
|
|
RSQLite no longer attaches DBI when loaded. |
RSQLite 1.1 (2016) |
|
|
They return the number of rows affected and release the result. |
DBI 0.5 |
|
|
|
n/a |
|
|
RSQLite’s NEWS calls the old functions never part of the official API, reimplemented with a deprecation warning. |
RSQLite 1.1 (2016) |
|
|
DBI leaves row names out by default (see the output in the section on tables). |
n/a |
|
|
R accepts a numeric argument; RSQLite reads declared |
R 4.3.0; RSQLite 2.2.x |
|
|
RODBC has its own function set; odbc is a DBI backend, so it works with dbplyr and the code on this page. RODBC, described in the manual, is still on CRAN. |
odbc package |
|
A DBI connection ( |
sqldf creates a database behind the scenes, copies the data frames into it, runs the query and returns the result; DBI shows each step. sqldf is still on CRAN. |
n/a |
|
|
dbplyr 2.6.0 deprecates |
dbplyr 2.6.0 |
A password written in the script |
|
A literal ends up in version control. |
always |
|
|
There is no method for RSQLite in 3.53.3; the connection is closed by the code that opened it. |
always |
Where to go next
-
SQL Reference for the SQL itself, and its transactions page for what
dbWithTransaction()relies on. -
Data Frames and The tidyverse and dplyr for the data frames that go in and come out and the verbs dbplyr translates.
-
Conditions and Error Handling and Defensive Programming and Input Validation for handling failures and validating the values that reach a query.
-
S4 Classes and Methods for the dispatch DBI is built on.
-
Connections and the File System for the other meaning of "connection" in R (
file(),url()), which is unrelated to DBI. -
Scripts and the Command Line for running a database script with
Rscriptand passing it the environment.
References
-
R Data Import/Export: 4 Relational databases; 4.1 Why use a database?, 4.2 Overview of RDBMSs, 4.2.1 SQL queries, 4.3 R interface packages, 4.3.1 Packages using DBI, 4.3.2 Package RODBC
-
R NEWS: Changes in R 4.3.0 and 4.6.0
-
DBI package documentation: DBI; Introduction to DBI; Advanced DBI usage; DBI specification; NEWS
-
DBI reference pages: dbConnect, dbDisconnect, dbWriteTable, dbCreateTable, dbListObjects, Id, dbDataType, dbGetQuery, dbExecute, dbSendQuery, dbSendStatement, dbFetch, dbHasCompleted, dbClearResult, dbBind, dbQuoteIdentifier, dbQuoteString, sqlInterpolate, dbWithTransaction, dbBegin, dbCommit and dbRollback, dbGetQueryArrow
-
RPostgres package documentation: Postgres; RMariaDB package documentation: dbConnect; odbc package documentation: dbConnect; duckdb package documentation: duckdb
-
dbplyr package documentation: dbplyr; tbl; show_query; collect; sql; Introduction to dbplyr; Function translation; NEWS