R Academy · Lesson

DBI and RSQLite Basics

Connect to SQLite databases, run queries, and retrieve results with DBI.

Lesson 1 of 413 steps

DBI and RSQLite Basics is a free R Academy lesson on CoddyKit. This is lesson 1 of 4. Any 3 lessons of this learning path are free to read in full — after that, CoddyKit PRO unlocks every lesson, plus hands-on practice with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, and your progress syncs across the web and the CoddyKit app. The R Academy course includes 4 lessons in total.

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?

Answer it in the interactive lesson

Recap: DBI and RSQLite

Key takeaways for DBI and RSQLite:

dbConnect(RSQLite::SQLite(), ':memory:') — create an in-memory SQLite connection
dbWriteTable(con, 'name', df) — load a data frame into a table
dbListTables(con) — list tables; dbListFields(con, 'tbl') — list columns
dbGetQuery(con, sql) — run SELECT, return data frame
dbFetch(rs, n=1000) — chunked retrieval for large results
dbExecute(con, sql) — run INSERT/UPDATE/DELETE/DDL
dbDisconnect(con) — always close; use on.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')

Free to start

Learn R with an AI tutor — free

Write and run real code in your browser, get instant help from a 24/7 AI tutor, and pick up where you left off on the web or in the app.

Courses: 43
Lessons: 159

Start learning free View the full course

Frequently Asked Questions

Is the “DBI and RSQLite Basics” lesson free?

Yes — any 3 lessons of the R Academy learning path, including “DBI and RSQLite Basics”, are free to read in full here on the web. After that, CoddyKit PRO unlocks every lesson, plus interactive practice with a built-in code editor and a 24/7 AI tutor. The R Academy course includes 4 lessons in total.

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, so you can start here or from the beginning and move at your own pace. This is lesson 1 of 4.

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.

All lessons in this course

← Back to R Academy