Get started with rpgconn

The Problem

PostgreSQL connection management in R is unnecessarily painful:

  • Scattered credentials: Connection strings copy-pasted across scripts
  • Security risks: Passwords committed to version control
  • Format inconsistency: Different formats between local dev and cloud deployments
  • Cryptic errors: “Could not connect” messages that don’t explain what’s wrong
  • Boilerplate fatigue: Repetitive DBI::dbConnect() calls with 5+ arguments

The rpgconn Solution

rpgconn eliminates this friction through three key principles:

1. Fail-Fast Validation

Connection strings are validated before attempting to connect. Instead of:

# ❌ Cryptic error deep in DBI
Error: could not connect to server

You get:

# ✅ Clear, actionable error
Error: Connection string must include a '/{database}' segment,
       e.g. 'postgresql://user@host:5432/dbname'

2. Portable Format

Uses the PostgreSQL URI standard that works everywhere:

  • Python (psycopg2, sqlalchemy)
  • Node.js (pg, sequelize)
  • Go (pgx, database/sql)
  • Command-line tools (psql, pg_dump)
  • Cloud providers (DigitalOcean, AWS RDS, Heroku)

One connection string, any environment.

3. Secure by Default

Configuration files live in user-specific directories (~/.config/rpgconn on Linux/Mac, %APPDATA%/rpgconn on Windows), never in your project repository.

init_yamls()      # Creates config in safe location
edit_config()     # Opens editor - NO manual file hunting
# ✅ Never accidentally commit secrets

Design Philosophy

Single Responsibility

rpgconn does one thing well: connect to PostgreSQL databases. It doesn’t:

  • Manage connection pools (use pool)
  • Query databases (use DBI or dbplyr)
  • Migrate schemas (use dbx or custom scripts)

Zero Dependencies Beyond Essentials

Only depends on: - DBI and RPostgres (required for Postgres anyway) - yaml, fs, stringr, usethis (ubiquitous, stable packages)

No heavy frameworks. No version conflicts.

Progressive Enhancement

Simple use cases stay simple:

# Beginner: Just works
Sys.setenv(RPG_CONN_STRING = "postgresql://user:pass@host/db")
cn <- dbc()

Complex scenarios are supported without cluttering the simple path:

# Advanced: Custom config, SSL, connection pooling options
cn <- dbc(
  cfg = "prod",
  cfg_path = "config/prod-db.yml",
  opt_path = "config/prod-opts.yml"
)

Architecture

Connection String Parsing

The package supports three PostgreSQL connection string formats:

  1. URI Format (recommended)

    postgresql://user:pass@host:5432/db?sslmode=require

  2. Keyword/Value (semicolon)

    user=alice;password=secret;host=localhost;port=5432;dbname=mydb

  3. Keyword/Value (whitespace) - libpq standard

    host=localhost user=alice dbname=mydb port=5432

Validation-First Approach

Old approach (DBI direct):

Parse → Connect → (Fail deep in driver)

rpgconn approach:

Validate → Parse → Connect
    ↓
 (Fail fast with clear message)

Validation catches: - Missing database name - Empty user/host - Invalid port numbers - Whitespace (copy/paste errors) - Wrong URI scheme (mysql://, http://)

URL Encoding Support

Handles special characters in passwords and parameters:

# Password contains @ and :
# URL-encoded: p@ss:word → p%40ss%3Aword
"postgresql://user:p%40ss%3Aword@host/db"

IPv6 hosts:

"postgresql://user@[2001:db8::1]:5432/db"

Comparison: rpgconn vs DBI::dbConnect()

Without rpgconn

library(DBI)
library(RPostgres)

# Scattered connection details
host <- "db.example.com"
port <- 5432
dbname <- "mydb"
user <- Sys.getenv("DB_USER")
password <- Sys.getenv("DB_PASSWORD")

# Verbose boilerplate (every script)
cn <- dbConnect(
  Postgres(),
  host = host,
  port = port,
  dbname = dbname,
  user = user,
  password = password,
  sslmode = "require",
  connect_timeout = 10
)

# ... later ...
dbDisconnect(cn)

With rpgconn

library(rpgconn)

# One environment variable (set once, use everywhere)
Sys.setenv(RPG_CONN_STRING =
  "postgresql://user:[email protected]:5432/mydb?sslmode=require")

# Single function call
cn <- dbc()

# ... later ...
dbd(cn)

Lines of code: 16 → 4 (75% reduction) Failure points: Many → Few (validated upfront) Secrets in code: Yes → No (environment variable)

When NOT to Use rpgconn

Use DBI::dbConnect() directly when:

  • Non-PostgreSQL databases (rpgconn only supports PostgreSQL)
  • Exotic connection parameters not in libpq standard
  • Educational contexts where understanding raw DBI is the goal

rpgconn is a convenience wrapper, not a replacement for understanding DBI.

Next Steps

  • Quick Start: See vignette("quickstart") for 5-minute setup
  • Advanced Workflow: See vignette("advanced-workflow") for:
    • SSL configuration
    • IPv6 hosts
    • Environment-specific configs
    • Troubleshooting guide

Learn More