Database connections (DBI, RSQLite) in R Programming

Introduction

We use database connections to talk to databases from R. This helps us save, get, and manage data easily.

You want to save your data in a database instead of a file.

You need to get data from a database to analyze it in R.

You want to update or delete data stored in a database.

You want to work with large datasets that don't fit in memory.

You want to share data between different programs using a database.

Syntax

R Programming

library(DBI)
library(RSQLite)

# Connect to a SQLite database file
con <- dbConnect(RSQLite::SQLite(), "path_to_database.sqlite")

# Use the connection to send queries or commands

# Disconnect when done
dbDisconnect(con)

Use dbConnect() to open a connection to the database.

Always close the connection with dbDisconnect() to free resources.

Examples

This creates a temporary database stored in memory. It disappears when you disconnect.

R Programming

library(DBI)
library(RSQLite)

# Connect to a new in-memory database
con <- dbConnect(RSQLite::SQLite(), ":memory:")

This connects to a database saved in the file my_data.sqlite. Data stays after R closes.

R Programming

library(DBI)
library(RSQLite)

# Connect to a file-based database
con <- dbConnect(RSQLite::SQLite(), "my_data.sqlite")

Always disconnect when you finish working with the database to avoid problems.

R Programming

dbDisconnect(con)

Sample Program

This program creates a small database in memory, adds a table and data, then reads and prints the data.

R Programming

library(DBI)
library(RSQLite)

# Connect to an in-memory SQLite database
con <- dbConnect(RSQLite::SQLite(), ":memory:")

# Create a table named 'people'
dbExecute(con, "CREATE TABLE people (id INTEGER, name TEXT)")

# Insert some data
dbExecute(con, "INSERT INTO people (id, name) VALUES (1, 'Alice'), (2, 'Bob')")

# Query the data
result <- dbGetQuery(con, "SELECT * FROM people")

# Print the result
print(result)

# Disconnect
dbDisconnect(con)

Important Notes

Use dbExecute() for commands that change data or structure (like CREATE or INSERT).

Use dbGetQuery() to run SELECT queries and get results as data frames.

SQLite databases are simple and good for learning or small projects.

Summary

Database connections let R talk to databases to save and get data.

Use dbConnect() to open and dbDisconnect() to close connections.

Use dbExecute() for changes and dbGetQuery() to read data.