SQLite in R with RSQLite and DBI: A Tested Tutorial

Ninad Pathak

October 7, 2026

R can keep its data in an SQLite file and query only the rows an analysis needs, instead of reading a whole CSV into memory. There’s no server to set up. In this article I’ll walk you through using SQLite from R with RSQLite and DBI, from creating a database to querying it with dplyr.

TLDR

Install with install.packages("RSQLite"). It bundles SQLite itself, so there’s nothing else to install.
dbConnect(RSQLite::SQLite(), "analysis.db") creates the database file if it’s missing and opens it.

dbWriteTable() saves a data frame as a table, and dbGetQuery() runs SQL and returns a data frame.
Pass values with params = list(…) instead of pasting them into the SQL.
With dbplyr installed, tbl(con, "table") lets you query with dplyr verbs, and collect() brings the result into R.

What is RSQLite and what will we build?

RSQLite “embeds the SQLite database engine in R, providing a DBI-compliant interface” (RSQLite). DBI is R’s common database interface, so functions such as dbGetQuery() also work with other databases.

To show you how it works, I will build a small analysis database from a data frame and R’s built-in mtcars data, then query it with SQL and with dplyr.

Prerequisites

What	Version used here
R	4.5.3 (in the `rocker/r-ver:4.5` container)
RSQLite	2.4.6, which bundles SQLite 3.51.2
DBI	1.3.0
dplyr and dbplyr (optional, Step 7)	1.2.1 and 2.5.2

install.packages(c("RSQLite", "dplyr", "dbplyr"))

In the test container, all four packages were installed:

> c("RSQLite", "DBI", "dplyr", "dbplyr") %in% rownames(installed.packages())
[1] TRUE TRUE TRUE TRUE

Building an analysis database in R

The steps run in order in one R session, from analysis.R:

library(DBI)
section <- function(name) cat("### ", name, "\n", sep = "")

section("connect")
con <- dbConnect(RSQLite::SQLite(), "analysis.db")   # creates the file if it's missing
print(file.exists("analysis.db"))
print(RSQLite::rsqliteVersion())

section("write_table")
sales <- data.frame(
  region = c("North", "South", "North", "East", "South", "East"),
  product = c("Pen", "Pen", "Ink", "Ink", "Paper", "Pen"),
  units = c(120L, 80L, 30L, 45L, 200L, 60L),
  price = c(1.5, 1.5, 4.25, 4.25, 0.1, 1.5)
)
dbWriteTable(con, "sales", sales)
print(dbListTables(con))
print(dbListFields(con, "sales"))

section("query")
print(dbGetQuery(con, "
  SELECT region, sum(units * price) AS revenue
  FROM sales GROUP BY region ORDER BY revenue DESC"))

section("params")
print(dbGetQuery(con, "SELECT product, units FROM sales WHERE region = ? AND units > ?",
                 params = list("North", 50)))

section("append_overwrite")
dbWriteTable(con, "sales", data.frame(region = "West", product = "Ink", units = 10L, price = 4.25),
             append = TRUE)
print(dbGetQuery(con, "SELECT count(*) AS rows FROM sales"))
dbWriteTable(con, "mtcars", mtcars, row.names = TRUE, overwrite = TRUE)
print(dbGetQuery(con, "SELECT count(*) AS cars FROM mtcars"))

section("index")
dbExecute(con, "CREATE INDEX idx_sales_region ON sales(region)")
print(dbGetQuery(con, "EXPLAIN QUERY PLAN SELECT * FROM sales WHERE region = 'East'")$detail)

section("dplyr")
library(dplyr, warn.conflicts = FALSE)
cars <- tbl(con, "mtcars")
fast <- cars |>
  group_by(cyl) |>
  summarise(n = n(), avg_mpg = mean(mpg, na.rm = TRUE)) |>
  arrange(cyl)
show_query(fast)
print(collect(fast))

section("transaction")
try(dbWithTransaction(con, {
  dbExecute(con, "UPDATE sales SET units = 0 WHERE region = 'North'")
  stop("something went wrong")
}))
print(dbGetQuery(con, "SELECT sum(units) AS north_units FROM sales WHERE region = 'North'"))

section("memory")
mem <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(mem, "iris", iris)
print(dbGetQuery(mem, "SELECT Species, round(avg(\"Sepal.Length\"), 2) AS avg_sepal FROM iris GROUP BY Species"))
dbDisconnect(mem)

section("disconnect")
dbDisconnect(con)
print(dbIsValid(con))

Step 1: Create or open a database

dbConnect() with a file name creates the database the first time. rsqliteVersion() shows the SQLite version bundled with the package:

[1] TRUE
  header  library
"3.51.2" "3.51.2"

Step 2: Save a data frame as a table

dbWriteTable() creates the table from the data frame’s columns:

[1] "sales"
[1] "region"  "product" "units"   "price"

Step 3: Run SQL and get a data frame back

  region revenue
1  North  307.50
2   East  281.25
3  South  140.00

Step 4: Pass values as parameters

params binds each ? to a value, which keeps quoting right and blocks SQL injection:

  product units
1     Pen   120

Step 5: Append to or replace a table

append = TRUE adds rows, and overwrite = TRUE replaces the table. row.names = TRUE keeps mtcars‘ car names as a column:

  rows
1    7
  cars
1   32

Step 6: Add an index

dbExecute() runs a statement that returns no rows, and prints how many rows it changed (0 for CREATE INDEX). The query plan shows SQLite using the new index:

[1] 0
[1] "SEARCH sales USING INDEX idx_sales_region (region=?)"

Step 7: Query with dplyr

With dbplyr installed, tbl() treats the table as a lazy data frame. dplyr turns the verbs into SQL, and show_query() prints it. collect() runs the query and returns a tibble:

<SQL>
SELECT `cyl`, COUNT(*) AS `n`, AVG(`mpg`) AS `avg_mpg`
FROM `mtcars`
GROUP BY `cyl`
ORDER BY `cyl`
# A tibble: 3 × 3
    cyl     n avg_mpg
  <dbl> <int>   <dbl>
1     4    11    26.7
2     6     7    19.7
3     8    14    15.1

Step 8: Wrap changes in a transaction

dbWithTransaction() “calls dbBegin() before executing the code, and dbCommit() after successful completion, or dbRollback() in case of an error” (DBI). The error below rolled the update back, so the North rows still have their units:

Error in force(code) : something went wrong
  north_units
1         150

Step 9: Use an in-memory database for scratch work

":memory:" gives you a database that disappears when you disconnect:

     Species avg_sepal
1     setosa      5.01
2 versicolor      5.94
3  virginica      6.59

Running it

Disconnect when you’re done. dbIsValid() confirms the connection is closed:

[1] FALSE

The full script ran with Rscript analysis.R in the container, and the outputs above are from that run.

Common problems

These weren’t reproduced in the test run, so the fixes are described without their exact error text:

R can’t find dbConnect(): load DBI with library(DBI), or call DBI::dbConnect().
dbWriteTable() refuses because the table already exists: pass append = TRUE to add rows, or overwrite = TRUE to replace the table.

tbl() on a database connection fails: install the dbplyr package.
Column names with dots, like Sepal.Length: quote them in SQL with double quotes, as in Step 9.

Conclusion

RSQLite gives R a full SQL database in one file with a single install.packages(). Write data frames with dbWriteTable() and query them with dbGetQuery() and parameters. Switch to dplyr with tbl() when you’d rather not write SQL.