R Academy · Pelajaran

Menghubungkan ke PostgreSQL dan MySQL

Gunakan driver RPostgres dan RMySQL untuk terhubung ke database berbasis server.

Menghubungkan ke PostgreSQL dan MySQL adalah pelajaran R Academy gratis di CoddyKit. Ini adalah pelajaran 2 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar R Academy, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus R Academy mencakup 4 pelajaran total.

Koneksi Basis Data Produksi

RSQLite cocok untuk pengembangan dan pengujian, tetapi data produksi berada di PostgreSQL, MySQL, SQL Server, atau basis data cloud. Antarmuka DBI tetap sama — hanya paket pengandar dan argumen koneksi yang berubah. Inilah keunggulan utama 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

Menghubungkan ke PostgreSQL

RPostgres::Postgres() adalah pengandar untuk PostgreSQL. Berikan parameter koneksi: host, port (bawaan 5432), dbname, user, dan password. Jangan pernah menulis kredensial secara langsung di dalam kode — gunakan variabel lingkungan.

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

Menggunakan Variabel Lingkungan untuk Kredensial

Menulis kata sandi secara langsung di dalam kode merupakan risiko keamanan. Simpan kredensial sebagai variabel lingkungan dan baca dengan Sys.getenv(). Dalam produksi, tetapkan variabel ini di .Renviron, file .env (yang tidak disertakan dalam git), atau melalui sistem pengelolaan rahasia.

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

Menghubungkan ke MySQL / MariaDB

Koneksi MySQL menggunakan RMariaDB::MariaDB() (pengandar MySQL modern) atau RMySQL::MySQL(). Argumen koneksinya mirip dengan PostgreSQL. MariaDB adalah pengandar yang direkomendasikan untuk server MySQL maupun 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.')

Batas Waktu dan Penyambungan Kembali

Skrip yang berjalan lama dapat mengalami batas waktu koneksi. Periksa apakah koneksi masih valid dengan dbIsValid(con). Untuk skrip ETL, pertimbangkan untuk menyambungkan kembali pada awal setiap kelompok pemrosesan, bukan mempertahankan satu koneksi terbuka selama berjam-jam.

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

Konsep Pengumpulan Koneksi

Membuka koneksi basis data baru untuk setiap kueri membutuhkan banyak sumber daya. Pengumpulan koneksi mempertahankan sekumpulan koneksi terbuka dan menggunakannya kembali. Hal ini sangat penting untuk API web dan aplikasi Shiny yang menerima banyak permintaan setiap detik.

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

Membuat Kueri dengan Parameter SQL (Aman)

Jangan pernah menggabungkan input pengguna secara langsung ke dalam string SQL (berisiko terkena injeksi SQL). Gunakan kueri berparameter dengan sqlInterpolate(con, sql, .dots=list(...)) atau glue_sql() dari paket glue untuk menyisipkan nilai dengan aman.

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)

Penerowongan SSH untuk Basis Data Jarak Jauh

Banyak basis data produksi tidak dapat diakses langsung dari internet. Pola yang umum digunakan adalah penerowongan SSH: teruskan port lokal ke port basis data jarak jauh melalui SSH, lalu hubungkan R ke 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.')

Membaca Tabel Besar dalam Potongan

Saat tabel basis data memiliki jutaan baris, memuat semuanya sekaligus dapat menghabiskan memori. Gunakan LIMIT/OFFSET atau pengambilan berbasis kursor untuk memproses tabel dalam beberapa potongan. Gabungkan dengan dbFetch() untuk mengalirkan hasil.

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() — Metadata Koneksi

dbGetInfo(con) mengembalikan metadata koneksi: versi server, nama basis data, pengguna, dan sebagainya. Hal ini berguna untuk pencatatan, diagnosis, serta memverifikasi bahwa Anda terhubung ke instans basis data yang benar dalam skrip yang dijalankan pada beberapa lingkungan.

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)

Membandingkan Penggunaan SQLite vs PostgreSQL

Pilih SQLite untuk pembuatan prototipe, data tertanam, dan pengujian; pilih PostgreSQL untuk produksi, akses multi-pengguna, dan fitur SQL tingkat lanjut. Kode DBI hampir identik — satu-satunya perbedaan adalah pengandar dan parameter koneksi.

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

Pemeriksaan Singkat

Apa cara yang disarankan untuk menyediakan kredensial basis data dalam skrip R?

Ringkasan: Koneksi PostgreSQL dan MySQL

Hal-hal penting untuk koneksi basis data produksi:

Pengandar PostgreSQL: RPostgres::Postgres() dengan argumen host, port, dbname, user, password
Pengandar MySQL/MariaDB: RMariaDB::MariaDB() dengan argumen serupa
Selalu gunakan Sys.getenv('VAR') untuk kredensial — jangan pernah menuliskannya langsung dalam kode
dbIsValid(con) memeriksa apakah koneksi masih aktif
Paket pool mengelola kumpulan koneksi untuk aplikasi dengan lalu lintas tinggi
sqlInterpolate() mencegah injeksi SQL dengan nilai yang diberikan pengguna
Terowongan SSH: teruskan port lokal untuk mengakses basis data pribadi
Semua fungsi DBI bekerja secara identik di berbagai 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)

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Menghubungkan ke PostgreSQL dan MySQL” gratis?

Ya — teks lengkap “Menghubungkan ke PostgreSQL dan MySQL” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus R Academy, upgrade ke CoddyKit PRO. Kursus R Academy mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Menghubungkan ke PostgreSQL dan MySQL”?

Gunakan driver RPostgres dan RMySQL untuk terhubung ke database berbasis server. Kamu berlatih R Academy dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai R Academy?

Tidak diperlukan pengalaman sebelumnya. R Academy di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 2 dari 4.

Berapa lama pelajaran “Menghubungkan ke PostgreSQL dan MySQL” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran R Academy ini?

Ya. Setiap pelajaran R Academy menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

← Kembali ke R Academy