DBI and RSQLite Basics
Connect to SQLite databases, run queries, and retrieve results with DBI.
DBI and RSQLite Basics is a free R Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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))dbWriteTable() — Load Data into DB
dbWriteTable(con, 'table_name', df) creates a table and loads a data frame into it. Set overwrite=TRUE to replace an existing table, or append=TRUE to add rows to an existing table.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
# Load a data frame into the database
students <- data.frame(
id = 1:5,
name = c('Alice','Bob','Carol','Dave','Eve'),
score = c(85, 92, 78, 88, 95)
)
dbWriteTable(con, 'students', students)
# Verify: list tables
cat('Tables in DB:', dbListTables(con), '\n')
dbDisconnect(con)dbListTables() and dbListFields()
dbListTables(con) returns the names of all tables in the connected database. dbListFields(con, 'table') returns the column names of a specific table. Use these to explore an unknown database.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students', data.frame(id=1:3, name=c('A','B','C'), score=c(80,90,85)))
dbWriteTable(con, 'courses', data.frame(code=c('R101','R201'), title=c('Intro','Advanced')))
cat('Tables:', paste(dbListTables(con), collapse=', '), '\n')
cat('Students fields:', paste(dbListFields(con, 'students'), collapse=', '), '\n')
dbDisconnect(con)dbGetQuery() — Run SQL and Return Data
dbGetQuery(con, sql) executes a SELECT query and returns the result as a data frame. This is the primary function for reading data from a database. It combines dbSendQuery() + dbFetch() + dbClearResult() in one step.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
data.frame(id=1:5, name=c('Alice','Bob','Carol','Dave','Eve'),
score=c(85,92,78,88,95)))
# Run SQL and get results as data frame
high_scorers <- dbGetQuery(con,
'SELECT name, score FROM students WHERE score >= 88 ORDER BY score DESC'
)
print(high_scorers)
dbDisconnect(con)dbFetch() — Retrieve Results in Chunks
For large result sets, use the three-step approach: dbSendQuery() sends the query, dbFetch(con, n=1000) retrieves chunks of n rows, and dbClearResult() cleans up. This controls memory usage for big tables.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'data', data.frame(x=1:100, y=rnorm(100)))
# Three-step approach for chunked reading
rs <- dbSendQuery(con, 'SELECT * FROM data WHERE x <= 5')
chunk <- dbFetch(rs, n=3) # Get first 3 rows
cat('Rows fetched so far:', nrow(chunk), '\n')
print(chunk)
dbClearResult(rs) # Always clear!
dbDisconnect(con)dbExecute() — Run Non-SELECT SQL
dbExecute(con, sql) runs SQL that modifies data (INSERT, UPDATE, DELETE, CREATE, DROP) and returns the number of rows affected. Use it for DDL and DML operations that don't return rows.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))
# Update a record
rows_affected <- dbExecute(con,
'UPDATE students SET score = 95 WHERE name = \'Alice\''
)
cat('Rows updated:', rows_affected, '\n')
# Verify
print(dbGetQuery(con, 'SELECT * FROM students WHERE name = \'Alice\''))
dbDisconnect(con)dbExistsTable() and dbRemoveTable()
dbExistsTable(con, 'name') checks if a table exists (returns TRUE/FALSE). dbRemoveTable(con, 'name') drops a table. These are useful for safe setup and teardown in scripts that may run multiple times.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
cat('Before: exists?', dbExistsTable(con, 'results'), '\n')
dbWriteTable(con, 'results', data.frame(x=1:3, y=4:6))
cat('After write: exists?', dbExistsTable(con, 'results'), '\n')
dbRemoveTable(con, 'results')
cat('After remove: exists?', dbExistsTable(con, 'results'), '\n')
dbDisconnect(con)dbDisconnect() — Closing Connections
Always close database connections with dbDisconnect(con) when you're done. Open connections consume memory and file handles. Use on.exit(dbDisconnect(con)) inside functions to guarantee cleanup even if an error occurs.
library(DBI)
library(RSQLite)
# Safe connection pattern using on.exit()
safe_query <- function(query) {
con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con)) # Runs even if error!
dbWriteTable(con, 'data', data.frame(x=1:5, y=6:10))
dbGetQuery(con, query)
}
result <- safe_query('SELECT * FROM data WHERE x > 3')
print(result)Loading mtcars into SQLite
A practical example: load the built-in mtcars dataset into SQLite, then run SQL queries on it. This demonstrates the full DBI workflow and lets you practice SQL from R.
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
# Load mtcars into the database
dbWriteTable(con, 'cars', mtcars, overwrite=TRUE)
# SQL query
result <- dbGetQuery(con, '
SELECT cyl,
COUNT(*) AS n,
ROUND(AVG(mpg), 1) AS avg_mpg,
ROUND(AVG(hp), 0) AS avg_hp
FROM cars
GROUP BY cyl
ORDER BY cyl
')
print(result)
dbDisconnect(con)DBI Workflow Summary
The DBI workflow follows a consistent pattern regardless of the database backend: Connect → Load/Query → Disconnect. The same code works for SQLite, PostgreSQL, MySQL, and others by swapping the driver in dbConnect().
library(DBI)
library(RSQLite)
# Complete DBI workflow
con <- dbConnect(RSQLite::SQLite(), ':memory:')
# 1. Create table
dbWriteTable(con, 'orders',
data.frame(order_id=1:4, customer=c('Alice','Bob','Alice','Carol'),
amount=c(100,200,150,80)))
# 2. Query
totals <- dbGetQuery(con,
'SELECT customer, COUNT(*) orders, SUM(amount) total
FROM orders GROUP BY customer ORDER BY total DESC')
print(totals)
# 3. Clean up
dbDisconnect(con)Quick Check
What is the difference between dbGetQuery() and dbExecute() in DBI?
Recap: DBI and RSQLite
Key takeaways for DBI and RSQLite:
dbConnect(RSQLite::SQLite(), ':memory:')— create an in-memory SQLite connectiondbWriteTable(con, 'name', df)— load a data frame into a tabledbListTables(con)— list tables;dbListFields(con, 'tbl')— list columnsdbGetQuery(con, sql)— run SELECT, return data framedbFetch(rs, n=1000)— chunked retrieval for large resultsdbExecute(con, sql)— run INSERT/UPDATE/DELETE/DDLdbDisconnect(con)— always close; useon.exit()in functions- DBI works with PostgreSQL, MySQL, BigQuery etc. by swapping the driver
library(DBI)
library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), ':memory:')
on.exit(dbDisconnect(con))
dbWriteTable(con, 'scores',
data.frame(student=c('Alice','Bob','Carol'), score=c(85,92,78)))
dbExecute(con, 'UPDATE scores SET score = score + 5 WHERE score < 80')
dbGetQuery(con, 'SELECT * FROM scores ORDER BY score DESC')Frequently asked questions
Is the “DBI and RSQLite Basics” lesson free?
Yes — the full text of “DBI and RSQLite Basics” is free to read here on the web, and the R Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the R Academy course, upgrade to CoddyKit PRO.
What will I learn in “DBI and RSQLite Basics”?
Connect to SQLite databases, run queries, and retrieve results with DBI. You practise R Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start R Academy?
No prior experience is required. R Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “DBI and RSQLite Basics” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this R Academy lesson?
Yes. Every R Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.