Database Interface with R's DBI Package: A Guide

What DBI is and why it exists

The DBI package is the universal seam between R and relational databases. The name stands for “DataBase Interface,” and the design borrows from Perl’s DBI, Java’s JDBC, and Python’s DB-API. DBI defines a set of generic verbs; pluggable backends (RSQLite, RPostgres, RMariaDB, odbc, bigrquery) implement them. You write code against DBI’s verbs and swap the backend by changing a single driver call.

This split matters because the verbs you learn once: connect, query, read, write, execute, transaction, work the same way across SQLite on your laptop, PostgreSQL in production, and a remote BigQuery warehouse. A query that runs in 200 ms on RSQLite runs the same on RPostgres. You only change the dbConnect() driver.

DBI is a front-end specification. It does not contain a database engine. You always install it alongside a backend.

TL;DR

The DBI R package is a thin specification that lets R talk to any SQL database through the same handful of functions. Learn these six and you cover most database work in R:

• dbConnect() and dbDisconnect() for the connection lifecycle.
• dbGetQuery() for SELECT statements; dbExecute() for INSERT, UPDATE, DELETE, and DDL.
• dbReadTable() and dbWriteTable() for moving whole data.frame objects in and out.
• dbWithTransaction() plus ? parameter placeholders (or dbQuoteLiteral() / dbQuoteIdentifier()) for the parts that go wrong in production: atomicity and SQL injection.

The rest of the article walks through each function, shows the gotchas the official docs flag, and ends with a complete worked example.

How do you install DBI and pick a backend?

Install DBI and at least one backend from CRAN. SQLite is the easiest starting point because it has no external server.

install.packages(c("DBI", "RSQLite"))

In your script or session, load the backend before DBI. The official docs flag this as a common foot-gun: the convenience idiom dbListTables(con <- dbConnect(RSQLite::SQLite(), ":memory:")) fails in subtle ways when library(RSQLite) has not been called, because the driver constructor is registered on package load.

library(RSQLite)   # registers the SQLite driver
library(DBI)        # the generic verbs

Once the backend is loaded, the driver is available as RSQLite::SQLite(). For other engines the pattern is the same: RPostgres::Postgres(), RMariaDB::MariaDB(), odbc::odbc(), bigrquery::bigquery().

How do you connect to and disconnect from a database?

dbConnect() opens a connection and returns an S4 object of class DBIConnection. The arguments after the driver depend on the backend. SQLite takes a file path or ":memory:", while PostgreSQL takes host, port, dbname, user, and password.

con <- dbConnect(RSQLite::SQLite(), ":memory:")

For a server-backed engine, wrap the call in dbCanConnect() first to test reachability without holding a handle. This validates connection parameters and lets you bail out cleanly if the server is unreachable or credentials are wrong, without leaking half-open sockets in your R session.

if (dbCanConnect(RPostgres::Postgres(),
                 dbname = "mydb",
                 host = "localhost",
                 port = 5432,
                 user = "me",
                 password = Sys.getenv("DB_PASS"))) {
  con <- dbConnect(RPostgres::Postgres(),
                   dbname = "mydb",
                   host = "localhost",
                   port = 5432,
                   user = "me",
                   password = Sys.getenv("DB_PASS"))
} else {
  stop("Database unreachable")
}

dbDisconnect() closes the connection, releases sockets, and rolls back any open transaction. Always pair dbConnect() with dbDisconnect(). For interactive work, on.exit(dbDisconnect(con)) inside a function is the cleanest pattern. For long-lived applications, look at the DBI companion guide for connection pooling patterns.

How do you run queries with dbGetQuery and dbSendQuery?

For a one-shot SELECT, dbGetQuery() is the path of least resistance. It sends the statement, fetches every row, clears the result, and returns a data.frame.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT * FROM mtcars")
#>      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
#> 1   21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
#> 2   21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
#> 3   22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1

dbGetQuery() is for SELECT only. Using it for INSERT, UPDATE, or DELETE either errors out on most backends or silently misbehaves. This is the most common DBI mistake. Pick the wrong one and the code stops working the moment you switch from a read query to a write.

When you need chunked retrieval (millions of rows, or rows that arrive faster than memory can hold), use the lower-level dbSendQuery() / dbFetch() / dbClearResult() triad. dbFetch() accepts a row count; -1 fetches all remaining rows.

res <- dbSendQuery(con, "SELECT * FROM mtcars")
chunk <- dbFetch(res, n = 50)
# process chunk ...
dbClearResult(res)

If you forget dbClearResult(), the result object leaks. The convenience functions handle that for you, which is why most day-to-day code should stick with dbGetQuery() and dbExecute().

How do you read and write whole tables?

dbWriteTable() and dbReadTable() move whole data.frame objects across the boundary. They are the right tool for ingest (load a CSV into a staging table) and for snapshot reads (dump a small lookup table to R).

dbWriteTable(con, "mtcars", mtcars)
dbReadTable(con, "mtcars")

The name argument accepts a string, a schema-qualified Id() object, or a verbatim SQL() call. Use Id() whenever the database has schemas, since the helper does the right quoting for whichever engine you target, and prefer SQL() only when you need to drop down to the engine’s native syntax for edge cases.

dbWriteTable(con, Id(schema = "staging", table = "events"), events_df)
dbWriteTable(con, SQL('"raw"."events_2026"'), events_df)

Several helpers are useful for exploring a database you did not create. These introspection calls return character vectors or booleans, so they compose well with dplyr-style logic when you want to gate downstream code on whether specific tables or columns actually exist.

dbListTables(con)
#> [1] "mtcars"
dbListFields(con, "mtcars")
#> [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear" "carb"
dbExistsTable(con, "mtcars")    # TRUE
dbRemoveTable(con, "mtcars")    # drops it

dbWriteTable() defaults to row.names = FALSE, which silently drops row names from named data frames. If you want them preserved as a column, set row.names = "rowname_col". For appending rather than replacing, set append = TRUE.

How do you execute INSERT, UPDATE, and DELETE?

Use dbExecute() for INSERT, UPDATE, DELETE, and DDL. It returns the number of rows affected and clears the result for you.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cars", head(cars, 3))
dbExecute(
  con,
  "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)"
)
#> [1] 3
dbReadTable(con, "cars")
#>   speed dist
#> 1     4    2
#> 2     4   10
#> 3     7    4
#> 4     1    1
#> 5     2    2
#> 6     3    3

dbExecute() is the DML counterpart to dbGetQuery(). Pick the wrong one and the code stops working the moment you switch from a SELECT to a write.

What are DBI transactions and when do you need them?

A transaction is a set of statements that commit together or roll back together. The classic case is a transfer: the debit and the credit must both happen, or neither happens.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "cash",    data.frame(amount = 100))
dbWriteTable(con, "account", data.frame(amount = 2000))

withdraw <- function(amount) {
  dbExecute(con, "UPDATE cash SET amount = amount + ?", list(amount))
  dbExecute(con, "UPDATE account SET amount = amount - ?", list(amount))
}

withdraw_if_funds <- function(amount) {
  dbWithTransaction(con, {
    withdraw(amount)
    if (dbReadTable(con, "account")$amount < 0) {
      dbBreak()   # silent rollback
    }
  })
}

dbWithTransaction() is the safe wrapper. Internally it calls dbBegin(), runs your code, and then dbCommit() on success or dbRollback() on error. dbBreak() exits the block early and rolls back. Manual dbBegin() calls do not nest on most backends; dbWithTransaction() errors out cleanly if you try to nest, which is the behavior you want.

DDL statements (CREATE TABLE, DROP TABLE) are not transactional on every engine. SQLite accepts them mid-transaction; PostgreSQL commits implicitly on most DDL. Test on the backend you target.

How do you bind parameters safely?

Two patterns are safe for interpolating user data into a query. The first uses DBI’s quoting helpers; the second uses ? placeholders with a params list. The placeholder approach is preferred because the database engine handles escaping.

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "films", data.frame(
  title  = c("ACE GOLDFINGER", "AFFAIR PREJUDICE"),
  rating = c("G", "G")
))

# Pattern 1: quoting + paste
safe_id    <- dbQuoteIdentifier(con, "rating")
safe_param <- dbQuoteLiteral(con, "G")
query      <- paste0("SELECT * FROM films WHERE ", safe_id, " = ", safe_param)

# Pattern 2: placeholder + params (preferred)
dbGetQuery(
  con,
  "SELECT * FROM films WHERE rating = ?",
  params = list("G")
)

The gotcha: ? placeholders bind values, not identifiers. Table and column names must go through dbQuoteIdentifier(). Constructing paste0("WHERE ", user_input, " = ?") is still an injection vector.

Placeholder syntax is backend-specific. SQLite, MariaDB, and ODBC use ? with an unnamed list. PostgreSQL uses $1, $2, .... DBI passes the SQL string through unchanged, so the example above will not work as written against Postgres. Translate the placeholders to $1, $2, and so on when you target it.

Common mistakes

A few patterns bite new DBI users reliably, and they fall into a small number of buckets: using the wrong verb for the job, forgetting connection or result lifecycle calls, and building SQL strings with paste0() instead of placeholders. The list below pairs each mistake with the safe alternative so you can scan it once and avoid the debugging session later.

• Using dbGetQuery() for INSERT or UPDATE. It is SELECT only; use dbExecute() for everything that writes.
• Forgetting library(<backend>) before dbConnect(). The driver is registered on package load, not on demand.
• Forgetting dbDisconnect(). Wrap it in on.exit() or use a connection pool for long-lived apps.
• Leaking DBIResult objects. Use the convenience functions, or always pair dbSendQuery() with dbClearResult().
• Building queries with paste0() on user input. Use ? placeholders or dbQuoteLiteral() / dbQuoteIdentifier().
• Hard-coding placeholder syntax across backends. ? is not universal; check the backend’s docs.

Frequently asked questions

What’s the difference between DBI and dbplyr? DBI is the low-level database interface. dbplyr translates dplyr verbs into SQL and uses DBI under the hood. Use DBI when you want explicit SQL; use dbplyr when you want to stay in tidyverse syntax. See the dbplyr guide for the latter.

Do I need DBI if I just want to read a CSV? No. For local files, readr::read_csv() or data.table::fread() is faster and simpler. DBI earns its weight when the data lives behind a SQL engine.

Can I use DBI without writing SQL? Yes. dbplyr compiles dplyr pipelines into SQL and pushes them through DBI. You get the performance of database execution with R syntax. If you do want to keep your SQL skills sharp, the R + SQLite guide walks through the engine-specific side of DBI.

Where to go next

The six functions dbConnect(), dbDisconnect(), dbGetQuery(), dbExecute(), dbReadTable(), and dbWriteTable() cover the bulk of day-to-day database work. They map onto the four things you actually do with a database: open a session, run a query, move a table, and clean up. Once those feel automatic, transactions, parameter binding, and result chunking are the next layer of polish. From there, three directions are worth exploring:

• For dplyr-style queries on top of DBI, see the dbplyr guide.
• For an in-process analytical backend that beats SQLite on columnar workloads, see the DuckDB guide.
• For production-grade connection pooling and Shiny integration, the pool package documentation is the right next read.

For a deeper tutorial covering transactions, prepared statements, and database-specific quirks, see the Advanced R + Databases tutorial.

A complete worked example

The snippet below ties the whole article together. It opens an in-memory SQLite database, writes a data frame, runs a parameterised query, executes a transaction, and tears the connection down cleanly. Run it top to bottom in a fresh R session and you have seen every verb from this guide in one place.

library(RSQLite)
library(DBI)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

# Write a data frame
dbWriteTable(con, "orders", data.frame(
  id     = 1:4,
  total  = c(10, 25, 5, 40),
  status = c("paid", "paid", "refund", "paid"),
  stringsAsFactors = FALSE
))

# Read a parameterised query
paid <- dbGetQuery(
  con,
  "SELECT id, total FROM orders WHERE status = ?",
  params = list("paid")
)
paid
#>   id total
#> 1  1    10
#> 2  2    25
#> 3  4    40

# Run a transaction
dbWithTransaction(con, {
  dbExecute(con, "UPDATE orders SET status = ? WHERE id = ?", list("paid", 1))
  if (any(dbReadTable(con, "orders")$total < 0)) {
    dbBreak()  # rolls back if any total is negative
  }
})

dbDisconnect(con)

The only thing missing for a real production setup is swapping RSQLite::SQLite() for RPostgres::Postgres() (or whichever driver you need) and reading credentials from environment variables rather than hard-coding them.

Conclusion

DBI is the small, stable interface that lets R talk to any SQL engine through the same six functions. Learn those, learn the difference between dbGetQuery() and dbExecute(), learn ? placeholders for parameter binding, and the rest is just transactions and cleanup. Switching between SQLite, PostgreSQL, and BigQuery becomes a one-line change at dbConnect() time. The verb set you build today will outlive whichever backend is fashionable next year.

See also

• R + SQLite
• DBI Companion Guide
• dbplyr Guide