R Academy · Lesson

Connecting to PostgreSQL and MySQL

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

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

All lessons in this course

← Back to R Academy