0Pricing
R Academy · Lección

Conexión a PostgreSQL y MySQL

Use los controladores RPostgres y RMySQL para conectarse a bases de datos basadas en servidor.

Conexión a PostgreSQL y MySQL es una lección gratuita de R Academy en CoddyKit. Esta es la lección 2 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de R Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de R Academy incluye 4 lecciones en total.

Conexiones a bases de datos de producción

Aunque RSQLite es excelente para el desarrollo y las pruebas, los datos de producción suelen residir en PostgreSQL, MySQL, SQL Server o bases de datos en la nube. La interfaz DBI permanece igual; solo cambian el paquete controlador y los argumentos de conexión. Esta es la principal fortaleza de 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

Conectarse a PostgreSQL

RPostgres::Postgres() es el controlador para PostgreSQL. Proporcione los parámetros de conexión: host, port (5432 de forma predeterminada), dbname, user y password. Nunca incluya las credenciales directamente en el código; utilice variables de entorno.

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

Usar variables de entorno para las credenciales

Incluir contraseñas directamente en el código supone un riesgo de seguridad. Guarde las credenciales como variables de entorno y léalas con Sys.getenv(). En producción, establézcalas en .Renviron, en archivos .env (que no deben incluirse en git) o mediante sistemas de gestión de secretos.

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

Conectarse a MySQL / MariaDB

Las conexiones a MySQL utilizan RMariaDB::MariaDB() (el controlador moderno de MySQL) o RMySQL::MySQL(). Los argumentos de conexión son similares a los de PostgreSQL. Se recomienda MariaDB como controlador tanto para servidores MySQL como 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.')

Tiempo de espera y reconexión

Los scripts de larga duración pueden sufrir tiempos de espera de conexión. Compruebe si la conexión sigue siendo válida con dbIsValid(con). En scripts de ETL, considere la posibilidad de reconectarse al principio de cada lote de procesamiento en lugar de mantener una conexión abierta durante horas.

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

Concepto de agrupación de conexiones

Abrir una conexión nueva a la base de datos para cada consulta resulta costoso. La agrupación de conexiones mantiene un conjunto de conexiones abiertas y las reutiliza. Es fundamental para las API web y las aplicaciones Shiny, donde llegan muchas solicitudes por segundo.

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

Consultar con parámetros SQL (de forma segura)

Nunca concatene datos introducidos por usuarios en cadenas SQL (existe riesgo de inyección SQL). Utilice consultas parametrizadas con sqlInterpolate(con, sql, .dots=list(...)) o glue_sql() del paquete glue para insertar valores de forma segura.

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)

Túnel SSH para bases de datos remotas

Muchas bases de datos de producción no son accesibles directamente desde Internet. Un patrón habitual consiste en crear un túnel SSH: redirija un puerto local al puerto de la base de datos remota mediante SSH y, después, conecte 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.')

Leer tablas grandes por bloques

Cuando una tabla de base de datos contiene millones de filas, cargarlo todo de una vez puede agotar la memoria. Utilice LIMIT/OFFSET o una recuperación basada en cursores para procesar la tabla por bloques. Combine este enfoque con dbFetch() para transmitir los resultados.

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() — Metadatos de conexión

dbGetInfo(con) devuelve metadatos de la conexión: versión del servidor, nombre de la base de datos, usuario, etc. Resulta útil para el registro, el diagnóstico y la verificación de que se conectó a la instancia correcta de la base de datos en scripts que se ejecutan en varios entornos.

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)

Comparación del uso de SQLite y PostgreSQL

Elija SQLite para la creación de prototipos, los datos integrados y las pruebas; elija PostgreSQL para producción, el acceso multiusuario y las funciones avanzadas de SQL. El código de DBI es prácticamente idéntico; la única diferencia está en el controlador y los parámetros de conexión.

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

Comprobación rápida

¿Cuál es la forma recomendada de proporcionar las credenciales de la base de datos en un script de R?

Resumen: conexiones a PostgreSQL y MySQL

Aspectos clave de las conexiones a bases de datos en producción:

  • Controlador de PostgreSQL: RPostgres::Postgres() con los argumentos host, port, dbname, user y password
  • Controlador de MySQL/MariaDB: RMariaDB::MariaDB() con argumentos similares
  • Use siempre Sys.getenv('VAR') para las credenciales; nunca las incluya directamente en el código
  • dbIsValid(con) comprueba si la conexión sigue activa
  • El paquete pool administra grupos de conexiones para aplicaciones con mucho tráfico
  • sqlInterpolate() evita la inyección de SQL con valores proporcionados por los usuarios
  • Túnel SSH: redirige un puerto local para acceder a bases de datos privadas
  • Todas las funciones de DBI funcionan de forma idéntica con los distintos 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)

Preguntas frecuentes

¿La lección «Conexión a PostgreSQL y MySQL» es gratis?

Sí — el texto completo de «Conexión a PostgreSQL y MySQL» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de R Academy, actualiza a CoddyKit PRO. El curso de R Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Conexión a PostgreSQL y MySQL»?

Use los controladores RPostgres y RMySQL para conectarse a bases de datos basadas en servidor. Practicas R Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar R Academy?

No se requiere experiencia previa. R Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 2 de 4.

¿Cuánto tiempo toma la lección «Conexión a PostgreSQL y MySQL»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de R Academy?

Sí. Cada lección de R Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Fundamentos de DBI y RSQLite
  2. Conexión a PostgreSQL y MySQL
  3. dbplyr: SQL mediante la sintaxis de dplyr
  4. Consultas parametrizadas y transacciones
← Volver a R Academy