R Academy · Lezione

Connessione a PostgreSQL e MySQL

Usi i driver RPostgres e RMySQL per collegarsi a database basati su server.

Connessione a PostgreSQL e MySQL è una lezione R Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento R Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso R Academy include 4 lezioni in totale.

Connessioni ai database di produzione

Sebbene RSQLite sia ottimo per lo sviluppo e i test, i dati di produzione risiedono in PostgreSQL, MySQL, SQL Server o database cloud. L'interfaccia DBI rimane la stessa: cambiano solo il pacchetto driver e gli argomenti della connessione. Questo è il principale punto di forza di DBI.

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

Connessione a PostgreSQL

RPostgres::Postgres() è il driver per PostgreSQL. Passi i parametri di connessione: host, port (predefinito 5432), dbname, user e password. Non inserisca mai le credenziali direttamente nel codice: utilizzi invece le variabili d'ambiente.

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

Utilizzo delle variabili d'ambiente per le credenziali

Inserire le password direttamente nel codice è un rischio per la sicurezza. Memorizzi le credenziali come variabili d'ambiente e le legga con Sys.getenv(). In produzione, le imposti in .Renviron, nei file .env (che non devono essere inclusi in git) o tramite sistemi di gestione dei segreti.

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

Connessione a MySQL / MariaDB

Le connessioni MySQL utilizzano RMariaDB::MariaDB() (il moderno driver MySQL) o RMySQL::MySQL(). Gli argomenti della connessione sono simili a quelli di PostgreSQL. MariaDB è il driver consigliato sia per i server MySQL sia per quelli MariaDB.

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

Timeout e riconnessione

Gli script di lunga durata possono incorrere in timeout della connessione. Verifichi che la connessione sia ancora valida con dbIsValid(con). Per gli script ETL, consideri la possibilità di riconnettersi all'inizio di ogni batch di elaborazione invece di mantenere una singola connessione aperta per ore.

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

Concetto di connection pooling

Aprire una nuova connessione al database per ogni query è costoso. Il connection pooling mantiene un insieme di connessioni aperte e le riutilizza. È fondamentale per le API web e le app Shiny, in cui arrivano molte richieste al secondo.

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

Query con parametri SQL (sicure)

Non concateni mai l'input dell'utente nelle stringhe SQL (rischio di SQL injection). Utilizzi query parametrizzate con sqlInterpolate(con, sql, .dots=list(...)) oppure glue_sql() del pacchetto glue per inserire i valori in modo sicuro.

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)

Tunnel SSH per database remoti

Molti database di produzione non sono direttamente accessibili da Internet. Un approccio comune consiste nel creare un tunnel SSH: inoltri una porta locale alla porta del database remoto tramite SSH, quindi connetta R a 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.')

Lettura di grandi tabelle a blocchi

Quando una tabella del database contiene milioni di righe, caricarla tutta in una volta può esaurire la memoria. Utilizzi LIMIT/OFFSET o il recupero basato su cursore per elaborare la tabella a blocchi. Combini questa tecnica con dbFetch() per trasmettere i risultati in streaming.

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() — Metadati della connessione

dbGetInfo(con) restituisce i metadati della connessione: versione del server, nome del database, utente ecc. È utile per la registrazione, la diagnostica e la verifica di aver effettuato la connessione all'istanza corretta del database negli script eseguiti su più ambienti.

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)

Confronto dell'utilizzo di SQLite e PostgreSQL

Scelga SQLite per la prototipazione, i dati incorporati e i test; scelga PostgreSQL per la produzione, l'accesso multiutente e le funzionalità SQL avanzate. Il codice DBI è quasi identico: l'unica differenza riguarda il driver e i parametri di connessione.

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

Verifica rapida

Qual è il modo consigliato per fornire le credenziali del database in uno script R?

Riepilogo: connessioni a PostgreSQL e MySQL

Punti chiave per le connessioni ai database in produzione:

Driver PostgreSQL: RPostgres::Postgres() con gli argomenti host, port, dbname, user, password
Driver MySQL/MariaDB: RMariaDB::MariaDB() con argomenti simili
Utilizzi sempre Sys.getenv('VAR') per le credenziali: non le inserisca mai direttamente nel codice
dbIsValid(con) verifica se la connessione è ancora attiva
Il pacchetto pool gestisce i pool di connessioni per le app ad alto traffico
sqlInterpolate() previene l'injection SQL con i valori forniti dall'utente
Tunnel SSH: inoltra una porta locale per accedere ai database privati
Tutte le funzioni DBI funzionano nello stesso modo su tutti i backend

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)

Domande Frequenti

La lezione «Connessione a PostgreSQL e MySQL» è gratuita?

Sì — il testo completo di «Connessione a PostgreSQL e MySQL» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso R Academy, passa a CoddyKit PRO. Il corso R Academy include 4 lezioni in totale.

Cosa imparerò in «Connessione a PostgreSQL e MySQL»?

Usi i driver RPostgres e RMySQL per collegarsi a database basati su server. Eserciti R Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare R Academy?

Non è richiesta alcuna esperienza precedente. R Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Connessione a PostgreSQL e MySQL»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione R Academy?

Sì. Ogni lezione R Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a R Academy