Real‑world examples of top examples of connecting to databases with R

Core examples of connecting to databases with R using DBI

Before touching specific backends, it helps to see the common pattern behind most examples of top examples of connecting to databases with R. Nearly all modern database packages implement the DBI interface, which gives you the same basic verbs regardless of vendor:

library(DBI)

con <- dbConnect(
  drv      = <DBI driver>,
  dbname   = <database name>,
  host     = <host>,
  port     = <port>,
  user     = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

## Read data
customers <- dbReadTable(con, "customers")

## Run a query
result <- dbGetQuery(con, "SELECT COUNT(*) AS n FROM customers")

## Always disconnect
dbDisconnect(con)

Every other example of a database connection in this article is a variation on that theme, with different drivers and authentication methods.

Example of connecting R to a local SQLite database

One of the simplest examples of top examples of connecting to databases with R uses SQLite. There is no server to manage, and the entire database lives in a single file on disk. That makes it perfect for reproducible analysis, teaching, and lightweight ETL.

library(DBI)
library(RSQLite)

con <- dbConnect(
  RSQLite::SQLite(),
  dbname = "data/my_db.sqlite"
)

## List tables
dbListTables(con)

## Write a data frame
dbWriteTable(con, "iris", iris, overwrite = TRUE)

## Query with SQL
setosa <- dbGetQuery(con, "SELECT * FROM iris WHERE Species = 'setosa'")

## Or use dplyr/dbplyr
library(dplyr)

iris_tbl <- tbl(con, "iris")
summary_tbl <- iris_tbl %>%
  group_by(Species) %>%
  summarize(across(where(is.numeric), mean, na.rm = TRUE))

summary_tbl %>% collect()

dbDisconnect(con)

In 2024–2025, SQLite remains a popular choice for reproducible research and teaching materials, and it’s often the first stop when people look for simple examples of connecting to databases with R without touching any external infrastructure.

Best examples of connecting R to PostgreSQL in production

PostgreSQL is the workhorse of many analytics stacks, and it’s one of the best examples of a database where R integrates cleanly via RPostgres. This is where you move from toy setups to real production pipelines.

library(DBI)
library(RPostgres)

con <- dbConnect(
  RPostgres::Postgres(),
  dbname   = Sys.getenv("PG_DB"),
  host     = Sys.getenv("PG_HOST"),
  port     = 5432,
  user     = Sys.getenv("PG_USER"),
  password = Sys.getenv("PG_PASSWORD"),
  sslmode  = "require"
)

## Parameterized query to avoid SQL injection
min_date <- as.Date("2024-01-01")

orders <- dbGetQuery(
  con,
  "SELECT order_id, customer_id, order_date, total
   FROM orders
   WHERE order_date >= $1",
  params = list(min_date)
)

## Use dbplyr for lazy queries
library(dplyr)

orders_tbl <- tbl(con, "orders")
recent <- orders_tbl %>%
  filter(order_date >= as.Date("2024-09-01")) %>%
  group_by(customer_id) %>%
  summarize(total_spent = sum(total), .groups = "drop")

recent_local <- collect(recent)

dbDisconnect(con)

This is a realistic example of top examples of connecting to databases with R in a production environment: environment variables for credentials, SSL required, parameterized queries, and dbplyr doing the heavy lifting server‑side.

If you want to align with security guidance, the pattern of using environment variables and avoiding hard‑coded secrets is consistent with recommendations you’ll see from organizations like the National Institute of Standards and Technology (NIST) in their broader cybersecurity guidance.

Examples include connecting to MySQL and MariaDB from R

MySQL and MariaDB still power a lot of legacy and mid‑size applications. From an R perspective, the connection pattern looks almost identical to PostgreSQL, just with a different driver. These are straightforward examples of top examples of connecting to databases with R when you inherit an existing LAMP‑style stack.

library(DBI)
library(RMariaDB)

con <- dbConnect(
  RMariaDB::MariaDB(),
  dbname   = Sys.getenv("MYSQL_DB"),
  host     = Sys.getenv("MYSQL_HOST"),
  port     = 3306,
  user     = Sys.getenv("MYSQL_USER"),
  password = Sys.getenv("MYSQL_PASSWORD")
)

## Inspect schema
str(dbListFields(con, "users"))

## Stream large result in chunks
rs <- dbSendQuery(con, "SELECT * FROM events")
chunk <- dbFetch(rs, n = 10000)
while (nrow(chunk) > 0) {
#  # process chunk here
  chunk <- dbFetch(rs, n = 10000)
}
dbClearResult(rs)

dbDisconnect(con)

The streaming pattern above is a good example of handling large tables without pulling millions of rows into memory at once.

Example of connecting to SQL Server via ODBC

Enterprise shops in the US are still full of Microsoft SQL Server. In 2024–2025, the most flexible R approach is the odbc package, which talks to any ODBC driver, including SQL Server and Azure SQL Database.

library(DBI)
library(odbc)

con <- dbConnect(
  odbc::odbc(),
  Driver   = "ODBC Driver 18 for SQL Server",
  Server   = Sys.getenv("MSSQL_SERVER"),
  Database = Sys.getenv("MSSQL_DB"),
  UID      = Sys.getenv("MSSQL_USER"),
  PWD      = Sys.getenv("MSSQL_PASSWORD"),
  Encrypt  = "yes",
  TrustServerCertificate = "no"
)

## Use dbplyr to query a schema-qualified table
library(dplyr)

sales <- tbl(con, in_schema("dbo", "Sales"))

monthly <- sales %>%
  filter(SaleDate >= as.Date("2024-01-01")) %>%
  group_by(Year = year(SaleDate), Month = month(SaleDate)) %>%
  summarize(Revenue = sum(Amount), .groups = "drop")

monthly_local <- collect(monthly)

dbDisconnect(con)

This is one of the best examples of connecting to databases with R in environments where you don’t control the database vendor but can insist on an ODBC driver being installed.

For those working in health or public‑sector analytics, this pattern often shows up in data pipelines feeding reports similar in spirit to what you see from agencies like the Centers for Disease Control and Prevention (CDC) or large academic medical centers.

Cloud‑scale examples of top examples of connecting to databases with R

Connecting R to Google BigQuery

As warehouses moved to the cloud, R followed. BigQuery is a good example of a columnar, serverless warehouse that plays nicely with R through the bigrquery package.

library(bigrquery)
library(dplyr)

bq_auth(path = Sys.getenv("GOOGLE_APPLICATION_CREDENTIALS"))

project <- Sys.getenv("GCP_PROJECT")
dataset <- "analytics"

table <- bq_table(project, dataset, "events")

events_tbl <- tbl(bq_src(project = project), table)

summary_tbl <- events_tbl %>%
  filter(event_date >= as.Date("2024-10-01")) %>%
  group_by(country) %>%
  summarize(events = n(), .groups = "drop")

summary_local <- collect(summary_tbl)

This is a realistic example of top examples of connecting to databases with R in modern analytics teams: authentication via service accounts, lazy queries, and heavy lifting done in the warehouse.

Connecting R to Snowflake

Snowflake has become a favorite in data engineering circles. With the odbc package and the Snowflake ODBC driver, the pattern mirrors other ODBC examples.

library(DBI)
library(odbc)

con <- dbConnect(
  odbc::odbc(),
  Driver   = "SnowflakeDSIIDriver",
  Server   = Sys.getenv("SNOWFLAKE_ACCOUNT"),
  UID      = Sys.getenv("SNOWFLAKE_USER"),
  PWD      = Sys.getenv("SNOWFLAKE_PASSWORD"),
  Warehouse = "COMPUTE_WH",
  Database  = "ANALYTICS",
  Schema    = "PUBLIC"
)

library(dplyr)

sessions <- tbl(con, "SESSIONS")

user_stats <- sessions %>%
  filter(SESSION_DATE >= as.Date("2024-09-01")) %>%
  group_by(USER_ID) %>%
  summarize(
    sessions = n(),
    avg_duration = mean(DURATION_SEC, na.rm = TRUE),
    .groups = "drop"
  )

user_stats_local <- collect(user_stats)

dbDisconnect(con)

Cloud warehouses like BigQuery and Snowflake are now standard in 2024–2025 data stacks, so any modern list of best examples of connecting to databases with R needs to show patterns like these.

Examples of connection pooling in Shiny apps with R

If you deploy Shiny dashboards or Plumber APIs, opening a new database connection for every request is a good way to anger your DBA. A more production‑ready example of connecting to databases with R uses connection pooling via the pool package.

library(pool)
library(DBI)
library(RPostgres)

pg_pool <- dbPool(
  drv      = RPostgres::Postgres(),
  dbname   = Sys.getenv("PG_DB"),
  host     = Sys.getenv("PG_HOST"),
  user     = Sys.getenv("PG_USER"),
  password = Sys.getenv("PG_PASSWORD"),
  maxSize  = 10,
  idleTimeout = 60
)

## In server logic of a Shiny app
server <- function(input, output, session) {
  output$order_count <- renderText({
    n <- dbGetQuery(pg_pool, "SELECT COUNT(*) AS n FROM orders")$n
    paste("Total orders:", n)
  })
}

onStop(function() {
  poolClose(pg_pool)
})

This pattern shows up in many real examples of production R apps, because it keeps connection counts under control while still being easy for analysts to work with.

Security and configuration: real examples that don’t hard‑code passwords

Many beginner examples of top examples of connecting to databases with R show passwords directly in the script. That’s fine for a throwaway demo, but not for anything that touches real users or regulated data.

A more realistic example of connecting to databases with R uses environment variables and .Renviron files:

## In ~/.Renviron (not committed to version control)
PG_DB=mydb
PG_HOST=db.example.com
PG_USER=myuser
PG_PASSWORD=supersecret

## In R script
library(DBI)
library(RPostgres)

con <- dbConnect(
  RPostgres::Postgres(),
  dbname   = Sys.getenv("PG_DB"),
  host     = Sys.getenv("PG_HOST"),
  user     = Sys.getenv("PG_USER"),
  password = Sys.getenv("PG_PASSWORD")
)

If your work touches health or clinical data, this pattern aligns with the general privacy and security mindset recommended by institutions like the National Institutes of Health (NIH) and large medical centers such as Mayo Clinic when they talk about safeguarding sensitive information.

FAQ: common questions about examples of connecting to databases with R

Q: What are some practical examples of connecting to databases with R for beginners?
For beginners, the easiest examples include RSQLite with a local .sqlite file, or connecting to a test PostgreSQL instance with RPostgres. Both let you practice DBI functions like dbConnect(), dbGetQuery(), and dbWriteTable() without dealing with corporate firewalls or VPNs.

Q: Can you give an example of using dplyr with a remote database in R?
Yes. A common pattern is tbl(con, "table_name") to create a lazy table, then use dplyr verbs like filter(), group_by(), and summarize(). Under the hood, dbplyr translates the pipeline into SQL and runs it on the database, returning results only when you call collect().

Q: Which packages show the best examples of modern R database workflows?
In 2024–2025, the best examples usually combine DBI for connections, a backend driver (RPostgres, RMariaDB, RSQLite, odbc, bigrquery), and dbplyr/dplyr for query construction. For apps and APIs, pool is widely used for safe connection pooling.

Q: How do I handle large tables when connecting R to a database?
Either stream results in chunks with dbSendQuery() and dbFetch(), or keep the heavy work in the database using dbplyr so only aggregated results come back to R. Both approaches appear in real examples from production pipelines where tables have tens or hundreds of millions of rows.

Q: Are these examples of R database connections portable across operating systems?
Mostly yes. SQLite, PostgreSQL, and MySQL examples are highly portable. ODBC‑based examples depend on having the correct driver installed for your platform (Windows, macOS, Linux), but the R code itself changes very little.