R Academy · Lesson

Connecting to PostgreSQL and MySQL

Use RPostgres and RMySQL drivers to connect to server-based databases.

Connecting to PostgreSQL and MySQL is a free R Academy lesson on CoddyKit — lesson 2 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.

Production Database Connections

While RSQLite is great for development and testing, production data lives in PostgreSQL, MySQL, SQL Server, or cloud databases. The DBI interface remains the same — only the driver package and connection arguments change. This is DBI's key strength.

library(DBI)

# Driver packages for each database:
# PostgreSQL  -> RPostgres::Postgres()
# MySQL       -> RMySQL::MySQL() or RMariaDB::MariaDB()
# SQL Server  -> odbc::odbc()
# BigQuery    -> bigrquery::bigquery()
# Redshift    -> RPostgres::Postgres()  (same protocol)
# DuckDB      -> duckdb::duckdb()

# All use the same DBI functions:
# dbConnect, dbGetQuery, dbExecute, dbDisconnect

Connecting to PostgreSQL

RPostgres::Postgres() is the driver for PostgreSQL. Pass connection parameters: host, port (default 5432), dbname, user, and password. Never hardcode credentials — use environment variables instead.

library(DBI)
# library(RPostgres)  # Uncomment when available

# PostgreSQL connection pattern:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   port     = 5432,
#   dbname   = 'analytics',
#   user     = 'analyst',
#   password = 'secretpassword'
# )

# Query exactly like SQLite:
# result <- dbGetQuery(con, 'SELECT * FROM sales LIMIT 5')
# dbDisconnect(con)

cat('PostgreSQL uses the same DBI interface as SQLite!')

Using Environment Variables for Credentials

Hardcoding passwords in code is a security risk. Store credentials as environment variables and read them with Sys.getenv(). In production, set these in .Renviron, .env files (not checked into git), or via secret management systems.

library(DBI)

# Set environment variables (normally done outside R):
# In .Renviron file:
# DB_HOST=db.example.com
# DB_NAME=analytics
# DB_USER=analyst
# DB_PASS=secretpassword

# Read credentials from environment
get_pg_connection <- function() {
  dbConnect(
    RPostgres::Postgres(),  # driver
    host     = Sys.getenv('DB_HOST'),
    port     = as.integer(Sys.getenv('DB_PORT', '5432')),
    dbname   = Sys.getenv('DB_NAME'),
    user     = Sys.getenv('DB_USER'),
    password = Sys.getenv('DB_PASS')
  )
}

cat('Sys.getenv() reads environment variables safely.')
cat('\nDB_HOST value:', nchar(Sys.getenv('DB_HOST')), 'chars')

Connecting to MySQL / MariaDB

MySQL connections use RMariaDB::MariaDB() (the modern MySQL driver) or RMySQL::MySQL(). The connection arguments are similar to PostgreSQL. MariaDB is the recommended driver for both MySQL and MariaDB servers.

library(DBI)

# MySQL / MariaDB connection pattern:
# library(RMariaDB)
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host     = Sys.getenv('MYSQL_HOST'),
#   port     = 3306,
#   dbname   = Sys.getenv('MYSQL_DB'),
#   user     = Sys.getenv('MYSQL_USER'),
#   password = Sys.getenv('MYSQL_PASS')
# )

# SSL connection:
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host = 'secure-db.example.com',
#   ssl.ca = '/path/to/ca-cert.pem'
# )

cat('RMariaDB supports both MySQL and MariaDB servers.')

Connection Timeout and Reconnection

Long-running scripts can hit connection timeouts. Check if the connection is still valid with dbIsValid(con). For ETL scripts, consider reconnecting at the start of each processing batch rather than holding one connection open for hours.

library(DBI)
library(RSQLite)

# Safe function with reconnection
query_with_check <- function(con, sql) {
  if (!dbIsValid(con)) {
    stop('Connection is no longer valid. Reconnect.')
  }
  dbGetQuery(con, sql)
}

# Demonstrate with SQLite
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 't', data.frame(x=1:3))

cat('Connection valid:', dbIsValid(con), '\n')
print(query_with_check(con, 'SELECT * FROM t'))

dbDisconnect(con)
cat('After disconnect, valid:', dbIsValid(con))

Connection Pooling Concept

Opening a new database connection for every query is expensive. Connection pooling maintains a set of open connections and reuses them. This is critical for web APIs and Shiny apps where many requests arrive per second.

# The 'pool' package provides connection pooling for R
# library(pool)

# Create a pool (connections managed automatically)
# my_pool <- pool::dbPool(
#   drv      = RPostgres::Postgres(),
#   dbname   = Sys.getenv('DB_NAME'),
#   host     = Sys.getenv('DB_HOST'),
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS'),
#   minSize  = 2,   # Always keep 2 connections ready
#   maxSize  = 10   # Maximum 10 simultaneous connections
# )

# Use the pool like a regular connection:
# result <- dbGetQuery(my_pool, 'SELECT * FROM table')

# Close the pool on shutdown:
# pool::poolClose(my_pool)

cat('pool package: connection reuse for high-traffic apps!')

Querying with SQL Parameters (Safe)

Never concatenate user input into SQL strings (SQL injection risk). Use parameterized queries with sqlInterpolate(con, sql, .dots=list(...)) or glue_sql() from the glue package to safely insert values.

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)))

# UNSAFE (SQL injection possible):
# name_input <- 'Alice' # imagine user-provided
# dbGetQuery(con, paste('SELECT * FROM students WHERE name =', name_input))

# SAFE: use sqlInterpolate
name_input <- 'Alice'
safe_sql <- sqlInterpolate(con,
  'SELECT * FROM students WHERE name = ?name',
  name = name_input
)
print(dbGetQuery(con, safe_sql))
dbDisconnect(con)

SSH Tunneling for Remote Databases

Many production databases are not directly accessible from the internet. A common pattern is SSH tunneling: forward a local port to the remote database port via SSH, then connect R to localhost:local_port.

# SSH tunnel setup (run in terminal before connecting from R):
# ssh -N -L 5433:db-server.internal:5432 user@jump-host.example.com

# Then connect in R as if the DB is local:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'localhost',
#   port     = 5433,   # Local forwarded port
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

# You can also script the tunnel with:
# system('ssh -fN -L 5433:db:5432 user@jump-host')

cat('SSH tunneling makes private databases accessible to R.')

Reading Large Tables in Chunks

When a database table has millions of rows, loading everything at once can exhaust memory. Use LIMIT/OFFSET or cursor-based fetching to process the table in chunks. Combine with dbFetch() for streaming results.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'big_table', data.frame(id=1:100, value=rnorm(100)))

# Process in chunks of 25 rows
chunk_size <- 25
offset <- 0
total_processed <- 0

repeat {
  chunk <- dbGetQuery(con, sprintf(
    'SELECT * FROM big_table LIMIT %d OFFSET %d',
    chunk_size, offset
  ))
  if (nrow(chunk) == 0) break
  total_processed <- total_processed + nrow(chunk)
  offset <- offset + chunk_size
}

cat('Total rows processed:', total_processed, '\n')
dbDisconnect(con)

dbGetInfo() — Connection Metadata

dbGetInfo(con) returns connection metadata: server version, database name, user, etc. This is useful for logging, diagnostics, and verifying that you connected to the correct database instance in scripts that run against multiple environments.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

info <- dbGetInfo(con)
print(info)

# For PostgreSQL, info would include:
# $host, $port, $dbname, $username, $server.version

dbDisconnect(con)

Comparing SQLite vs PostgreSQL Usage

Choose SQLite for prototyping, embedded data, and testing; choose PostgreSQL for production, multi-user access, and advanced SQL features. The DBI code is nearly identical — the only difference is the driver and connection parameters.

library(DBI)
library(RSQLite)

# SQLite (development/testing)
con_dev <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con_dev, 'data', mtcars)
dev_result <- dbGetQuery(con_dev, 'SELECT COUNT(*) n FROM data')
cat('SQLite dev DB rows:', dev_result$n, '\n')
dbDisconnect(con_dev)

# PostgreSQL (production) — same interface:
# con_prod <- dbConnect(RPostgres::Postgres(),
#   host=Sys.getenv('PG_HOST'), dbname=Sys.getenv('PG_DB'),
#   user=Sys.getenv('PG_USER'), password=Sys.getenv('PG_PASS'))
# prod_result <- dbGetQuery(con_prod, 'SELECT COUNT(*) n FROM data')
# dbDisconnect(con_prod)

cat('Same DBI code works for both!')

Quick Check

What is the recommended way to provide database credentials in an R script?

Recap: PostgreSQL and MySQL Connections

Key takeaways for production database connections:

PostgreSQL driver: RPostgres::Postgres() with host, port, dbname, user, password args
MySQL/MariaDB driver: RMariaDB::MariaDB() with similar arguments
Always use Sys.getenv('VAR') for credentials — never hardcode
dbIsValid(con) checks if connection is still alive
pool package manages connection pools for high-traffic apps
sqlInterpolate() prevents SQL injection with user-provided values
SSH tunnel: forward a local port to access private databases
All DBI functions work identically across backends

library(DBI)
library(RSQLite)

# Production-ready connection pattern (SQLite for demo)
open_connection <- function() {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  if (!dbIsValid(con)) stop('Failed to connect')
  cat('Connected successfully\n')
  con
}

run_query <- function(con, sql) {
  if (!dbIsValid(con)) stop('Connection lost')
  dbGetQuery(con, sql)
}

con <- open_connection()
dbWriteTable(con, 't', data.frame(x=1:3, y=4:6))
print(run_query(con, 'SELECT * FROM t'))
dbDisconnect(con)

Frequently asked questions

Is the “Connecting to PostgreSQL and MySQL” lesson free?

Yes — the full text of “Connecting to PostgreSQL and MySQL” 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 “Connecting to PostgreSQL and MySQL”?

Use RPostgres and RMySQL drivers to connect to server-based databases. 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 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Connecting to PostgreSQL and MySQL” 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