DBI and RSQLite Basics
Connect to SQLite databases, run queries, and retrieve results with DBI.
Database Access in R with DBI
The DBI package provides a unified interface for database access in R. The same functions work across different database backends (SQLite, PostgreSQL, MySQL, etc.) by pairing DBI with a backend driver package. RSQLite is the most accessible backend — it requires no server installation.
library(DBI)
library(RSQLite)
# DBI + RSQLite: connect to an in-memory SQLite database
# ':memory:' creates a fresh database in RAM
con <- dbConnect(RSQLite::SQLite(), ':memory:')
cat('Connection class:', class(con), '\n')
cat('Database backend:', dbGetInfo(con)$dbname, '\n')
# Always close connection when done
dbDisconnect(con)
cat('Connection closed.')dbConnect() — Creating a Connection
dbConnect(drv, ...) creates a database connection. The first argument is the driver object. For SQLite: RSQLite::SQLite(). Additional arguments (like the database file path) depend on the backend.
library(DBI)
library(RSQLite)
# In-memory database (disappears when connection closes)
con_mem <- dbConnect(RSQLite::SQLite(), ':memory:')
# File-based SQLite database (persists to disk)
# con_file <- dbConnect(RSQLite::SQLite(), '/tmp/mydb.sqlite')
# Always wrap connections in tryCatch or use on.exit()
on.exit(dbDisconnect(con_mem))
cat('Connected! Is valid:', dbIsValid(con_mem))